SSRS Tutorial: Create Report Dataset

Report Dataset

In this chapter, we will create the main dataset used by the report. You will import the query definition from a file, and then modify the query to support a query parameter.
1.       In the Report Data window, right-click the AdventureWorksDW2008R2 data source, and then select Add Dataset.
2.       In the Dataset Properties window, in the Name box, replace the text with dsMain.
3.       To create the query, click Query Designer.
4.       Maximize the Query Designer window.
5.       To import a query file, on the toolbar, click Import.
6.     SELECT CalendarYear, CalendarQuarter, EmployeeID, SalespersonName, SalesAmount
FROM dbo.vReportSalespersonSummary
WHERE CalendarYear = 2014;
7.       Review the imported query, and in particular notice the WHERE clause that restricts the query result to calendar year 2007.
8.       On the toolbar, click the Run button.
9.       Review the query result, and in particular notice the five columns that are returned.
10.   In the query statement, in the WHERE clause, replace 2014 with @Year.

T-SQL

WHERE (CalendarYear = @Year)

 

11.   Click OK to commit the query and close the Query Designer window.
12.   Click OK.
13.   In the Report Data window, expand the Parameters folder, and notice the Year report parameter.

Reviewing the Report Data Window