Adhoc Reporting Using SSRS 2008 R2

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

How to use the Adhoc Reporting Model?

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.

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

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

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

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.


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.

Pages: 1 2 3