Posted by: Charles Maitland | Wednesday 26 April, 2006

Reporting Services 2000 Multiple Parameters

In the rush to talk about SQL Server 2005 it is important to remember that many many clients are still happily running SQL 2000 and the reportingServices for it.

One of our clients Dave McCluskey asked

I want to set up a parameter that returns a value for the order status of an order in one of our systems. I've set up a datasource like this:

Select '(0,2,5,6)' as StatusKey, 'A – All Orders' as statusText
UNION
Select '(0,5)' as StatusKey, 'B – Live Orders' as statusText
UNION
Select '(2)' as StatusKey, 'C – Cancelled Orders' as statusText
UNION
Select '(6)' as StatusKey, 'D – Complete Orders' as statusText
UNION
Select '(0)' as StatusKey, 'E – New Orders' as statusText
order by statustext

This returns me a list I can use in the parameter box. So far so good. This would return '(0,5)' when the user selects B

The problem I get is when I try to use the IN operator with a parameter in the main datasource.

WHERE ((dbo.view_MySystem_order_item.order_status = 0) OR
(dbo.view_Mysystem_order_item.order_status = 5)) AND (dbo.view_Mysystem_order_item.order_status IN @pOrderStatus))

This brings up the parameter window, but when I enter something I get:

Error har Occurred during Report processing .. Query Execution…Incorrect Syntax near @OrderStatus

I've tried every combination of brackets and so on I can think of

Is there a limitation here?

The solution was to take the SQL select out of being a native select and replace it with an evaluated expression.

This turned it into :

="Select ………. Where …… in (" & Parameters!StatusKey.Value & ")"

This solved the problem in this case and is always worth remembering in SQL 2000 especially when doing MDX parameters.

Of course a lot of this is made easier in SQL 2005 which has native multi select parameters but still needs care!

Advertisements

Responses

  1. It looks like you really had a nice time. orif broery marantika mp3 hips and curves kitsap county sheriff snood registration code crack maxoderm linzie dawn mckenzie boys in bikinis elaphant list risa kouda pictures of ballerinas pokemon lemons goglee pictures of anorexic people thinner nation acreage for sale in oklahoma disnychannel ratemypussy how to inject oxycontin hayden christensen naked kat osterman hilarious sayings bodyhose moe kirimura rose mcgowan nude sinful curves mastubation 88square bud not buddy lesson plans goku super saiyan 6 epicurous

  2. Follow your dreams, you can reach your goals. best exercises to trim the waistline good friday homilies toonporn marquetta jewel yuko hamano julianna rose mauriello ariel washing powder cloud coloring page ayaka fujisaki grand theater in conroe texas traci topps gallery skilfulhands timothy treadwell audio tape captain stabbins callkelly forum crutchfeild budwiser paper masha dimitri iwanowski eros houston escort how to draw dragonball z characters stampin up ideas nonsensical rhymes crazy angels bbs cosmonaut wings bessie nesmith eve sextape gearhead garage free full version download examples of pulleys analysis of shirley jackson the lottery

  3. Your site is amaizing. Can I share some resources with you? sako l579 knarls barkley yui seto van nuys fly away primerica scam chillygirls examples of bandwagon propaganda joanne latham nudies amittyville murders melanie coste hairy mariam nikki alexander aka hoopz dining booths breakfast nooks oily babes rdb songs amai mitsu cheap sectional couches brazilian bunda leather jumpsuits iflyswa pearl buck heroine libby hoeller palomar skipper 300 solitaire manufactured homes hornyspanish flies places to stay in ruidoso emmanuelle in space clips mixed face smother wrestling netarabic

  4. Your pictures are great. skin video guba tallest woman in holland the history of napkin folding herclips cathy does blacks

  5. I am using SQL Server 2000 Reporting Services. I have a parameter whos options as suppose to be populated by a query. EveryTime I run the code the query options are not in the parameters. IE I have a DataSetParameter that = the reportParameter. The report Parameter is populated using a query. But when the DataSetParameter is shown when the report runs, none of its optional values are in the dropdown.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: