PerformancePoint – Using SSAS Actions

The latest project I worked on had a heavy dose of PerformancePoint and one of the requirements was to launch Analysis Services Actions from PerformancePoint dashboard objects.  I knew this was possible but did not no all the limitations that exist when you want to do this.  Most of the available post reference PerformancePoint 2007 so I thought it was time to update the topic. 

I mentioned there are a lot of caveats to doing actions in PerformancePoint so I came up with this quick flowchart to first see if you can even consider doing them.

Yes at first is was confusing enough to me that I needed this!


So assuming you meet the conditions above in the diagram let’s show you how to enable action usage. 

First we’ll look at the scorecard which can only use Drillthrough actions from Analysis Services.  Drillthroughs are simple enough to create so I won’t focus on that but a couple things you’ll need to make sure are set here are Measure group members cannot be set <All> for PerformancePoint to recognize it and you need to change the Default property to true.  The Default property is a little hidden because it is in the Additional Properties section at the bottom of the action.


Once the Drillthrough is setup properly in SSAS then you must build your PerformancePoint KPI so it will pickup the action.  This means that in Dashboard Designer you must change the Calculation property on each field on your KPI that you wish to allow users to launch the action must be set to Data value.  This is a change in the property value name which was called Source data in PPS 2007.


When you save the KPI your scorecard should automatically be updated.  Only any field you changed you can now right-click on and select Show Detail, which will launch your SSAS action.


Analytic grids and charts do not require this KPI change.  They can do URL, Drillthrough and Reporting Services actions by simply right-click on the measure you want to get details on then go to Additional Actions and select the Action name. 


So just to just to summarize if you’re still having problems doing actions in PerformancePoint here are your requirements:

  • You can only use actions that are regular measures (Cannot be a dimension attribute or calculation).
  • Scorecards can only do Drillthrough actions but the action you create must be the default.
  • Analytic Grid and Charts can do URL, Drillthrough, and SSRS actions.  Drillthrough action does not need to be set to the default to use here.
  • The SSAS action cannot be apart of the <All> Measure Group Member
  • If using an unattended service account for PPS make sure it has read definition on the cube.
  • Make sure you are in the visitor permission group on the SharePoint site.

SSRS – Dealing with truncating spaces

I apologize ahead of time on this post today because it will require lots of pre explanation and screenshots to set the stage for what I want to show you!  Often when building a report with multiple row groupings I like to change the default way SSRS places the fields on the report.  Default multiple row groups:


While this behavior is fine it can lead to a lot of useless white space when browsing the report.


So what I like to do is still have the three groups by eliminate all that dead space.


Of course this presents a new problem.  When you view this report now the white space is eliminated but, it appears that all row groups are at the same level, which would confuse an end user.


So I thought to do the obvious  I’ll make each group and expression with some leading spaces to indent the second and third groups.  I could have also used the SPACE function to simulate the problem I’m demonstrating as well.

="   "+Fields!Subcategory.Value

This works beautifully from the BIDS preview screen but once deployed to the Report Server those spaces are then truncated.  This happens because the compresses the leading spaces for you automatically.

The workaround is to use a non truncating space character.  By using the ASCII key code for 0160 you will get the spaced effect you’re looking for.  So by holding Alt + 0160 on your number pad non truncating spaces will be added to your report expression.  Do this the desired number of times on each group and your report will look great!  By the way number pad was intentionally bolded here because if you’re working on your laptop make sure to turn on the number lock and use the appropriate number pad keys for this to work.  The final report looks like this!


PASS Pre Con – Building a Microsoft Business Intelligence Platform

PASS Summit is quickly approaching and while many of you are registering I wanted to remind you of some of the great Pre Conference seminars that are available (more specifically mine!).  On Tuesday, October 11, 2011 Brian Knight and I will be presenting on building a BI Solution from the ground up in our seminar called “Building a Microsoft Business Intelligence Platform”.  Brian and I have presented several times together on the topic and we would love to have you join us for a full day learning extravaganza!  We always make these sessions entertaining so the 7 hours we have together will likely fly by.

Here’s an extract from the Q and A blog run by Lori Edwards about the session written on 7/18/2011:

Today’s post is from Brian Knight who, along with Devin Knight, will be presenting "Building a Microsoft Business Intelligence Platform". You can read more about his session here:

Is there an audience that would benefit especially from this session?
Someone who has a beginner to novice experience in some of the BI suite will gain a huge amount from the real-world experience we’re going to bring to the table. If you’re a beginner, you’ll also gain quite a bit from the interactive style of the session. During the day, we’re going to build an end-to-end BI solution using SSRS, SSIS and SSAS. Attendees should bring their own laptops to build the solution with us and get the most out of the session.
After having attended your seminar, what are two or three things that an attendee will be able to take back to the office and put to use right away?
An attendee will have the broad knowledge to build an end-to-end BI solution at his or her company. This includes designing and loading the data warehouse, building a cube on the warehouse and building a presentation-layer.
What background should attendees ideally have to be fully prepared for your seminar?
The session is interactive. Because of this, for the attendee to get the most out of the session, he or she should know how to navigate around the Business Intelligence Development Studio (BIDS) environment. The attendee should also have a good understanding of T-SQL and a fundamental knowledge of the BI tools.
What experience are you, as a speaker, bringing to this session?
Devin and I have created BI solutions across the globe for more than a hundred different companies. In 2011, we trained more than 3,000 people in a fun and interactive way that will make the day fly by. You’ll hear tons of information about real-world examples that we can provide from the field.

Go here to register for the 2011 PASS Summit.