Hi all
I have a difficult requirement which is to apply traffic light formatting to rows in a crosstab based on the top 20%, middle 60% and bottom 20% of the rows which are dynamically sorted from a prompt. So for example I have a crosstab with a member set expression in the rows and in the columns I have 3 different measures. I have setup a value prompt called 'pSort' and hardcoded the measure references in to the static choices like so:
[Cube].[Measures].[Measure1]
[Cube].[Measures].[Measure2]
[Cube].[Measures].[Measure3]
I have then wrapped an Order function around the member set:
Order(MemberSet,#Prompt('pSort','token')#,DESC)
This works perfectly, when I change the prompt selection the member set is sorted in descending order based on the selection. My client now wants to apply dynamic formatting to the rows, so for example if I have 20 rows in the crosstab they want the top 4 (20%) rows to be green, the next 12 rows (60%) to be amber and the bottom 4 (20%) rows to be red. I know there are functions for topPerent and bottomPercent but I cant work out how to apply them for styling. Does anybody have any idea?
Thanks
Lloyd
The thing to remember with crosstabs is that you can not only nest nodes, but set them as peers as well.
Top 20: order(topPercent(set,20,#Prompt('pSort','token')#),#Prompt('pSort','token')#,desc)
middle 60: order(except(topPercent(set,80,#Prompt('pSort','token')#),[Top 20]),#Prompt('pSort','token')#,desc)
bottom 20: order(bottomPercent(set,20,#Prompt('pSort','token')#),#Prompt('pSort','token')#,desc)
Instead of having a single node in the rows of your crosstab, you should now have three. You can then rightclick -> select member fact cells, and apply styling as needed.
Top man cheers Paul :)