How to pass a list of parameters from ASP.net into a SQL Server Report using SSRS2005, using a single parameter
I thought I would share this with you all in case you may have had issues (like I did) with passing multiple values in a single parameter from your web page to a report.
NOTE: This is different from passing multiple parameters, each with its own value into a report. The later, there are plenty of examples on the web.
This is very usefull when you need to basically pass into your report's SQL command a list of values for your report's SQL command to use using a "special" function, and where you do not know the number of times the values may be required, as the user can choose anything from one value to 'n' values (but we will hit a limit, as I'll explain later). It's also useful for generating Excel row-by-row extracts from your website - say for Pivot table handling or charting later on.
Unfortunately using IN() on its own tricks a lot of people and they cannot figure out why it does not work. That's because if you define your report in SSRS to expect a parameter straight into the IN() function, the system literally places the value as a parameter in the function and tries to compare what is essentialy a parameter "data type" with your column's data type and you will get errors.
If you report has SQL similar to this ...
SELECT t.Col1, t.Col2, etc
FROM myTable t
WHERE t.myColumn IN (@myListOfValues)
where @myListOfValues is something like "'value1','value2','value3',..." it "may" work but I found passing such a string from ASP.net into SSRS did not work and there are technical issues with string handling from the ASP.net side plus a limit depending your system and browser.
To get around possible issues, create a function in your SQL Server database to receive a string of values delimited by a comma and allow the function to turn your list into a table. That way the table can easily be linked using SQL and passed as a sort of "parameter feeder" into your report's SQL or dataset.
So without babbling on too much lets start with code and an example:
Firstly lets create a special utility function that converts a list of values into a table, and by the way this function can be used within your projects to do exactly that - split strings delimited by something into a table for anything else.
Open SQL Server and create a new function using your normal right-click NEW function command. Something like this ...
CREATE FUNCTION [dbo].[fnMakeTableFromList](@List VARCHAR(MAX),@Delimiter VARCHAR(255))
RETURNS TABLE
AS
RETURN (SELECT Item = CONVERT(VARCHAR, Item)
FROM (SELECT Item = x.i.value('(./text())[1]', 'varchar(max)') FROM (SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter,'</i><i>')+ '</i>').query('.')) AS a
CROSS APPLY [XML].nodes('i') AS x(i)) AS y
WHERE Item IS NOT NULL);
NOTE: the delimiter does not have to be a single character! Again useful for delimiting using keywords, etc.
Note the XML logic and conversion in the function? That is because ASP.Net is going to literally pass some HTML into SQL Server and we're going to use it to strip off the data we need into a table.
Run the function with some values to test:
SELECT * FROM dbo.fnMakeTableFromList ('a,b,c,d', ',');
You should see 4 rows of data returned ...
a
b
c
d
That is the results in a table.
Now use this function in your SQL Reporting Services report:
Here is my report as an example:
SELECT DISTINCT s.StudentID
FROM tblStudents s
LEFT OUTER JOIN dbo.fnMakeTableFromList(@StudentList,',') AS list ON list.Item = s.StudentID
WHERE (@StudentList IS NULL
OR @StudentList='')
AND (l.Item IS NULL
OR l.Item = s.StudentID)
Note my example also caters for reporting every student ID if there was no value passed at all. So report every student found in tblStudents or report those based on the list of student IDs given, delimited by a comma. When you run this directly in SSRS, you'll be asked for a parameter @StudentList. In there type what ever values you need separated by a comma, and you should only get those student IDs. If not, make sure the report works "stand alone" first before going over to the ASP side.
Once you are happy your report works, and the function in SQL Server works, then we are ready to code the ASP.net side of things:
In your ASPX code behind page (C#) we need to control what the list is and how to pass it over to SSRS. Because we are dealing with a LIST<> here, I am only going to illustrate the way to do using a LIST<> to mimic an array. As you know C# does not have array terminology like you have with VB. So in your ASP.net page paste this code in your PageLoad event ...
protected void Page_Load(object sender, EventArgs e)
{
//get parameters from the URL list
string strStudentList = Request.QueryString["StudentList"];
//create a List of parameters and pass it over to the report
List<ReportParameter> paramList = new List<ReportParameter>();
paramList.Add(new ReportParameter("StudentList", strStudentList));
//run the report
ReportViewer1.ServerReport.SetParameters(paramList);
}
Of course some objects in here have to be defined in your ASPx page.
For example I use a master page and as you can see, I did all of this to create a mailing list for printing on special sticky label paper.
<%@ Page Language="C#" MasterPageFile="~/rptsStudentAdministration/StudentAdminReports.master" AutoEventWireup="true" CodeFile="rptStudentLabels.aspx.cs" Inherits="rptsStudentAdministration_rptStudentLabels" Title="Student Mailing Labels" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<rsweb:reportviewer id="ReportViewer1" runat="server" font-names="Verdana" font-size="8pt"
height="800px" processingmode="Remote" width="900px">
<ServerReport ReportServerUrl="<%$ AppSettings:ReportServerURL %>" ReportPath="/rptsStudentAdministration/rptStudentLabels"></ServerReport>
</rsweb:reportviewer>
</asp:Content>
Make sure you are using these as well in your .cs file:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Reporting.WebForms;
And that's it folks!
CONCLUSION:
If you need to generate a report in SQL Server Reporting services that relies on users selecting none, one or many values to control the logic in the report, then think of passing them all as a single parameter and using a function to turn your values into a table for ease of SQL management. Once you have the SQL working, you should be able to generate the report easily in design mode and using the above ASPx logic, be able to pass all the values delimited by a comma into into your report. An added bonus is to HIDE the parameter in SSRS and that way the user does not have to see what values they chose, and you control the entire report being generated programmatically.
Join me on Google+
NOTE: This is different from passing multiple parameters, each with its own value into a report. The later, there are plenty of examples on the web.
This is very usefull when you need to basically pass into your report's SQL command a list of values for your report's SQL command to use using a "special" function, and where you do not know the number of times the values may be required, as the user can choose anything from one value to 'n' values (but we will hit a limit, as I'll explain later). It's also useful for generating Excel row-by-row extracts from your website - say for Pivot table handling or charting later on.
Unfortunately using IN() on its own tricks a lot of people and they cannot figure out why it does not work. That's because if you define your report in SSRS to expect a parameter straight into the IN() function, the system literally places the value as a parameter in the function and tries to compare what is essentialy a parameter "data type" with your column's data type and you will get errors.
If you report has SQL similar to this ...
SELECT t.Col1, t.Col2, etc
FROM myTable t
WHERE t.myColumn IN (@myListOfValues)
where @myListOfValues is something like "'value1','value2','value3',..." it "may" work but I found passing such a string from ASP.net into SSRS did not work and there are technical issues with string handling from the ASP.net side plus a limit depending your system and browser.
To get around possible issues, create a function in your SQL Server database to receive a string of values delimited by a comma and allow the function to turn your list into a table. That way the table can easily be linked using SQL and passed as a sort of "parameter feeder" into your report's SQL or dataset.
So without babbling on too much lets start with code and an example:
Firstly lets create a special utility function that converts a list of values into a table, and by the way this function can be used within your projects to do exactly that - split strings delimited by something into a table for anything else.
Open SQL Server and create a new function using your normal right-click NEW function command. Something like this ...
CREATE FUNCTION [dbo].[fnMakeTableFromList](@List VARCHAR(MAX),@Delimiter VARCHAR(255))
RETURNS TABLE
AS
RETURN (SELECT Item = CONVERT(VARCHAR, Item)
FROM (SELECT Item = x.i.value('(./text())[1]', 'varchar(max)') FROM (SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter,'</i><i>')+ '</i>').query('.')) AS a
CROSS APPLY [XML].nodes('i') AS x(i)) AS y
WHERE Item IS NOT NULL);
NOTE: the delimiter does not have to be a single character! Again useful for delimiting using keywords, etc.
Note the XML logic and conversion in the function? That is because ASP.Net is going to literally pass some HTML into SQL Server and we're going to use it to strip off the data we need into a table.
Run the function with some values to test:
SELECT * FROM dbo.fnMakeTableFromList ('a,b,c,d', ',');
You should see 4 rows of data returned ...
a
b
c
d
That is the results in a table.
Now use this function in your SQL Reporting Services report:
Here is my report as an example:
SELECT DISTINCT s.StudentID
FROM tblStudents s
LEFT OUTER JOIN dbo.fnMakeTableFromList(@StudentList,',') AS list ON list.Item = s.StudentID
WHERE (@StudentList IS NULL
OR @StudentList='')
AND (l.Item IS NULL
OR l.Item = s.StudentID)
Note my example also caters for reporting every student ID if there was no value passed at all. So report every student found in tblStudents or report those based on the list of student IDs given, delimited by a comma. When you run this directly in SSRS, you'll be asked for a parameter @StudentList. In there type what ever values you need separated by a comma, and you should only get those student IDs. If not, make sure the report works "stand alone" first before going over to the ASP side.
Once you are happy your report works, and the function in SQL Server works, then we are ready to code the ASP.net side of things:
In your ASPX code behind page (C#) we need to control what the list is and how to pass it over to SSRS. Because we are dealing with a LIST<> here, I am only going to illustrate the way to do using a LIST<> to mimic an array. As you know C# does not have array terminology like you have with VB. So in your ASP.net page paste this code in your PageLoad event ...
protected void Page_Load(object sender, EventArgs e)
{
//get parameters from the URL list
string strStudentList = Request.QueryString["StudentList"];
//create a List of parameters and pass it over to the report
List<ReportParameter> paramList = new List<ReportParameter>();
paramList.Add(new ReportParameter("StudentList", strStudentList));
//run the report
ReportViewer1.ServerReport.SetParameters(paramList);
}
Of course some objects in here have to be defined in your ASPx page.
For example I use a master page and as you can see, I did all of this to create a mailing list for printing on special sticky label paper.
<%@ Page Language="C#" MasterPageFile="~/rptsStudentAdministration/StudentAdminReports.master" AutoEventWireup="true" CodeFile="rptStudentLabels.aspx.cs" Inherits="rptsStudentAdministration_rptStudentLabels" Title="Student Mailing Labels" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<rsweb:reportviewer id="ReportViewer1" runat="server" font-names="Verdana" font-size="8pt"
height="800px" processingmode="Remote" width="900px">
<ServerReport ReportServerUrl="<%$ AppSettings:ReportServerURL %>" ReportPath="/rptsStudentAdministration/rptStudentLabels"></ServerReport>
</rsweb:reportviewer>
</asp:Content>
Make sure you are using these as well in your .cs file:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Reporting.WebForms;
And that's it folks!
CONCLUSION:
If you need to generate a report in SQL Server Reporting services that relies on users selecting none, one or many values to control the logic in the report, then think of passing them all as a single parameter and using a function to turn your values into a table for ease of SQL management. Once you have the SQL working, you should be able to generate the report easily in design mode and using the above ASPx logic, be able to pass all the values delimited by a comma into into your report. An added bonus is to HIDE the parameter in SSRS and that way the user does not have to see what values they chose, and you control the entire report being generated programmatically.
Join me on Google+
Comments
Post a Comment