"States" for Scenario Hierarchy *OLAP*

#1

Although my use case is a bit different then what I see in the documentation available for states, I find it highly unlikely that dundas is not able to perform this type of calculation against data from my OLAP datasources.

I understand from the videos and documentation here: (https://www.dundas.com/support/learning/documentation/analyze-data/set-up-states-on-a-metric-set) that states can only utilize the condition “Equal to” when its target is set to a hierarchy member. I understand why.

Again my case is a bit odd, but the task I am assigned to work on is to display states based on a hierarchy which is used to show plans and actuals for measure calculations. This type of hierarchy is often referred to as a scenario dimension in accounting. For a picture of what I mean, please see what I am trying to do here: (https://imgur.com/SdwRhjM)

As you can see, this scenario dimension splits the Actual side by side with the plan. Unlike most hierarchies, the secnarios members are not part of a whole. They are completely separate percentages and it works out very nice in the current product we are converting to Dundas from.

You might be wondering how this works. This ‘Measure’ is actually a calculated member in the Analysis Services cube (datasource). The calculation looks like this:

iif(Scenario.currentmember is [Scenario].&[1],
[Measures].[ACTUAL_numerator]/[Measures].[ACTUAL_Denominator],
[Measures].[PLAN_numerator]/[Measures].[PLAN_Denominator])

(If you are not familiar with MDX, this code is saying - if a certain scenario hierarchy member is selected then show the actual numerator divided by the denominator, otherwise, show the plan. PS. This scenario hierarchy does not have an all member).

I would ideally like to set a bunch of color coded states in a seperate column at “80%, 90%, 100%, 110% of plan” without creating new calculated members in my Analysis Services cubes. This would require me to set the condition to <=, >=, etc - but I think dundas will only allow me to set conditions like that on measures and formulas. I have tried creating formulas, and shifting dimensions around that would satisfy this requirement but have had no luck of coming up with something valid - nevermind pretty. Unfortunately, I can not reference the things that I need to when making a formula, probably from my own lack of experience. Could any of the experts here provide some insight on how I might be able to complete this?

Thanks

#2

@ryan
I looked at the picture you included. To me the percentages are measure values that you can set a state on with less than and greater than conditions.
I have not used MDX yet so I am not sure on the details of that.
Is it possible that in the Dundas Data cube you can changes those to Measures?

I have a lot of dashboards that deal with percentages (calculated on hours charged versus expected hours charged) and I also have a percentage of goal that has a state based on at 80% set it this color and so on.

#3

Hi James,

Thank you for your response.

I am not sure what you mean.

“To me the percentages are measure values that you can set a state on with less than and greater than conditions”

These are listed as measures in Dundas. I am trying to set a state up on the measure when it is filtered by this ‘scenario’ hierarchy.

“Is it possible that in Dundas data cube you can change those to measures”

I am working with an OLAP Cube, and they are set to measures. Would changing to a Dundas Data Cube make a difference here?

“I have a lot of dashboards that deal with percentages (calculated on hours charged versus expected hours charged) and I also have a percentage of goal that has a state based on at 80% set it this color and so on.”

This is what I’m trying to do against my scenario hierarchy.

#4

have you looked at these:
https://www.dundas.com/support/learning/documentation/analyze-data/set-up-states-on-a-metric-set
and
https://www.dundas.com/Support/videos/states

I just looked at my states for the percentage complete I have and all I did was go on that metric set from the main properties go into that measure and then the look and set up a state for it.

I think you might be trying to set the state for the measure by creating a state for the dimension.

When you first create the state and set a name for it what are you picking to set up the state on?

#5

James,

Thanks for responding again and yes I have looked at the documentation however it doesn’t specify how I can complete what it is that I am trying to do. I am beginning to think it is impossible in Dundas.

I set the state on the measure. Anytime I try to set the condition of the state to the plan member of my scenario hierarchy, it only will allow me to choose equal too. I need it to be able to select greater then / less then.

#6

So you want to color the Dimension based on the “State” of a measure?

#7

I understand that it is a tricky subject and I hope I am doing a good enough job explaining.

I think the best way to define what II want to do is color the measure based on which value it holds when filtered by a level of the scenario dimension.

#8

That I have not done before.
@jeff Need you help here too.

1 Like
#9

Hi Ryan,

I believe the use case described here can be much efficiently understood via a web meeting. I would recommend you to drop by an email with your use case at support@dundas.com and then one of us will get back and further assist you with the issue.

#10

@ryan
did support find you a good solution?
Can you briefly tell us what the solution was?