Dimensional Prompt with calculated default value - solution

[Andrei I]

I've seen  quite a few questions on this forum about specifying a default value for a Prompt in a report with Dimensional Package.
Below is a real life example from one of my numerous Cognos projects.
It might give an idea what you can do with a little creativity.
This expression calculates a highest level available for a user in the School Dimension based on the user Security context.
School Dimension levels: Region / Quadrant / Family / School
If a user is a Supervisor of Region A then the expression will return [Region A] member and so on...

Enjoy!

Code Select Expand

# prompt('School','MUN',
'item( ' +
'union( filter ( [Region],  '+
sq(csv(  array (
     grep( '-RegionSUP',      split( '''', CSVIdentityNameList(':') )   )
    , grep( '-RegionAA',       split( '''', CSVIdentityNameList(':') )   )
    , grep( '-RegionSSI',       split( '''', CSVIdentityNameList(':') )   )
              ) ,':','' )
)   +'   starts with   [Region Code]) ' +

', union( filter([Quandrant],  '+
sq(csv(  array (
     grep( '-QUADSUP',      split( '''', CSVIdentityNameList(':') )   )
    , grep( '-QUADAA',       split( '''', CSVIdentityNameList(':') )   )
    , grep( '-QUADSSI',       split( '''', CSVIdentityNameList(':') )   )
              ) ,':','' )
) + '  starts with   [Quandrant Code]) ' +

', union( filter([Family],  '+
sq( csv(  array (
     grep( '-FOSSUP',      split( '''', CSVIdentityNameList(':') )   )
    , grep( '-FOSAA',       split( '''', CSVIdentityNameList(':') )   )
    , grep( '-FOSSSI',       split( '''', CSVIdentityNameList(':') )   )
               )  ,':',''  )
)   + '   starts with   [Family Code]) ' +

', union( filter([School Name],  ' +
sq( csv(  array (
    grep( '-PL',        split( '''', CSVIdentityNameList(':') )   )
    , grep( '-VP',       split( '''', CSVIdentityNameList(':') )   )
    , grep( '-SSI-LC', split( '''', CSVIdentityNameList(':') )   )
    , grep( '-TCH',     split( '''', CSVIdentityNameList(':') )   )
               ) ,':','' ) 
)   +  '   contains   [School Code] )' +

' , item( [School Name], 0 )  ' +
')  )  )  ),0)'
)