Lesson 3: Adding a Single Value Parameter with an Available Values List

  • Article

New: 17 July 2006

Available values, or valid values, provide a report reader with a list of possible values for a report parameter. As a report author, you can provide valid values from a query specifically designed to retrieve a set of values from the data source, or you can provide a predefined set of values. By binding a set of available values to a dataset query that runs when the report is processed, you make sure that only values that exist in the database can be chosen from the drop-down list.

In this lesson, you will modify the Sales Orders report to present a drop-down list of available salesperson names from the AdventureWorks database. You will set a table property to display a message when there are no rows in the result set for the selected parameter value. When you choose a name and view the report, the report shows the sales for that salesperson only.

To open the Sales Orders report

  1. In SQL Server Business Intelligence Development Studio, open the Tutorial Report Server project created in the previous lesson.

  2. In Solution Explorer, double-click the Sales Orders report. The report opens in Layout view.

  3. Click the Data tab.

To add a query parameter to the AdventureWorks dataset query

  1. On the Data tab, from the Datasets drop-down list, choose AdventureWorks.

  2. Add a new query parameter, @SalesPersonID, to the query. In the query, expand the WHERE clause to include the following comparison: AND S.SalesPersonID = (@SalesPersonID).

    Replace the existing query with the following text:

    SELECT S.OrderDate, DATENAME(weekday, S.OrderDate) as Weekday,
    S.SalesOrderNumber, S.TotalDue, C.FirstName, C.LastName, 
    C.ContactID
FROM    HumanResources.Employee E INNER JOIN
        Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
        Sales.SalesOrderHeader S ON E.EmployeeID = S.SalesPersonID
WHERE
    (
    S.OrderDate BETWEEN (@StartDate) AND (@EndDate) 
    AND
    S.SalesPersonID = (@SalesPersonID)
    )

    Note

    The parentheses around the query parameter are an important part of the query syntax.

  3. Click the Run (!) button. When prompted for the query parameters, use the following table to enter values.

    @StartDate

    20010101

    @EndDate

    20030101

    @SalesPersonID

    286

  4. Click OK. The result set appears for the sales person Ranjit Varkey Chudakatil with SalesPersonID = 286.

  5. (Optional) Verify that the query parameter @SalesPersonID has the value: =Parameters!SalesPersonID.Value. Click the Edit Selected Dataset (…) button and select the Parameters tab.

  6. (Optional) Verify that the report parameter SalesPersonID was automatically created for you. From the Report menu, select Report Parameters. The Report Parameters dialog box opens. Verify that SalesPersonID is in the Parameters pane. You will edit this parameter in a later procedure in this topic.

    In the next procedure, you will create a separate dataset that provides the values for the available values drop-down list for the report parameter SalesPersonID.

To create the SalesPersons dataset for available values

  1. From the Dataset drop-down list, select <NewDataset>. The Dataset dialog box opens.

  2. Provide a name for the new dataset. In the Name field, type SalesPersons. This dataset will be used as input for your valid values list.

  3. Paste the following Transact-SQL query in the query pane:

    SELECT SP.SalesPersonID, C.FirstName, C.LastName
FROM   Sales.SalesPerson AS SP INNER JOIN
     HumanResources.Employee AS E ON E.EmployeeID = SP.SalesPersonID INNER JOIN
     Person.Contact AS C ON C.ContactID = E.ContactID

  4. Click the Run (!) button. The SalesPersonID, FirstName, and LastName columns appear in the result set and as fields in the dataset SalesPersons.

    Although you can add calculated fields to your query (for example, adding LastName + N' ' + FirstName as Name to your SELECT statement as an additional column), Reporting Services provides a way to create new calculated fields from existing dataset fields. You can use this feature when your query uses stored procedures that retrieve a predefined set of columns. In the next procedure, you will create a new field for the dataset that combines two existing fields.

To add a new calculated dataset field

  1. In the Datasets window, right-click the SalesPersons dataset and select Add.

    If the Datasets window is not open, click ALT+CTRL+D.

  2. In the Name text box, type Name.

  3. Select the Calculated field option.

  4. Paste the following expression in the text box:

    =Fields!LastName.Value + ", " + Fields!FirstName.Value

  5. Click OK.

    From the data toolbar, click Refresh Fields. The new field Name appears in the field collection for dataset SalesPersons.

    In the next procedure, you will set the table data region property NoRows. The text in this property is displayed when the data retrieved for the table's associated dataset contains no results.

To add a NoRows message to the table

  1. Click the Layout tab to change to Layout view.

  2. From the View menu, select Properties Window.

  3. From the objects drop-down list in the Properties window, select the table. By default, the table name is table1.

  4. In the Properties window, scroll to NoRows.

  5. Click in the text box next to NoRows and enter the following text:

    No results available for this combination of parameters.

    This message will be displayed when the result set for a particular query parameter produces no rows.

  6. (Optional) Click Preview. In the SalesPersonID parameter, type 1. The NoRows message appears in place of the table data region.

    In the next procedure, you will edit the automatically generated report parameter SalesPersonID. You will set the report properties to display the name of the salesperson in the available values drop-down list instead of the database identifier.

To set the properties of the report parameter SalesPersonID

  1. On the Report menu, select Report Parameters. The Report Parameters dialog box opens. The SalesPersonID parameter appears in the Parameters pane.

  2. From the Data type drop-down list, select Integer.

  3. In the Prompt text box, type Select Sales Person:.

  4. In the Available Values section, select From query.

  5. From the Dataset drop-down list, select SalesPersons.

  6. For Value field, select SalesPersonID.

  7. For Label field, select Name.

    The drop-down list of valid values for the SalesPersonID parameter will now show the name of each sales person instead of the SalesPersonID.

  8. In the Default Values section, select From query.

  9. From the Dataset drop-down list, select SalesPersons.

  10. For Value field, select SalesPersonID.

  11. Click OK.

  12. Click the Preview tab. The report shows a drop-down list with sales person names.

Next Steps

You have successfully added a single-value parameter to an existing report. Next, you will modify the DayoftheWeek and SalesOrderID parameters to be multivalued. See Lesson 4: Adding a Multivalue Parameter with Select All.

See Also

Other Resources

Working with Parameters in Reporting Services
Using Single-Valued and Multivalued Parameters
Using Parameters in Expressions

Help and Information

Getting SQL Server 2005 Assistance