If you want to build your own UI front end to SQL Server Reporting Services, one web method you’ll need to become familiar with is GetReportParameters. GetReportParameters will return metadata about the parameters in a report, including names, types, and valid values.
This article assumes you are familiar with adding parameters to a report in SQL Server reporting services, if not, you can visit the resource links at the bottom of the article. This article also assumes you have established a web reference to the reporting service, for more details on this step see our previous article.
When you create a report parameter in Reporting Services, you can assign the parameter a name, a data type, and specify if null is a legal value for the parameter. A report designer can also assign valid values for a report parameter, which can be done using a query. For example, a report from the example Northwind database in SQL Server might have a parameter for product category. To ensure the user selects a product category which exists in the database, a designer can create a new dataset for the report and use the following query:
SELECT CategoryID, CategoryName FROM Categories.
If we were building our own front end tool for report viewing, we would want to show the valid values for a parameter to the user. Using the GetReportParameters as shown below will give us the information we need.
ReportingService rService = new ReportingService();
rService.Credentials =
System.Net.CredentialCache.DefaultCredentials;
string historyID = null;
bool forRendering = true;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters;
parameters = rService.GetReportParameters
(
"/Northwind Reports/SampleReport",
historyID,
forRendering,
values,
credentials
);
foreach(ValidValue v in parameters[0].ValidValues)
{
Response.Write(v.Label + ": " + v.Value);
Response.Write("<br>");
}
The important point in the above code is to pass a value of ‘true’ in the forRendering method parameter. If the valid parameter values are based on a query, setting this parameter to true will tell Reporting Services to execute the query and return the results in the ValidValues array of ValidValue objects. The code above should print the following to the web form:
Beverages: 1
Condiments: 2
Confections: 3
Dairy Products: 4
Grains/Cereals: 5
Meat/Poultry: 6
Produce: 7
Seafood: 8
It is also possible to set cascading parameters. For instance, we might setup a product parameter for our report which has a dependency on the category selected:
SELECT ProductID, ProductName FROM Products WHERE CategoryID = @CateogryID
This restricts the products a user can select to the products in the selected category. What happens when we call GetReportParameters to look at the valid values?
The code presented earlier will still work for the first parameter (CategoryID). We can still see valid values for parameter[0]. The second parameter (ProductID), parameter[1], however, will have a null reference for the ValidValues property. The State property for parameter[1] will be set to ParameterStateEnum.HasOutstandingDependencies, and the Dependencies property (a string array) will have a length of 1, with Dependencies[0] set to “CategoryID”.
To resolve the dependency and find valid values for our ProductID parameter, we will need to select a CategoryID and call GetReportParameters again. Let’s append the following code to our earlier sample.
values = new ParameterValue[1];
values[0] = new ParameterValue();
values[0].Name = parameters[0].Name;
values[0].Value = "8";
parameters = rService.GetReportParameters
(
"/Northwind Reports/Report2",
historyID,
forRendering,
values,
credentials
);
foreach(ValidValue v in parameters[1].ValidValues)
{
Response.Write(v.Label + ": " + v.Value);
Response.Write("<br>");
}
In the code above we create an array to hold a ParameterValue object for CategoryID. We set the CategoryID to 8, which is the seafood category. Now the list of valid values for ProductID will appear in ValidValues.
Ikura: 10
Konbu: 13
Carnarvon Tigers: 18
Nord-Ost Matjeshering: 30
Inlagd Sill: 36
Gravad lax: 37
Boston Crab Meat: 40
Jack's New England Clam Chowder: 41
Rogede sild: 45
Spegesild: 46
Escargots de Bourgogne: 58
Röd Kaviar: 73
If you are creating your own reporting UI, you can check the Dependencies and State properties to determine when multiple invocations of GetReportParameters will be required. One additional tip: when a parameter has a dependency, it will always appear at a higher index in the ReportParameter array.
by K. Scott Allen
Resources
MSDN: Using Parameters in a Report
Read more Reporting Services articles on OdeToCode.