Using Reporting Services (SSRS) with SSAS data
Over the past few weeks I have had to work on a project to generate Reporting Services 2005 reports with data from an Analysis Services (SSAS) 2005 database. I decided I would go ahead and blog about some of my discoveries and discuss the different options that are available along with any findings (gotchas). I cannot cover every single option in detail, that would require writing a few chapters in a SSRS book (which I would be more than willing to do), but I will go into some detail into what I experienced.
I basically explored four different ways to reference the SSAS data in SSRS. Two of the ways utilized the Microsoft SQL Server Analysis Services type of data source and the other two used Integration Services (SSIS) 2005 and SQL Server 2005 stored procedures. With using the SSAS data source within SSRS you can either use the new SSAS designer within SSRS and drap-and-drop to create your report and parameters or you can edit the dataset and enter in your own query string. If you go with the SSAS designer you can switch from design mode to see the generated MDX statement by click on the icon in the toolbar (see picture below) and when using this designer you need to keep the Measures in the columns.
Tags: ssrs