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

4 05 2015

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.

SSAS Tabular Modeling Article Series on SQL Server Pro Magazine

2 05 2015

I’ve just finished a series of four articles for SQL Server Pro Magazine, along with sample projects and hands-on exercises. The series will take you through SSAS Tabular model design from start to finish, using the Adventure Works sample data in SQL Server 2012 or 2014.  Here are links to all four articles followed by an excerpt from each.

Part 1 – Getting Started with SSAS Tabular
Part 2 – Easy DAX – Getting Started with Data Analysis Expressions
Part 3 – Tabular Model Administration
Part 4 – Deep Dive DAX – Solving Complex Business Problems with Data Analysis Expressions

Download the sample projects here:  Part 1, Part 2, Part 3, Part 4

Starting Your Modeling Career with Analysis Services Tabular Models Part 1

Four-part series

This is the first of a four-part series about getting started with Tabular model design using SQL Server Analysis Services in SQL Server 2012 and 2014.  You will learn the basics from start to finish and build a complete solution.  A sample project is provided for for each stage of the solution so you can follow-along with each article.

Article 1 excerpt:



Getting Started with SSAS Tabular

Microsoft’s introduction of the xVelocity in-memory analytics engine in 2010 is a significant game-changer for business intelligence and data management.  The availability of this impressive technology and its future application in new product features will change the way you store and consume data, and it’s at the heart of SSAS Tabular.  In this first in a series of four articles about designing and using Tabular models, you will experience how to create a model and analyze data.  In the articles that follow, we will add more sophisticated features to deliver production-scale solutions.

Why Choose Tabular?

On a server equipped with sufficient memory and CPU horsepower, Tabular SSAS can be very fast.  xVelocity, also known as VertiPaq, is the engine behind Tabular and Power Pivot.  In simple terms, it applies some foundational data principles elegantly to achieve high performance.  If you work with SQL Server databases, you know that table lookups are much faster when the columns used in a query are indexed.  Tabular take this principle to the next level.  The xVelocity column store essentially creates a separate index for every column and then compresses the data, eliminating redundant and sparse values.  Unlike other compression methods, the storage for each column is unaffected by adjacent columns.  For numeric, date and short text values; this approach can drastically reduce the size of the stored data.  All of the compressed data is loaded into memory so queries incur no disk IO cost and return results quickly.

In the sixteen year history of multidimensional Analysis Services, many features and capabilities were added to the platform to address some specific business needs and “newer” is not always “better”.  SSAS Tabular is not designed to be a comprehensive replacement for multidimensional Analysis Services.  It is, however, a streamlined platform for analytic reporting and data exploration.  Tabular is a ground-up re-engineered platform with a lot of solid business value today and even more promise for the future.  Think if it as a Formula One race car without cup holders and seat warmers; just designed to go really fast.  A number of publications and resources are at your disposal to compare both options and determine which best meets your requirements.  I encourage you to take Tabular for a test drive.

Create a Tabular Project

The Developer, Business Intelligence and Enterprise editions of SQL Server 2012 and 2014 support SSAS Tabular projects.  SQL Server Data Tools for BI installs with SQL Server 2012.  For SQL Server 2014, install any edition of Visual Studio 2012 or 2013 and then install the SSDT-BI add-in.  You should also have Excel 2010 or 2013 installed.  To follow-along, you’ll need the AdventureWorksDW2012 or AdventureWorksDW2014 sample database.  Links to these downloads and more background information are included in the Resources at the end of this article.


Article 2 excerpt:

Easy DAX: Getting Started with Data Analysis Expressions

The DAX language really serves three different purposes. Calculated columns perform row-by-row operations that are processed when data is loaded and the calculated results are persisted into the Tabular model. Measures are calculated at query time on a filtered set of rows. DAX is also a query language and can return an entire result set for reporting.

Continuing our Tabular modeling journey, we will use the project created in the first article, “Getting Started with SSAS Tabular.”

This article will give you a good start with DAX measures, and then we’ll resume and go deeper in the fourth article: “Deep Dive DAX – Solving Complex Business Problems with Data Analysis Expressions.”

Simple Measures

At its heart, DAX is a very simple expression language that, much like Excel, uses functions to perform operations on values and sets of data. The most fundamental functions are simple and easy to use, and then they get more complex and powerful. The first group of DAX functions you’ll learn are aggregates that roll up a range of values into a single result. These functions include SUM, AVERAGE, COUNT, DISTINCTCOUNT, MIN and MAX.

Article 3 excerpt:

Part 3 – Tabular Model Administration & Security

It’s common knowledge that SSAS Tabular and Power Pivot use the same in-memory engine and support the same essential features.  Tabular supports a number of features which take it from desktop and small group scale to a truly enterprise class business solution.  We explore two important enterprise features in the following exercise.

The first thing to understand is that a Tabular solution sits on the SQL Server Analysis Services foundation, which is a 14-year-old mature technology.  When a project is deployed, an SSAS database is created.  In SQL Server Management Studio (SSMS), you may notice the lack of objects that may be familiar to SSAS multidimensional users; like cubes and dimensions.  The database simply contains tables (some of which may be “measure group” type tables and others serve as dimensions.)

To follow-along, use the solution you completed in Part 2 or download the Article 2 Completed Project.

Managing the Workspace Database

When opening the Model.BIM file in the SSDT model designer, a user-specific workspace database is automatically deployed to the server specified in the model properties.  As a general rule, I don’t recommend the workspace database be on a production server. It should be on an instance where you have administrative rights.

Just a word about model stability, recovery and maintenance…


Article 4 excerpt:

Part 4 – Deep Dive DAX

The heart and soul of Tabular models is the DAX language.  In part 2 of this series you learned to use some of the basic DAX functions like SUM and CALCULATE.  You also learned about row and filter context and how functions like ALL() and ALLEXCEPT() can be used to override natural row context to change the scope of a calculation.  In simple terms, when you use aggregate functions like SUM(), AVERAGE(), MIN() or MAX() with a numeric measure value, the calculation is applied to all the rows in the context of current filters and groups.  This functionality is much like the behavior of a T-SQL query using a SUM() function and GROUP BY clause but that’s about where the similarities end.

The power of DAX goes well beyond emulating simple grouping and filtering logic.  It can be used to apply advanced business logic to perform complex calculations that would be difficult to duplicate with the same performance in relational tools.  For this final exercise, I’m going to queue some important topics you are likely to encounter, I’ll describe a reporting scenario and the calculations needed to address requirements and then I’ll step you through the exercise using the project we completed in the previous article.

User Interaction and Data Browsing

Analytical reporting is pretty simple in concept.  Typically every measure is going to be grouped and/or filtered by something so when designing calculations, measures and KPIs; we must anticipate different ways users might interact with a model.  The easiest way to think about this is in the context of an Excel Pivot Table.  Connected to the model, a user could place any of the table attributes on rows or columns. They could place any measure or KPI in the intersecting data cells to perform calculations grouped by the rows or column attribute values; and they can apply filters and slicers.

Conditional Calculations

Some calculations only make sense when performed in a certain context.  For example, if financial budgets are calculated for the end of each fiscal year and actual expenses are recorded for each month period, the logic for reporting on a daily, monthly or annual basis will be different and in some cases, may just not make any sense.

Read the entire series starting with Part 1 – Getting Started with SSAS Tabular


Get every new post delivered to your Inbox.

Join 306 other followers