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.
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.
OfficeWriter uses Microsoft Query to add parameters to a report. For more information see 8.1 - Defining Parameters with MS Query.
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).
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 |
Note: For available and default values, you may use queried or non-queried values, i.e., you can base your values on a query or a list. Each will work with OfficeWriter Designer. |
We will now open the report definition file created in Visual Studio and create a report with OfficeWriter Designer.
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:
SELECT Sales.SalesPersonID.Person.Contact.LastName + ', ' + Person.Contact.FirstName + ' ' + Person.Contact.MiddleName AS Name FROM Sales.SalesPerson INNER JOIN HumanResources.Employee ON Sales.SalesPerson.SalesPersonID = HumanResources.Employee.EmployeeID INNER JOIN Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID ORDER BY Person.Contact.LastName, Person.Contact.FirstName, Person.Contact.MiddleName |
To define a default value, change the query to:
SELECT 0 AS SalesPersonID, 'All' AS Name, 0 AS SortOrder UNION SELECT Sales.SalesPersonID.Person.Contact.LastName + ', ' + Person.Contact.FirstName + ' ' + Person.Contact.MiddleName AS Name, 1 AS SortOrder FROM Sales.SalesPerson INNER JOIN HumanResources.Employee ON Sales.SalesPerson.SalesPersonID = HumanResources.Employee.EmployeeID INNER JOIN Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID ORDER BY SortOrder, Name |
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).
Download an .rdl file created with Visual Studio and OfficeWriter.
Download an .rdl file with selective filtering.