1. | Add the following data sets using the AdventureWorks data source. |
2. | Name the data set TopEmployees. |
3. | Use the following query: |
[View full width]SELECT TOP 5 C.LastName, C.FirstName, E.EmployeeID, SUM(SOH.SubTotal) AS SaleAmount FROM Sales.SalesPerson SP INNER JOIN HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN Person.Contact C ON E.ContactID = C.ContactID INNER JOIN Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID INNER JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID INNER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS .ProductSubcategoryID INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID WHERE PC.ProductCategoryID in (@ProductCategory) AND PS.ProductSubcategoryID in (@ProductSubcategory) GROUP BY C.LastName, C.FirstName, E.EmployeeID, PC.ProductCategoryID, PS .ProductSubcategoryID order by SUM(SOH.SubTotal) desc
4. | Create two more data sets from the following:
|
These two data sets will not return any data without specifying the values for the query parameters. This should not stop the field list from displaying the Datasets window.
5. | Switch to Layout view. |
6. | Drag a table control from the toolbox over to the report's Layout window. |
7. | From the Datasets tab in the toolbox, drag LastName, FirstName, and SaleAmount from the TopEmployees data set into the table columns . |
8. | Format SaleAmount with the format string c0. |
9. | Go to the Report menu and select Report Parameters. |
10. | There should be two parameters. Change the prompt on ProductCategory to Category and change the data type to Integer. Check the Multi-value option. |
11. | Under Available Values, check the From Query option. |
12. | Select Product Category for the data set. |
13. | Change the Value field to ProductCategoryID and the Label field to Name. |
14. | Select the ProductSubcategory parameter. |
15. | Change the data type to Integer. |
16. | Change the prompt to Subcategory, and check the Multi-value option. |
17. | Under Available Values, check the From Query option. |
18. | Select the Subcategory data set. Enter SubCategoryId for the value and Name for the label. |
19. | Click OK. |
20. | Preview the report by clicking the Preview tab of the Report Designer. |
Note
As you have probably noticed, the report-processing engine was clever enough to properly place comma-separated multiple parameter values in the WHERE clause. If you are writing expressions, the multivalue parameters can be accessed like an array:
=Parameters!ProductCategory.Value(0)