Lesson 4: Designing the Report
In the previous lesson, you created the report model query. In this lesson, you will select the structure that you want to use for the report, assign fields to the columns, rows and details areas, and add totals. Then, you will preview the report in Report Designer.
To select a report style
On the Design the Query page of the Report Wizard, click Next.
On the Select the Report Type page, select the Matrix option, and then click Next.
To design the report
In the Available fields list of the Design the Matrix page, select Product_Category1 and then click Rows.
Important
The Available fields list displays the list of fields that were added to the query and any additional fields required based on how the fields are grouped within the query. In situations where the query references entities and then groups your data by an entity, the entity keys for each group are also added to the Available fields list. When this occurs, the Available fields list contains duplicate names, for example Product and Product1. Product represents the entity key and Product1 represents the value of the field that you added to the query. When organizing the fields within your report layout, always select the value field for displaying data and select the entity key for grouping data, if you want to group by the entity instead of the value.
In the Available fields list, select Product_Subcategory2 and then click Rows.
In the Available fields list, select Product3 and then click Rows.
In the Available fields list, select Order_Year and Order_Quarter, and then click Columns.
In the Available fields list, select Sum_Total_Due and then click Details.
Click Next.
On the Choose the Matrix Style page, select Corporate.
Click Next.
In the Report name box, type QDReportTutorial and then click Finish.
The report appears in the Design pane of the Report Designer. Now, you can modify the report just as you can any other Report Designer report. Next, you will change the aggregate expression because the wrong aggregate is displayed in the formula and add totals fields.
To preview the report
In the Report Designer window, select the Preview tab.
In the Order Date box, type 5/1/2007
Click View Report.
The results are rendered on the Preview tab. Just like any other Report Designer report, you can continue to modify, format, and publish the report to the report server.
Note that you're seeing orders on or after May 1, 2007. If you type a later date, you'll see orders from on or after that date.
To change the prompt text
In the Report Data pane, click Parameters, right-click Order Date, and then click Parameter Properties.
In the Prompt box, type Orders on or after this date:.
Click OK.
To modify the report field formula
On the Design tab, double-click the [Sum(Sum Total Due)] cell.
The Placeholder Properties dialog box opens. In the Value box you see: [First(Sum Total Due)].
Select First and then type Sum.
It now reads: [Sum(Sum Total Due)].
Click OK.
Report Designer defaults to First, which is the first aggregate function in the list of available aggregates. For this report, you want to use the Sum aggregate function; therefore you need to change the aggregate used in the Fields!Sum Total Due.Value field from First to Sum.
The correct aggregate function is now used in the field. Next, you will add total fields.
To add totals fields
On the Design tab, right-click [Order Year], point to Add Total, and select After.
The sum total sales will be displayed when the report is rendered.
Right-click [Order Quarter], point to Add Total, and select After.
The sum total sales for all quarters within each year will be displayed when the report is rendered.
Right-click [Product3], point to Add Total, and select After.
The sum total sales for each product will be displayed when the report is rendered.
Right-click [Product_Subcategory], point to Add Total, and select After.
The sum total sales for each product subcategory will be displayed when the report is rendered.
Right-click [Product Category], point to Add Total, and select After.
The sum total sales for each product category will be displayed when the report is rendered.
To preview the report
In the Report Designer window, select the Preview tab.
In the Orders on or after this date box, type 5/1/2007.
Click View Report.
The results are rendered on the Preview tab.
Note
Before attempting to publish the report to the report server, make sure that you specify the TargetServerURL address by opening the Project menu, clicking Properties, typing the TargetServerURL address in the Property Pages dialog box, and then clicking OK.
Next Steps
You have successfully created a report in Report Designer that is based on a report model query. To learn more about how to format Report Designer reports, see Lesson 5: Formatting a Report, in Tutorial: Creating a Basic Table Report (SSRS).