Lesson 1: Creating a Report Builder Report
You can create your customized clickthrough report entirely in Report Builder, or you can start building your report in Report Builder and then continue to modify it in Report Designer by using the Report Model Query Designer. The Report Model Query Designer lets you use all of Report Designer's functionality, including hyperlinks and multiple data regions, in your model-based report.
In this lesson, you will design your customized clickthrough report in Report Builder using the Adventure Works report model, set the required properties on your customized report, and then publish the report to the report server.
To open Report Builder
Start Microsoft Internet Explorer 6 or later.
In the Address bar of the Web browser, type the Report Manager URL.
By default, this is http://<servername>/reports, where <servername> is the name of the server.
On the Home page of Report Manager, click Report Builder.
In the Getting Started pane, in the Select a source of data for your report box, select Adventure Works.
Make sure the Table format option is selected, and then click OK.
Next, you will start to create a report that contains pertinent information about products, including the Product Category, Product Subcategory and Product name fields, the number of sales orders that include this product, and the total due for each product.
To create a Report Builder report
In the Entities list, select Product.
In the Fields list, double-click Product Category.
In the Fields list, double-click Product Subcategory.
In the Fields list, double-click Name.
Three groups are displayed: Product Category, Product Subcategory, and Name.
In the Entities list, select Sales.
In the Fields list, double-click #Sales.
The #Sales field is added to the Product group.
In the Entities list, select Sales Order.
In the Fields list, double-click Sum Total Due.
The Sum Total Due field is added to the Product group.
To render the report
On the View menu, select Run Report.
The report data is displayed.
Scroll through the report.
The Sum Total Due field is not wide enough to display the monetary value on one line, and the Product Category and Product Subcategory line totals blend together. Next, you will change the heading field colors, widen the Sum Total Due column, and change the background and text colors for the Product Category and Product Subcategory line totals. In addition, you will add a title to the report and delete the Total Products and Filter text boxes.
Note
When creating customized clickthrough reports, it is important to delete the Total and Filter text boxes because the text is hard-coded into the report and the information displayed in these text boxes will not be accurate when the report is viewed as a clickthrough report.
To format the report
On the View menu, select Design Report.
Click the Click to add title box and type Total Earned for Product.
Select the title text box and on the Format menu, select Font.
The Format dialog box opens with the Font tab selected.
In the Font style list, select Bold.
On the Color palette, select Dark Blue and then click OK.
Multi-select the Field headers in the report.
On the Format menu, select Alignment.
The Format dialog box opens with the Alignment tab selected.
In the Horizontal drop-down list, select Center.
In the Vertical drop-down list, select Middle and then click OK.
Select the Product Subcategory Total row, and on the Format menu, select Fill.
The Format dialog box opens with the Fill tab selected.
On the Color palette, select Ocean and then click OK.
Select the Product Category Total row, and on the Format menu, select Fill.
On the Color palette, select White.
Select the Border tab, and then click None.
Select the Font tab.
In the Font style, select Bold.
On the Color palette, select Dark Blue and then click OK.
Select the Total Products box and press Delete.
Select the Filter text box and press Delete.
Position the pointer along the right side of the Sum Total Due field and drag the pointer to the right edge of the report.
The Sum Total Due column widens.
To render the report again
On the View menu, select Run Report.
The report data is displayed.
Scroll through the report.
Make sure the Sum Total Due field is wide enough to display on one line the monetary value of the Total row on the last page.
To permit this report to be used as a clickthrough report
On the View menu, select Design Report.
On the Report menu, click Report Properties.
Select the Allow users to drill to this report from other reports check box.
This check box must be selected to use this report as a clickthrough report.
Note
When you select this option and then publish the report to the report server, this report cannot be rendered as a published report any longer. Instead, to view the contents of this report, it must be viewed as a clickthrough report.
Click OK.
To publish the report to the report server
On the File menu, click Save.
Move to the location on the report server where you want to save the report.
In the Name box, type ProductClickthrough Report.
Click Save.
To close Report Builder
- On the File menu, click Exit.
Next Steps
You have successfully created a report in Report Builder and assigned the required report properties in order to use the report as a customized clickthrough report. In the next lesson, you will open SQL Server Management Studio and link the report you just created to the report model. See Lesson 2: Linking the Report to the Report Model.
Note
To view the Report Builder Help, in the Report Builder window, click Help and then click Report Builder Help. Alternatively, press F1.