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

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)

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

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.

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

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

Verify that the data source is AdventureWorks2008.

Click Query Designer, and then click Edit As Text.

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

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.

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.

In the last Field Name text box, type Name.

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

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

Click OK.

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.

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

In Prompt, type Select sales person:.

In Data type, select Integer.

Click Available Values.

Select the Get values from a query option.

From the Dataset drop-down list, select BusinessPersons.

From the Value field drop-down, select BusinessEntityID.

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.

Click Default Values.

Select the Get values from a query option.

From the Dataset drop-down list, select BusinessPersons.

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

Click OK. 

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

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.