<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
20,958 Points
14,258 Views
2 Endorsements
Last Modified:
Approved
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.
Report Manager

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.)
Generate Data Model for SSAS cube

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.
Report Builder

4. Create Report Data Source

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

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.
Data Model

6. Select user Perspective

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

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.
Fields from Source

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.
Fields on Report

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.
Layout

10. Choose Report Style

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

11. Report using Report Builder

Now report is ready to execute.
Report

12. Execute Report

Just hit run report to view report live.
ExecuteWe 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.
Select SSAS source in Excel

2. Select SSAS Server instance

Select or type SSAS server instance.
SSAS source

3. Select Cube

Select cube from which you wish to generate report.
Select SourceSelected Source

4. Import Data for ad-hoc report to excel

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

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.
Import Data from CubeAd-hoc Report using Excel
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
2
Comment
  • 2
2 Comments
LVL 21

Author Comment

by:Alpesh Patel
Hi LHerrou,

I have no issue. It's good to give something better to other.

Thanks,

Alpesh
0
LVL 21

Author Comment

by:Alpesh Patel
It's look fine. Thanks for you kind help.
0

Featured Post

HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Join & Write a Comment

Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function. See how to call a wrapper function from a query, and …
If you, like me, have a dislike for using Online Subscription anti-spam services, then this video series is for you. I have an inherent dislike of leaving decisions such as what is and what isn't spamming to other people or services for me and insis…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month