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.







Different Options to Use D&B Cleanse & Match

3 04 2015

Dun and Bradstreet offers services to cleanse and validate business records from their comprehensive reference database.  There are different ways to use the service depending on the scenario and sophistication of the overall solution.  This slide, from my presentation called D&B Premium Options & Solution Scale which you can download and view in the Presentations page on my blog, shows three categories of solutions.

image

The Business Verification and Cleanse & Match service may be integrated and used in scenarios ranging from very simple for occasional desktop use to sophisticated and complex for integrated enterprise-scale solutions.  I have two presentations in video form that demonstrate these options.  These are on my YouTube channel and here on my blog.  SolidQ has recently entered into a partner relationship with D&B to help consulting clients find the best way to integrate D&B services.  If you need help in this area, please let us know.





Glossary of Terms Used in Microsoft BI Projects

14 03 2015

At the beginning of many new BI projects it can be challenging to get everyone using common terminology and language.  This is especially true when people have biases with different vendor products or may have learned to use terms with different meanings.  Miscommunication can be costly on a project so getting everyone on the same page is important.  This list is intended bridge the gap for technical and non-technical members of the project effort.

Microsoft Business Intelligence Solution Components

Using common terminology and language helps stakeholders, developers and project leaders to have more effective communication. It is very common for these and other terms to be used differently because of prior experience with products and tools. The following terms are most commonly used in Microsoft BI projects:

Dashboard

A dashboard is a style of report and analytic data visualization that brings important data to the surface for decision-making. Like the dashboard in a car, a data dashboard tells the operator what’s happening at an aggregate level without the clutter and confusion of finite detail reporting. Dashboards may contain links and actions allowing a user to drill-through to a detailed report outside of the dashboard.

Characteristics of an effective dashboard:

· Contains the most important business metrics that may be measures across different groups of categories and time periods.

· Emphasis on simplicity and actionable information.

· Each visualization should be consumable in a few seconds.

· Data dashboards are typically comprised of common visual elements that bring data together from different systems and functional areas of the business.

· Common dashboard elements include:

  • Summary metrics (a single measurement value)
  • Key performance indicators (KPIs) that show how a metric is performing against a target or goal. KPIs are often visualized as traffic light indicators or trend arrows.
  • Column and bar charts that show segmented metric values across different categories.
  • Trend charts that show metric values trending across time (e.g. years, months, days, etc.)
  • Sparklines that reduce chart visuals to a thumbnail to show a trend for each item in a series.
  • Gauges and bullet graphs that show how a metric compares to a target, goal or threshold; on a scale.

Dashboard style reports can be created using conventional report design tools (that developers use to create operational reports), or specialized dashboard design tools. Tool choice affects the sophistication of the dashboard, interactivity behavior and the level of effort needed for design.

Drill down

A drill-down action allows the user to view more details without leaving the dashboard. This is often done by expanding the levels of a hierarchy or double-clicking on a level to show members of the next level in the hierarchy. The key to enabling drill-down reporting is to understand hierarchal structures in the data and design them into a data model which is used to serve data to the dashboard.

Drill through

A drill-through action allows a user to navigate from a dashboard or report to another report to see greater detail. The typical drill-through experience allows the user to see more details within the context of the item they selected.

A well-designed drill-through dashboard solution keeps the initial dashboard simple and high-performing, allowing users to navigate to different reports in different subject areas and levels of detail.

Data model

Most modern dashboard and self-service data analysis tools rely on a specialized semantic data model to optimize data for analysis. Microsoft’s BI semantic modeling platforms include Analysis Services and Power Pivot. A data model is different compared to a relational database (like Oracle or SQL Server). Common terms used to refer to these models are “cubes”, “tabular models” and “in-memory models”.

Model data is stored in hierarchal structures with pre-defined aggregated measures, calculations and KPIs. Reading data from a semantic model is typically faster and easier using tools designed for business analysts rather than application developers. Model data can also be used by developers to create more sophisticated reports and dashboards. In a well-designed BI solution, a relational database (called a data mart or dimensional model) is usually created as the foundational data source for the semantic model and then data from that database is processed into the model.

Data mart

A data mart is a relational database created specifically to store analytic data that will be processed into a semantic model for reporting. There are different philosophies and approaches in the industry for preparing data for analysis but the essential concepts are the same. A data mart may either be a component of, or an alternative to, a larger data warehouse. Commonly referred to (in general terms) as “star schema” or “dimensional model”, a data mart holds a copy of data intended for reporting. In many modern Business Intelligence solutions, data moves through three (or more) different storage layers: operational database systems, data mart and the semantic model.

* Prior to the proliferation of semantic modeling technologies like SQL Server Analysis Services, dashboard-like reports often queries the relational data mart or data warehouse directly. However, modern tools like Power View and Excel are optimized to query cubes and tabular models. Therefor the term “model” now refers to a semantic model or cube and the term data mart refers to a relational database.

Reporting Services

SQL Server Reporting Services is a product included in the Microsoft SQL Server suite. SSRS reports are typically created by skilled report designers or developers using one of these report design tools:

· Visual Studio (an application development tool used by IT developers)

· SQL Server Data Tools (“SSDT” is a simplified version of Visual Studio that installs with the SQL Server management tools)

· Report Builder (a simplified version of the design tool for moderate-advanced skilled designers)

Compared to self-service tools like Power View, SSRS offers the advantage of more sophisticated and intricate dashboards & reports. These reports take more effort and time to develop. A server licensed for SQL Server can be used as a report server with no additional licensing costs. A new report server must be licensed according to Microsoft’s SQL Server licensing policy.

Power View

Power View is a self-service dashboard and analysis tool that is highly interactive and easy to use. Dashboards and reports can be created by a report designer and saved to SharePoint for users. Users with moderate data skills can create their own dashboards and reports. Power View requires Analysis Services cubes, tabular models, or Power Pivot models; and cannot be used with data in a relational database.

This tool emphasizes interactive data exploration, and slicing and filtering data in a semantic model but it doesn’t have the same level of sophistication to customize reports as Reporting Services. The built-in data slicing, interactive filtering and quick design experience are superior to SSRS.

Power View is currently (as of march 2015) integrated into four different Microsoft products:

  • Using SharePoint that has been configured for BI integration, Power View reports can be created by SharePoint users without installing special software (just the Silverlight add-in for your web browser).  Reports are saved to a Gallery type library and can be shared and used by many users.
  • Power View is a pre-installed add-in for Excel 2013 ProPlus edition that can be used on a desktop computer by one user at a time.  The data analyzed in this way can either be a Power Pivot model stored in the same Excel workbook or an SSAS Tabular model on a shared server.
  • Using SharePoint within your organization or SharePoint Online (Power BI option for an Office 365 Enterprise subscription), an Excel Power View report can be published and shared by users in your organization.
  • Although not referred to as “Power View”, the next version of Power View features are part of the new Power BI subscription that is currently in preview.  It is a less-expensive cloud-based subscription that doesn’t require an Office 365 subscription or Excel.

SQL Server

Microsoft relational database platform (similar in many ways to Oracle, IBM DB2 and MySQL) is used to manage data in enterprise-scale databases. SQL Server is used for transactional/operational databases (supporting applications to run the business.) In a BI solution, SQL Server is typically used for ETL data staging, data warehouses and data marts.

Analysis Services

Also a component of the SQL Server product suite, Analysis Services is a data storage and semantic modeling technology used to optimize data for dashboards, analytic reporting and self-service analysis. SSAS now comes in two flavors: multidimensional and tabular. The conventional multidimensional product, which was introduced with SQL Server 7.0 in 1998, is both sophisticated and mature. The tabular option, introduced with SQL Server 2012, is less feature-laden but can be faster to develop and easier to implement. Tabular models run entirely in-memory which makes them fast, typically without the need for advanced tuning.

Tabular is available in SQL Server Developer, BI and Enterprise editions. Standard edition includes SSAS multidimensional with a limited feature set. SSAS Tabular runs on the same service platform as multidimensional and has a similar management experience. Both options have pros and cons due to the relative maturity of the development tool, advanced features and existing design skills that may favor the established tool.









Follow

Get every new post delivered to your Inbox.

Join 306 other followers