Share via

  • Article

Lesson 2: Adding Parameters to Create a List of Available Values (SSRS)

Available values, or valid values, provide a users 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 SQL Server AdventureWorks2008R2 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

  1. In the Report Data pane, right-click the dataset AdventureWorksDataset, and then click Dataset Properties.

    Note

    If you do not see the Report Data pane, from the View menu, click Report Data.

  2. In Data source, verify that AdventureWorks_Ref is selected.

  3. In Query type, verify that Text is selected.

  4. Click the Query Designer button to open the query designer.

  5. Replace the text with the following query into the text box:

    SELECT 
   soh.OrderDate AS [Date], DATENAME(weekday, soh.OrderDate) as Weekday,
   soh.SalesOrderNumber AS [Order], 
   pps.Name AS Subcat, pp.Name as Product,  
   SUM(sd.OrderQty) AS Qty,
   SUM(sd.LineTotal) AS LineTotal
FROM Sales.SalesPerson sp 
   INNER JOIN Sales.SalesOrderHeader AS soh 
      ON sp.BusinessEntityID = soh.SalesPersonID
   INNER JOIN Sales.SalesOrderDetail AS sd 
      ON sd.SalesOrderID = soh.SalesOrderID
   INNER JOIN Production.Product AS pp 
      ON sd.ProductID = pp.ProductID
   INNER JOIN Production.ProductSubcategory AS pps 
      ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
   INNER JOIN Production.ProductCategory AS ppc 
      ON ppc.ProductCategoryID = pps.ProductCategoryID
GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, 
   pps.Name, pp.Name,    soh.SalesPersonID
HAVING 
ppc.Name = 'Clothing' 
AND (soh.OrderDate BETWEEN (@StartDate) AND (@EndDate))
AND  soh.SalesPersonID = (@BusinessPersonID)

    This is the same query as before, except that a condition has been added that limits the result set to one sales person:

    AND soh.SalesPersonID = (@BusinessPersonID)

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

    @StartDate

    1/31/2001

    @EndDate

    1/31/2003

    @BusinessPersonID

    290

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

To populate a valid values list for a report parameter

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

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

  3. Verify that the data source is AdventureWorks_Ref.

  4. 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. A list of fields is populated for the BusinessPersons dataset. This dataset will be used to provide valid values for the parameter BusinessPersonID.

  5. You will notice that the BusinessPersons dataset has fields called FirstName and LastName. Next, we will concatenate these fields into one field called Name.

To define a calculated field in the Report Data pane

  1. In the Report Data pane toolbar, right-click the BusinessPersons 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 BusinessPersons dataset, the new field Name appears in the field collection for the dataset.

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 business 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 sales person number.

  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 the Preview tab. The report shows a drop-down list with business person names.

  15. Click View Report. Select other parameter values to review the 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 SalesPersonID parameters to be multivalued. See Lesson 3: Adding Parameters to Select Multiple Values in a List (SSRS).