Filtering a measure on an OLAP Cube

[AaseMargreteS_Strømsodd]

My customer has created an OLAP Cube, where answers from their employees are categorized into gender, age, which job title they have etc. Their employees have given answers to more than 70 different questions. These results are then summarized on different organizational levels.

Our customer want that only organizational sections, f.ex. a department, where more than 30 employees have answered, are shown. In other words, if a question is answered by only 10 people in a department, that question should not be shown in report if the user has drilled down to that level.

I have created a measure that counts the number of answers for each questions, but there's not possible to create filters in Information Map Studio based on measures...? My customer's data source is an OLAP Cube.

I know it is possible to make a filter on a report object in Web Report Studio on a measure from an OLAP cube, but the problem is that in WRS, a user can easily remove the filter again.

Can anyone help my customer with this issue?

Thank you so much in advance.

Kind regards,
Aase Margrete Strømsodd

[UlfB]

I'm not sure if this solves your problem, but you could try using the iif function in an expression in the info map to check if the value is less than 30 and then set it to another value.

Let's say the measure in question is called No_of_answers and we want to set it to 0 if it's less than 30, then the expression would be something like this:

IIF(<<MEASURES.No_of_answers>> < 30,0,<<MEASURES.No_of_answers>>)

The blue part is the condition to test, the green part is what should be returned if the condition is true and the red part is what should be returned if the condition is false. In this case we return zero if the condition is true, otherwise the measure value is returned.

[AaseMargreteS_Strømsodd]

First of all, thank you very much for your answer.

We had already considered a similar solution to the one that you are suggesting. We thought that we could use an MDX-expression like this: IIf([Measures].[Antall_svarSUM]>30, [Measures].[SvarAVG], .) and create a new numerical expression based on it.

The problem is that the user still will have the possibility to see the measure that we don't want the user to see (SvarAVG), it will still be a part of the cube.If we define this calculated measure in SAS OLAP Cube Studio, then afterwards, the user will see our new, calculated measure in addition to the measure SvarAVG.