Page History

...

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 using MS Query or in Visual Studio. We will explore both so you can determine which is best for you./Report Builder.

Defining Parameters with OfficeWriter

...

When you create data sources and queries in Visual Studio .NET, you can also define parameters. MSDN has thorough documentation for working with parameters in Reporting Services:

• Adding Parameters to Your Report (SSRS 2008)
• Working with Parameters in Reporting Services (SSRS 2005)

Adding Parameter Values to your OfficeWriter Report

If you want to display the parameter values used in the report, you can use Reporting Services formulas to show these values.

Viewing Reports with Parameters in the OfficeWriter Designer

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

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

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

...

After you have retrieved the report you want to view, when you click View the Report Parameters dialog will open.
!xlw_parameter12.jpg!

...

{note}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 for a parameter, the Valid Values dialog will open

...

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 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)parameter has a predefined list of values. Otherwise, the user can type in the value for the parameter.
\Once all the parameters have values, click Ok.

Samples

Download an .rdl file created with Visual Studio and OfficeWriter.

...