I was recently asked if there was a way to pass a list of values from one dataset as a parameter to a sub-report. I searched the internet and found a couple of abbreviated solutions, but most of the things I found said it wasn’t possible. I will say I did not put much effort into my search because I already had a potential solution in mind. In this article I will provide the steps required to accomplish this task.
Master Report
First create a report, let’s call it the master report, which will contain a table that list all of the master data. Ensure that the dataset includes a column that contains the set of data that will act as the filter for the sub-report. In my example I will use the following query:
SELECT
TOP(10)
soh.SalesOrderID,
soh.OrderDate,
soh.AccountNumber,
soh.TotalDue
FROM Sales.SalesOrderHeader soh
ORDER BY
soh.TotalDue DESC
After creating the data source and the data set on the report, you should add a Table item that will include all the columns returned from the query. The report will resemble the following:
Secondly, you will add a parameter to the report and set the following properties on the report:
· Allow multiple values
· Internal
Then set default values for the parameter. Choose the radio button labeled Get values from query from the Select from one of the following options on the Report Parameter Properties dialog box. Then select the data set that you created using the above query. Finally, select the field that will serve as the filter for the sub-report. In this example I choose the SalesOrderID. When complete your screen should look like this or something similar:
Next you will need to create a comma delimited list of values that will be passed from the Master Report to the sub-report. To do this you will be required to write a little VB code. To do this, click on the report and from the menu bar select Report->Report Properties. Select Code from the options in the left Pane. Paste the following code snippet into the textbox labeled Custom Code.
Public Function ListOfIDs(ByVal values As Object()) As String
Dim sb As New System.Text.StringBuilder()
For i As Integer = 0 To values.Length - 1
sb.Append(values(i))
sb.Append(",")
Next
Return sb.ToString()
End Function
The snippet loops over the list that is generated from the Multi-Select parameter that was created earlier and produces a comma delimited list of values that will be passed to the sub-report. Now that the Master Report is created, let’s create the sub-report.
Sub-Report
First, create a data source and a dataset. In this case, I will use a stored procedure as the query instead of inline SQL. This is because I will pass the comma delimited list directly to the stored procedure and use a function that will parse the values into a table that can be used in the query. The following stored procedure is a sample:
CREATE PROC Detailsrpt
@salesOrderList varchar(max)
AS
DECLARE @SalesOrders TABLE(SalesOrderID int)
INSERT INTO @SalesOrders
SELECT *FROM dbo.Split(@salesOrderList)
SELECT sod.SalesOrderDetailID, p.Name, sod.OrderQty, sod.UnitPrice, sod.LineTotal, sod.SalesOrderID
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
INNER JOIN @SalesOrders so
ON sod.SalesOrderID = so.SalesOrderID
The first thing to note in the stored procedure is the use of the dbo.Split function. You can search the internet and find a function to accomplish this. The one used in this example is included in the .zip file that will contain the sample project. Using the function the values are parsed and inserted into a variable table. Then in the last two lines of the final query the table variable is used in a join and acts as a filter for the result set. One thing I would like to mention is that you could use Table-Valued parameters and avoid using the function, but this will acquire some additional VB code. If you are interested in using this method watch my SQL Lunch, Reporting Services and Table-Valued parameters.Since a stored procedure is used as the source query in the data set for the sub-report, any parameters associated with that stored procedure is automatically added to the report. Accept all the defaults for the parameter. Do not make any changes to it. The last thing to do on this report is to add a Table item and add all the fields from the data set onto the item. Your sub-report will resemble the following screen shot: Putting it TogetherNow that all of the pieces are in place, it’s time to bring it all together. First, drag a sub-report onto the Master Report. Then right-click on the sub-report and select Subreport Properties. The following dialog box will appear:Choose the sub-report from the drop down list labeled Use this report as a subreport. Select Parameters from the left pane. Click the button labeled Add. In the drop down list that appears in the Name column select the parameter that that will accept the list. Then click the expression button. In the Expression screen paste the following expression in the textbox labeled Set expression for Value.
The first thing to note in the stored procedure is the use of the dbo.Split function. You can search the internet and find a function to accomplish this. The one used in this example is included in the .zip file that will contain the sample project. Using the function the values are parsed and inserted into a variable table. Then in the last two lines of the final query the table variable is used in a join and acts as a filter for the result set. One thing I would like to mention is that you could use Table-Valued parameters and avoid using the function, but this will acquire some additional VB code. If you are interested in using this method watch my SQL Lunch, Reporting Services and Table-Valued parameters.
Since a stored procedure is used as the source query in the data set for the sub-report, any parameters associated with that stored procedure is automatically added to the report. Accept all the defaults for the parameter. Do not make any changes to it. The last thing to do on this report is to add a Table item and add all the fields from the data set onto the item. Your sub-report will resemble the following screen shot:
Putting it Together
Now that all of the pieces are in place, it’s time to bring it all together. First, drag a sub-report onto the Master Report. Then right-click on the sub-report and select Subreport Properties. The following dialog box will appear:
Choose the sub-report from the drop down list labeled Use this report as a subreport. Select Parameters from the left pane. Click the button labeled Add. In the drop down list that appears in the Name column select the parameter that that will accept the list. Then click the expression button. In the Expression screen paste the following expression in the textbox labeled Set expression for Value.
=Code.ListOfIDs(Parameters!SalesOrderIDList.Value())
Finally, run the Master Report and the sub-report will automatically be filtered by the values that are contained within the master data result set, resulting in a new report similar to the following:Download the sample project here.As always, if you have a more elegant solution please share it with us and if you have any questions comments are concerns regarding this topic please feel free to email me at pleblanc@pragmaticworks.com.
Finally, run the Master Report and the sub-report will automatically be filtered by the values that are contained within the master data result set, resulting in a new report similar to the following:
Download the sample project here.
As always, if you have a more elegant solution please share it with us and if you have any questions comments are concerns regarding this topic please feel free to email me at pleblanc@pragmaticworks.com.
Good Day Patrick,
Nice article, Thank you.
Kind Regards
Reddy
Hi Patrick,
Thanks much for this article. When we reviewed this issue during the Virtual Mentoring session, I knew I was missing something - turns out it was the VB needed to define the top10 list for the subreport. I will use this in future reports of this nature. Thanks again for your help!
Take Care,
Sid