SSRS multiple values parameter not working
I have a report showing Sales Order information, basically a Header section and a Sub-report showing the lines. The report has a parameter defined for the order number. Everything works great when showing a single order.
However, if select more than 1 order in the parameter drop-down, then the report is blank.
I'm using 3 data sets: Header Detail, Line Detail & a 3rd for the Order Number query.
The parameter value(s) for the header and lines datasets are set to use the values returned from this 3rd dataset.
This piece is working as I do get a correct list of Order Numbers to select in the drop-down, it just won't work if I choose more than 1 (or All).
What am I missing??
However, if select more than 1 order in the parameter drop-down, then the report is blank.
I'm using 3 data sets: Header Detail, Line Detail & a 3rd for the Order Number query.
The parameter value(s) for the header and lines datasets are set to use the values returned from this 3rd dataset.
This piece is working as I do get a correct list of Order Numbers to select in the drop-down, it just won't work if I choose more than 1 (or All).
What am I missing??
ASKER
Thanks,
I'm not using a stored Procedure but my report is setup just like this in that it uses the same method of joining the parameter, i.e.
(=Join(Parameters!Departme
but it just doesn't show multiple records :(
I'm not using a stored Procedure but my report is setup just like this in that it uses the same method of joining the parameter, i.e.
(=Join(Parameters!Departme
but it just doesn't show multiple records :(
Hi Brian,
Are you using a split function to separate your parameter in separate values? When using a multi value parameter SSRS passes this as a string value to the dataset. You have to split them before they can be used in an "IN" statement.
Are you using a split function to separate your parameter in separate values? When using a multi value parameter SSRS passes this as a string value to the dataset. You have to split them before they can be used in an "IN" statement.
If you're not using a stored proc then you don't need to use Join() to pass the multi-valued parameter into the dataset. Just specify your parameter as if it were single-valued: Parameters!OrderNumber.Val
And use IN() in the WHERE clause: WHERE YourOrderNumberField IN ( @OrderNumber )
And use IN() in the WHERE clause: WHERE YourOrderNumberField IN ( @OrderNumber )
Koen: the join/split is only needed when using stored procs. If the query code resides in the dataset then somehow SSRS knows how to deal with the array of values.
Ah, ok. Thanks for that piece of info Valentino. I've only ever worked with procedures and assumed it was always required.
My apologies Brian.
My apologies Brian.
ASKER
Thanks guys. I'll check this out later today and let you know how I get on
ASKER
OK, getting closer :)
The report now runs and show information, however it shows ALL the lines for ALL the orders against just the one order header.
Here's what I have:
Pretty sure I'm missing something that splits the report by header record?
The report now runs and show information, however it shows ALL the lines for ALL the orders against just the one order header.
Here's what I have:
A Parameter called OrderNumber
An OrderNumber dataset (a query which returns a list of order numbers) that the above parameter uses
A header dataset - which uses the above parameter (=Parameters!OrderNumber.V
A Lines Dataset - which again uses the above parameter (=Parameters!OrderNumber.V
Pretty sure I'm missing something that splits the report by header record?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
Good luck.
Jim