Hello All,
I am trying to get the username in uppercase for using it in a filter in FM.
I am using the below expression.
# toupper (sq($account.personalInfo.userName))#
This works in report studio but gives me an error QE-DEF-0406 Unknown function 'toupper' in macro expression in FM.
My cognos version is 10.2.1 FP2.
Any help would be appreciated.
Thanks in advance
I cannot verify this at the moment but I;m almost certain the macro function is "upper", not "toupper".
I'm looking at the list of functions and I'm not seeing either 'upper' or 'ToUpper' as a macro function.
MF.
This should work:
# 'upper ('+sq($account.personalInfo.userName)+')'#
Thanks Nimrod and MFGF for looking into it.
Please see the screenshot. toupper is available in the macro functions. It is a filter creation window.
I am not very sure how could I use this # 'upper ('+sq($account.personalInfo.userName)+')'#
in the below expression.
#sq($PARAM_MAP{$account.personalInfo.userName})# contains [Database view].[TBL_XX].[CLM]
The parameter map 'PARAM_MAP' has the login in uppercase. So I need to pass the session parameter also in upper case.
Right,
I don't have access to 10.2.1.FP2 at the moment, but on my 10.2.1 FP1 that function is simply not there. I can however see it in IBM documentation - weird.
Check that your FP is installed for your framework server as well as your BI server (You'll need a 32 bit version of the FP).
Yes, I installed 10.2.1 FP2 32 bit on FM also. Restarted server.
I tried to create a calculation upper(#sq($account.personalInfo.userName)#) and insert inside the Parameter map. But it shows error. It looks like parameter map needs the exact string inside it.
I am wondering how it is normally done in the previous versions :o
Is it required to have exactly the same id in the DB as in the active directory!!
I've had a similar issue. The user needed do a lookup on the uppercase version of the username. You can't embed a database function in a param map. The solution was done in framework manager.
Create a new namespace, call it session params and hide it from your packages. In it, create a new query with two data items:
1. Name: key expression: 1
2. Name: userName expression: upper(#sq($account.personalInfo.userName)#)
Create a parameter map, call it upperUserNameLookup on that query, with key as the key and userName as the value.
Next, create a session parameter called upperUserName with the expression #$upperUserNameLookup{1}#
Now you can use the upperUserName session param in place of the account.personalInfo.userName session param. I'm not sure if there's any optimizations in Cognos when using a session param instead of a simple param map, so you could probably skip that step and just call the parameter map.
Thank you Paul :).
I will try and update you.