Example Using MultiValueNEW in 2005


Example Using MultiValue NEW in 2005

Let's use a parameter to modify the Top SalesPeople.rdl report to include product categories and subcategories . First create a new report called "Top SalesPeople by Category."

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]
 
[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:

  • Data Set Name: ProductCategory

  • Query:

     SELECT DISTINCT ProductCategoryID, Name          FROM        Production.ProductCategory          ORDER BY    Name 
  • DataSet Name: SubCategory

  • Query:

     SELECT          ProductSubcategoryID, ProductCategoryID, Name         FROM            Production.ProductSubcategory         WHERE           ProductCategoryID in (@ProductCategory) 

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) 



Microsoft SQL Server 2005 Reporting Services
Microsoft SQL Server 2005 Reporting Services
ISBN: 0672327996
EAN: 2147483647
Year: 2004
Pages: 254

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net