If you are unable to create a new account, please email support@bspsoftware.com

 

Need to filter the member at the Hierarchy using member in a related level

Started by Mardude55, 06 Jul 2019 11:53:10 AM

Previous topic - Next topic

Mardude55

Hi there ... I thought that its due time that I join this group of like minded people.

UPDATED with more details ... July 7th

Well after over a week of frustration with trying to build a report against a DMR I'm reaching out for suggestions.
I am using Cognos 11.0.12 accessing a SQL Server datamart. My client does not want to change anything in the FM model but if I have a strong case to do it, I can push it. I think that there has to be an easier way to do this.

I have a report where I filter on the members at the Hierarchy[Root] of the Dimension using a prompt.
This hierarchy can have up to 15 levels [Company, Region, Division,, Level 6, Level 7, ..., Lowest Level] .
The last level of a set of members is called 'Lowest Level' containing of course the lowest levels of all the other 1-14 levels.
This level also has additional attributes. One of which identifies which "Level Type" it represents. Actually Level 4 to Lowest contain the same attributes. They are not defined above that.

What I am trying to do is ... based on the entry point where I enter the hierarchy I want to be able to somehow compare that root member to the member of the lowest level and if they are either NOT the same level or the same value, I don't want to report any data.  This is because only the lowest level with Level Type 'Project' is supposed to have access to the transaction level dimensional data that only certain users are allowed to see [Invoice Number, Employee name, etc]. The security control for this is enabled at level 4 down. Above this [Lowest]level the users should only be allowed to see the aggregate totals from the calling report but not the additional dimensional data provided in this report.

This report is called from another report as a drill through report passing the unique root member to it.

I have tried using a filter against the hierarchy trying to match the members or captions of the members.

QUESTIONS:
   1) I can determine the Level of the selected root member from the Hierarchy. Can I also at the same time determine the Level of the "Lowest Level" member from a Level within the same Hierarchy ?  If So, then I could match on Level.

   2) I gather in order to have properties[attributes] defined against a hierarchy they have to exist within a level ... Correct ?

The member that I am selecting for my report is using the following expression:
 
[Selected Hierarchy Member SET]   
           SET( [Presentation Layer].[Dimension].[Hierarchy] -> ?Prompt_Dimension? ) 
     ?Prompt_Dimension? is passed from another report.

To get the members of the Lowest Level I am using the following expression:

[Lowest Level Set]     
       members( [Presentation Layer].[Dimension].[Hierarchy].[Lowest Level] )
  OR
   DESCENDANTS( SET( [Presentation Layer].[Dimension].[Hierarchy]->?Prompt_Dimension? ) , [Presentation Layer].[Dimension].[Hierarchy].[Lowest Level] )

To get the captions of the members I tried using the following expression:

    [Hierarchy Caption]          Rolevalue('_memberCaption', [Selected Hierarchy Root Member SET] )
    [Lowest Level Caption]      Rolevalue('_memberCaption', [Lowest Level Set] )

Then to perform the comparison I tried both the following expressions not at the same time:

1) Calculation in the Query
    FILTER( [Selected Hierarchy Member SET] ,
            [Hierarchy Caption] = [Lowest Level Caption]
            AND [Lowest Level Type Property] = 'Project' ) 
OR

2) Added a detail filter
    [Hierarchy Caption] = [Lowest Level Caption]
            AND [Lowest Level Type Property] = 'Project'

   'Project' is the Level at which the user may have access to the transaction dimensions and detail measures.

If I don't use the filter and run the report, the 2 captions are visually identical on the report when the [Selected Hierarchy Member] is at the same level as [Lowest Level Set].

QUESTION: I thought that unlike the members, the Captions do not internally identify the Hierarchy and levels. What you see is what you get ... right ?

With the filter the report runs but irrespective of which member I select in the hierarchy it doesn't return any data.

Sorry that I can't copy and paste any screen images into this post due to security.

Thanks for looking ...