SSRS 2008 / 2008 R2 Reports with SSAS as data source

Question

Hi,

Can someone point me to where I can find significant samples? The AdventureWorks downloads I've used for SQL 2008 only contain sample reports which have the relational database as the data source.

Thanks in advance,

Kieran.

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood

Answer 1

I would suggest to look into Adventure works sample. which has very good samples for SSRS. 

Here is the one you can browse. there are different varieties of sample reports available for the developers  

http://msdn.microsoft.com/en-us/library/ms160954(v=sql.90).aspx

 

 

Thanks,

Ayyappan

http://sqlserverrider.blogspot.com

Answer 2

Thanks for your reply.

In response I have double checked the AdventureWorks samples I have access to for SQL Server 2008 R2 SSRS, e.g. downloaded from; - http://msdn.microsoft.com/en-us/library/ms160954(v=SQL.105).aspx. They all have the Database Engine as the data source not, SSAS.

If you have found an AdventurwWorks sample SSRS report using SSAS as the data source please be more specific about where you found it and what it is called and the name of the RDL file.

I've found a sample AdventureWorks SSRS OLAP report for SQL 2005 called Sales Reason Comparisons.rdl, this is missing from AdventureWorks2008. I am currently migrating thisSales Reason Comparisons.rdl to 2008.

Kind Regards,

Kieran.

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood

Answer 3

Hey Kieran, did you ever get an answer to this question?

Also, I"m trying to create a report with an SSAS data source and the SSAS provider isn't available in BIDS 2008.  Any ideas on this?

Answer 4

Sorry Lee,

In short I didn't!

I've found a few samples on http://ssrsdrilltrough.codeplex.com/# but they are quite basic so they may not be what you were looking for. When I have the time I'll attempt at upgrading the sample which came with AdventureWorks 2005 to work with SQL Server 2008 R2 and maybe add a few bells and whistles which are new to 2008 R2.

Adding an SSAS data source to SSRS should be quite simple though, do the following; -

1) Open you SSRS solution

2) Right click your mouse on your "Shared Data Sources" folder

3) Select "Add New Data Source"

4) Select Type "Microsoft SQL Server Analysis Services"

5) Type the server name

6) Select the database.

7) Click "Test Connection"

Away you go!

 

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI, PGD SoftDev (Open), MBCS, MCC http://uk.linkedin.com/in/kieranpatrickwood

Answer 5

Did you end up finding an answer to your question? If so, could you please provide the solution.

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 6

Sorry no solution yet. Realistically I won't be able to create one until I am inbetween contracts since creating meaningful SSRS samples with the AdventureWorks 2008 R2 data source will take some time. 

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI,MCC, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com/ http://uk.linkedin.com/in/kieranpatrickwood

Answer 7

Try these links when you get some spare time :-)

The following document focuses on the technical concepts and techniques used to implement reports with SQL Server 2008 Reporting Services, using an Analysis Services cube as the data source: Analysis and Reporting Services, Design and Implementation: A SQL Server 2008 technical case study.

Some blog posts

http://beyondrelational.com/modules/2/blogs/101/posts/13461/ssrs-amp-ssas-62-create-a-data-source-on-report-server-for-an-analysis-services-database.aspx

http://beyondrelational.com/modules/2/blogs/101/posts/13453/ssas-21-steps-to-create-a-ssrs-report-with-mdx-accessing-a-cube.aspx

http://denglishbi.wordpress.com/2008/03/01/using-reporting-services-ssrs-with-ssas-data/

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 8

Many thanks for the links.

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI,MCC, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com/ http://uk.linkedin.com/in/kieranpatrickwood

Answer 9

Hi Kieran,

If you can't see Microsoft SQL Server Analysis Services as a data source, there is probably something wrong in your set up.

There are several tricks that are useful to know when creating reports from cubes.  Here is a blog I created on the topic http://richardlees.blogspot.com.au/2009/06/reporting-services-tips-for-olap-cubes.html  There are also many reporting services reports demonstrations on http://RichardLees.com.au/Sites/Demonstrations from cubes and data mining models.

By the way, I believe it is worth the effort to create cube based reports.  They tend to be much faster than relational reports, and I tend to only use relational sources for the transaction level reports.

Hope that helps,

http://RichardLees.com.au

---

Richard

Answer 10

Hi Derrick,

I appreciate Richard has supplied some helpful links. However my original question was "Can someone point me to where I can find significant samples with SSRS 2008 / 2008 R2 Reports with SSAS as a data source? "

As I'm a Microsoft BI Consultant the implicit requirement here is that I need access to commercial quality samples including source code.

Kind Regards,

Kieran.

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI,MCC, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com/ http://uk.linkedin.com/in/kieranpatrickwood

Answer 11

Hi Kieran,

We are in the process of migrating Sales Reason Comparisons.rdl that you found for SQL 2005 to SQL Server 2008R2. I will let you know when the report is upgraded. Hopefully this will help you.

Answer 12

Many thanks Derrick :)

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI,MCC, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com/ http://uk.linkedin.com/in/kieranpatrickwood

Answer 13

We uploaded AdventureWorks Sample Reports 2008 R2.zip which contains the Sales Reason Comparisons report. You can download the report project at http://msftdbprodsamples.codeplex.com/releases/view/89245.

Sales Reason Comparisons report

Sales Reason Comparisons Report Prerequisites

To publish and render the Sales Reason Comparisons SQL2008R2 report, you will need the following prerequisites:

1. Attach AdventureWorksDW2008R2 Data File to a SQL Server 2008R2 instance.
2. Deploy the AdventureWorks 2008R2 Analysis Services Project from the enterprise folder.
3. Deploy the AdventureWorks Sample Reports 2008 R2 report project.

When you see answers and helpful posts, please click Vote As Helpful,Propose As Answer, and/or Mark As Answer

Answer 14

Many thanks Derrick :)

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI,MCC, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com/ http://uk.linkedin.com/in/kieranpatrickwood