Chapter 10. Report Parameters

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

Setting Up Parameters

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.

Parameter Properties

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.

Data-Driven Parameters

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.

Parameters and Data Sources

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.

Expressions with Parameters

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.

Dynamic SQL with Parameters

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)

Parameter Dependencies

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 DataSetBgets processed.

Example Using MultiValue^{NEW in 2005}

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

• Query:

        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.

=Parameters!ProductCategory.Value(0)

Summary

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