Parameter Map Syntax

[cognostechie]

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

[MFGF]

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.

[cognostechie]

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.