Last time in Part 3 of Building a Report from the Ground Up, we covered how to create a Table to display the data returned by a stored procedure. In Part 4, I'll show you how to create a template with a Page Header and Footer that uses your company logo as well as globals to give your report that polished professional look. Then, we'll go over using a Matrix in your report. So lets create a template to be used for our reports.
1. First, create a blank report for us to work with. Right-click on the report folder in the Solution Explorer and select Add and then select New Item. Select Report and in the Name text box, name your report Template.rdl. Click OK.
2. To add the Page Header and Footer, click Report up top in the menu bar and click Add Page Header and Add Page Footer.
3. To add your company logo to your report, right-click inside the Header in the design area, select Insert, and select Image. In the Image Properties window, click Import and navigate to the image you wish to use. Then click OK. Resize your Image using the bounding box around the image. I'm using the Adventure Works logo from MSDN. Move your company logo to the top left corner of your report.
4. Next, right-click in the header, select Insert, and select Text Box. Right-click in the newly created text box and select Expression. I used this expression to display the execution time each time the expression is reported: " ="Execution Time: " + Globals!ExecutionTime ". Move the text box directly beneath the company logo. Move The Globals!ExecutionTime is one of the built-in fields you have available to you. To see the other built-in fields available, right-click the text box, select Expression, and click Built-In Fields in the Category window. There are Global Expressions such as page number, total pages, and language.
5. Add another text box in the top-middle area of your report with the word Title, bolded, and size 14 font. This will be the place holder for your report title.
6. In the Footer, add another text box with the expression: " =cstr(Globals!PageNumber) + " of " + cstr(Globals!TotalPages) ". This expression displays the page numbers in your report using the built-in global fields.
7. Click preview to view the template.
8. Save the Template and then right-click on Template.rdl in the Solution Explorer and select Exclude From Project. Now anytime you need to create a report using your Template, simply right-click the Report folder in the Solution Explorer, select Add, select Existing Item, navigate to Template.rdl and click OK. Change the name of the report to something besides Template.rdl and start building your report.
Now that you know how to give your reports a consistent and polished look, lets take a look at the matrix. Sometimes using a table isn't the best option. A table is great if you need to display a list of records, but when you need to display a grouping or summary of data, such as a total or average, a matrix is what you want.
Here is the SQL query I'm using for my data set (using the AdventureWorksDW database):
SELECT DimCustomer.Gender, DimCustomer.YearlyIncome, DimCustomer.NumberChildrenAtHome, DimGeography.CountryRegionCode
FROM DimCustomer INNER JOIN
DimGeography ON DimCustomer.GeographyKey = DimGeography.GeographyKey
After adding a Shared Data Source referencing the AdventureWorksDW database and adding a dataset that uses my stored procedure, it's time to create a matrix.
1. Drag a matrix into the design area.
2. Drag the Gender field into the Columns group of the matrix, the NumberChildrenAtHome field into the Rows group, and the YearlyIncome field into the Data group.
3. Right-click the YearlyIncome field in the matrix, select Expression and change "Sum" to "Avg" to return the average yearly income. The expression should look like this: "=avg(Fields!YearlyIncome.Value) ".
4. Add an expression to the NumberChildrenAtHome that uses an If statement, like this: " =iif(Fields!NumberChildrenAtHome.Value>=4,"4+",Fields!NumberChildrenAtHome.Value) ". This If statement says that if the NumberChildrenAtHome field is greater than or equal to 4, display "4+", else simply display the NumberChildrenAtHome field value. That way, all AdventureWorks employees that have 4 or more children will be grouped together. I could have simply done the grouping within the SQL statement, but for example wise I'm doing it in the report.
5. Also, put an expression on the Gender field using: " =switch(Fields!Gender.Value="F","Female",Fields!Gender.Value="M","Male") ". The switch statement says that if the Gender value equals F, then display Female. If the Gender value equals M, display Male.
6. Next, right-click the YearlyIncome field, select Text Box Properties, select Number, and select Currency.
7. Lastly, add some nifty formatting to the matrix header to make it stand out, similar to what I covered in Part 3. Then click preview to view the report!
You can see how the all the female and male employees of Adventure Works have been grouped together by the number of children they have with their average income being displayed in the data column.
Now you know how to use a matrix to display a summary of data. In Part 5 of Building a Report from the Ground Up, I'll start getting into how to add parameters to your report that allow you to select multiple values.