Início > Calculated Member, YearToDate > How to hide Calculated Members in MDX SSAS

How to hide Calculated Members in MDX SSAS

 Link:http://www.bidn.com/blogs/MikeDavis/ssis/2302/how-to-hide-calculated-members-in-mdx-ssas

 

With some calculated members in MDX it only makes sense to see the calculation if a certain Hierarchy is used.

For example:

Aggregate(YTD([Date].[Calendar].CurrentMember),[Internet Sales Amount])

This calculation only works in the Date.Calendar Hierarchy. I wanted to show the end users a message informing them about this and hide the calculation at the same time.

Here is how I did this:

Case when ([Date].[Calendar].level is [Date].[Calendar].[Calendar Year] or
[Date].[Calendar].level is [Date].[Calendar].[Calendar Semester] or
[Date].[Calendar].level is [Date].[Calendar].[Calendar Quarter] or
[Date].[Calendar].level is [Date].[Calendar].[Month]) then
aggregate(ytd([Date].[Calendar].currentmember),[Internet Sales Amount])
else “Use Date Hierarchy”
End

image

Now the user will see the message “Use Date Hierarchy”  if they are not at a level in the correct Hierarchy. The only downside to this is the grand total shows the message instead of the total.

image

If you can figure out a way to show the grand total when the user is in the correct hierarchy, let me know.

  1. Ainda sem comentários.
  1. No trackbacks yet.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

%d bloggers like this: