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.


more ▼

asked Jun 03, 2010 at 04:02 AM in Default

avatar image

33 3 2 5

(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, 2010 at 04:47 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

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, 2010 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, 2010 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, 2010 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, 2011 at 01:25 AM

avatar image


(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jun 03, 2010 at 04:02 AM

Seen: 7924 times

Last Updated: Jun 03, 2010 at 04:02 AM

Copyright 2018 Redgate Software. Privacy Policy