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.

[bbtresoo]

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

[cognos810]

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.

Userid	Access
1	A,B
2	B,C

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:

Userid	Access
1	A
1	B
2	B
2	C

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')

[bbtresoo]

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.

Userid	Access
1	A,B
2	B,C

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:

Userid	Access
1	A
1	B
2	B
2	C

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 :)

[cognos810]

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

[bbtresoo]

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