Error in expression for Parameter Maps

[Raghuvir]

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

[MFGF]

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.

[Raghuvir]

Hi MFGF,

yes i am coding the expression in the filter tab.

[Raghuvir]

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?

[Raghuvir]

Hi MFGF,

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

thanks in advance

[MFGF]

Hi MFGF,

yes i am coding the expression in the filter tab.

Ok. Good! :)

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.

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.

[Raghuvir]

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

[MFGF]

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.

[Raghuvir]

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

[Raghuvir]

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!

[MFGF]

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.

[Raghuvir]

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!

[MFGF]

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.

[Raghuvir]

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!

[MFGF]

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.

[Raghuvir]

Hi MFGF,

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

[MFGF]

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.

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

MF.

[Raghuvir]

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!

[MFGF]

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.

[Raghuvir]

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

[MFGF]

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.

[Raghuvir]

Hi MFGF,

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

are those entries case sensitive ?

Thanks!

[MFGF]

Yep - case sensitive :)

Glad we got it working!

MF.

[Raghuvir]

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

[MFGF]

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.