Error in expression for Parameter Maps

[Raghuvir]

Hi MFGF,

Thanks for the reply.

In that case i need to replace the user name in the Key tab of the parameter maps with the Role or Group name. And in the filter expression of the Query subject i need to use CSVIdentityName() .

What session parameter should be used ?

Regards!

[Raghuvir]

Hi MFGF,

Could you please guide me with the expression ?

Regards!

[MFGF]

Have you looked at the "Tips" text when you select the CSVIdentityName() macro function? It gives a really good example.

This is what it says:

CSVIdentityName ( %parameter_map_name [ , separator_string ] )
Uses the identity information of the current authenticated user to look up values in the specified parameter map. Each individual piece of the user's identity (account name, group names, role names) is used as a key into the map. The unique list of values that is retrieved from the parameter map is then returned as a string, where each value is surrounded by single quotes and where multiple values are separated by commas.
Example: # CSVIdentityName ( %security_clearance_level_map ) #
Result: 'level_500', 'level_501', 'level_700'

So in your case

cast([SECURITY_FM].[COUNTRY].[COUNTRY_CODE],varchar(4)) in (#CSVIdentityName(%Country)#) or '0' in (#CSVIdentityName(%Country)#)

should probably do the trick

MF.

[Raghuvir]

Hi MFGF,

i saw the "Tips" but got confused. the expression worked. you are just awesome.

I dont know if i am asking for too much but could you please explain the logic behind the expresion.

Regards

[MFGF]

Yay! Glad it worked! :)

Your Country query item is an integer field, but the CSVIdentityName() function returns one or more results from your parameter map as character strings, so there's obviously a data type mismatch there. To fix this, we use the cast() function to get a 4-character string value representing your country code - this is what the following excerpt does:

cast([SECURITY_FM].[COUNTRY].[COUNTRY_CODE],varchar(4))

Since the CSVIdentityName() function can return multiple values from your parameter map (if a user belongs to multiple groups or roles), we need to use an 'in' operator rather than the '=' we used before.

The next piece is where we call the CSVIdentityName() macro function for your Country parameter map. This automatically takes your user name and each group and role you belong to and tries to get a match for each in the parameter map. Where it does, it returns the corresponding value or values. Each value is included in single quotes automatically - eg '1004' rather than 1004 - and if there are multiple matches, the results are automatically comma-separated - eg '1004', '1003'. Because we are using an 'in' operator, the result or results should really be included in parentheses, so that the returned string would look like: in ('1004', '1003'). This is what the next piece of the expression is doing:

in (#CSVIdentityName(%Country)#)

The last piece is to cater for what happens if a user has no groups or roles that exist in the parameter map. In this case the default value from the parameter map would be returned. We coded this as 0 (zero), so the CSVIdentityName() function would return it as '0' (remember - it automatically includes the result in single quotes). The last piece of the expression is to cater for this - so that all countries are returned if no matches are found in the parameter map:

or '0' in (#CSVIdentityName(%Country)#)

Cheers!

MF.

[Raghuvir]

Hi MFGF,

Thanks a lot! Keep up the good work! :)

Regards!