.
OfficeWriter and Reporting Services allow you to use parameters to filter the data returned by your database query, and display a subset of the data in your report.
What is a parameter?
A parameter is a value that filters data. For example, you have a sales report that lists each sales group and each sales rep within that group. However, you only want to see one sales group at a time. If you use the sales group field as a parameter, when the report is executed, you will be prompted to select a group. The report will only display data for the group that you selected.
A single report can include multiple parameters.
To use parameters, you must first define them. Defining parameters differs depending on whether you set them up in OfficeWriter Designer or in Visual Studio. We will explore both so you can determine which is best for you.
Defining Parameters with OfficeWriter
OfficeWriter uses Microsoft Query to add parameters to a report. For more information see 8.1 - Defining Parameters with MS Query.
Defining Parameters in Visual Studio .NET
When you create data sources and queries in Visual Studio .NET, you can also define parameters.
When a report with a parameter is opened, OfficeWriter will prompt the user to enter a parameter value. If the parameter was defined with OfficeWriter and Microsoft Query, a list of values that may be entered will not be displayed. The user will have to know the value - for example, a sales rep ID number - to enter it. If the parameter was defined in Visual Studio .NET, OfficeWriter will display a list of valid values. These may be the actual values (for example, sales rep IDs) or meaningful labels (for example, sales rep names).
- Open Visual Studio and create an empty report project containing an empty report.
# Click the Data tab and set up a data source that points to the AdventureWorks database.
# Enter the query as shown below. It is the same query we used in the example in the previous section. This will be our main query.
!xlw_parameter7.jpg!
# We want the report user to select a sales rep name from a list rather than remember an id number. So, we must create a query that we will use to display the list of names. The query requires only two fields. The first is the name of the sales person. This field will be displayed to the user for selection before the report is rendered. The second field is the employee id, which will be passed to the WHERE clause of the main query to narrow down our results for one specific sales person. This query returns the names and employee ids of all AdventureWorks employees who are also sales people:
!xlw_parameter8.jpg!
# While in Layout view in Visual Studio, select Report/Report Parameters from the menu bar and click Add to create a new parameter. Fill in the dialog as shown:
!xlw_parameter9.jpg!
|| Properties ||Name
The name of the report parameter, which is used when accessing the parameter through a query or formula
Prompt
The text used to prompt the user to select a value for the parameter
Data Type
The data type of the field you will be using as a parameter (not the data type of the value displayed to the user)
Dataset
The dataset that will populate the list of possible parameter values
Value field
The field that will be used as a parameter
Label Field
The field that the user will see when selecting a parameter from a list
Non-queried
The default value when no parameter has been selected
- Click Ok to save the parameter.
# Click the Data tab and select the first dataset we created, AdventureWorks2000.
# Go back to the main query and add the parameter to the query. Include '@' before the parameter name, as shown:
!xlw_parameter10.jpg!
# Save the report definition file in a location you can access with OfficeWriter Designer and close Visual Studio.
We will now open the report definition file created in Visual Studio and create a report with OfficeWriter Designer.
- Open Microsoft Excel.
# Click Open Report on the OfficeWriter toolbar.
# Navigate to the report file you just created in Visual Studio and open it.
# Click Select Query on the OfficeWriter toolbar. You will see the two queries created in Visual Studio .NET. Select AdventureWorks2000, the main query.
# Using the Insert Field button, place all the fields in the report, as shown:
!xlw_parameter11.jpg!
# Publish and View the report. The Report Parameters dialog will open.
!xlw_parameter12.jpg!
\When we defined the parameter with Microsoft Query, the Report Parameters dialog did not instruct the user to click to enter a parameter value. When you click to enter a value, the Valid Values dialog will open.
# Select a name from the drop-down list of valid parameter values. These are actually labels, not the values. When you select a name, the corresponding ID will be entered in the Report Parameters dialog.
!xlw_parameter13.jpg!
# In the Report Parameters, click Ok. The report displayed will contain only records for the sales rep that you selected.
!xlw_parameter14.jpg!
Selective Filtering
One of the restrictions of using simple parameter selection is that you are required to select a parameter. What if sometimes you do not want to limit your report? What if you want a report with multiple parameters and you don't want to be required to select a value for each? For example, you have a report with Sales Person and Product parameters. If you are required to select one of each, you can never see all the product sales for one rep or all the sales for one product.
If you want to design a query that returns all the records if no parameter value is chosen, define a default value in the selection list query and add an OR to the WHERE clause of the main query comparing the parameter to its default value.
For example, in the example above, the selection list query was:
To define a default value, change the query to:
The second query adds a sales person record called 'All' with a value of zero. When you do this, make sure that you select a value that is not in your table, or you will have a duplicate key value. Zero is not in the Employee table in the EmployeeID field, therefore it can be used. Adding the SortOrder field insures that this extra entry will display at the top of the list. Also be sure that the key value for the added entry (zero in this case) is set as the default value for the parameter on the Visual Studio Report Parameter screen.
To change the main query so that it will return all the records if no parameter is chosen, change the WHERE clause of the query from 'WHERE SalesPersonID=@SalesPersonID' to 'WHERE (SalesPersonID=@SalesPersonID OR @SalesPersonID=0)' (remember, zero was our default value).
Samples
Download an .rdl file created with Microsoft Query and OfficeWriter.
Download an .rdl file created with Visual Studio and OfficeWriter.