Applying security to SSAS cube using lookup table(relational) in the report

[lalitha.nov20]

Hi All,

I facing challenge where we need to apply a filter to the report based in the user login.

here is what i have done.
I have created a lookup table, which has username and member it should filter in the reports.

example : id=102 and members: 'China','India'.

I have imported this into the model and created a parameter map.
i have used this parameter map in the report and filter the report.

Here is the  challenge.

Data in the cube does not have unique membercaption. say if the Country is the highest level, and does not have any state level , the leaf level has data, as bangalore, the ancestor caption is repeating as parent.

example: India
                    -> india
                             ->india
                                 -> bangalore.

when i apply a filter as hierarchy in ('india'). it return all the 3 rows instead of one.

is there any way that we can mention the level at which is should pick the values.

i cannot use MUN, as the lookup table should be userfriendly.

Any thoughts!!!

Thank you,
Lalitha

[lalitha.nov20]

guys any thoughts on this....  :o :( :-\

[MFGF]

guys any thoughts on this....  :o :( :-\

Looks like you have a fundamental flaw in your approach. You are using captions in your parameter map, but your captions are not unique, so the end result is that you are delivering all members with those captions.

You should probably change your approach to use Member Unique Names (MUNs) instead of captions.

MF.