 Use OfficeWriter and Reporting Services to Create Excel Spreadsheets with Live Links to Analysis Services Cubes
By Chris Webb
IT Consultant, SQL Server MVP
One of the biggest complaints about SQL Server Reporting Services’ out-of-the-box functionality is that when you export a report to Excel you only get static values pasted into cells in the resulting workbook – all connections with the underlying database which you used to create the report have gone. This is a particular problem when you’re working on a business intelligence project and you’re chasing after that elusive ‘one version of the truth.’ As soon as that export happens, the data in Excel gains a life of its own. It can (and probably will) be used to create new reports or be emailed out to other users, and all the time it will be getting more and more out of sync with the original database.
When it comes to Analysis Services and Excel 2007, there are two main methods of connecting to a cube to ensure that the data you see in your workbook is always up-to-date. As with previous versions of Excel, you can connect a pivot table to Analysis Services and query the cube directly; what’s new in Excel 2007 is that you can then opt to convert that pivot table to a range of cells which use a set of new functions to retrieve individual values from your cube. There’s a great explanation of what these new ‘cube’ functions do on David Gainer’s blog, here:
What I’ll be describing in this article is how you can use OfficeWriter and Reporting Services to generate Excel workbooks which use these new functions so that after you’ve exported your data to Excel you can ensure it stays synchronised with the data in your cube. I’m going to assume you already know how OfficeWriter works - if you don’t, you may want to take a look at some of the online documentation available on the SoftArtisans web site here: http://officewriter.softartisans.com.
First of all, let’s take a look at the kind of workbook we want to be able to generate. Open Excel 2007 and click on the Data tab in the ribbon bar. To open a connection to an Analysis Services cube go to the ‘Get External Data’ group, click on the ‘From Other Sources’ button and select ‘From Analysis Services’ in the dropdown menu. Type in the name of your Analysis Services server, click Next, select the database you want to connect to and then a cube, click Next again, type in a suitably friendly name for your connection and click Finish. Once you’ve done this, an empty pivot table will appear and you can drag and drop dimensions and measures to query the cube; after doing so then click on a cell in the pivot table so that the Option tab appears in the ribbon. Then click on the ‘OLAP Tools’ button in the ‘Tools’ group and select ‘Convert to Formulas’ in the dropdown menu. The pivot table will then disappear and in its place you’ll see cells containing the same values that were in the pivot table, but which instead use the new ‘cube’ functions to retrieve their data. Generating this kind of report in Reporting Services will be our goal.
To start, we need a Reporting Services report which uses data from Analysis Services. If you’ve got one handy then you can use that - if not, it’s quite easy to use the wizard to generate a report for you. Next, open up the report and go to the data tab and you’ll find the MDX SELECT statement that’s used to query the cube. Our first major problem is going to be how to translate an MDX query like this into a group of Excel cube functions, and to do this we’re going to need to understand how MDX addresses values in a cube. Take the following MDX query that can be run against the AdventureWorks cube:
SELECT [Measures].[Internet Sales Amount] ON COLUMNS ,
[Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS
FROM
[Adventure Works]
WHERE([Product].[Category].&[1])
It displays the measure Internet Sales Amount on the columns axis, all Calendar Years on rows and slices the data by the Product Category Bikes. The topmost cell in the result set therefore displays the value of Internet Sales Amount for Calendar Year 2001 for Bikes and this value can be addressed directly in MDX using a tuple of the form:
([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Year].&[2001], [Product].[Category].&[1])
The Excel function to get a value from the cube is CUBEVALUE(), and you can use this function to get the same value in an Excel cell as follows:
=CUBEVALUE("MyConnectionName"," ([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Year].&[2001], [Product].[Category].&[1]) ")
Getting the tuple that represents the value for each cell in a query isn’t straightforward. You could create an MDX expression which returned the unique name of the current member on each hierarchy of each dimension, but if you were using a lot of hierarchies in your query the expression could become very long and be difficult to maintain. The most elegant solution is to write an Analysis Services stored procedure and luckily there’s one already available called FindCurrentTuple() which does the job. You can find it in the “Analysis Services Stored Procedure Project” which can be downloaded here:
http://www.codeplex.com/ASStoredProcedures
(Please Note: At the time of writing, this function isn’t included in either of the downloads for the last major release, 1.0 Beta, shown on the Release tab of the project site. In order to use it you need to download the latest version of the source code, compile it and then register the resulting ASSP.dll on your Analysis Services server.) You can then use it with your original query by adding a WITH clause and declaring a calculated cell in it as follows:
WITH CELL CALCULATION [ShowTuple] FOR '*' AS
'"=CUBEVALUE(""MyConnectionName"",""" + ASSP.FINDCURRENTTUPLE() + """)"'
SELECT [Measures].[Internet Sales Amount] ON COLUMNS ,
[Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS
FROM
[Adventure Works]
WHERE([Product].[Category].&[1])
The results of this query look like this:
As you can see, for every cell in the query we’ve now got the Excel cube function equivalent. You can now use this query in a new dataset in our Reporting Services report and either just save it for later use or deploy the report up to our Reporting Services server.
Before we move on, I need to point out some shortcomings in this technique. First of all, the stored procedure I’ve used will raise an error when there is a set in the WHERE clause, although it probably could be modified to handle this scenario. More importantly this technique cannot be used when a subselect is used in the FROM clause or when session or query-scoped calculated members are used in the query. These are quite significant limitations but since neither subselects nor query or session-scoped calculated members can be reproduced using the Excel cube functions anyway they can probably be lived with.
Having done this, you can now move on to creating an Excel template using the OfficeWriter Designer. Open up Excel and then on the Addins tab of the ribbon bar click the ‘Open Report’ button from the OfficeWriter Designer addin in the ‘Custom Toolbars’ group. You can then either select the .rdl file containing the report you just designed from a local drive or you can connect to your Reporting Services server and select the report from there if you deployed it earlier. Next, click on the ‘Select Query’ button and choose the dataset from that report that returns the Excel cube function values; you can then go to the ‘Insert Field’ button and place data markers for one or all of the fields from the selected dataset in the cells. You can also enter the data markers yourself manually. Using the query from earlier, here’s an example of what a worksheet containing data markers might look like:
What I’ve done here is use two sets of data markers: the ones in cells A1 and B1 contain markers which will be replaced by the field names of the two columns from my data set (“Calendar Year” and “Internet Sales Amount” respectively), and the ones in A2 and B2 will be replaced by the data itself. You can, of course, add any amount of other formatting, charts and Excel formulas to augment this template and make it as user-friendly as possible.
Once you’ve done this (and it is important to follow these steps in order), you need to add two more things to the workbook. First of all, you must add a connection to the Analysis Services cube you want to query. The Excel cube functions are no use without a connection, and this is the reason why you have to use OfficeWriter for this process and not Reporting Services’ own Excel rendering functionality: Reporting Services can’t generate Excel workbooks with data connection objects in them.
The easiest way to add a connection to the worksheet is simply to follow the steps given earlier to connect to Analysis Services and create a new pivot table. Then convert the Excel cube functions and delete the formulas from the resulting cells. You can test to make sure your connection is still there in the worksheet by going to the Data tab of the ribbon bar and clicking on the ‘Connections’ button in the ‘Connections’ group – you should see your connection listed in the dialog box that appears. It’s important when creating a connection to make sure its ‘Friendly Name’ is the same as the name you’ve given for the connection in your Reporting Services query; in the earlier example I used the name “MyConnectionName”.
Next you have to add the following line of VBA code to the Open event of the Workbook:
Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
(If you’re looking for where to edit VBA code in Excel 2007, the Developer tab on the ribbon is hidden by default – see the following article on how to display it: http://office.microsoft.com/en-us/excel/HP100141111033.aspx)
This simply does a search and replace on the equals sign character (“=”), replacing it with itself. This might seem somewhat pointless but it is necessary: when Reporting Services renders an Excel spreadsheet using OfficeWriter it injects values into the Value property of cells. This means that even if you are injecting a formula definition you will only see the text of the formula rather than the value the formula returns. Excel formulas are held in the Formula property of a cell and OfficeWriter does not support injecting values into this property, unfortunately. However doing this search and replace operation when the workbook opens is the simplest way to get Excel to recognize the formula expressions for what they are, so your cube functions will actually return the values you want to see.
The template is now ready to be deployed up to the Reporting Services server. Clicking on the ‘Publish’ button in the Addins tab will allow you to choose which folder you want to deploy your report to on the Reporting Services server. If you previously deployed the report you created in Visual Studio you will probably want to overwrite it. You should now be able to go directly into Report Manager to your new report and it should behave identical to the report you originally designed in Visual Studio but with one important difference: when you select the ‘Excel (.xls) designed by OfficeWriter’ export option you will download a new Excel workbook with cube functions in place. Depending on how Excel 2007 has been configured you may not see your data immediately, though. If there is a security warning visible below the ribbon bar like this:
…you’ll need to click on the ‘Options’ button and explicitly enable both the macro and the Analysis Services connection in the workbook. Once you’ve done this, though, you should see the cube functions evaluated and the values from the cube displayed.
There are a number of different ways in which the technique described in this paper could be built upon. For instance only the CUBEVALUE() function has been used, but the CUBESET() and CUBERANKEDMEMBER() functions could be used to create more sophisticated reports. Equally, several third-party Analysis Services Excel addins are available which use similar functions to retrieve data from Analysis Services and this technique could probably be used with them as well. Finally, although dynamically creating pivot tables directly connected to Analysis Services is a much harder task than using the ‘cube’ functions, it would be possible to create a template spreadsheet which contained a pivot table based on cells within the same workbook and which were in turn populated using the ‘cube’ functions. Hopefully, though, this paper has demonstrated just how easy it is to get Reporting Services to maintain live links to an Analysis Services cube when generating Excel workbooks.
To conclude, you’ve seen how to use OfficeWriter and Reporting Services to solve the problem of out-of-date data in your Excel reports. This is an incredibly powerful technique that illustrates the kind of ambitious solutions that are possible with OfficeWriter’s Excel workbook generation capabilities and template-based reporting paradigm. Hopefully you’ll be able to adapt it to your own project’s requirements and show your users the benefits of a centralized data store and reporting solution while letting them carry on using their favorite tool, Excel, to create reports.
Chris Webb works as an independent consultant specializing in the Microsoft BI toolset. He is the co-author of the book “MDX Solutions with Microsoft SQL Server 2005 Analysis Services” and blogs on BI matters at http://cwebbbi.spaces.live.com. | 
