Share via

  • Article

Lesson 2: Adding Parameters to Create a List of Available Values (Report Builder 2.0)

Available values, or valid values, provide a users with a list of possible values for a report parameter. 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 ensure that a user can choose only values that exist in the underlying source of data.

In this lesson, you will modify the Sales Order report to present a drop-down list of available salesperson names from the AdventureWorks2008 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 replace the existing dataset query

  1. In the Report Data pane, right-click DataSet1, and then click Query. The query designer opens.

    In the Query pane, replace the existing query text with the following query:

    SELECT
  SH.OrderDate
  ,DATENAME(weekday, SH.OrderDate) as Weekday
  ,SH.SalesOrderNumber
  ,SD.OrderQty
  ,SD.LineTotal
  ,P.Name AS [Product]
  ,PS.Name AS [Subcategory]
FROM Sales.SalesPerson SP 
  INNER JOIN Sales.SalesOrderHeader AS SH 
      ON SP.BusinessEntityID = SH.SalesPersonID
  INNER JOIN Sales.SalesOrderDetail AS SD 
     ON SH.SalesOrderID = SD.SalesOrderID
  INNER JOIN Production.Product AS P
   ON SD.ProductID = P.ProductID
  INNER JOIN Production.ProductSubcategory AS PS
   ON PS.ProductSubcategoryID = P.ProductSubcategoryID
  INNER JOIN Production.ProductCategory AS PC
   ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE PC.Name = 'Clothing' 
   AND (SH.OrderDate BETWEEN (@StartDate) AND (@EndDate))
   AND SH.SalesPersonID = (@BusinessPersonID)

    This query includes a query parameter for the sales person who is responsible for the sales order:

    AND SH.SalesPersonID = (@BusinessPersonID)

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

    @StartDate

    20010101

    @EndDate

    20030101

    @BusinessPersonID

    290

  3. Click OK. The result set appears for the sales person Ranjit Varkey Chudukatil with SalesPersonID = 290. 

    Next, you will create a new dataset that lists the names of the salespersons.

To populate a valid values list for a report parameter

  1. In the Report Data pane toolbar, click New, and then click Dataset. The Dataset Properties dialog box opens.

  2. In the Name field, type SalesPersons. This dataset will be used to populate the valid values list for the SalesPersonID report parameter.

  3. Verify that the data source is AdventureWorks2008.

  4. Click Query Designer, and then click Edit As Text.

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

    SELECT SP.BusinessEntityID, C.FirstName, C.LastName
FROM   Sales.SalesPerson AS SP INNER JOIN
     HumanResources.Employee AS E ON E.BusinessEntityID = 
     SP.BusinessEntityID INNER JOIN
     Person.Person AS C ON C.BusinessEntityID = E.BusinessEntityID
ORDER BY SP.BusinessEntityID

  6. Click OK twice. In the Report Data pane, the new dataset displays three fields: BusinessEntityID, FirstName, and LastName. This dataset will be used to provide valid values for the parameter BusinessPersonID.

    Next, you will create an additional dataset field that concatenates FirstName and LastName into one field called Name. Fields that you define are known as calculated fields.

To define a calculated field in the Report Data pane

  1. In the Report Data pane toolbar, right-click the SalesPersons dataset, and click Add Calculated Field. The Fields page of the Dataset Properties dialog box opens with a new row added to the grid.

  2. In the last Field Name text box, type Name.

  3. In the Field Source text box, paste the following expression:

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

  4. Click OK.

  5. In the Report Data pane, under the SalesPersons dataset, the new field Name appears in the field collection for the dataset.

    Next, you will point the report parameter BusinessPersonID to use this dataset for its valid values.

To populate the report parameter with a list of available values

  1. In the Report Data pane, expand the Parameters node and right-click BusinessPersonID, then click Parameter Properties.

  2. In Prompt, type Select sales person:.

  3. In Data type, select Integer.

  4. Click Available Values.

  5. Select the Get values from a query option.

  6. From the Dataset drop-down list, select BusinessPersons.

  7. From the Value field drop-down, select BusinessEntityID.

  8. From the Label field drop-down, select Name.

    By selecting Name for the label, the drop-down list of valid values for the BusinessEntityID parameter will now show the name of each sales person instead of the business identifier.

  9. Click Default Values.

  10. Select the Get values from a query option.

  11. From the Dataset drop-down list, select BusinessPersons.

  12. From the Value field drop-down list, select BusinessEntityID.

  13. Click OK. 

  14. Click Run. The report shows a drop-down list with business person names.

  15. Click View Report.

    The report displays the sales orders for the selected sales person that match the limits that are specified by the report parameters. Some combinations of dates, days of the week, and sales person might have no results.

Next Steps

You have successfully added a list of available values for a parameter to an existing report. Next, you will modify the DayoftheWeek and BusinessPersonID parameters to accept multiple values. See Lesson 3: Adding Parameters to Select Multiple Values in a List (Report Builder 2.0).