Can you access report server models through Excel
Hi,this question has probably been answered, but I cannot find any references to it. I have been asked to find out if Excel 2007 can connect to a SQL2008 reporting services model and if not then are there any plans to incorporate this feature in future releases.Any feedback would be appreciated.thanks Jason
January 7th, 2010 2:11pm
Are you looking to open a published report via excel WITHOUT accessing report server? i'm not sure if it could be accomplished. Or do you want to create a report via excel? you could use analysis stuido to create a model, and use excel to create reports. Search for "data mining addin for office 2007". And there are ways to have it configured. Its not necessary to do data mining but this may act as a bridge helping to connect db via excel. Let me know your thoughts after exploring this option. :)
January 7th, 2010 4:04pm
HiYes, this is your lucky day. I have just tested it and have a screen dump for you should you want it.This is what you must do.1) Open a new workbook.2) Click the Data Tab.3) Click 'From Web' on the left hand side.4) Enter the URL of your report server e.g. http://ssc20460:8080/Reports_SSC20460/Pages/Folder.aspxThat's it.I trust that this is what you are looking for.Please let me know how you fare.regards Steve
January 7th, 2010 4:05pm
Hi RajI was looking to use the Report Model as the data source without having to create a report. I will search for the data mining add in as detailed.thanks Jason
January 7th, 2010 6:50pm
Hi Stevethanks for taking the time to respond, however I wanted to connect Excel directly to the report model without writing a report. thanks Jason
January 7th, 2010 6:54pm
Jason, I couldnt get what you meant by Report model? on the other hand, i'm packing my bags for vacation and happened to check this post. Will circle back on monday.. hopefully it should be resolved by then. :)
January 11th, 2010 7:54pm
There is no way to access a report model in Excel without creating a report in SSRS 2008. You can create a report and export it to Excel (not updatable) or use Excel's web page link feature to connect to a report in SSRS 2008 and refresh it as desired from Excel.In SSRS 2008 R2 (due later this year), you can use the new Excel PowerPivot feature to connect directly to a report model and perform data analysis over large datasets within Excel. This sounds like the feature you are looking for. Check it out @ http://powerpivot.com.
January 12th, 2010 1:29am
Also FYI there is a public beta for Excel PowerPivot and SQL Server 2008 R2 available now which you can try out.Download Excel PowerPivot from http://powerpivot.com Download SSRS 2008 R2 from http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx
January 12th, 2010 1:31am
Hi Aaron,I've been trying to find an answer for this issue as well and I noticed you said the following:'In SSRS 2008 R2 (due later this year), you can use the new Excel PowerPivot feature to connect directly to a report model 'Have you done that yourself, because I see no evidence of that in the documentation for PowerPivot. I'm just wondering if we're talking about the same report model. The Report Model I'm inquiring about is the .smdl semantic layer that is created using SSRS' report manager.
February 3rd, 2010 10:08pm
Yes, I am referring to .smdl report models, however I didn't get the details quite right. It is still necessary to create a report and then connect to the report using PowerPivot (which can be refreshed on demand). You cannot connect directly to a report model from PowerPivot without going through a report.
February 4th, 2010 9:44pm
Hi Aaron, If "PowerPivot for Excel" cannot directly connect to SSRS 2008 R2 report models, can you unmark the "answer"?
January 26th, 2011 9:07pm
Done. As stated above, you need to create a report and can connect PowerPivot to the report.
March 3rd, 2011 11:38pm