Using blank ASCII character in PerformancePoint to avoid trimming

Recently I had a problem with a PerformancePoint filter, which got automatically trimmed. The filter was displaying a small custom cut of a hierarchy and the Business Analyst on the project wanted to see it always expanded in a simple single select drop-down instead of in a tree. The tree view was undesirable because the hierarchy was very small and clicking twice to get to the third level was too much for the users of the reports.

Unfortunately, indenting the drop-downs with blank spaces is not possible when using a List filter as PerformancePoint trims the items in it providing a flat list of names. Indenting with dashes or some other visible character is also usually not visually pleasing, and since there is no way to use rich text and color some of the characters in white, the only option is to use an invisible character which does not get trimmed.
Fortunately, there is such a character with an ASCII code of 255. It is a space-like character, which does not get trimmed by PerformancePoint in a List filter. Therefore, if we indent our list with ASCII-255 characters we achieve the goal of having nicely arranged list in a simple filter. Keeping Alt pressed and then typing 255 from the numeric keypad results in typing one such whitespace character.
As it is generally not a good practise to use the character in our code as distinguishing it from a space is not easy, it is advisable to provide a lot of comments around code fragments utilising it, including the way to type it.

Filtering Unneeded Dimension Members in PerformancePoint Filters

Published on SQLServerCentral on the 2008-11-14:
http://www.sqlservercentral.com/articles/PerformancePoint/64565/
 

Sometimes we need to utilise dynamic dimension security in an Analysis Services solution and we also need to display dynamically the allowed members in a PerformancePoint filter. 

In case our hierarchy is multi-level and in case we can expect to have security on multiple levels, PerformancePoint will display the full path upwards to the root member of the dimension. So, in the case where in a Business Unit hierarchy we have access to some third level members, in PerformancePoint we will see all their parents. In example if our hierarchy looks like this: 

All Business Units
-Europe
–UK
—-France
—-Bulgaria
-North America
—-USA
—-Canada 

and we give someone access to France, they will in fact see: 

All Business Units
-Europe
—-France 

Indeed, when they select All Business Units or Europe they will still see only France data but this may be confusing. To eliminate the top levels we need to change the way we create our PerformancePoint filter. 

To achieve this, first we need to create a MDX Query filter. For more information about PerformancePoint filters and dynamic dimension security you can read the following brilliant post on Nick Barclay’s blog: PPS Data Connection Security with CustomData. Nick explains how to set up PerformancePoint to work with Analysis Services dynamic dimension security and related topics. I will now concentrate on actually filtering the members of the already set-up MDX Query filter. 

Instead of requesting all dimension members with a simple statement like: 

DESCENDANTS([Business].[Business Hierarchy].Members,, SELF_AND_AFTER) 

we can write some fairly simple MDX which means: 

Get me the descendants of all dimension members whose ascendants (excluding themselves) have no more than one child. 

And the MDX code is: 

 DESCENDANTS( FILTER([Business].[Business Hierarchy].Members AS a,   ((FILTER(ASCENDANTS(a.CurrentMember) AS a_asc,     a_asc.CurrentMember.CHILDREN.Count > 1).Count = 1)     And     a.CurrentMember.Children.Count > 1)   Or   ((FILTER(ASCENDANTS(a.CurrentMember) AS a_asc,     a_asc.CurrentMember.CHILDREN.Count > 1).Count = 0)     And     a.CurrentMember.Children.Count = 0)),,SELF_AND_AFTER) 

The result will be France being displayed as the only available member in the PerformancePoint drop-down. Also, if we had two different allowed members, the code above would show us the top common parent of the two. Therefore, if we had France and Canada as the allowed set of dimension members, the drop-down would consist of the following hierarchy: 

All Business Units
-Europe
—-France
-North America
—-Canada 

thus satisfying our requirements.