Adhoc Reporting Using SSRS 2008 R2

By Franco Lopes
Reporting Services comes with a built-in modeling tool called Report Model Designer which is used for developing adhoc reports. Due to their ease of creation, adhoc reports are often developed by end users as opposed to developers. The main purpose of the report model is to shred the dependency on the coding and give a semantic model to the user to  develop their own reports on the fly without consulting the developer.

How to use the Adhoc Reporting Model?

The report model can be developed for an OLTP or an OLAP database using a wizard based approach. The Report model developed using the model designer is then deployed on the report server for developing the adhoc reports. The model is accessed using the Report Builder tool which is freely available for download from Microsoft. Report Builder 3.0 allows the users to develop reports not only on the report model but also on the variety of data sources. Report Builder 3.0 comes with rich visualizations including the sparklines, databars, indicators, a variety of charts and graphs and also with a the map visualization.

Building the Report Model

Now that we know what the report model and report builder is let us have look how a report model is developed from scratch and the adhoc reports are built using the same. For this exercise we will use the AdventureWorks database.
 Open the Business intelligence development studio.
1. Click File->New Project->add Report Model project->Name it as Adhoc Reporting and click Ok.

2. Add Data Source by right clicking on the data sources folder->Add New Data Source

3. Click New.

4. Specify the server name and the Database as the AdventureWorks. Click Ok to add the data source.

5. Click Next and then click Finish to add the AdventureWorks.ds as the data source to the solution explorer.

6. Add the Data Source view – right-click the Data Source views->Add New Data Source view.
Note: A DSV lets you work on the database schema to change and extend it without affecting the underlying data source. You can add custom calculations and named queries (similar to view building in the database). Using the Explore Data feature you can view the content of the table in the Visual Studio’s BIDS. Explore Data allows you to manipulate the data using OWC components.

7. Click Next.

8. In this example the data source is a relational database. Select the required tables from the database. I have selected tables for sales analysis.

9. Click Finish to complete the wizard.


10. To add Report Model, right-click on the Report Models->Add New Report Model

11. Click Next.

12. Click Next , we will use the default rules for the model generation but you can always select the required rules for model. To understand the rules in more detail click help.

13.  The first option is recommended for the report model generation rather than using the current statistics of the model. Click Next.

14. Click Run to generate the model.

15. After the model is generated click Finish to complete the wizard.

16. Now to deploy the model to the ReportServer. Right-click on the Adhoc Reporting solution and go to properties to set the ReportServer properties.

17. Right Click on the Solution and Click Deploy.

18. Open Internet explorer and type http://localhost/Reports to open the report manager. Report Manager is the Web Application provided by Microsoft to manage the deployed reports. You can see the Models and Data Sources deployed on the report server. Click on the on the Report Builder link to start the Report Builder.

Building An Adhoc Report

1. The Report Builder will be downloaded on your machine after clicking run.

2. Report Builder 3.0 will open once it has downloaded. As you can see Report Builder 3.0 gives you four options to begin report building. There is a table or matrix wizard, chart wizard, map wizard and finally a blank report. Click on the blank report for this run-through.



3. Right click the data sources and Select the option Use shared connection or report model.

4. Browse to the Models folder and select the model AdventureWorks. Click Open and then OK.

5. Right-click on Datasets and add dataset. Click Select the dataset embedded in my report and click on the query designer.

6. The below dialog box opens where you can now drag and drop the fields from the left pane.

7. I have chosen sales group, sales current year and sales last year fields to analyze. Click OK.

8. Click Ok.

9. Thus using the report model and Report Builder the following report was created in less than 5 mins.