Lesson 4: Designing the Report
New: 5 December 2005
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.
The Design the Matrix page appears.
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 on the Layout tab 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 modify the report field formula
In the Fields!Sum Total Due.Value cell, double-click First and then type Sum.
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 Layout tab, right-click Fields!Order Year.Value and select Subtotal.
The sum total sales will be displayed when the report is rendered.
Right-click Fields!Order Quarter.Value and select Subtotal.
The sum total sales for all quarters within each year will be displayed when the report is rendered.
Right-click Fields!Product1.Value and select Subtotal.
The sum total sales for each product will be displayed when the report is rendered.
Right-click Fields!Product_Subcategory.Value and select Subtotal.
The sum total sales for each product subcategory will be displayed when the report is rendered.
Right-click Fields!Product Category.Value and select Subtotal.
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 Order_Date box, type 5/1/2003.
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
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 Tutorial: Adding Grouping, Sorting, and Formatting to a Basic Report.
See Also
Other Resources
Working with Report Model Query Designer
Report Model Query Designer F1 Help
Working with Models
Working with Report Designer