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.

14 thoughts on “How to Get SSRS Reports in Excel Without Exporting from Reporting Services

  1. Paul, this looks great, but I am confused that in your video the values shown in Excel do not match those shown in SSRS.
    For example, at around 11:50 you sort using the month number you have added to the SSRS report. In SSRS the month numbers correspond to the month names correctly, but in Excel the numbers are different and you end up with the months sorted as January, February, April, March, June, May…
    This is also the case for the Order Qty column, the values do not match.
    It looks like some of the values are being doubled (February, April and June) and some tripled (the rest of the months) when shown in Excel.
    Any thoughts on why this is happening.

    Like

  2. Hi,
    How do I get the “feed” option added to our SSRS? It’s not on any of our SSRS screens. Is it an add-on? A separate product? We are on version 2008 R2. I can’t find any other details about this on-line.

    Like

    • As of SSRS 2008 R2, the atom feed is a standard feature. When viewing a report, you should see the orange feed icon in the toolbar. This can be disabled by an administrator. An easy way to double-check is to install SSRS on a development machine with default settings.

      Like

  3. Just a quick note about something in the video: Excel can actually recognize month names spelled out or abbreviated, you just have to tell it to sort by custom list instead of alphabetically (on the field in the Pivot Table, choose More Sort Options, and change the First Key Sort Order) . So adding the month number to the SSRS report was unnecessary.

    Like

    • Erik, I don’t have a definitive answer for you but I’m interested in what you discover. The documentation and field experience with this topic isn’t particularly thorough. Please share what you find on this.

      Like

  4. For option # 3, I searched in YouTube for “SQL Server Reporting Services Atom Feed” but could not locate any videos. Any pointers would be appreciated

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s