{"id":1702,"date":"2013-09-26T00:00:00","date_gmt":"2013-09-26T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/ssrs-reports-as-a-data-source-in-excel-2013\/"},"modified":"2021-05-17T18:33:17","modified_gmt":"2021-05-17T18:33:17","slug":"ssrs-reports-as-a-data-source-in-excel-2013","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/ssrs-reports-as-a-data-source-in-excel-2013\/","title":{"rendered":"SSRS Reports as a Data Source in Excel 2013"},"content":{"rendered":"
\n

As DBAs, our bosses often assume blithely that, among all the urgent tasks and car crashes of our daily DBA existence, we find time somewhere to remain “current” on most database and data-related technologies, or really anything with the word “SQL” somewhere in its name. At any given point, they expect us to be able to converse intelligently on SSRS, SSIS, SSAS as well as BI analytics, big data, and so on.<\/p>\n

Back in January<\/a>, I described my brief foray, as a DBA, into the world of BI analytics, marrying my reports with various elements of the Microsoft BI “stack” to produce interactive PowerView reports in PowerPoint. The results were mixed and aside from adding yet another technology to my “must learn” list (Data Quality Services), the main upshot is that I now only pretend to be a BI professional on the weekend.<\/p>\n

Still, the BI bug didn’t leave me. I still browse the BI-related online documentation, keep my test environments up to date, and what I’d learned left me eager to find out more about a whole raft of BI-related technologies, and especially the new features of Excel 2013.<\/p>\n

In particular, I became intrigued by the possibility of using an SSRS report directly as a data source for Excel. As new Excel features go, it’s one of the more obscure, I will admit, and yet its potential seemed to jump out at me. The life of an SSRS report begins with a request, like any other development effort. This request is then passed to a designer or developer who may create tables, views, stored procedures, ETL processes, and ultimately a report that is distributed either in an ad-hoc or scheduled output to the requestor. In short, it’s a substantial development effort. If we need another incarnation of the report in Excel, surely it’s a lot of unnecessary duplication to access the underlying tables or code and develop it from scratch. Why not just use and adapt the developed report with its data feed and parameters? If I were a report developer, I could see that it is now possible to, in a single application, tie into the hard work and creativity of others and take full credit. OK, that is a stretch and unfair, but the possibilities seem clear.<\/p>\n

SSRS Reports as Data Feeds<\/h2>\n

Open Microsoft Excel 2013. I am going to assume that you have Excel 2013 and all of its various data add-ins. Everyone seems to have it these days, because it is the porthole into which you will view most of the new BI tools and services in “The Stack”. Open a workbook and navigate to the PowerPivot<\/strong> tab and click on Man<\/strong>a<\/strong>ge<\/strong> on the Data Model<\/strong> tab. This will bring up the PowerPivot for Excel<\/strong> workbook. Click on Get External Data<\/strong>, and you will see that we can retrieve data from a database, including SQL Server and MS Access (my old standby), or from Data Service, or from “Other Sources”, which is the one we need. Scroll down to the Data Feeds<\/strong> section and you should find the Report<\/strong> option.<\/p>\n

\"1876-3627892e-a3fc-4dc7-8ced-3fadc19cbfc\"<\/p>\n

Figure 1 – Data from Other Sources in PowerPivot<\/p>\n

Of course this is not a new idea. Data feeds like this use Open Data Protocol or OData to access data via a standard format, in the case of SSRS, XML-based Atom. You can learn more about OData, Atom and JSON here:<\/p>\n

http:\/\/www.odata.org\/documentation\/odata-v3-documentation\/atom-format\/#12_Collections_of_Primitive_or_Complex_Scalar_Values<\/a><\/p>\n

SSRS Disk Space Report as Excel Data Feed<\/h2>\n

So let’s see how this idea of pulling data from a feed like SSRS translates to a pragmatic alternative data source in Excel. In this example, I’ve used one of my existing SSRS reports, one with few parameters that the DBA team uses to monitor the drive space on their SQL Servers, although almost any report will do (ones with row-based detail, or summarized reports, work well).<\/p>\n

Simply enter the URL of the ReportServer<\/code> Web service, which was on my local system in my case, at http:\/\/localhost:8080\/ReportServer, and we’re presented with a list of folders on the report server.<\/p>\n

\"1876-e2240cfc-76a6-42a0-a322-6d1e49e00a4\"<\/p>\n

Figure 2 – Opening a Report from the ReportServer instance.<\/p>\n

In the DBA_Rep<\/strong> folder, I have the report called SQL Drive Space<\/strong> that contains a summary of used and available disk space on each drive for each SQL Server. It also calculates at what percent capacity is each drive, based on the size of the entire volume and the total size of all of the databases. Open the report, and it will load into the Table Import Wizard<\/strong>. In Figure 3, you can see that the “I” drive on server 0LCRPQSK<\/code> is 89% full.<\/p>\n

\"1876-9cfcde1c-548d-4e97-a466-82bb61c3864\"<\/p>\n

Figure 3 – SQL Drive Space Report Used as a Data Source in Excel<\/p>\n

The report has one parameter, High Usage<\/strong>, which is nothing more than a filter that when applied displays high usage on only those disks that are above a certain capacity threshold, in my case above 30% (at the time I created this report I was one paranoid DBA). Notice also that the high usage cases appear in scary red.<\/p>\n

The main reason I am noting the parameter is because I want to understand how the report will function as a data source, depending on the parameter value. Will the report correctly display more or less data based on the value? How will refresh options work? Will I be able to see the report parameters when I refresh? What happens when I add a calculated column to the model? These are all fair questions and there is only one way to finds the answers, and that’s to try it out.<\/p>\n

Choose High Usage<\/strong> instead of All<\/strong> as the parameter value and click Next<\/strong>, before I think of any more questions. On the next screen, we have the option to give the table a Friendly Name<\/strong>; I chose Amity<\/strong>, since it’s a very friendly name but also it reminds me of a certain shark who will eat all of my disk space. There is also a Preview and Filter<\/strong> button that presents a grid of data and the ability to check or uncheck individual columns that you may r may not want to include in the data model. I am going to go with the default, which is to select all of the columns.<\/p>\n

\"1876-49f7bd6a-87ef-4bf3-b6b4-e9fbfe7ffa7\"<\/p>\n

Figure 4 – Previewing the SSRS Data.<\/p>\n

After clicking OK<\/strong> on the preview window and then clicking the Finish<\/strong> button, I can see that I imported 364 rows into the new table called Amity<\/strong>, as shown in Figure 5. Now I can set about answering some of those nagging questions.<\/p>\n

\"1876-tableimport-d229dc6b-87c9-4fc5-b47c\"<\/p>\n

Figure 5 – Successful Import of Rows<\/p>\n

The first question regarding a refresh is an easy one to answer. The import and the parameter selection is a onetime process and the parameter choices are stored with the data model. Clicking the Refresh<\/strong> button will not bring up the report to allow me to choose other parameters. However, it is possible to Edit<\/strong> an Existing Connection<\/strong> to change the parameter values and overwrite the existing model data with new data, filtered by other parameters.<\/p>\n

Let’s edit the exiting connection “Report SQL Drive Space” and choose ALL<\/strong> instead of High Usage<\/strong> for the parameter, as shown in Figure 6.<\/p>\n

\"1876-2a6c2e96-cf9d-492d-bb5e-6032ef07312\"<\/p>\n

Figure 6 – Editing the Existing Connection for the Data Model<\/p>\n

So, it is possible to use a single report to modify the data model within Excel by editing the existing connection. It is a manual process, for sure, and users would need to be trained on how to perform this task. But it is an important point to make as I could see Excel power users wanting the ability to pull in filtered data from the source as opposed to pulling in all of the data and then filtering it locally in Excel.<\/p>\n

At this point we are still working exclusively on the data model, though we certainly could pop open a pivot table or pivot chart and prepare a report for publication, perhaps to SharePoint, or even to send to another user.<\/p>\n

Adding a new column to the Data Model<\/h2>\n

Before we do that, though, let’s add a simple calculated column called Total Capacity<\/strong>, which will simply sum the Megs Free<\/strong> and Total DB Size<\/strong> columns.<\/p>\n

All we need to do it put this formula, =[<\/strong>Total_DB_Size<\/strong>]+[<\/strong>MBFree<\/strong>]<\/strong>, in a cell in Add Column<\/strong>. To make it even simpler, you can click the column headers and they will be placed automatically in the formula, instead of having to type. So for the simple formula above all you would really need to type in would be an “=” and “+”. As a DBA and not an Excel Master, this was a leap forward in my thinking. Please don’t share that revelation with anyone. When done, the model looks like Figure 7.<\/p>\n

\"1876-ca21598c-9d14-4a90-85f4-a04c8ee2427\"<\/p>\n

Figure 7 – Adding a Column<\/p>\n

PowerPivoting on a SSRS Report<\/h2>\n

Now we have the model populated from the SSRS report and have added a calculated column. Now it is time to visualize. We can use a Pivot Table, Pivot Chart or PowerView to work with the Amity<\/code> table in the model we just created. Let’s use a simple PivotTable. Simply click on the PivotTable<\/strong> button in the toolbar and you will be prompted to create a new worksheet or use an existing one to place the PivotTable. I will choose Sheet1<\/strong>. As shown in Figure 8.<\/p>\n

\"1876-f89ddcb1-be2f-4452-b517-4d62ef4c629\"<\/p>\n

Figure 8 – PivotTable for Amity Table<\/p>\n

I could spend the next 10 pages demonstrating PivotTables, PivotCharts and PowerView in more detail, but our focus is the utility of the SSRS Data Feed more rather than the finer points of Excel maneuverability, and I am going to make an assumption that you may already be familiar with building PivotTables.<\/p>\n

\n

PowerPivot reports in Excel 2013<\/h4>\n

If you need a primer or refresher, I recommend the tutorial at http:\/\/technet.microsoft.com\/en-us\/library\/gg413497.aspx<\/a>. If you prefer videos, try https:\/\/support.office.com\/en-ie\/article\/power-view-and-power-pivot-videos-4386de23-041a-4792-b6ec-c40f72ecf693<\/a>.<\/p>\n<\/div>\n

Figure 9 shows the end result of my power pivoting.<\/p>\n

\"1876-25757beb-f76a-4822-aa4f-a647b532361\"<\/p>\n

Figure 9 – A PivotTable from the SSRS Data Source Using Conditional Formatting.<\/p>\n

So there it is, the data model generated from an existing SSRS report. All of the logic, parameters, connections and calculations from the original report can be reused here inside of Excel. As data changes in the underlying data source, we can refresh the Excel report with a simple click of a button. It is now possible for me to distribute this report to the Spreadmart universe and if the user has access to the report then they will be able to refresh, manipulate and analyze the same data.<\/p>\n

The next steps for this test would be to deploy this worksheet to SharePoint where users can then take advantage of PowerPivot’s Analysis Services component as well as PowerView in SharePoint. What’s more, the data model itself can now be a data source for other applications. I have certainly just touched upon the possibilities.<\/p>\n

Summary<\/h2>\n

As DBAs we are expected to know how to administer the technologies that are available with and peripheral to SQL Server. And to properly administer them, it certainly helps to understand the technology from the point of view of the user. By using an existing SSSRS report as a data feed for Excel, these users can now take advantage of development efforts in new ways, rather than shouldering the entire burden of writing their own reporting queries, or conversely relying solely on IT to develop new models and universes from ODS and EDW systems, which take long months to complete.<\/p>\n

By following an interest to make use of existing development efforts via an SSRS report as a data feed to Excel, I touched upon several technologies in the BI stack, but have only really just started to understand what can be achieved. I will continue exploring interesting new features like the Windows Azure Marketplace and Data Alerts for SSRS reports as to examples. I hope this article gave you at least a good starting reference for an otherwise hidden gem of a data source.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

DBAs are expected to know how to administer the technologies that are available with and peripheral to SQL Server. To properly administer them, it certainly helps to understand the technology from the point of view of the user. By using an existing SSRS report as a data feed for Excel, Rodney Landrum explains how these users can now take advantage of development efforts in new ways.…<\/p>\n","protected":false},"author":221800,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[4178,4170,4643,4379,5614,4179,4150,4151,4400],"coauthors":[11298],"class_list":["post-1702","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-bi","tag-database-administration","tag-excel","tag-reporting-services","tag-reports","tag-source-control","tag-sql","tag-sql-server","tag-ssrs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1702","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221800"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1702"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1702\/revisions"}],"predecessor-version":[{"id":77786,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1702\/revisions\/77786"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1702"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1702"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1702"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1702"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

My main focus for this article was to show how to use an SSRS data source so I won’t go deeper into the BI Semantic Model (BISM) or Data Analysis Expressions (DAX), which could be used to further develop the data model. You can learn more about DAX here: http:\/\/office.microsoft.com\/en-us\/excel-help\/data-analysis-expressions-dax-in-powerpivot-HA102836919.aspx<\/a>.<\/p>\n