Archive for the ‘DrillTrough’ Category

SSAS Reporting Actions with Date Parameters

Reporting Actions in Analysis Services allows you to open a report in Reporting Services. Most of the time users want to pass in some parameters to the report so it shows the relevant data. If the parameter name matches the item in excel then this is a breeze. The problem comes when you need to pass something different to the report. If the report has a date range on it you need to pass the report a start date and end date. If a user clicks on a reporting action in excel at the year, quarter, or month level, you will need to get the start date and end date dynamically. Let’s see how to do that. Here you can see three reporting actions in a cube in SSAS. Each one handles a different date level. The user will see only one action when clicking in excel.

SSAS Reporting Actions with Date Parameters

To ensure the user only sees the proper action at the proper date level you need a conditional statement in the optional condition box. So for the month level the code is: [Due Date].[Date].CurrentMember.Level is [Due Date].[Date].[Month Name]

SSAS Reporting Actions with Date Parameters

The server name is the name of the server where reporting services is running. The report path is the URL where the report is located. This is not the report manager URL. This is the report server URL. The parameters will be different at each level.

Year Level Start Date Parameter:

1 URLEscapeFragment(
2 "01/01/" +
3 [Due Date].[Date].CurrentMember.Properties("Key")
4 )

The key for the year is the year, so I am able to just concatenate the date together here for the first day of the year. The last of the year works very similar.

Year Level End Date Parameter:

01 URLEscapeFragment(
02 "12/31/" +
03 [Due Date].[Date].CurrentMember.Properties("Key")
04 )
05 Month Level Start Date Parameter:
06 URLEscapeFragment(
07 [Due Date].[Date].CurrentMember.Properties("Key1") + "/01/" +
08 [Due Date].[Date].CurrentMember.Properties("Key0")
09 )
10 Month Level End Date Parameter:
11 URLEscapeFragment(
12 Cstr(
13 DateAdd("d",-1,
14 DateAdd("M",1,
15 DateValue(
16 [Due Date].[Date].CurrentMember.Properties("Key1") + "/01/" +
17 [Due Date].[Date].CurrentMember.Properties("Key0")
18 )))))

The keys for month are year and month. So Key1 is the month and Key0 is the year. This is typical to avoid duplicate key issues in a date hierarchy.

For the Month end date you need to use the dateadd function to go to the first day of the next month, then dateadd again to step back one day. This will give you the last day of the month.   Now for the hard one, Quarter level. The key for quarter is year and quarter. In this you are multiplying the quarter number by three and then subtracting 2 to get the first month in the quarter. The end date for the quarter is the same as the start date with two DateAdd functions to get to the end of the quarter. First you add 3 months then subtract one day.

Quarter Level Start Date Parameter:

URLEscapeFragment( Cstr( (Cint( [Due Date].[Date].CurrentMember.Properties(“Key1”) ) * 3)-2) +”/01/”+ [Due Date].[Date].CurrentMember.Properties(“Key0”) )

Quarter Level End Date Parameter:

01 URLEscapeFragment(
02 Cstr(
03 DateAdd("d",-1,
04 DateAdd("m",3,
05 DateValue(
06 Cstr(
07 (Cint(
08 [Due Date].[Date].CurrentMember.Properties("Key1")
09 ) * 3)-2
10 )+"/01/"+
11 [Due Date].[Date].CurrentMember.Properties("Key0")
12 )))))
13 Now in excel, when the users right click on a field, they will see the report action for the clicked level. Here are some images of this in action.

Year Level in Excel

SSAS Reporting Actions with Date Parameters

Year date ranged passed to report

SSAS Reporting Actions with Date Parameters

Quarter Level in Excel

SSAS Reporting Actions with Date Parameters

Quarter date ranged passed to report

SSAS Reporting Actions with Date Parameters

Month Level in Excel

SSAS Reporting Actions with Date Parameters

Month date ranged passed to report

SSAS Reporting Actions with Date Parameters



Default Cube Action in Excel


When using Excel 2007 to connect to a SQL 2005\2008 SSAS cube, double-clicking a cell, or right clicking the cell and selecting Show Details, within a Pivot table brings up details about the specific sliced cell within the Pivot table. However, the default nature of the cube is to bring up the raw Fact table data for that slice, which displays all fields within the Fact table for the records that make up the cell. The fields within a Fact table are often surrogate keys that point to the Dimensions that join to the Fact table as well as all Measurements stored within the Fact table. This default nature is not the most helpful information to business analysts since the keys have no business meanings to the users.

The question then is how to change this default behavior to bring back information to supply meaningful details to a business analyst when using the cube. The answer is to create a Drillthrough Action with the SSAS cube and set the Default property to True. The Drillthrough Action will return the information that the business defines as meaningful when wanting to see the details within the cube.

The following lists out the steps on how to create the Drillthrough Action within the SSAS project for the cube and then demonstrates how to use the Drillthrough Action in an Excel Pivot Table.

Setup the Drillthrough Action

In the SSAS cube, navigate to the Action table and select the icon for the New Drillthrough Action:


After selecting the New Drillthrough Action, setting up the what the Action should do is the next step. For this example, the AdventureWorks cube is being used to build the action and the example will return the following fields for the sliced cell as its default:

  • Reseller Sales Amount, Reseller Order Quantity
  • Date
  • City, State, Country

The following screen prints lays out these fields and sets up the Default property to True:


Using the SSAS Action

After creating the action and processing the cube, an analyst can now connect to the cube via Excel 2007 and create a Pivot table as its starting report. For the example, the report is going to select Sales Territory by Calendar Year for Reseller Sales Amount.

With the Pivot setup, the following methods will display the the SSAS action, note these actions can only be performed on Non Calculated Members within the cube:

  • Double click the cell within the Pivot Table
  • Right click on the cell to drill into and select Show Details
  • Right click on the cell and select Additional Actions–>AW Default Action (this is the name of our Drillthrough Action in this example).

Right Click–>Show Details Menu


Right Click–>Additional Actions Menu:


Excel Report

Regardless of the method chosen, Excel will create a new Tab within the Excel workbook and display the data as defined within the Drillthrough Action:



As demonstrated, a Drillthrough Action within a SSAS cube is another tool for a business analyst to dive into their data and answer questions that is pertinent for the question at hand. Setting up a Drillthrough Action with the Default behavior set to True allows for the users to not see data that is meaningless to the users, since the users will not be able to translate surrogate keys to actual dimension text, but instead provides useful information to the analyst to help make business decisions. The tool of providing of this useful information is the point of developing the cube in the first place.

Posted by Chuck Rivel