• Article

Lesson 1: Creating Shared Datasets and Configuring Report Parameters (SSRS)

In this lesson, you will create shared datasets and configure report parameters based on the dataset query parameters for the Employee_Sales_Summary_2008R2 report.

What You Will Learn

In this tutorial, you will learn how to do the following:

  • Create a copy of the base report to start with.

  • Add multiple shared datasets to the project and to the report.

  • Configure report parameters that are automatically created for each dataset query parameter.

  • Add a scenario description that explains the purpose of the report to the report user.

  • Add conditionally hidden text to display the data source and dataset query information.

  • Add conditionally hidden text to display parameter values that were chosen by the report user.

  • Add conditionally hidden text to display the report description.

  • Add a report title.

  • Preview and verify the report.

  • Deploy the report.

Estimated time to complete this tutorial: 15 minutes.

Requirements

For information about requirements, see Prerequisites for AdventureWorks 2008R2 Sample Reports (SSRS).

This lesson assumes that you have completed Creating the Report Server Project and the AdventureWorks2008R2_Base Report (SSRS).

Tips

For a list of general tips, see Report Design Tips (Report Builder 3.0 and SSRS).

To open the project and make a copy of a report

  1. In Business Intelligence Development Studio, open the report server project AdventureWorks 2008R2 Sample Reports.

  2. In Solution Explorer, do the following:

    1. Right-click the report AdventureWorks2008R2_Base.rdl, and then click Copy.

    2. Right-click the project node, and then click Paste.

    3. Rename the copied report to Employee_Sales_Summary_2008R2.rdl.

Create Shared Datasets

This report project uses four shared datasets that are based on the shared data source AdventureWorks2008R2.

Create EmployeeSalesYearOverYear2008R2

To add a shared dataset for year over year sales

  1. In Solution Explorer, add a shared dataset named EmployeeSalesYearOverYear2008R2.

    The data helps answer the question: How do my seasonal sales compare year over year?

    In the report, the line chart and sparkline chart will display this data.

  2. Point to the AdventureWorks2008R2 shared data source and add the following query:

    SELECT   P.FirstName + ' ' + P.LastName AS Employee, 
   DATEPART(Year, SOH.OrderDate) AS [Year], 
   DATEPART(Month, SOH.OrderDate) AS MonthNumber, 
   DATENAME(Month, SOH.OrderDate) AS [Month], 
   SUM(DET.LineTotal) AS Sales
FROM [Sales].[SalesPerson] SP 
   INNER JOIN [Sales].[SalesOrderHeader] SOH ON SP.[BusinessEntityID] = SOH.[SalesPersonID]
   INNER JOIN Sales.SalesOrderDetail DET ON SOH.SalesOrderID = DET.SalesOrderID
   INNER JOIN [Sales].[SalesTerritory] ST ON SP.[TerritoryID] = ST.[TerritoryID] 
   INNER JOIN [HumanResources].[Employee] E ON SOH.[SalesPersonID] = E.[BusinessEntityID] 
   INNER JOIN [Person].[Person] P ON P.[BusinessEntityID] = SP.[BusinessEntityID]
WHERE (DATEPART(Year, SOH.OrderDate) <= @ReportYear - 1 OR
   DATEPART(Year, SOH.OrderDate) = @ReportYear AND DATEPART(Month, SOH.OrderDate) <= @ReportMonth) AND 
   (SOH.SalesPersonID = (@EmployeeID))
GROUP BY P.FirstName + ' ' + P.LastName, SOH.SalesPersonID, 
   DATEPART(Year, SOH.OrderDate), DATEPART(Month, SOH.OrderDate), 
   DATENAME(Month, SOH.OrderDate)

    The shared dataset appears in Solution Explorer.

  3. In the Report Data pane, create a dataset named EmployeeSalesYearOverYear2008R2.

  4. In the Dataset Properties dialog box, select Use a shared dataset, and click the dataset that you just created in Solution Explorer.

    The dataset query includes query variables @ReportMonth, @ReportYear, and @EmployeeID.

  5. In Dataset Properties, verify that a dataset parameter is created for each query variable.

  6. In the Report Data pane, verify that a report parameter is created for each dataset parameter.

The dataset in the Report Data pane is a reference to the shared dataset in Solution Explorer. After the report is published, the reference points to the shared dataset on the report server or SharePoint site.

Create EmployeeSalesDetail2008R2

To add a shared dataset that includes data for each sales order

  1. In Solution Explorer, add a shared dataset named EmployeeSalesDetail2008R2.

    This data helps answer the question: Am I meeting my sales quotas for the whole month and for each category?

    In the report, the matrix and embedded indicators will display this data.

  2. Use the AdventureWorks2008R2 shared data source and the following query:

    SELECT PER.FirstName + ' ' + PER.LastName AS Employee,
   PS.Name AS Subcategory, SUM(DET.LineTotal) AS Sales, 
   SOH.SalesOrderID,  SOH.SalesOrderNumber, 
   P.Name AS Product, 
   SUM(DET.OrderQty) AS OrderQty, DET.UnitPrice, 
   PC.Name AS Category
FROM Sales.SalesOrderHeader SOH 
   INNER JOIN [Sales].[SalesPerson] SP ON SP.[BusinessEntityID] = SOH.[SalesPersonID]
   INNER JOIN Sales.SalesOrderDetail DET ON SOH.SalesOrderID = DET.SalesOrderID
   INNER JOIN [HumanResources].[Employee] E ON SOH.[SalesPersonID] = E.[BusinessEntityID] 
   INNER JOIN [Person].[Person] PER ON PER.[BusinessEntityID] = SP.[BusinessEntityID]
   INNER JOIN Production.Product P ON DET.ProductID = P.ProductID 
   INNER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID 
   INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE (DATEPART(Year, SOH.OrderDate) = @ReportYear) AND 
   (DATEPART(Month, SOH.OrderDate) = @ReportMonth) AND 
    (SOH.SalesPersonID = @EmployeeID)
GROUP BY PER.FirstName + ' ' + PER.LastName, 
   DATEPART(Month, SOH.OrderDate), SOH.SalesOrderID, SOH.SalesOrderNumber, 
   P.Name, PS.Name, DET.UnitPrice, PC.Name

    The shared dataset appears in Solution Explorer.

  3. In the Report Data pane, create a dataset named EmployeeSalesDetail2008R2.

  4. In the Dataset Properties dialog box, select Use a shared dataset, and click the dataset that you just created in Solution Explorer.

Create EmpSalesMonth2008R2

To add a shared dataset for month over month sales

  1. In Solution Explorer, add a shared dataset named EmpSalesMonth2008R2.

    This data helps answer the question: How do the category sales for this month compare to sales for the same month in previous years?

    In the report, the column chart and a databar chart will display this data.

  2. Use the AdventureWorks2008R2 shared data source and the following query:

    SELECT PER.FirstName + ' ' + PER.LastName AS Employee, 
   DATEPART(Year, SOH.OrderDate) AS [Year], 
   DATEPART(Month, SOH.OrderDate) AS [MonthNumber], 
   DATENAME(Month, SOH.OrderDate) AS [Month], 
   PC.Name AS Category, SUM(DET.LineTotal) AS Sales
FROM Sales.SalesOrderHeader SOH 
   INNER JOIN [Sales].[SalesPerson] SP ON SP.[BusinessEntityID] = SOH.[SalesPersonID]
   INNER JOIN Sales.SalesOrderDetail DET ON SOH.SalesOrderID = DET.SalesOrderID
   INNER JOIN [HumanResources].[Employee] E ON SOH.[SalesPersonID] = E.[BusinessEntityID] 
   INNER JOIN [Person].[Person] PER ON PER.[BusinessEntityID] = SP.[BusinessEntityID]
   INNER JOIN Production.Product P ON DET.ProductID = P.ProductID 
   INNER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID 
   INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE
   (DATEPART(Year, SOH.OrderDate) <= @ReportYear) 
   AND (DATEPART(Month, SOH.OrderDate) = @ReportMonth)  
   AND (SOH.SalesPersonID = @EmployeeID)
GROUP BY PER.FirstName + ' ' + PER.LastName, 
   DATEPART(Year, SOH.OrderDate), DATEPART(Month, SOH.OrderDate), 
   DATENAME(Month, SOH.OrderDate), PC.Name

    The shared dataset appears in Solution Explorer.

  3. In the Report Data pane, create a dataset named EmpSalesMonth2008R2.

  4. In the Dataset Properties dialog box, select Use a shared dataset, and click the dataset that you just created in Solution Explorer.

Create SalesEmployees2008R2

To create the dataset for an available values list for employees

  1. In Solution Explorer, add a shared dataset named SalesEmployees2008R2.

    This data provides valid values for the @EmployeeID parameter.

  2. Use the AdventureWorks2008R2 shared data source and the following query:

    SELECT s.[BusinessEntityID], p.[FirstName] + N' ' + p.LastName AS Employee, e.[JobTitle]
FROM [Sales].[SalesPerson] s
   INNER JOIN [HumanResources].[Employee] e ON e.[BusinessEntityID] = s.[BusinessEntityID]
   INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = s.[BusinessEntityID]

    The shared dataset appears in Solution Explorer.

  3. In the Report Data pane, create a dataset named SalesEmployees2008R2.

  4. In the Dataset Properties dialog box, select Use a shared dataset, and click the dataset that you just created in Solution Explorer.

Configure Report Parameters

The shared datasets contain dataset queries with query variables for ReportYear, ReportMonth, and EmployeeID. Corresponding report parameters are automatically created for each query variable. By default, the properties for each report parameter are set for data type String, single value, and no default value or valid values list. You must manually configure each parameter as needed.

Change the Order of Parameters

To change the order of parameters on the report viewer toolbar

  1. In the Report Data pane, click ShowAll.

  2. On the Report Data pane toolbar, click Move Down until the parameter is last in the list.

  3. Click ReportMonth.

  4. Click Move Up until the parameter is first in the list.

Configure ReportMonth

Configure @ReportMonth to accept values only from a set of values that you specify individually. The default value must be one of these values or the report will not automatically run.

To configure the report parameter @ReportMonth

  1. In the Report Data pane, expand Parameters.

  2. Open Parameter Properties for @ReportMonth.

  3. On the General page, change Data type to Integer.

  4. On the Available Values page, select Specify values.

  5. Add an entry for each of the 12 months. Set Label to the name of the month and Value to the ordinal value of the month. For example, the first entry is January and 1. The last entry is December and 12. Fill in all 12 values.

  6. On the Default Values page, select Specify values. In Values, type 7.

This sets the default value to July. When each report parameter has a default value, the report runs automatically on first view.

Configure ReportYear

To configure the report parameter @ReportYear

  1. Open Parameter Properties for @ReportYear.

  2. On the General page, change Data type to Integer.

  3. On the Default Values page, select Specify values.

  4. Add a value. In Values, type 2007.

Configure EmployeeID

To configure the report parameter @EmployeeID

  1. Open Parameter Properties for @EmployeeID.

  2. On the General page, change Prompt to Employee.

  3. Change Data type to Integer.

  4. On the Available Values page, select Get values from a query.

  5. In Dataset, select SalesEmployees2008R2.

  6. In Value, select BusinessEntityID.

  7. In Label, select Employee.

  8. On the Default Values page, select Specify values. In Values, type 283. This value is the employee ID for David Campbell.

Add the Scenario Description

To add the scenario description

  • In the scenario description text box, replace the existing text with the following:

    As a salesperson for AdventureWorks, I want to know how my sales compare to seasonal sales in previous years.

    The line and sparkline charts show year over year sales for all categories .

    The matrix with indicators shows current sales compared to quotas. Change quota values on the reportviewer toolbar.

    The column and databar charts show sales in each category.

In later lessons, you will link from words in the text to the related data regions.

Display Data Source, Dataset, and Parameter Values

You can display dataset query command text and the values chosen by the user for each report parameter. To reduce clutter in the report, place this information in text boxes in a conditionally hidden rectangle container on the last page.

To add a rectangle

  1. Add a Rectangle at the bottom of the report, above the page footer.

  2. In Rectangle properties, do the following:

    1. On the General page, select Add a page break before.

    2. On the Visibility page, select Show or hide based on an expression, and type: =NOT Parameters!ShowAll.Value

In the following steps, add each text box to the rectangle as you complete it. Expand the rectangle as needed. By using the rectangle as a container, you can easily move all the text boxes as one unit.

To add a text box that displays parameter values

  1. To the report body, add a text box with the following text on separate lines:

    1. Report Month: [@ReportMonth] [@ReportMonth.Label]

    2. Report Year: [@ReportYear]

    3. EmployeeID: [@EmployeeID] [@EmployeeID.Label]

  2. Drag the text box to the rectangle and adjust sizes as needed.

To add a text box that displays data source and dataset information

  1. To the report body, add a text box named DataSourceandDatasets with the following text:

    1. Data Source AdventureWorks2008R2:

    2. Create a Placeholder named AdventureWorks2008R2 with the following value: =DataSources!AdventureWorks2008R2.DataSourceReference

    3. Dataset EmployeeSalesYearOverYear2008R2:

    4. Create a Placeholder named EmployeeSalesYearOverYear2008R2 with the following value: =DataSets!EmployeeSalesYearOverYear2008R2.CommandText.

    5. Dataset EmployeeSalesDetail2008R2:

    6. Create a Placeholder named EmployeeSalesDetail2008R2 with the following value: =DataSets! EmployeeSalesDetail2008R2.CommandText.

    7. Dataset EmpSalesMonth2008R2.

    8. Create a Placeholder named EmpSalesMonth2008R2 with the following value: =DataSets! EmpSalesMonth2008R2.CommandText.

  3. Format the text as needed. For example, underline the dataset name.

  4. Drag the text box to the rectangle and adjust sizes as needed.

Add the Description

To add the description

  1. In the page footer, replace the text in description text box with the following text:

    Purpose: Summary report. Displays year over year sales for all categories, for individual categories, and for quotas. Includes charts (line, sparkline, column with scale breaks, databar), tablix (toggle rows, row group, indicators at table and group level), group and report variables, rectangle containers, shared datasets, shared data source, custom code for chart line color, bookmarks, drilthrough action, multivalue parameters, and parameters to control conditional visibility of text boxes and toggle rows.

  2. Click the report background to display Report Properties in the property pane.

  3. In Description, paste the description text.

Add the Report Title

To add the report title

  1. In the page header, add a text box with the following text on two lines:

    Sales Report for [@EmployeeID.Label]

    [@ReportMonth.Label], [@ReportYear]

  2. Format as needed.

Preview the Report

To preview and verify the report

  1. Preview the report.

  2. Verify that the report runs automatically,

  3. On the report viewer toolbar, verify the following:

    1. Report Month has a drop-down list of all months. By default, the value is July.

    2. Report Year has a default value 2007.

    3. Employee has a drop-down list of the names of all employees. By default, the value is David Campbell.

  4. In the page header, the logo, report name, and report title appears.

  5. When you click the toggle, the scenary text appears.

  6. When you set ShowAll to True, and run the report, the parameter values, data source and dataset information, and the description appears on the last page of the report.

Deploy the Report

To deploy the report

  1. In Solution Explorer, right-click the project, and then click Deploy. Check for errors on the status line of the BI Development Studio or in the Output pane.

    Note

    Because the report depends on shared datasets, the first time you deploy the report, you must deploy the entire project.

  2. In a browser, navigate to the report server or SharePoint site, and verify that the shared data sources, shared datasets, and reports appear in the correct folders, and that you can view the reports.

Next Steps

You have completed creating a report project and a base report. You can now create a sample report. For more information, see Tutorials: Creating AdventureWorks 2008R2 Sample Reports (SSRS).