How to Create Word Reports > Reports with Parameters

Reports with Parameters

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.

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 AdventureWorks2000 sample database, which ships with Reporting Services.

1. Open Microsoft Word and create a new document.
   
   
2. Click Add Query on the OfficeWriter toolbar.
   
   
3. Follow steps 2-12 of Create a Database Query in Create Your First Word Report.
   
   
4. In the Add Tables dialog box, find the SalesOrderHeader table in the list and select Add. Click Close.
   
   
5. In Microsoft Query, drag the following fields to the query: SalesOrderID, SalesPersonID, and TotalDue.
   
   
   
   
6. Click the Hide/Show Criteria button on the Microsoft Query toolbar to open the criteria table.
   
   
   
   
7. Click within the top left cell in the criteria table and select SalesPersonID from the drop-down list.
   
   
8. In Value cell beneath SalesPersonID, type [Enter the Sales Person ID]. This is the text that will be used to prompt the user.
   
   
   
   
9. Microsoft Query will prompt you to enter the default value for the parameter. Enter a sales person ID value.
   
   
   
   
10. From the File menu, select Return to OfficeWriter Designer.
    
    
11. Using OfficeWriter Designer's Insert Merge Field button, add the fields to your report.
    
    
12. Set the query range to Selection.
    
    
13. Publish the report.
    
    
14. Click View on the OfficeWriter toolbar. You will be asked to enter a parameter value.
    
    
    
    
15. Replace the 0 with 31. Every row displayed in the report will have SalesPersonID number 31.
    
    
    
    

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).

1. Open Visual Studio and create an empty report project containing an empty report.
   
   
2. Click the Data tab and set up a data source that points to the AdventureWorks2000 database.
   
   
3. 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.
   
   
   
   
4. 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 id or field that will be used in the main query (SalesPersonID in the example above). The second is the field that will be displayed to the user. Use the following query.
   
   
   
   The AdventureWorks2000 database stores its sales reps in the Employee table and sets the Salesperson flag to true (1).
   
   
5. From the Visual Studio menu bar select Report/Report Parameters and click Add to create a new parameter. Fill in the dialog as shown:
   
   
   
   

   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
   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.

   
   
   
6. Click Ok to save the parameter.
   
   
7. Click the Data tab and select the first dataset we created, AdventureWorks2000.
   
   
8. Go back to the main query and add the parameter to the query. Include '@' before the parameter name, as shown:
   
   
   
   
9. Save the report defintion 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.

1. Open Microsoft Word.
   
   
2. Click Open Report on the OfficeWriter toolbar.
   
   
3. Navigate to the report file you just created in Visual Studio and open it.
   
   
4. Click Select Query on the OfficeWriter toolbar. You will see the two queries created in Visual Studio .NET. Select AdventureWorks2000, the main query.
   
   
5. Using the Insert Merge Field button, place all the merge fields in the report, as shown:
   
   
   
   
6. Highlight the row containing the merge fields.
   
   
7. Click Query Range and choose Selection.
   
   
8. Publish and View the report. The Report Parameters dialog will open.
   
   
   
   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.
   
   
9. 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.
   
   
   
   
10. In the Report Parameters, click Ok. The report displayed will contain only records for the sales rep that you selected.
    
    
    
    

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:

    SELECT EmployeeID, Lastname+', '+Firstname+' '+Middlename AS Name
    FROM Employee
    WHERE SalesPersonFlag = 1
    ORDER BY Lastname,Firstname,Middlename

To define a default value, change the query to:

   SELECT 0 AS EmployeeID,'All' AS Name,
       0 AS SortOrder
    UNION
    SELECT EmployeeID, Lastname+', '+Firstname+' '+Middlename AS Name,
       1 AS SortOrder
    FROM Employee
    WHERE SalesPersonFlag = 1
    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 SalesRepID=@SalesRepID' to 'WHERE (SalesRepID=@SalesRepID OR @SalesRepID=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.

Download an .rdl file with selective filtering.

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.