COGNOiSe.com - The IBM Cognos Community

Hi All,

i am writing an expression using parameter maps and session parameters but getting a parsing error. below is the expression. Kindly request you to guide me where i am going wrong.

[SECURITY_FM].[COUNTRY].[COUNTRY_CODE] in #$Country{$account.personalInfo.userName}#

Thanks

Hi,

Where are you coding the expression - in a filter? Since your macro will return only one result from the parameter map, you could try:

[SECURITY_FM].[COUNTRY].[COUNTRY_CODE] = #sq($Country{$account.personalInfo.userName})#

The = operator is used since you have only one result returned, and the sq() function encapsulates the returned value in single quotes, so the filter evaluates to an expression such as the following:

[SECURITY_FM].[COUNTRY].[COUNTRY_CODE] = 'muppet'

Cheers!

MF.

Hi MFGF,

yes i am coding the expression in the filter tab.

Hi MFGF,

once i configure this parameter maps, it should be prompting me for an input while running a report or would it directly give me the output?

Hi MFGF,

can we use the 'in' query instead of '=' ?

thanks in advance

Quote from: Raghuvir on 01 Apr 2014 09:33:15 AM
Hi MFGF,

yes i am coding the expression in the filter tab.

Ok. Good! :)

Quote from: Raghuvir on 01 Apr 2014 09:48:17 AM
Hi MFGF,

once i configure this parameter maps, it should be prompting me for an input while running a report or would it directly give me the output?

You haven't used a prompt function in your macro so no prompt will be issued. It will use your current authenticated user name and translate it to a single value from the parameter map, which is then used in the filter syntax.

Quote from: Raghuvir on 01 Apr 2014 10:22:03 AM
Hi MFGF,

can we use the 'in' query instead of '=' ?

thanks in advance

That doesn't make sense. The 'in' operator is to allow comparison with multiple values, but your macro is returning a single value. Why would you want to use 'in' rather than '='?

MF.

Hi MFGF,

Thanks for the support you have provided me. The expression which you had given is working in Framework Manager.

i am now facing an issue when i run the report using the country field on which i have applied the filter.

please find the attached file for the same. i am doing parameter maps for the first time so facing many obstacles, but m determined to do it.

Request you to guide me to overcome the obstacles.

Thanks

Hi,

Two questions...

Firstly, the error refers to a returned value of '1003, 1004'. What value is in the parameter map for the country corresponding to your user name?

Secondly, it looks like the data type of the country item is integer? Is this the case?

Cheers!

MF.

Hi MFGF,

1>>'1003,1004' refers to the country code, i.e 1003 for United States and 1004 for Canada. In parameter map in the Key field i have entered the username 'xyz' and in the value field i have entered '1003'

2>>i am refering to country code field. the datatype is integer.

Also find the attached file for Parameter map configuration. i am trying to achieve user1 should only be able to view data for country code 1003 and user2 data for 1004.

Thanks

Hi MFGF,

Also find the attached file for the expression written in the filter tab. Its the one which you had given me in your previous reply.

Thanks!

Ah - I see. One issue is that you have 1003,1004 as the default value in the parameter map. The other is that we are putting single quotes around the value with sq()

Try changing the default value in the parameter map to 0 (zero)

Then modify the expression as follows:

[SECURITY_FM].[COUNTRY].[COUNTRY_CODE] = #$Country{$account.personalInfo.userName}# or 0 = #$Country{$account.personalInfo.userName}#

Cheers!

MF.

Hi MFGF,

I did the above steps, it resolved the error in the report but the filter condition is not getting satisfied. when i log on as user which i have mentioned in the parameter map, he is able to view list of all countries.

Thanks!

Hi,

Where did you add the filter? To the Countries query subject?

What happens if you add an item to the report that displays $account.personalInfo.userName - does it tie up with the value you entered as the key in your parameter map?

MF.

Hi MFGF,

1>>yes i did add the filter to the countries query subject. have attached the same in the file.

2>>i am not too clear about the second point  :(

Thanks!

Hi,

When you are testing in Framework Manager it looks like the parameter map is returning 1004 for the user you are logged in as (which is promising). I'm interested to see what Cognos thinks your user name is when you are logged in via the browser and running the report.

In your report, add a singleton to your page header and drag a query calculation into the singleton. Create the expression as #sq($account.personalInfo.userName)#

Does the user name it displays match the expected entry in the parameter map?

MF.

Hi MFGF,

i follwed the steps given by you and got the follwing output which i am attaching below.

Quote from: Raghuvir on 02 Apr 2014 08:19:12 AM
Hi MFGF,

i follwed the steps given by you and got the follwing output which i am attaching below.

Hi,

Thanks - but you didn't answer the question I asked.

Quote from: MFGF on 02 Apr 2014 08:04:35 AMDoes the user name it displays match the expected entry in the parameter map?

MF.

Hi MFGF,

Sorry for that.
The expected entry was '1004' which is mapped to a user named Prasad. So username is not matching the expected entry in the parameter map.

Thanks!

Quote from: Raghuvir on 02 Apr 2014 08:29:58 AM
Hi MFGF,

Sorry for that.
The expected entry was '1004' which is mapped to a user named Prasad. So username is not matching the expected entry in the parameter map.

Thanks!

Hi,

This is why you are not getting the desired 1004 country code - the user you are logged in as (Bradley.Dias1) does not have a user name that matches the entry you defined in the parameter map (Prasad). If you add an entry in the parameter map for Bradley.Dias1 and the relevant country code for this user, your filter will display only that country. If you log in as a user not defined in the parameter map you will see all countries.

Is this the behaviour you want?

MF.

Hi MFGF,

Please find the attached file for parameter map configuration. My requirement is bradley.dias1 should only be able to see data for country code 1003. same for user prasad should only see data for country code 1004. rest anyone logged in should be able to see all countries.


Thanks

Quote from: Raghuvir on 02 Apr 2014 08:46:58 AM
Hi MFGF,

Please find the attached file for parameter map configuration. My requirement is bradley.dias1 should only be able to see data for country code 1003. same for user prasad should only see data for country code 1004. rest anyone logged in should be able to see all countries.


Thanks

Hi,

You don't have an entry in your parameter map for Bradley.Dias1 - the entry is currently bradley.dias1

Change the entry to Bradley.Dias1 and it should work.

MF.

Hi MFGF,

I did the changes and its working now  :) thanks a lot for ur guidance and support.

are those entries case sensitive ?

Thanks!

Yep - case sensitive :)

Glad we got it working!

MF.

Hi MFGF,

Thanks! Would not have been possible without your guidance. Apologies if i have bothered you.

One query, Can we add Groups or Roles in the Key for Parameter Maps ?

Thanks

Quote from: Raghuvir on 02 Apr 2014 10:33:40 AM
Hi MFGF,

Thanks! Would not have been possible without your guidance. Apologies if i have bothered you.

One query, Can we add Groups or Roles in the Key for Parameter Maps ?

Thanks

You're welcome! :)

Yes you can use Groups and/or Roles as keys in your parameter map. In this case your macro would need to use the CSVIdentityName() function to take each of your user's groups and roles and look them up in your parameter map.

Cheers!

MF.

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!

Hi MFGF,

Could you please guide me with the expression ?

Regards!

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.

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

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.

Hi MFGF,

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

Regards!