How to Get SSRS Reports in Excel Without Exporting from Reporting Services

I’ve recently seen a wave of questions from clients and peers about difficulties exporting reports to Excel lately.  Every few weeks I get a call or question about this.  This topic has been a recurring theme for a very long time and one that I have encountered many times over the past – oh, eleven years or more – using SSRS.  Business users like Excel because it’s what they know and they can reformat and manipulate data in a workbook.  People like Reporting Services because all the hard work of connecting to data sources, writing queries, totaling, grouping and formatting the results gets done once and then all they need to do is run the report.  Users want the best of both worlds and they expect that when they export a report to Excel that they should have their cake and eat it.  In other words; they should be able to get a report, with all the goodness of headers, scrolling regions, pagination, interactive sorting – you name it – to work exactly the same way in Excel.  Many improvements have been made to all of the report rendering extensions over the years and the Excel export story is better than ever but here’s the deal – bottom line – the more rich formatting and advanced functionality that gets designed into an SSRS report, the less likely it is to export all that functionality to Excel or any other rendering format.

I’ve come to realize that if users really want their data in Excel, rather then Reporting Services, they should be using Excel!  Before you laugh or blow this off, please consider that there is a whole spectrum of opportunity to use Excel as a serious reporting and analytic tool with or without SSRS in the mix.  If you know me, you know of my love for Reporting Services but I’ve learned to open my mind to other options and I hope to convince you to do the same.  Six or seven years ago, the BI and reporting product teams at Microsoft came to the realization that Excel was much more than a spreadsheet application and people were using it for all kinds of serious business functions.  A shift in thinking took place which resulted in add-in tools like Power Pivot, Power Query, rich native visual components and a slew of new Office Apps.  If you understand Power Pivot you know that it’s not really an extension of Excel but rather an analytical technology that you can get to from Excel – and then Excel has all kinds of functionality to leverage “the Data Model”, or Power Pivot. Likewise, Power Query is essentially and ETL tool that can be enabled as an Excel add-in but isn’t really part of Excel.  Of course user perception is that these are Excel features but they’re actually Excel extensions that can also be used externally in tools like the Power BI Dashboard Designer.

I digress… realistically, what other options do you have to get richly-formatted data into Excel – in native form – without all the baggage to comes with exporting from and SSRS report?  I’ll enumerate some options and then explore them.

1. Connect to data sources directly from Excel and build reports using tables, Pivot Tables and Pivot Charts.  I see this option dismissed frequently because some Excel users don’t realize the power and flexibility of Pivot Tables with filters and slicers.  It doesn’t take a lot of work to flatten a Pivot Table so it looks and works just like a grouped table or matrix in SSRS.  Conditional formatting, data bars, sparklines, KPI indicators, Excel filters and slicers rock when they’re used correctly.
2. Use Power Query and/or Power Pivot to import data and then build Excel reports.  These tools are extremely powerful but the skill ramp-up can be significant for a novice Excel jockey without some special training and experience.  They will also need Excel ProPlus and probably the 64-bit version of Windows and Office installed.  Businesses are successfully creating a data analytic culture by arming their users with these tools but it’s not for everyone or every business.
3. Use an Atom Feed to stream live data from a Reporting Services report to an Excel workbook.  This option has the advantage of being both simple and flexible but it can solve many problems with one fairly simple solution.  The report datasets have already been written and the data is structured the way users need it.  A preformatted Excel report is created with the connection.  The user simply opens the Excel file and data is automatically refreshed in the right format.  If they want to change parameter values, all they need to do is resave the Atom Feed file and the workbook magically sees filtered data based on the updated parameter selection.  I’ll post a more detailed follow-up to demonstrate just how cool and flexible this capability is.

I’m most encouraged by option 3 as an extension to option 1.  Excel truly is the undiscovered business reporting tool that is on every user’s desktop and learning to use it in concert with these add-ins and SSRS Atom Feeds can literally give you the best possible combination of options.  With a little preparation and very little user training, business users truly can have it all.  In many cases, they won’t even know the difference but they’ll be delighted when Excel behaves exactly like …Excel.

The following video walk-through shows this technique.  Rather than exporting to Excel, using a data feed from a Reporting Services report lets users utilize native Excel features and formatting instead of creating a static copy of the report in a workbook.  For true spreadsheet users, this means that they can are able to use Pivot Tables & charts, filters, slicers, subtotals, totals & formulas with report data just like they would with any other analytic data in Excel.  When a workbook is opened or refreshed, data is consumed by the data sources and datasets using parameters in the report, and then fed to the workbook as a live data source.  Security & complex queries are managed within the report and users get all the flexibility they want in Excel.