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
|If Microsoft Query cannot display a query graphically, it will not allow you to define a parameter for the query. For example, if a query contains the TOP keyword, which cannot be represented graphically, Microsoft Query will warn you that the query cannot be shown graphically and will not allow you to add parameters to the query.|
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.