.
...
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 the 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
OfficeWriter uses Microsoft Query to add parameters to a report.
| Note |
|---|
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.
...
Defining Parameters in Visual Studio
When you create data sources and queries in Visual Studio, you can also define parameters.
When For more information, see Defining Parameters with MS Query in Word.
Defining Parameters in Visual Studio
When you create data sources and queries in Visual Studio, you can also define parameters. MSDN has thorough documentation for working wtih parameters in Reporting Services:
Adding Parameter Values to your OfficeWriter Report
If you want to display the parameter values used in your report, you can use Reporting Services formulas to show these values.
Viewing Reportings 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, 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 |
|---|
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. |
...
After you have retrieved the report you want to view, when you click View, the Report Parameters dialog will open:
\
| 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, 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 use can type in the value for the parameter.
Once all the parameters have values, click OK to view the report.
Samples
Download an .rdl file created with Microsoft Query and OfficeWriter.
Download an .rdl file created with Visual Studio and OfficeWriter.