RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Generate Ad-hoc Reports with Microsoft Reporting Services 2005 : Page 6

Among the many new features in Microsoft Reporting Services 2005 is one that can truly help reduce the workload for developers—the ability to give end users the power to generate their own ad-hoc reports.

Deploying the Model
Now that the ad-hoc model is ready, you can publish it to the report catalog.

  1. In the Solution Explorer pane, right-click on the ReportBuilderDemo project node and choose Properties from the menu. BI Studio uploads the data source definitions to the report folder specified by the TargetDataSourceFolder property. By default, that's the Data Sources folder. Similarly, BI Studio uploads the models defined in the project to the folder specified under the TargetModelFolder property (Models by default). Finally, make sure that the TargetServerURL property specifies the correct URL to the Report Server. Change these settings as needed.
  2. Close the ReportBuilderDemo Property Pages dialog.
  3. Right-click on the on ReportBuilderDemo project node and choose Deploy. BI Studio will build and deploy the project.
    Figure 10. Report Manager Catalog View: Use the Report Manager to manage the report catalog.
  5. Open the Report Manager (http://localhost/Reports). Observe the Data Sources and Models folders.
  6. Click on the Data Sources folder and then on the Adventure Works data source to see its definition. BI Studio doesn't pass the data source credentials if you're using standard authentication. For this reason, take a moment to verify the data source authentication settings.
  7. Finally, click on the Home folder and then on the Models folder. Observe that the Current Month Sales model is listed alongside any other ad-hoc models you have deployed (see Figure 10). Also, notice the Report Builder button on the toolbar.
The model administrator can use the Report Manager to manage the model. For example, if you want to enforce restricted access to the Current Month Sales model to members of specific Windows groups, you can set the security policies using the Security link on the model properties page. Note that the release version of RS 2005 will support fine-grained security where you will be able to set up security polices for individual elements of the model, such as entities and attributes.

Authoring Ad-hoc Reports
After deploying and setting up the model, end-users can author ad-hoc reports using the Report Builder. Behind the scenes, the Report Manager launches the Report Builder by requesting its click-once URL (http:///ReportServer/ReportBuilder/9.00.1090.00/ReportBuilder.application), where 9.00.1090.00 is the version number of the Report Builder application.

Figure 11. Report Builder: Use the Report Builder to author your ad-hoc report by dragging model entities and attributes to the report canvas.
Here's the procedure to author a matrix (crosstab) report that shows product sales broken down by products on rows and order date on columns.

  1. Click on the Report Builder button in the Report Manager. The Report Builder launches and prompts you to select a report model.
  2. Select the Current Month Sales model from the "Source of data" list and click OK.
  3. The Report Builder switches to Report Designer mode (see Figure 11).
The Explorer pane lists the model entities. The Fields pane shows the fields of the selected entity. The Report Layout pane lists several pre-defined report layouts to jumpstart the report design process. The pre-release version of the Report Builder comes with two tabular and two matrix report layouts. The final version will most likely include other layouts, including a chart report layout. Unfortunately, the first release of the Report Builder will not support user-defined report layouts.

Now, create a matrix report by following these steps:

  1. Choose any of the two matrix report layouts from the Report Layout pane. The Report Builder canvas displays an empty matrix template.
  2. Select the Product entity in the Explorer pane and drag it to the "Drop row groups" area of the matrix report. Because Product Name is the default attribute of the Product Entity (as specified by the DefaultDetailAttributes entity property), it will be displayed in the rows of the matrix report.
Notice that once an entity attribute is added to the report, the Explorer pane changes to a tree view that shows the defined entity roles. In our case, the tree view shows the Product entity as a root level and the Order Details role as its descendant. This reflects the fact that the only role defined for the Product Entity is the Order Details role.

  1. Click on the Order Details role. Observe that the Explorer tree view expands to show the Sales Order entity.
  2. Drag the Sum Line Totals aggregate attribute to the "Drop totals" report area.
  3. Click on the Sales Order entity. From the Fields pane drag and drop the Order Date onto the "Drop columns group" report area.
Figure 12. Finished Report: The Monthly Sales Report in Report Design mode
You're done! If you wish, you can spend some time prettying up the report by setting fonts and selecting colors for the report groups. You should also experiment with filtering the report data by using the Filter toolbar button. Note that the Report Builder allows you to use expressions in your report. To do so, right-click on a matrix group and choose the Formula context menu. Unfortunately, the first version of the Report Builder will not support .NET expressions or calling external custom .NET code.

Your final version of the matrix report should look like the one shown in Figure 12 when open in Report Design mode.

Figure 13 shows a preview-mode view of how the final rendered report might look.

Figure 13. Report Preview: The Monthly Sales Report in Report Preview mode.
The report needs desperately some final touches but the pre-release version of the Report Builder doesn't support sorting and formatting.

Viewing and Saving Ad-hoc Reports
After authoring the report, you can preview it by clicking on the View Report button. Behind the scenes, the Report Builder uploads the report definition to the Report Server instance which hosts the model and instructs the Report Server to generate the report. To initiate the drilldown feature, click on the name of a product. Whenever a user activates a drilldown link, the Report Server generates a new report that lists the details of the selected product.

Finally, you can save the ad-hoc report by uploading it to the report catalog. To do so, choose the Save main menu and select the RS folder where the report will be uploaded.

In this article, you saw an introduction to the forthcoming ad-hoc reporting model of RS 2005. The cornerstone of the RS ad-hoc model is the semantic layer that the model designer builds on top of the data source. The semantic layer organizes the data structures in entities that users can easily understand and work with. It allows less-technical users to author professional reports in the Report Designer without requiring familiarity with the database schema or SQL language. For those of you already familiar with Analysis Services, see the sidebar SMDL or UDM? for a brief comparison and recommendation as to when to use each. Happy ad-hoc reporting!

Teo Lachev works as a technical architect for a leading financial institution where he designs and implements .NET-centric Business Intelligence solutions. He is a Microsoft Most Valuable Professional (MVP) for SQL Server. Teo is the author of the books Applied Microsoft Analysis Services 2005 and Microsoft Reporting Services in Action.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date