I was being triggered by my own remark from my previous blogpost Solving a business problem with Powerpivot CTP3 using DAX Timefunctions where i stated:
I wish i could use this in Reporting services on top of cubes, doing these same things in MDX wil most definitely give us the feared too many rows error.
I decided this had to be possible, when a PowerPivot app is published to SharePoint the data is loaded into SSAS. So this blogpost will describe how we can use reporting services to show data we created in PowerPivot.
First we have to save the App to SharePoint, thanks to Dave Wickert we know manual uploading to SharePoint is the best way to go, resulting in a published PowerPivot App:
Next we start Report builder 3.0 to make a connection to the SSAS integrated server.
The server is called “Geminibi” by default, you can find your sheet name in the name of your SSAS database, i called my sheet BP followed by Sandbox which is the name of the cube (default) and followed by what seems a generated Guid. I would recommend to create a account to use as credential and not use integrated security and add this to a role in the SSAS database.
UPDATE: I just remember reading a blog post a while back using a published PowerPivot App in SSRS, seems you can use the XLSX path from SharePoin as datasource (is identical to above):
We can now create the report using the measure we created in PowerPivot with DAX, somehow these DAX measures are created as MDX in SSAS, i wish i could see the MDX that was created.
Putting it together in a report (remember to select a date, we used time intelligent functions) we get the same result as in Excel:
We now can build a similar report like our excel sheet, we can even make the year a parameter (which i wanted in PowerPivot):
which we can run, resulting in a report build on a published PowerPivot App:
We now can publish this report to SharePoint to make the report available to other users (i added a new R2 sparkleline chart as well, just because i love m 🙂 )
As you can see it’s pretty easy to use Published PowerPivot App as a reporting source, you obvious don’t have the same tweaks available as you would have on SSAS (think aggregations etc) and when someone changes the PowerPivot app you are doomed 🙂 (you could backup and restore the cube in it’s current state on a different server). Who can name me more potential issues ?