SSRS Tutorial : Report Header Design

In this chapter, you will introduce a report header. Inside the report header you will then add a report title and image.

In the report designer, right-click inside the report body (the white rectangle), and then select Insert | Page Header.
Select the Toolbox tab.
To add a report title, from the Toolbox, drag the Textbox into the top left corner of the page header.
Right-click the textbox, and then select Expression.
In the Expression window, in the Category list, select Built-in Fields, and then in the Item list,
double-click Report Name.
Click OK.
Ensure that the textbox is selected, and then in the Properties window, configure the following:

Property	Value
BorderStyle | Bottom	Solid
Font | Size	22
Location | Left	0
Location | Top	0
Size | Width	4
Size | Height	0.4

 

To add the company logo into the page header, from the Toolbox, drag the Image and drop it to the right of the report title.
In the Image Properties window, click Import.
In the Open window, navigate to the folder where image exist, and then click Open.
In the Image Properties window, click OK.
Ensure that the image is selected, and then in the Properties window, configure the following:

Property	Value
Location | Left	4.5
Location | Top	0
Size | Width	2
Size | Height	0.6

 

SSRS Tutorial : Dynamically populating Report Parameter

In this Chapter, we will create an additional dataset to provide the available values for the Year report 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 dsYear.
3.       To import a query file, click Import.
4.       In the Import Query window, navigate to the Assets folder located in the Source folder for this lab, select the dsYear.sql file, and then click Open.
5.       Click OK.

Now we will configure the Year report parameter to prompt the user with available values sourced from the dsYear dataset.
1.       In the Report Data window, right-click the Year parameter, and then select Parameter Properties.
2.       In the Report Parameter Properties window, select the Available Values page.
3.       Select the Get Values From a Query option.
4.       Configure the query properties based on the following, and then click OK.

Property	Value
Dataset	dsYear
Value Field	CalendarYearKey
Label Field	CalendarYearLabel

 

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

SSRS Tutorial : Create Data Source

In this chapter you will create a reference to the AdventureWorksDW2008R2 shared data source created in Task 3.
1.       In the Report Data window (located on the left), right-click the Data Sources folder, and then select Add Data Source.
2.       In the Data Source Properties window, in the Name box, replace the text with AdventureWorksDW2008R2.
3.       Select the Use Shared Data Source Reference option, and then in the corresponding dropdown list, select the AdventureWorksDW2008R2 shared data source.

Creating the Report Data Sourc4.       Click OK.