Using Power View in Excel with Reporting Services Reports as data source

 

With the addition of Power View in Excel 2013 there are a lot of new things you can do, for instance I have a report with some data which I want to do data exploration. The right data is already  published in a report so I have two options:

  1. Check the report definition, view the data source, copy the query and the connection and create a model on top of that. Which is possible if you have permissions to the original data source and also you will have to deal with some t-sql and put together the relationships between datasets etc etc.
  2. Use the report as a data source, the data is already published filtered and ready to consume, and if you can see the report you have all the permissions you need.

 

The first step is to export the report as a Data Feed

 

image

 

The report is exported as YourReport.atomsvc, you can open this file in excel and it will show the data regions of you report

image

 

You select the regions and then it will ask you how you want to import your data, I just selected Power View Report

 

image

 

And now you have all the richness of Power View to analyze your data, 

 

image

 

‘If you need to refresh your data you can use the Data, Refresh All button in Excel and it will gather the data from the Reporting Services Report.

 

After I played with this I looked around in the documentation and found an article with more details in Import Data from a Reporting Services Report