Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Excel Actions and Drill Down for SQL Server Analysis Services


By:   |   Read Comments (7)   |   Related Tips: > Analysis Services Development

Problem

I am using Excel to access my SQL Server Analysis Services cubes and I want to give end users additional drill down functionality.  Can you let me know what Excel drill down actions are available for SQL Server Analysis Services (SSAS)?

Solution 

Of all the available client tools for SSAS, Excel pivot tables are likely (and by far) the most popular method for looking at SSAS data. Certainly, pivot tables are exceptional at providing users with the ability to quickly and accurately query and navigate a cube and its measures and dimensions. The ability to use Excel to place this data at a user's fingertips and then allow them to slice and dice data is truly a great tool. However, there are times when a user needs more data, such as a detailed list of customers, or needs a set of data not in a pivot format for input into another system. Alternately the date may be needed for further analysis or maybe a link needs to open to another system for further input. From my experience, the drill down / action functionality available for OLAP pivot tables created in Excel is an often overlooked and underutilized set of functionality. 

SSAS Drill Down Actions

 Actually, SSAS provides nine different drill down actions including:

  • Dataset-returns a data set
  • Rowset-returns a row set
  • Drillthrough-returns a predefined drill down statement
  • Report-runs report server report
  • Commandline-runs a command prompt command
  • OLE DB Statement-runs an OLE DB statement
  • URL-opens a web page
  • HTML-runs an HTML script
  • Proprietary- runs a command not listed above

To be able to see some these actions "in action", we will use the AdventureWorks 2012 MultiDimensional database which can be downloaded on Codeplex at: http://msftdbprodsamples.codeplex.com/releases/view/55330.

Once you have installed the 2012 SQL Server and SSAS databases and have processed the SSAS database, you will want to open up SQL Server Data Tools (SSDT) which replaced Business Intelligence Development Studio (BIDS). Once SSDT is opened and you have navigated to the Adventure Works project, double click on the cube and then navigate to the Actions tab as shown below. Although we will not have the ability to go over all the available actions, we will cover three of them.

Action Tab

Once on the actions tab, three action buttons are available. As noted below, the three action buttons are grouped together on the action tab. The first button, New Action handles all the actions except a Drillthrough action or a report action. The middle button handles the drillthrough actions while the last button handles a new reporting action.

New Action

New Drill Through

New Report Action

SSAS URL Action

The first action we will explore is the URL method. This method allows for a URL to be passed to the default web browser when the user executes the action in Excel. This example, as illustrated below, will use the postal code field to open a browser session (default browser is used) to Bing Maps, mapped to that postal code.

The following items needed to be completed for the action. First an action name needs to be specified; this name is the one that will show up in Excel. Next, the target type and target object are selected. The target type defines the object type to be used such as attributes members, dimension members, cube, cells while the target object is filtered based on the selected type. For this example the attribute member type is selected and then the Geography > Postal Code attribute is selected. These items define which fields will "enable" the created action.  

The condition area allows the action to be toggled on and off based on an input MDX expression; in this case, the condition will not be utilized. The action content describes the action type to be executed; this list includes all the action methods except the drillthrough action and report action.  For our example we are using URL.

The next step, the action expression, allows us to define the URL to be opened for this action. Notice in the below illustration, that we can also append values to the URL in order to pass those to the browsers. For this particular illustration we are appending the postal code to the URL. This URL will lookup the postal code when the browser is opened.

Finally under additional properties, we invoke the action in an interactive mode, use the caption property to add a "tool tip" for the action (you will see this tool tip when we look at the Excel results next). The application property identifies the name of the client application that can run the action, although in most cases this item is just a suggestion or recommendation. The caption will say "Map of : ZipCode-City Name". If the caption is not specified then the action name will be displayed.  Finally, adjust the Caption is MDX to True and then save the action.  

URL Action Setup

Now that the action is setup and saved, we can see how it is implemented in Excel. To use the Action, I right click on the 03064 postal code, and then click on Additional Actions. Notice how the caption appears with the postal code and then the city name, this items is the tool tip mentioned earlier.

Excel URL Action

Once the caption is selected, the default browser (Internet Explorer on this test machine), opens to a Bing map of selected zip, as shown below. 

Bing Map Results

The URL action is a great start for actions; next we will move through a drill through action.

SSAS Drillthrough Action

A drill through action provides the users with a dataset of a predefined values. The values to be returned can be dimension attributes or measures, although calculated measures cannot be selected (a work around does exist, please see the next steps at the end of this tip).

As illustrated below, first you must name the drill through action. Next, the measure group member's list box can be used to limit the list of drill through columns.

The condition box works similarly to the URL action; for instance, we could create a condition so that the drillthrough was only available for reseller order quantities amounts greater than 500 as noted in the example below. The drillthrough columns area allows you to select which fields, either measures or attributes, will be returned when the drillthrough action is invoked. The list is filtered by measures or specific dimension names. For our example, we are selecting several of the reseller sales measures plus the promotion, promotion type, and promotion category which fall under the promotion dimension.

Under additional properties, two new properties are available for the drill through action. The first additional option defines whether this drillthrough action is the default drill through when a user double clicks or invokes the drill through in Excel; only one drillthrough action can be the default. It may be beneficial to select a smaller set for this default action. The second additional property, maximum rows, limits the numbers of row returned when this action is initiated. A user may try to drill into a million rows. The remaining properties are similar to the URL action.

Drill Through Setup

Jumping back to the Excel spreadsheet, we can now see the drillthrough in action. By right clicking on the order quantity, selecting Additional Actions, we now have the Excel Drillthrough Action method available.

Excel Drill Through Action

Clicking on the action creates a new worksheet with a table that contains each of the fields selected in the drillthrough columns. This data can now be used for further detail analysis. 

Drill Through Excel

Navigating back to the original pivot notice in the below screen print that when attempting to drill down from the 499 order quantity value, no drill through action is available because of the condition restriction. Furthermore, if the number of rows exceeded 2,000 rows, only the first 2000 rows would be returned.

Condition Result

SSAS Report Server Report Action

The last of the actions we are going to cover in this tip is a drill through to a Report Server (SSRS) report. Again, many of the properties are the same as the URL action. For instance and as shown below, we specify the action name, the target type and target object, and optionally a condition. Furthermore, the additional properties such invocation, application, and caption resemble the URL action implementation.

Two sections, the Report Server and Parameters sections are what differentiates the SSRS action from the other actions.  With the Report Server area, as noted below, the SSRS server name and report path location (note the path is the ReportServer path) are defined. Further, you can define the render format. In the Parameter area, you define what parameters to pass from the pivot table to the SSRS report. For our example below, we are passing the Employee ID to the Adventure Works Employee Sales Summary report found on my local machine.

SSRS Action Setup

Now when we right click on the Employee ID, the SSRS Drill Down Action is available, as shown below.

SSRS Drill Down Action

This drill down action submits a http request to the default browser telling it to open the noted Report Server URL for the Employee Sales Summary Report and use the selected employee id for the Employee parameter. The report in our below example is rendered in HTML format, but we could select other options in the setup including PDF or Excel.

SSRS Action Result

Conclusion

SSAS drillthrough actions provide rich functionality for Excel users (and other user applications) that seems to be used only sparingly by SSAS designers. Nine different actions are available to use, but the three most common of these are: 1) URL action 2) drillthrough action or 3) SSRS action. With these three methods, user are able to drill to a URL, a SSRS reports, or drillthrough dataset. For the drillthrough report, the data is returned in a tabular form. Each action requires the action to be given a name and also optionally allows for the definition of a MDX condition to determine if the action is enabled or disabled. Additionally, each action allows for the customization of the caption used in the Action; this caption is sort of a tool tip. Finally, the drillthrough action allows for one drillthrough action to be set as the default action while also providing the ability to create a row limit for the number of rows to return.

Next Steps


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, January 19, 2015 - 10:04:06 AM - scott Back To Top

not sure the update will work since no data is returned....I have not tried that, sorry.


Monday, January 19, 2015 - 9:15:30 AM - Edgar Walther Back To Top

Scott,

The MDX statement works fine in SSMS. If I choose action type Rowset Excel complains that there is no rowset returned and crashes. If I use action type Statement the action is not shown in the Additional actions context menu.

Any other ideas?

Thanks!


Monday, January 05, 2015 - 12:50:24 PM - scott Back To Top

Statement should work... Have you checked that MDX in SSMS?  I would also try the rowset first.


Monday, January 05, 2015 - 11:35:05 AM - Edgar Walther Back To Top

Hi Scott,

Thanks for the great post. It really helped me to explore the actions functionality.

I am looking for an example of another type of action: the Statement. The help description makes me think I can execute an MDX statement here. But I cannot find any example of this on the web.

Whatever I write in the action expresion box, the syntax check fails. Eventhough the MDX works in SSMS. Example of my MDX satement:

UPDATE CUBE [Budget] SET
([Measures].[Forecast],
[Date Attributes].[Period].&[201201],
[Cost Elements].[Cost Element].&[123]) = 1000

What I eventually try to achieve is that I want to execute an UPDATE CUBE statement to give the users a more advanced version of writeback and allocation.

I hope you can help me out.

Thanks!


Wednesday, May 07, 2014 - 8:56:34 AM - Simonas Back To Top

Hello,

thanks for the great tutorial, but I cannot find on the Internet about some other action types, e.g.

  • Dataset-returns a data set
  • Rowset-returns a row set

how to use these? Is it possible to return rows in Excel dynamically by passing variables?


Wednesday, March 19, 2014 - 3:17:55 PM - scott murray Back To Top

Ivan,

Yes, that limitation is still around. The easiest way around it is to remove your filters or place them in the page headers.  It does present a problem from time to time because you actually have to drill into a larger dataset.


Wednesday, March 19, 2014 - 1:24:36 PM - ivan Back To Top

Hi Scott,

Thanks for the info. I am using SQL 2008 with Excel 2010 and have the issue where I cannot drillthrough to the result set when I select multiple slicer values.  I get the error "Show details cannot be executed when multiple items are selected in a report filter..."

 

Is this still the case in newer versions of either SSAS, or excel?  What I read is that the limitation was a result of the drillthrough command envoked by excel to get the drillthrough results.  

 

Thanks,

 

Ivan


Learn more about SQL Server tools