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. This will cover how to define parameters in a report using MS Query in the OfficeWriter Designer. For more on parameters in OfficeWriter reports, please refer to Excel Reports with Parameters
Let's add a parameter to a sample query. The sample will use the AdventureWorks sample database, which ships with Reporting Services.
- Open Microsoft Excel.
- Click Add DataSet on the OfficeWriter toolbar.
- Follow steps to Creating a Data Source and Building a Query in Creating a New Template.
- In the Add Tables dialog box, find the SalesOrderHeader table in the list and select Add. Click Close.
- In Microsoft Query, drag the following fields to the query: SalesOrderID, SalesPersonID, and TotalDue.
- Click the Hide/Show Criteria button on the Microsoft Query toolbar to open the criteria table.
- Click within the top left cell in the criteria table and select * SalesPersonID* from the drop-down list.
- In Value cell beneath SalesPersonID, type [Enter the Sales Person ID]. This is the text that will be used to prompt the user.
- Microsoft Query will prompt you to enter the default value for the parameter. Enter a sales person ID value.
- From the File menu, select *Return to OfficeWriter Designer*.
- Using OfficeWriter Designer's Data Marker button, add the fields to your report.
- Deploy the report.
- Click View on the OfficeWriter toolbar. You will be asked to enter a parameter value.
- Replace the 0 with 280. Every row displayed in the report will have SalesPersonID number 280.