How to Create Ad-hoc report using SSRS and SSAS cube

Recently, I got a challenging work project. The client want to create reports on their own and have users all able to create report as well. We have been using SSRS report but the challenge is how?

We have heard about ad-hoc reporting using SSRS but there is not proper information on web to sort out the issue.

So, having worked on this, I thought I would provide some information on how to create a SSRS ad-hoc report, where users have the capability to create reports on their own, they just need knowledge of how to create report and report structure: i.e. what columns and rows he/she need and what details need to be summarized.

Let's walk through creating an ad-hoc report using SSRS Report Builder+ SSAS.

1. Open Report Manager

First open a report manager in IE 8 or with compatible mode in IE 9 as administrator.

2. Generate Data Model from SSAS data source

We are going to create an ad-hoc report using SSAS cubes so we need to create Data model from existing cube to access in report. It will give a way to select cube with facts and dimensions with hierarchies on report. You can create perspective (view) as require for security reasons. (If you wish to some of user have no permissions to view sales cube and only view/create reports from purchase cube then perspective will come in picture.)

3. Open Report Builder

Now time to create report. Hence, click on the image/ link of report builder to create report and follow the friendly steps to create.

4. Create Report Data Source

Create new data source for report from the published data source from report server.

5. Select Data Model for ad-hoc report

Select data model from report server because we are going to create ad-hoc report from cube.

6. Select user Perspective

As we have discussed regarding perspective above, select the perspective related to area of yours.

7. Select fields for Report

Now you can see the facts and dimensions on report. Select fields from fact and dimension table as require to generate report.

8. Arrange fields on Report

Now turn to arrange fields on report as desire to generate report. For example, if you need date-wise report with filter of product and category with summary of product order and price.

To do this, put the date dimension fields on row, fields from product dimension on column and price and quantity on detail/summary section.

9. Choose Report Layout

The main portion of report creation is completed. Now move to UI look-n-feel part of report. Select the options to select further summary detail of report. i.e where you wish to show totals and grand totals.

10. Choose Report Style

Select existing styles from list for you report for look n feel.

11. Report using Report Builder

Now report is ready to execute.

12. Execute Report

Just hit run report to view report live.
We have now generated a report using SSAS + Report Builder.

There IS another way to create an ad-hoc report, using SSAS cube + MS excel. So, let's walk through creating an ad-hoc report using SSAS source and MS excel.

The same first steps are needed to create your Data Model and source on report manager, then you can skip forward to below steps.

1. Use SSAS as data source

We are generating report from SSAS  hence, have to select cube as source using SSAS service.

2. Select SSAS Server instance

Select or type SSAS server instance.

3. Select Cube

Select cube from which you wish to generate report.

4. Import Data for ad-hoc report to excel

Now we need data on excel to create report so import data on excel.

5. Execute Report

Arrange/ select fields from Pivot area as per your desired view of report. i.e. Date parts on row and product and it's category on column.

That's it! I hope you enjoyed the article and learned something new here.

Read more on Ad-hoc reporting here:

http://technet.microsoft.com/en-us/library/aa964121%28v=sql.90%29.aspx