Multi Value Parameters

I have an issue using a Muiti Value Parameter. Upto a point it works
quite well.
The parameter is based on a field within the report & so when I preview
I get a list of values, and can select all, or some records. Thats the
bit that works.
However when I run the report it returns data for for 1 value only. I
have made sure that the filter on my report uses 'in' instead of '='.
One thing I would rater do thatn use the report filter is pass the
parameter directly to my SQL statement, does anyone know how this can
be done?
Thanks
PaddySo you're using a filter on the dataset (retrieve all rows first). This is
not very efficient, especially if you're dealing with tables with millions
of rows...
To do it on the data source side:
If your data source is SQL Server 2005, you can simply write your TSQL as
"...and myTable.myField IN (@.myParameter)"
If your data source is SQL Server 2000 or something else you could do either
of the following:
stored proc, pass the parameter to the stored proc as
=join(Parameters!myParameter.Value, ",")
Then write some code that splits your values out by "," and writes them to
an in-memory join table. Typically in the past, we've used a UDF to return
a table as well.
For a "straight query" (IOW TSQL, PLSQL, etc), as long as the data source
supports the IN clause, you could write dynamic SQL in the form:
="select ..... where myTable.myValue IN (" &
join(Parameters!myParameter.Value, ",") & ")"
If you go the dynamic sql path, write the query out first normally so you
don't have to fill in the field list, then change it to dynamic.
Clear as mud?
-Tim
"Paddy" <paddymullaney@.btopenworld.com> wrote in message
news:1152180850.976447.110760@.75g2000cwc.googlegroups.com...
>I have an issue using a Muiti Value Parameter. Upto a point it works
> quite well.
> The parameter is based on a field within the report & so when I preview
> I get a list of values, and can select all, or some records. Thats the
> bit that works.
> However when I run the report it returns data for for 1 value only. I
> have made sure that the filter on my report uses 'in' instead of '='.
> One thing I would rater do thatn use the report filter is pass the
> parameter directly to my SQL statement, does anyone know how this can
> be done?
> Thanks
> Paddy
>

Multi value parameter in db2 query

hi,

i'm trying to perform a query against a db2 database like this:

SELECT ... FROM ... WHERE (field IN (?))

Then i let reporting services pass the parameter to the report. When i try to preview the report, i get the following error:

An error occurred during local report processing,
An error has occured during report processing,
Cannot add multi value query parameter '?' for data set ... because it is not supported by the data extension

But when i type the query like this

SELECT ... FROM ... WHERE (field IN ('value1','value2'))

it executes flawlessly.

I am using the IBM ole db driver for db2 if that matters

Can anyone help me?

IBM's Ole DB driver only supports parameterized queries with single valued parameters. See e.g. this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=81223&SiteID=1

SSRS 2005 contains specific functionality to perform query rewrite for multi-valued query parameters in the cases of SQL Server, Oracle 9 or later, and SSAS / XML/A-based data sources.

For all other cases you would need a custom data extension implementation to provide the query rewrite functionality.

-- Robert

Multi Parameter with Boolean Field

Hello,

Can anyone say me how i can make a Report with Parameter Boolean field,and as Default Value true and False. ( Both ).

With Multivalue and in the Query = Field in (@.BoolPara) have i a Error in the Query.

Thanks

Hi,

It is not possible to have a Both clause by default. Although, here is a workarround:

In case of using a Bool report parameter, I use an integer report parameter and set its available values as follow:
Both -1
True 1
False 0

Defaultvalues: 1

In the were clause set the format as below:
SELECT *
FROM SampleBoolTable
WHERE (BoolCol = ABS(@.BoolParameter) OR @.BoolParameter = -1)

So when you now render your report, you will see the options True, False and Both. When selecting Both, a dummy filter is used so everything is shown.

NOTE: The ABS function is necessary to avoid the comparison between -1 and a BIT column.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

multi field select using IN...

here is what i am wanting to do but is it possible to use multi fields with an IN statement?

SELECT *
FROM usmastf
WHERE usm_book, usm_acct IN
(SELECT usac_book, usac_acct
FROM uscommf
WHERE usac_code = 'O/W')i finally found it|||Not with an IN function. Use a standard JOIN instead:
SELECT usmastf.*
FROM usmastf
inner join uscommf
on usmastf.usm_book = uscommf.usac_book
and usmastf.usm_acct = uscommf.usac_acct
WHERE uscommf.usac_code = 'O/W'|||What does the pipe sign actually represent in the following

select * from usmastf
where ((usm_book | usm_acct) in (select usac_book | usac_acct from uscommf where usac_code = 'O/W'))|||The pipe character represents a boolean "OR" evaluation, but I don't think it is syntactically correct in your statement.|||in particular, the vertical bar means *bitwise* or, not logical or (same as in C/C++).

logical or is just the word "or" in sql (this would be || in C/C++).|||oh you silly microsoft peoples

hicpics, i think you're thinking of two vertical pipes, which is the sql standard for concatenation

... where usm_book||usm_acct in (select usac_book||usac_acct from ...|||oh you silly microsoft peoplesI can't imagine why people would assume that the post was talking about Microsoft SQL in this forum! ;)

You have a good point, and I thought of that myself, but we need to assume that the poster really is working with Microsoft SQL when they post a question here. If they are using a different SQL or SQL-like language then one of the moderators ought to move the post for them.

-PatP|||I too thought of concatenation, as I am unfortunately mired in the Oracle world at the moment. But the poster asked about the meaning of a single pipe, not double-pipes.|||actually the original poster never said "single pipe" -- and he was clearly using it as a concatenation operator in post #4

and being mired in oracle would only be an interesting coincidence, the double pipes are standard sql -- are you suggesting that oracle peoples have a better understanding of standard sql than microsoft peoples?

:)|||can one of you educate me? I have never heard of | being used as a concatenation operator. Do you mean string concatenation? Every reference in BOL I can find about | says it's bitwise or.

the | means bitwise or in most languages I am familiar with (except befunge, but I can't say I'm that familiar with it :))

and this don't work at all: select 'asdf' || 'qwerty'

:)|||Double-pipes concatenation is not allowed syntax for SQL Server.

You are trying to say "If A OR B is in C OR D", but this is a logical comparison and not a bitwise "OR" which is what the pipe character stands for. So your syntax will not work.

Did you try the code I posted?|||Who uses a single pipe as a conact operator?

MySQL?

I doubt it...

Oracle and DB2 are ||

I always thought + was a silly conact operator for chars|||I always thought + was a silly conact operator for chars

really? makes perfect sense to me.

||, on the other hand, makes no sense to me as concat. || has always meant logical or to me. But I was raised on C, not sql.

If you were implementing a string class in C++ and overloaded || to mean string concat, I'd have to, well, I won't say it. :)

EDIT: actually, you should never overload || for any reason, but that's a separate issue.|||really? makes perfect sense to me. oh?

what is the result of SELECT 2 + '2' then?

:)|||IMO, that *should* return a cast error. Only in nasty languages such as vb or javascript would I expect such things to be permitted.

it appears the ms (or maybe sybase) devs thought otherwise.

EDIT: SELECT 2 + '0.2' -- now there's a result I like (well, sort of)

Multi column

Hi,
I dont know how to word this but I'll try. I have a report that shows two
field in the output, i.e city and count. When the report is run it displays
about 44 rows on one page, but since there are only two fields the rest of
the page is left blank on the right hand side which can easily accomodate two
more column of same fields of city and count. For example it prints row 1
thru 44 on one page and 45 thru ... I would like it to print the 45 thru ...
on the right side of the page which is blank. I'm pretty sure this can be
done.
Please help!while in Layout tab in Report Designer, go to Report then Report Properties.
Click the Layout tab and play around with the number in Columns box.
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:D65B284A-2C06-4D3E-BC1A-BBF03DD51679@.microsoft.com...
> Hi,
> I dont know how to word this but I'll try. I have a report that shows two
> field in the output, i.e city and count. When the report is run it
> displays
> about 44 rows on one page, but since there are only two fields the rest of
> the page is left blank on the right hand side which can easily accomodate
> two
> more column of same fields of city and count. For example it prints row 1
> thru 44 on one page and 45 thru ... I would like it to print the 45 thru
> ...
> on the right side of the page which is blank. I'm pretty sure this can be
> done.
> Please help!|||I changed the column from 1 to 2 but it still wont move over to the second
column. Any idea?
Thanks
"ME" wrote:
> while in Layout tab in Report Designer, go to Report then Report Properties.
> Click the Layout tab and play around with the number in Columns box.
>
> "Shan" <Shan@.discussions.microsoft.com> wrote in message
> news:D65B284A-2C06-4D3E-BC1A-BBF03DD51679@.microsoft.com...
> > Hi,
> >
> > I dont know how to word this but I'll try. I have a report that shows two
> > field in the output, i.e city and count. When the report is run it
> > displays
> > about 44 rows on one page, but since there are only two fields the rest of
> > the page is left blank on the right hand side which can easily accomodate
> > two
> > more column of same fields of city and count. For example it prints row 1
> > thru 44 on one page and 45 thru ... I would like it to print the 45 thru
> > ...
> > on the right side of the page which is blank. I'm pretty sure this can be
> > done.
> >
> > Please help!
>
>

MSSQL2005 Analysis Service Distinct Count

hi,

i am currently trying to build a distinct count on my cube (mssql2005 analysis services).

But after i added the discount count on the field i want to and start the processing, the following errors appear.

- Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.

- Errors in the OLAP storage engine: An error occurred while processing the 'FACT VIEW STATISTIC' partition of the 'FACT VIEW STATISTIC 1' measure group for the 'Accident Statistic' cube from the OLAP_PROJECT database.

the count measure works fine.

will appreciate any help on this distinct count problem.

thanks in advance.

-

HY

Try and see what kind of query Analysis Services sends to the relational database during processing of distinct count measure.

You will see it sending a query containing ORDER BY clause asking relational database to sort results accourding to the distinct count measure.

It it possible the view you defined your partitions on, brings data sorted differently?
Any new data becomes avaliable during processing of the partition?

The error indicates Analysis Server detecting inconsistencies in sorting of data coming from relational database.

See if you might need to define collation correctly for your sort.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks edward.

as i drew data from Oracle view, the collation must be specify correctly.

check the Oracle collation and discovered it is binary.

changed the distinct count to binary collation and it works.

thanks.

-

HY

|||

Could any one explain about the error and solution elaborately. I am not sure how this can be rectified. Appreciate any help.

Thanks

|||

Hi there,

We encountered the same issue:

And changing the collation to binary allowed us to process the cube ...

But I still don't understand why I got the error with the collation set to SQL_Latin1_CI_AS

By the way, I also encountered a difference of 1 by browsing the cube and when I count on the table:

Browse on the measure with the distinct count = 800

Result of "select count (distinct (sessionid)) from dbo.facttransaction" = 799

(NB: some sessionid are NULL)

Does the cube take in consideration the NULL values ?

Thanks

|||

OK found why I got the diff :

Analysis Services handle a NULL value like a 0 value in a DISTINCT COUNT measure

MSSQL2005 Analysis Service Distinct Count

hi,

i am currently trying to build a distinct count on my cube (mssql2005 analysis services).

But after i added the discount count on the field i want to and start the processing, the following errors appear.

- Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.

- Errors in the OLAP storage engine: An error occurred while processing the 'FACT VIEW STATISTIC' partition of the 'FACT VIEW STATISTIC 1' measure group for the 'Accident Statistic' cube from the OLAP_PROJECT database.

the count measure works fine.

will appreciate any help on this distinct count problem.

thanks in advance.

-

HY

Try and see what kind of query Analysis Services sends to the relational database during processing of distinct count measure.

You will see it sending a query containing ORDER BY clause asking relational database to sort results accourding to the distinct count measure.

It it possible the view you defined your partitions on, brings data sorted differently?
Any new data becomes avaliable during processing of the partition?

The error indicates Analysis Server detecting inconsistencies in sorting of data coming from relational database.

See if you might need to define collation correctly for your sort.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks edward.

as i drew data from Oracle view, the collation must be specify correctly.

check the Oracle collation and discovered it is binary.

changed the distinct count to binary collation and it works.

thanks.

-

HY

|||

Could any one explain about the error and solution elaborately. I am not sure how this can be rectified. Appreciate any help.

Thanks

|||

Hi there,

We encountered the same issue:

And changing the collation to binary allowed us to process the cube ...

But I still don't understand why I got the error with the collation set to SQL_Latin1_CI_AS

By the way, I also encountered a difference of 1 by browsing the cube and when I count on the table:

Browse on the measure with the distinct count = 800

Result of "select count (distinct (sessionid)) from dbo.facttransaction" = 799

(NB: some sessionid are NULL)

Does the cube take in consideration the NULL values ?

Thanks

|||

OK found why I got the diff :

Analysis Services handle a NULL value like a 0 value in a DISTINCT COUNT measure

MSSQL2005 Analysis Service Distinct Count

hi,

i am currently trying to build a distinct count on my cube (mssql2005 analysis services).

But after i added the discount count on the field i want to and start the processing, the following errors appear.

- Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.

- Errors in the OLAP storage engine: An error occurred while processing the 'FACT VIEW STATISTIC' partition of the 'FACT VIEW STATISTIC 1' measure group for the 'Accident Statistic' cube from the OLAP_PROJECT database.

the count measure works fine.

will appreciate any help on this distinct count problem.

thanks in advance.

-

HY

Try and see what kind of query Analysis Services sends to the relational database during processing of distinct count measure.

You will see it sending a query containing ORDER BY clause asking relational database to sort results accourding to the distinct count measure.

It it possible the view you defined your partitions on, brings data sorted differently?
Any new data becomes avaliable during processing of the partition?

The error indicates Analysis Server detecting inconsistencies in sorting of data coming from relational database.

See if you might need to define collation correctly for your sort.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks edward.

as i drew data from Oracle view, the collation must be specify correctly.

check the Oracle collation and discovered it is binary.

changed the distinct count to binary collation and it works.

thanks.

-

HY

|||

Could any one explain about the error and solution elaborately. I am not sure how this can be rectified. Appreciate any help.

Thanks

|||

Hi there,

We encountered the same issue:

And changing the collation to binary allowed us to process the cube ...

But I still don't understand why I got the error with the collation set to SQL_Latin1_CI_AS

By the way, I also encountered a difference of 1 by browsing the cube and when I count on the table:

Browse on the measure with the distinct count = 800

Result of "select count (distinct (sessionid)) from dbo.facttransaction" = 799

(NB: some sessionid are NULL)

Does the cube take in consideration the NULL values ?

Thanks

|||

OK found why I got the diff :

Analysis Services handle a NULL value like a 0 value in a DISTINCT COUNT measure

mssql wont find NULL values in datetime field?

Hi

I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:

SELECT * FROM ADMIN WHERE date_edited = NULL

I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:

thanksignore me, found the answer

SELECT * FROM ADMIN WHERE date_edited is NULL|||Consider yourself ignored. ;)|||I'm ignoring him right now. Intensely.|||please dun treat newbies like that, we all started off as a newbie right?

maybe we shall have a newbie section for them to post elementary questions...

but seriously, this NULL question does look dumb......TS shall try harder before posting it......|||personally I still hate nulls and three part logic. It causes problems for so many programmers. I slap as many not null constraints in my software as I can. Of course I spend so much time fighting fires these days, I only get to do reactive development or redevelopment.|||ROTFL ... look at Sean's title in the above post ... i almost missed that.

Another contradictory Seanism :p|||Declare @.ZenosParadox varchar(50)
Set @.ZenosParadox = 'This value is null'|||I learned in math class that zeno's paradox was that you could never reach any destination because you first had to travel 1/2 the distance, then 1/2 again, then again, so you'd never reach it.

poor zeno didn't know an infinite series can sum to a finite number.|||http://en.wikipedia.org/wiki/Liar_paradox ?

:)|||NULL value is good, I dun need to be bothered about putting constraint while showing them on a report hehe...

MSSQL Question

Is there a way to find out the last time a row was updated without creating a field to contain the timestamp?
Any help is greatly appreciated!
Thanks,
JeffAnyway you have to have field where it is possible to save last time of update. It could be datetime, varchar, etc. You can use trigger for saving data in this field or use sp during update.|||If this is a requirement you might look into a third party tool called Lumigent Log Explorer or Lumigent Entegra. www.lumigent.com

MSSQL over IIS 5

I'm having troble with MSSQL 2k and IIS 5
a textarea field (text), can't post more then 150 chars, If I try, the text
recorded are something like "üh3", and no sight of my typed text!!
What's hapen? this does not occurs over Sambar or Xitami.The SQL Server will only store what you ask it to store. Suggest running a
SQL Profiler trace to see exactly what is being passed to the SQL Server.
--
HTH
Ryan Waight, MCDBA, MCSE
"Feijó" <feijo@.x.suply.com> wrote in message
news:%23Vh52VmjDHA.1708@.TK2MSFTNGP12.phx.gbl...
> I'm having troble with MSSQL 2k and IIS 5
> a textarea field (text), can't post more then 150 chars, If I try, the
text
> recorded are something like "üh3", and no sight of my typed text!!
> What's hapen? this does not occurs over Sambar or Xitami.
>|||Ok, I will use the profiler to get some hint
the version is 7.0, any bugs on it?
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> escreveu na mensagem
news:u08KWamjDHA.2676@.TK2MSFTNGP11.phx.gbl...
> The SQL Server will only store what you ask it to store. Suggest running a
> SQL Profiler trace to see exactly what is being passed to the SQL Server.
> --
> HTH
> Ryan Waight, MCDBA, MCSE|||There were but they've been rectified with Service Packs. Ensure you are
running the latest Service Pack. For SQL 7 it's 7.00.1063, SELECT @.@.VERSION
will show which version you are running.
--
HTH
Ryan Waight, MCDBA, MCSE
"Feijó" <feijo@.x.suply.com> wrote in message
news:OFX1alojDHA.1656@.tk2msftngp13.phx.gbl...
> Ok, I will use the profiler to get some hint
> the version is 7.0, any bugs on it?
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> escreveu na mensagem
> news:u08KWamjDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > The SQL Server will only store what you ask it to store. Suggest running
a
> > SQL Profiler trace to see exactly what is being passed to the SQL
Server.
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
>