Adhoc Reporting Using SSRS 2008 R2 – SQL Server Performance

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

$Description: C:\Franco's data\SSP\Adhoc Report2.jpg$

3. Click New.

$Description: C:\Franco's data\SSP\Adhoc Report3.jpg$

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

$Description: C:\Franco's data\SSP\Adhoc Report4.jpg$

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

$Description: C:\Franco's data\SSP\Adhoc Report5.jpg$

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.