I am trying to use a Parameter Map by passing the value of a Session Parameter for security. I can't figure out what am I doing wrong. It just doesn't like the syntax. This is what I have:
[Database Layer].[Orders].[Exclude_These] not in #$DataSecurity{$account.personalInfo.userName}#
Exclude_These has the data in the format "HR,IT,Purchasing" without the double-quotes. DataSecurity is the name of the Parameter Map and the Value column is mapped to the Exclude_These which has the comma seperated values. The 'Key' column in the Parameter Map is the Userid whose value is exactly the same as the Session Parameter account.personalInfo.userName
Hi,
The easiest way to debug this is to extrapolate the results of the macro into your query. If I'm understanding your post correctly, what you have so far would look like this:
[Database Layer].[Orders].[Exclude_These] not in HR,IT,Purchasing
This is not legal syntax - the list of items should each be in single quotes, and the entire list should be within parentheses - something like:
[Database Layer].[Orders].[Exclude_These] not in ('HR','IT','Purchasing')
Take a look at the CSVIdentityName macro function - will this return an appropriate result?
Regards,
MF.
Thanks !
It is correct that the values should have been like ('HR','IT','Purchasing') with the single quotes but when you put values like HR,IT,Purchasing, the 'in' operator works as long as the values are in a single field in the Table.
I was able to make it work by using INSTR function, breaking the values into 3 different values and then using them in the 'not in' operator.
I will check the CSVIdentity Macro you suggested.
Quote from: cognostechie on 26 Aug 2010 04:00:24 PM
Thanks !
It is correct that the values should have been like ('HR','IT','Purchasing') with the single quotes but when you put values like HR,IT,Purchasing, the 'in' operator works as long as the values are in a single field in the Table.
I was able to make it work by using INSTR function, breaking the values into 3 different values and then using them in the 'not in' operator.
I will check the CSVIdentity Macro you suggested.
Hi could you both guys be more explicit about using CSVIdentity Macro or INSTR function.
in my case that syntax [PV_Positions].[DIM_ACCOUNT].[ACCT_KEY] in #$User_LookUp{$account.personalInfo.userName}# doesn't work, User_lookUp conatins User key and ACCount Key
Thank you in advance for ur feedback
Hello Cognostechie,
If the column in the DB has a value in the format (A,B,C,D,E), then to apply your filtering from parameter maps you will have to break it into multiple rows and then do an "in" as you are currently doing.
Example: (My table name is ACCESS_CSV with two columns Userid and Access.
Use this to break each Comma separated value into separate records
SELECT A.[Userid],
Split.a.value('.', 'VARCHAR(100)') AS Access
FROM (SELECT [Userid],
CAST ('<M>' + REPLACE([Access], ',', '</M><M>') + '</M>' AS XML) AS Access
FROM ACCESS_CSV) AS A CROSS APPLY Access.nodes ('/M') AS Split(a);
This will give you a result as such:
Then, you could apply your filtering based on the parameter map. Example: Your parameter map returns ('B','C')
The condition would then be [Access] in ('B','C')
Quote from: cognos810 on 17 Sep 2014 03:35:30 PM
Hello Cognostechie,
If the column in the DB has a value in the format (A,B,C,D,E), then to apply your filtering from parameter maps you will have to break it into multiple rows and then do an "in" as you are currently doing.
Example: (My table name is ACCESS_CSV with two columns Userid and Access.
Use this to break each Comma separated value into separate records
SELECT A.[Userid],
Split.a.value('.', 'VARCHAR(100)') AS Access
FROM (SELECT [Userid],
CAST ('<M>' + REPLACE([Access], ',', '</M><M>') + '</M>' AS XML) AS Access
FROM ACCESS_CSV) AS A CROSS APPLY Access.nodes ('/M') AS Split(a);
This will give you a result as such:
Then, you could apply your filtering based on the parameter map. Example: Your parameter map returns ('B','C')
The condition would then be [Access] in ('B','C')
hi cognos techie ,
where do u apply that query??
SELECT A.[Userid],
Split.a.value('.', 'VARCHAR(100)') AS Access
FROM (SELECT [Userid],
CAST ('<M>' + REPLACE([Access], ',', '</M><M>') + '</M>' AS XML) AS Access
FROM ACCESS_CSV) AS A CROSS APPLY Access.nodes ('/M') AS Split(a);
first time to use parameter session please could you detail the process.
tks :)
Hello bbtresoo,
You would need to create a separate data source query subject with the SQL expression provided earlier. Apply your paramter macro filter on it, then join it to any dimension/fact to filter your records.
Thanks,
Cognos810
Quote from: cognos810 on 18 Sep 2014 01:42:58 PM
Hello bbtresoo,
You would need to create a separate data source query subject with the SQL expression provided earlier. Apply your paramter macro filter on it, then join it to any dimension/fact to filter your records.
Thanks,
Cognos810
tks I'll give it a try