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
Select '(0,5)' as StatusKey, 'B – Live Orders' as statusText
Select '(2)' as StatusKey, 'C – Cancelled Orders' as statusText
Select '(6)' as StatusKey, 'D – Complete Orders' as statusText
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!