x

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

more ▼

asked Jun 03 '10 at 04:02 AM in Default

mcoombes gravatar image

mcoombes
33 2 2 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Jun 03 '10 at 04:47 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

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()'.
Jun 08 '10 at 08:51 PM mcoombes
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.
Jun 09 '10 at 03:00 AM Pavel Pawlowski

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.
Jun 28 '10 at 05:12 PM mcoombes
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Sep 01 '11 at 01:25 AM

Sharke gravatar image

Sharke
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1936
x536
x72

asked: Jun 03 '10 at 04:02 AM

Seen: 5751 times

Last Updated: Jun 03 '10 at 04:02 AM