• Setting Up Parameters
• Parameter Properties
• Data-Driven Parameters
• Parameters and Data Sources
• Expressions with Parameters
• Dynamic SQL with Parameters
• Parameter Dependencies
• Example Using MultiSelect
Report-level parameters can serve a number of functions, including the following:
• Manipulating report data
• Connecting related reports together
• Varying report layout and presentation
Parameters are used to pass information to a report to influence report processing. For example, a parameter can serve as a condition in a WHERE
of a query that generates the report’s data set. Parameters are relatively easy to set up and are very flexible.
Report parameters can be presented to the user in four ways, as shown in Table 10.1.
Parameters can also be hidden from the user. Although this might sound strange at first, hidden parameters can be used to alter report processing based on the input from other parameters, or based on the result from a data set.
Table 10.1. Parameter Presentation Options
You can set up parameters from the Report Designer by selecting Report and then Report Parameters from the menu. The initial screen looks similar to Figure 10.1.
Figure 10.1. Report parameters.
Table 10.2 outlines several properties of parameters.
Table 10.2. Parameter Properties
Later on in the chapter, you will walk through creating report parameters. For now, all you need to know is that to create parameters, simply click the Add button in the Report Parameters dialog box and simply fill in the values.
Report parameter values can be driven from a list of valid values. This list can come from a data set. Under Available Values, simply add a list of values for the values to remain hard-coded in the RDL. If you want to drive them from a data source, select From Query. The screen should look similar to Figure 10.2.
Figure 10.2. Query parameter values.
The same holds true for the default values. They can be either data-driven through the same mechanism or hard-coded within the RDL.
The label is the value displayed to the user, whereas the value is the value passed back to the Report Server. For example, this is useful in a query parameter in which you want the user to select a familiar name from a drop-down list, yet the query expects the ID.
If a data source is set to prompt the user for an ID and password, two more parameters are created at runtime for the data source. Because they are created at runtime, they do not show up in the Report Parameters dialog box. The only way to find the names is by making a web service call to the Report Server. Report Server web service calls are explained in more detail in Chapter 25, “How to Use Reporting Services Web Services.” This does not preclude them from being passed in via a URL; however, this is not recommended because the user ID and password would be in clear text.
Up to this point, we have only talked about parameters within queries or queries being used as a source for data in parameters. Because the parameter collection is part of the Globals collection, you can use a parameter anywhere in the report.
For example, suppose you have a parameter called Emphasis
. Its values come from a predefined list of controls within the report. The intention is to change the font style from normal to bold if the value of the parameter is set to the name of the control.
The following expression on the bold property of a fictitious Location
control does exactly that:
=IIF(Parameters!Emaphasis.Value="Location", True, False)
Taking this a step further, you can change the visible property or any of the other properties in any report item. This allows you to use parameters in all sorts of ways not necessarily related to the data retrieved for a report.
So far, you have seen how parameters can be passed onto query values, but can you rearrange the whole query with parameters? The answer is yes.
Believe it or not, the query is just an expression like any other expression. By default, it is evaluated as a constant string, but with the use of parameters and custom code blocks, you can make the query behind the report dynamic. There is a catch to all that flexibility. You must return the same number of columns with the same names no matter what the query.
A good example of this is in the ORDER BY
or GROUP BY
clause within a query. Although any of the controls can sort or group the results of a data set by any column, they are limited in speed and capacity of the Report Server. By contrast, most databases are built for exactly this sort of thing, and with the effective use of indexes, a lot of spare CPU cycles can be recovered.
To make a dynamic query, open the Generic Query Designer and type in an expression that evaluates to a query. The following is an example that uses the Emphasis
parameter used previously:
=”select * from test_tb order by “ & Parameters!Emaphasis.Value
You could also declare this in a custom code block:
Function fnGetSql(Byval parameter as String) as String
Return “select * from test_tb order by “ &
Parameters!Emaphasis.Value
End Function
And call it like this:
=Code.fnGetSql(Parameters!Emaphasis.Value)
You can make parameter values dependent on other parameters. The trick to doing this is to derive the list of values from a data set. That data set must use the parent parameter to get its data.
For example, suppose you have two parameters A
and B
.Parameter B
»s values are queried from DataSetB
.DataSetB
needs a value from Parameter A
to process. From the user’s perspective, the second parameter (Parameter B
) does not display until a value is passed in from Parameter A
and DataSetB
gets processed.
Let’s use a parameter to modify the Top SalesPeople.rdl
report to include product categories and subcategories. First create a new report called “Top SalesPeople by Category.”
1. Add the following data sets using the AdventureWorks data source.
2. Name the data set TopEmployees.
3. Use the following query:
SELECT TOP 5 C.LastName, C.FirstName, E.EmployeeID, SUM(SOH.SubTotal) AS SaleAmount
FROM Sales.SalesPerson SP INNER JOIN
HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID INNER JOIN
Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN
Production.Product P ON SOD.ProductID = P.ProductID INNER JOIN
Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID INNER JOIN
Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE
PC.ProductCategoryID in (@ProductCategory) AND PS.ProductSubcategoryID in (@ProductSubcategory)
GROUP BY C.LastName, C.FirstName, E.EmployeeID, PC.ProductCategoryID, PS.ProductSubcategoryID
order by SUM(SOH.SubTotal) desc
4. Create two more data sets from the following:
• Data Set Name: ProductCategory
• Query:
SELECT DISTINCT ProductCategoryID, Name
FROM Production.ProductCategory
ORDER BY Name
• DataSet Name: SubCategory
SELECT ProductSubcategoryID, ProductCategoryID, Name
FROM Production.ProductSubcategory
WHERE ProductCategoryID in (@ProductCategory)
These two data sets will not return any data without specifying the values for the query parameters. This should not stop the field list from displaying the Datasets window.
5. Switch to Layout view.
6. Drag a table control from the toolbox over to the report’s Layout window.
7. From the Datasets tab in the toolbox, drag LastName, FirstName, and SaleAmount from the TopEmployees data set into the table columns.
8. Format SaleAmount with the format string c0.
9. Go to the Report menu and select Report Parameters.
10. There should be two parameters. Change the prompt on ProductCategory to Category and change the data type to Integer. Check the Multi-value option.
11. Under Available Values, check the From Query option.
12. Select Product Category for the data set.
13. Change the Value field to ProductCategoryID and the Label field to Name.
14. Select the ProductSubcategory parameter.
15. Change the data type to Integer.
16. Change the prompt to Subcategory, and check the Multi-value option.
17. Under Available Values, check the From Query option.
18. Select the Subcategory data set. Enter SubCategoryId for the value and Name for the label.
19. Click OK.
20. Preview the report by clicking the Preview tab of the Report Designer.
As you have probably noticed, the report-processing engine was clever enough to properly place comma-separated multiple parameter values in the WHERE
clause. If you are writing expressions, the multivalue parameters can be accessed like an array:
=Parameters!ProductCategory.Value(0)
Over the course of this chapter, you have learned what report parameters are and how they can be used. This includes their use in queries or in expressions that can be used throughout the report.
Parameters allow for dynamism by adding user input to the report-rendering process. A number of parameter options as well as a number of ways SSRS prompts the user are available using familiar controls. Parameters can be data-driven, or they can have a static list of valid values.
After being entered, a parameter can be used as a parameter for a data set’s query or anywhere within the report as a part of an expression.
SSRS 2005 adds multivalued parameters that can be used in theWHERE
clause of a data set query(WHERE PC.ProductCategoryID in (@ProductCategory)
) or within an expression (=Parameters!ProductCategory.Value(0))
.