question

mcoombes avatar image
mcoombes asked

How to pass a multi-value parameter value through to a drill down report?

Hi there,

I seem to have done something wrong with a report that I have in MS SQL 2005, using SSRS.

The master report is a matrix report that groups unfilled purchase orders by year and month on one axis, and by the purchaser on the other axis. It then does a count in each data 'segment'. There are no parameters.

All is well and good so far, and it produces the right results.

The drilldown report is a link of each segment that goes to a child report that shows the detail of all the purchase orders that occur in that time segment by that user. When the data is entered manually into this subreport it works fine.

The problem is that I can't set up the parameters from the master report to be passed through to the child report correctly. Initially the child report was based on Date and Purchaser, but I've since re-written it to be based on PurchaseOrderNumber using the multivalue variable @POList

What code should I use to pass the multiple values through to the child report?

Your help would be gratefully accepted.

Mikey

sql-server-2005ssrsparameters
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered

You simply pass the multivalue variable to the child report. Then depends what you need to do with the variable. If you need to pass the ID to a query, function or stored procedure, then you need to pass it as string of comma delimitted values.

This you can achieve this by using the Join function. If you need the reverse, you can use Split function.

3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mcoombes avatar image mcoombes commented ·
I've tried that a few different ways, without success. I'm mostly getting this error message. Any suggestions as to what I'm doing wrong would be really helpful [rsRuntimeErrorInExpression] The DrillthroughParameterValue expression for the textbox ‘textbox98.ActionInfo.Action’ contains an error: Overload resolution failed because no Public 'Join' can be called with these arguments: 'Public Shared Function Join(SourceArray As String(), [Delimiter As String = ]) As String': Argument matching parameter 'SourceArray' cannot convert from 'String' to 'String()'.
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Don't know your parameters, but from the message the parameter you are passing ans SourceArray in the Join function is not an array but simple string.
0 Likes 0 ·
mcoombes avatar image mcoombes commented ·
Hey Pavel, The parameter is just a bunch of numbers (Purchase order numbers) pulled from a SAGE table that I'm trying to collect and pass through to the drill-through report. These PO numbers are collected as the output of a SQL statement that is displayed as a Matrix report grouped by Month and Year on the top, and requisitioner on the side. The drill through is mean to collect the PO numbers for that month/year, and requisitioner, or for the totals, that month/year for all requisitioners, or all POs from that requisitioner - depending on the axis. I'm completely stuck and it's frustrating me.
0 Likes 0 ·
Sharke avatar image
Sharke answered
Please check how many values you have in your multi-parameter. You may have too many values. I think by default the maximum value count would be 20, if I remember correctly.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.