Ad-Hoc Reports using Report Builder

You have so far learned to create reports using Report Designer from relational and multi-dimensional databases. However these reports are pre-defined and often business users want to modify reports to better understand the data. Business users are most likely not used to understanding the underlying data sources and the query languages to retrieve data and design their reports. Most of them do understand entities and relationships between entities since they analyze data. Reporting Services 2005 provides Report Builder for the business users to explore the business data in a timely way and to make effective decisions. Report Builder exposes the business data through a model called report model and translates users' actions into appropriate queries to retrieve the underlying data source.

Report Builder is a Winforms application that is accessed from the Report Server for centralized management. Users can create reports using Report Builder through simple drag and drop of entities that are exposed through the report model. The reports generated by Report Builder are published to Report Server using the Report Definition Language (RDL).

Report Model

A report model is a metadata description of data objects and the relationships between the data in the underlying data source. Report models expose the data objects and relationships from the data sources as entities and relationships logically grouped together. Also, note that the entities and relationships are easier to understand than the underlying data source objects for business users. A report designer or a report server administrator would typically create a report model using a report model project in BIDS or SSMS and deploy it to the Report Server. These report models can then be accessed through the Report Builder application by business users.

You can create report models from relational and multi-dimensional databases. The report model consists of three objects — the semantic model, the physical model, and the mapping between the semantic model and physical model. The semantic model is the end users' view of the data which is defined using the Semantic Model Description Language (SMDL). The physical model is the physical representation of the objects such as cubes, dimension, levels, measures, etc. The mapping between the semantic model and physical model is used by Report Builder to translate users' actions into appropriate queries to the data source.

A Report Model project is created using BIDS when you need to create a report model from a relational data source. You need to create a data source, data source view, and then finally using the Report Model Wizard you create the report models. The generated model is then deployed to the Report Server. To create report models from Analysis Services data source you create a data source on the Report Server using SSMS and then generate a report model.

In this chapter you will create a report model from an Analysis Services UDM. Each UDM from Analysis Services is translated to a single report model. You will not be able to edit the report models generated from Analysis Services. Follow the steps below to generate a report model from the AdventureWorksDW UDM in the AnalysisServices2005Tutorial multi-dimensional database.

1. Open SSMS and create a connection to the Report Server as shown in Figure 17-44.

   
   Figure 17-44

2. Right click on the Data Sources folder on your report server and select New Data Source. You will now see the Data Sources dialog. Provide the name AnalysisServices2005Tutorial and click on Connection to specify the connection details. Select the Data source type as Microsoft SQL Server Analysis Services. This dialog lacks the ability to launch the Connection Manger dialog to specify the connection parameters. You can obtain the connection to an Analysis Services database by creating a data source in Analysis Services project or Report Designer project. Type the connection string as "Provider=MSOLAP.3;Data Source=localhost;Integrated Security= SSPI;Initial Catalog=AnalysisServices2005Tutorial" as shown in Figure 17-45. Finally you need to specify the credentials under which Report Server needs to connect to the Analysis Services database. Select the Windows integrated security for the credentials and click OK.

   
   Figure 17-45

3. Right-click on the Analysis Services data source created in step 2 and select Generate model as shown in Figure 17-46.

   
   Figure 17-46

4. You will now be in the Generate Model dialog as shown in Figure 17-47. Provide a name for the report model to be generated and if needed, you can change the path where the report model needs to be generated and then click OK.

   
   Figure 17-47

The report model AnalysisServices2005TutorialModel is now generated under the Data Sources folder. You have now successfully created a report model from AnalysisServices2005Tutorial database. In the next section you will learn to create reports using the Report Builder application.

Ad-hoc Reports

Once the report models are available on the Report Server, users can create ad-hoc reports using the Report Builder application. Report Builder represents the report model as entities and relationships and makes it easy for business users to generate ad-hoc reports. Follow the steps below to create a report using Report Builder application.

1. Using internet explorer go to the report server interface at http://localhost/reports. Launch the Report Builder application by clicking on the button Report Builder.

2. The Report Builder application is downloaded on your machine and launched. You now need to choose the report model as a data source. Select the AdventureWorksDW as the data source, tabular report for report layout as shown in Figure 17-48, and click OK.

   
   Figure 17-48

You will now see the various entities in the data source as well as the fields for the selected entity on the left side. On the right side you can see the design surface to create a report as shown in Figure 17-49. On the top you can see a tool bar containing buttons to operations on a report such as design, run, filtering, open, and save reports, and another tool bar for formatting the reports.

Figure 17-49

Assume you want to create an ad-hoc report to analyze the customer internet sales in various territories based on the number of cars owned. Enter the title of the report as "Sales Territory and Cars Owned by Customers." Select the entity Dim Geography in the explorer window. Drag and drop the field Sales Territory on the design surface.

Your explorer view now contains the entity Dim Geography and the related entities Fact Internet Sales and Fact Reseller Sales. If you click on the Fact Internet Sales you will see all the related entities.

Since you need to analyze the sales based on the number of cars owned by customers, click on the Customers entity under Fact Internet Sales. Drag and drop the field Number Cars Owned to the design surface. Click on the entity Fact Internet Sales and drag and drop the fields Distinct Customers and Fact Internet Sales - Sales Amount on to the design surface. Your Report Builder will now look like Figure 17-50.

Figure 17-50

1. You have now designed an ad-hoc report which should just have taken you a few minutes and you didn't have to write any SQL query or change properties. You can see how easy it is for end users to create an ad-hoc report that is needed by them. To preview the report click on the button Run Report. You will now see the report as shown in Figure 17-51.

   
   Figure 17-51

2. Once you have reviewed the report you can save the report on the Report Server. Click on the Save icon on the tool bar to save the report. In the Save As Report dialog enter the report name as Sales Territory and Cars Owned as shown in Figure 17-52.

   
   Figure 17-52

In this section you have learned about creating report models and later generating ad-hoc reports using the Report Builder. There are several features in the Report Builder application that help you to filter, sort, and group the data in the report which are not covered in this section. We recommend you explore these features to refine your reports.