BookmarkSubscribeRSS Feed
Calcite | Level 5

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

Calcite | Level 5

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.

Calcite | Level 5

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.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1 like
  • 2 in conversation