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!
# 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)'
)
wow this is some badass prompt coding
Yeah, I was surprised myself it did work at the end.
The challenge was to do Security Context to Dimension mapping in the report itself, without any custom programming.