How to create filter function that uses two different hierarchies?

[anthus]

I need to create a filter () function in Report Studio (10.2.1.1) off a dynamic cube that uses two different hierarchies.  What is the correct syntax?  I've been creating reports on relational data for many years but am new to the dimensional reporting and like others, struggling with the switch =(

My crosstab would be

               #Students |  Target
Term 1
Term 2

I would like to create a filter () function on the Term but also need to use the load date in the filter (for example Term = 123 and Load Date = 456).  I tried the following.  It validated but returned no results.  However, when I created separate query calculations (one filtering for Term and one filtering for Load Date) I get the correct results.

filter ( [cube].[Term Dimension].[Term Hierarchy].[Term Level] ,  [cube].[Term Dimension].[Term Hierarchy].[Term Level].[Term Key] = 123 and [cube].[Load Date Dimension].[Load Date Hierarchy].[Load Date Level].[Load Date Key] = 456  )

Any help would be greatly appreciated.

Thanks so much!

[MFGF]

I need to create a filter () function in Report Studio (10.2.1.1) off a dynamic cube that uses two different hierarchies.  What is the correct syntax?  I've been creating reports on relational data for many years but am new to the dimensional reporting and like others, struggling with the switch =(

My crosstab would be

               #Students |  Target
Term 1
Term 2

I would like to create a filter () function on the Term but also need to use the load date in the filter (for example Term = 123 and Load Date = 456).  I tried the following.  It validated but returned no results.  However, when I created separate query calculations (one filtering for Term and one filtering for Load Date) I get the correct results.

filter ( [cube].[Term Dimension].[Term Hierarchy].[Term Level] ,  [cube].[Term Dimension].[Term Hierarchy].[Term Level].[Term Key] = 123 and [cube].[Load Date Dimension].[Load Date Hierarchy].[Load Date Level].[Load Date Key] = 456  )

Any help would be greatly appreciated.

Thanks so much!

Hi,

I'm not seeing how this makes sense. Since load dates are in a completely separate dimension, they are disconnected from the term level members you are trying to filter. Are you trying to reduce the set of term level members, or are you trying to filter the measure values for these members? Can you explain your requirement in a little more detail?

Cheers!

MF.