Adding Parameters to a Report
 
In the article titled Reporting Services - A First Look, we saw the basic architecture of SQL Server Reporting Services and saw how a simple report can be authored with ease. Having created a basic report, it is often a common need to have parameters to a report so that a user can look at only the required information. For example, if you are showing a list of authors to the user, it would be useful to have a parameter that allows the user to select a state and then see the list of authors belonging to that state. By not having this parameter, a user would be forced to look at all the authors although his interest would have been only on authors from a particular state. SQL Server Reporting Services has excellent support for parameters. The following are the features available:
  1. Defining simple parameters that a user can enter.
  2. Defining a predefined list for a parameter. The list can be fetched from a table or several tables.
  3. Define dependent parameters. For example, the value for one parameter will depend on the selection made in another parameter.
  4. Defining multi-select parameters. There is no out-of-the box support for this feature, but you can simulate it using Reporting Services.

In this article we will see how to use some of the above features. Note that we will not go into step-wise discussions on how to use the report designer. Most of the basics are covered in my earlier article Reporting Services - A First Look. I however will provide detailed information wherever required.

Simple Parameters using Lists

Let us first see how to define and use simple parameters that derive their values from a list. The list in our case, can be a table. As an example to demonstrate this, we will define a report that has a parameter called state. This parameter will show all the state values from the authors table. The user can then select a particular state and then view all the authors belonging to that state. The following are the steps that you will need to follow to define this report.

  1. Create a new Business Intelligence Project in Visual Studio 2003.
  2. Create a shared data source to the pubs database.
  3. In the query box, type the query with the parameter in place SELECT * FROM authors WHERE state = @state. Note the way in which we are specifying the parameter in the query. Reporting Services will parse this query and then create the appropriate parameter definitions.
  4. Choose the Tabular report type.
  5. Choose au_id, au_lname, au_fname, phone, city, state and zip as the details for the report.
  6. Choose the Compact table style.
  7. Give the name of the report as AuthorsByState.

At this stage, if you finish the report wizard, Visual Studio will crank for a while and then create the basic report definition along with the appropriate parameters. Let us now see how the parameters have been defined.

  • Switch to the Data tab.
  • In the DataSet combo, choose <New Dataset...>.
  • Specify a name, connection and query for the dataset as shown in the following figure. Note that we want the list of states arranged in the ascending order.

  • Choose Report > Report Parameters to open the report parameters dialog box. Note that Reporting Services has automatically added the state parameter to the list.
  • Specify a prompt for the parameter, and make the dataset that we created above (States) as the data source for the parameter. The label field is the value that will be shown in the list and value will be the value that will be returned after selection. For our case, we need both to be the same. The following image shows the selections and entries that we have made for the parameters.

  • Choose the layout tab and modify the title of the report and change the font / color for the tables as shown in the following figure.

  • Click on the Save All button and then choose the Preview tab. You can now choose various state values and click on the View Report button to see the authors from that state as shown in the following figure.
 
That was easy! Note that SQL Reporting Services automatically adds a toolbar kind of interface to the top of the report where the parameters are shown. Also, a button is added for you to click and view the report. As you add more parameters, this piece of the interface will change appropriately.
 
One small glitch that you will notice in this report is that a user can view only authors from a particular state. What if you want to view all the authors across all the states?? For this, we possibly will want a value in the parameter list called All, which when choosen will show all the authors. There is no out-of-the box support for such a feature and you basically will need to modify your query a bit. Here is how we can do it.
  1. Switch to the Data tab and choose the States dataset in the Dataset combo.
  2. Change the query for the data set as follows: SELECT DISTINCT state FROM authors UNION SELECT 'All' ORDER BY state. Essentially what we have done is to add a constant to the query result called All using the UNION operator. If you now switch from the Data tab, Reporting Services will return a query parse error. Choose Yes to close the dialog box. The reason you get this error is because of the inability of the graphical designer to represent a UNION query.
  3. We will now have to account for this new parameter in our data set that gets all the authors (called pubs in my example). You will need to change the query to the following: SELECT * FROM authors WHERE (state = @state) OR (@state = 'All'). Note the addition of the extra clause in the WHERE OR (@state = 'All'). This ensures that when the user chooses the All option, this query will return true for all rows and will thus show the entire author list.

If you now switch to the Preview tab, you can see the parameter list having an All option. Choose this option and click on the View Report button to view all authors. The other options continue to perform as they are, which is to show authors belonging to a particular state.

Using Dependent Parameters

In the earlier example, we used a single parameter that fetched its values from a table. Typically, reports can have more than one parameter that are dependent on each other. For example, you can have a parameter that shows the list of departments and then on selection show a list of employees belonging to that department. SQL Reporting Services allows you create such dependent parameters with ease. In this example, we are going to use the AdeventureWorks database that is installed along with Reporting Services to demonstrate the dependent parameters concept. The AdventureWorks database is a vast improvement over the pubs database that was originally shipped with SQL Server. AdeventureWorks contains tables with company data pertaining to sales, human resources, manufacturing and production, and purchasing and is ideal for writing many kinds of reports.

For this example, we will show the user a list of product categories and for each category, the user will then be able to select a sub-category. The report then, will display all the products belonging to the sub-category along with relevant information. This report will also include an image of the product as an illustration of how easy it is to integrate with image columns in the database. To create this report, follow these steps:

  1. Create a new report (named ProductList.rdl) from the existing project by right-clicking on the Reports node and choosing "Add > Add New Item..."
  2. Create a new shared data source to the AdventureWorks2000 database by right-clicking on the Shared Data Sources node and choosing "Add New Data Source"
  3. We will need to create the following datasets. To create each of these datasets, use the Data tab and click on the "New Dataset..." option and enter the relevant parameters.
    1. One for the list of product categories (called Categories)
    2. One for the list of product sub categories (called SubCategories)
    3. One for the product list (called Products)
  4. For the Categories data set, we will use the following query: SELECT productCategoryID, Name FROM productCategory
  5. For the SubCategories, we will use the following query: SELECT productSubCategoryID, productCategoryID, Name FROM productSubCategory WHERE productCategoryID = @productCategoryID. Note that we use a parameter for the category ID to get only the required sub categories.

At this stage, we can test these the parameters in isloation to ensure that the required information is being populated. Choose Report > Report Parameters. The dialog already shows our productCategoryID parameter. We will just need to modify the prompt and data source information as shown in the following figure:

 

Since we want all products belonging to a sub-category (based on the data model of AdventureWorks), we will add one more parameter while we are in this dialog. Click on the Add button and add a new parameter called productSubCategoryID. Modify the prompt and data source information as shown in the following figure.
 
 
At this stage, we can test our report by clicking on the Preview tab. You should now see two parameter combo boxes shown. The first combo will list the various categories and if you select one of them, the next combo-box will refresh to show the various sub-categories. We will now create the dataset for the product list and then design the actual report. Switch to the Data tab and create a new data set. The new data set will be named Products and will use the following query.

SELECT
	product.productID, product.Name, product.productNumber, product.color,
	product.standardCost, product.listPrice,
	productPhoto.thumbNailPhoto
FROM
	product product
	LEFT OUTER JOIN productPhoto productPhoto ON product.productPhotoID = productPhoto.productPhotoID
WHERE
	product.productSubCategoryID = @productSubCategoryID
 
Note that we use the new parameter that we created above to ensure that all products for a sub-category are fetched. We also do an OUTER JOIN with the photos table, since not all products have their images captured. We will now design the actual report. Switch to the Layout tab and then drag and drop the following fields to the layout canvas as shown. Before dragging the fields, you will need to drop a table onto the canvas. The table will help us to display a tabular report.
 
 
Note that the Thumbnail field requires special treatment. Instead of a text field, you will need to drop an image control into the canvas and then choose Database as the source. Then, point to the thumbNail field in the Products data set. After this, you can add whatever embellishments you want to make the report look nice. Once done, click on the Preview tab and then choose a category and sub-category. For example, if you choose Bike and Touring Bike, the following report will be shown:
 
 
We have shown only a portion of the report above, but note the ease with which you have generated this report! You can deploy the project to the report server so that you can access these reports from a browser.
 
That brings us to the end of this article. In this article we saw the use of simple parameters and dependent parameters. There is more that you can do with parameters, but we will defer that to a later article. The concepts covered here are sufficient to get you exploring with the basic feature set of parameters. Have fun!!

Home