Dynamic Filter with Parameter Map

[kenrisen]

Hi all..

I've a requirement to filter my client data in Framework Manager using Data Security with a conditional logic.

For example, on 2011, i've 1 cognos group(group1) and 1 Framework Manager filter (sales office contains SO1,SO2) that will restrict the data for group 1. On 2012, my client change the policy and i need to update my filter for data security from 'sales office contains SO1,SO2' to 'sales office contains SO1,SO3'

If i just change the old filter with new policy, the report will give the correct comparison between SO1 & SO3 when my user on group1 choose 2012 in fiscal year period prompt. The Other hand, when user on group1 choose 2011, the report will give the wrong result because it still compare between SO1 & SO3.

We can use parameter map to make the filter dynamic if the requirment just base on user session and in the filter we put [Trading Domestic - Profitability Figures].[Sales Region].[Sales Region].[Sales Region].[Sales Region]  contains #sq($ParamMap{$account.personalInfo.userName})#

From that dynamic concept filter, I think to solve my problem i need get my cognos user group concate with the year value that they choose in report. That value will use as a Parameter Key.

can we create a key for parameter map using cognos group concate with year?so when user choose a year from year prompt FM will concate with cognos group.

does anybody have an experience using paramater map for my client requirement?Or any other solution to meet the requirement?



Thx,

Kenrisen

[CognosPaul]

I think you're going in the right direction.

Now you're saying this is only for a specific group? Do other groups have similar restrictions?

You could do this by creating three filters in FM.

First one, call it Default, with the expression 1=1.

The second one would handle the sales office filter, lets call it Group1. Create a table with the fields year and set up a parameter map on that table. The Group1 filter would then be:

Code Select Expand

[Trading Domestic - Profitability Figures].[Sales Region].[Sales Region].[Sales Region].[Sales Region]  contains #sq($soFilterLookup{prompt('FiscalYear','integer')})#

Now the third filter will simply point to one of previous two.  I'm making the assumption that ParamMap is pointing to a query that's only has users in Group1, and returns the value Group1. Set the ParamMap default value to Default, and use the expression:

Code Select Expand

[Namespace].#sb($ParamMap{$account.personalInfo.userName})#

So if the user is in Group1, the filter will resolve to [Namespace].[Group1], which then resolves to [Trading Domestic - Profitability Figures].[Sales Region].[Sales Region].[Sales Region].[Sales Region]  contains #sq($soFilterLookup{prompt('FiscalYear','integer')})#. Otherwise the filter resolves to [Namespace].[Default], which in return resolves to 1=1.

[kenrisen]

Hi Paul..thx for the reply, you are my savior.. ;D

in the real situation, I have 26 group that will apply similar restriction like group 1.

can you explain more detail fro the step to achive your solution because I still confused to implement that.

I already create 3 filter in framework but i still confused which filter that i need put on specify data security function in FM.

Btw in the second filter you mention #sq($soFilterLookup{prompt('FiscalYear','integer')})#, if i not wrong that syntax is used for creating the filter in FM (but i never implement that..^^')  to achive that, i need to create parameter map with name soFilterLookup that contain 2 columns,the first column will use for year and the second column for the sales region value.

can I use my SAP BW FiscalYear filter like in report studio?because every time we run the report cognos will propmt the SAP BW fiter first.

[kenrisen]

hi paul,

i got this error when i applied the third filter either in data security or filter in query subject

"ANS-MES-0003 A server error occurred. Unable to complete the action."

If i just applied the second filter, the report run perfect so I think the problem in my third filter.
My third filter = [Trading Domestic - Profitability Figures].#sb($ParamMap2{$account.personalInfo.userName})#
Namespace = Trading Domestic - Profitability Figures

In ParamMap2, I define username in the first column, and filter name in the second column
ex:
kenrisen | group1
andy       | group 2

could you give me some advise to solve this problem?

[CognosPaul]

Can you put #sq(sb($ParamMap2{$account.personalInfo.userName}))# into a new query?

In theory it should resolve to something like [group1] (the sq function wraps the string with apostraphes, and the sb function wraps the string in brackets). It should match, including case, one of the first two filters you made.

So if your filter is [Trading Domestic - Profitability Figures].[Group 1], but #sb($ParamMap2{$account.personalInfo.userName})# resolves to [group 1] then it won't work.

[kenrisen]

Hi Paul,

When I put #sq(sb($ParamMap2{$account.personalInfo.userName}))#, I got '[GROUP1]' (exactly same with the value in ParamMap2.
My ParamMap2 --> kenrisen | GROUP1
Last Filter --> [Trading Domestic - Profitability Figures].#sb($ParamMap2{$account.personalInfo.userName})#

but when i applied my last filter  and run the package from the analysis studio, I got
"ANS-MES-0003 A server error occurred. Unable to complete the action."

But in report studio i got another error
XQE-PLN-0117

      Invalid expression involving item '[Trading Domestic - Profitability Figures].[GROUP1]'.

Btw I attached my framework file, can you help to check my framework?


Detail error in report studio

=== JAVA STACK TRACE === XQE-PLN-0117 Invalid expression involving item '[Trading Domestic - Profitability Figures].[GROUP1]'. at com.cognos.xqe.transformation.v5.ValidateBoundIdentifier.apply(ValidateBoundIdentifier.java:63) at com.cognos.xqe.query.engine.QTETransformationEngine.applyTransformation(QTETransformationEngine.java:1194) at com.cognos.xqe.query.engine.QTETransformationEngine.applyApplicableTransformation(QTETransformationEngine.java:1112) at com.cognos.xqe.query.engine.QTETransformationEngine.applyIndexedTransformations(QTETransformationEngine.java:630) at com.cognos.xqe.query.engine.QTETransformationEngine.transformationIteration(QTETransformationEngine.java:565) at com.cognos.xqe.query.engine.QTETransformationEngine.applyTransformations(QTETransformationEngine.java:368) at com.cognos.xqe.query.planner.QueryPlanner.plan(QueryPlanner.java:304) at com.cognos.xqe.query.planner.QueryPlanner.planQuery(QueryPlanner.java:344) at com.cognos.xqe.query.engine.QueryEngine.prepareRequest(QueryEngine.java:594) at com.cognos.xqe.query.engine.QueryEngine.fetchRSAPIDatasets(QueryEngine.java:448) at com.cognos.xqe.query.engine.QueryEngine.executeRequest(QueryEngine.java:400) at com.cognos.xqe.bibushandler.ExecuteRequestAdapter.executeRequest(ExecuteRequestAdapter.java:96) at com.cognos.xqe.cubingservices.V5QueryHandler.executeRequestInSequence(V5QueryHandler.java:555) at com.cognos.xqe.cubingservices.V5QueryHandler.execute(V5QueryHandler.java:397) at com.ibm.cubeservices.mdx.v5.V5ProviderFacade.execute(V5ProviderFacade.java:98) at com.cognos.cubics.providers.cubingservices.CSStatement.execute(CSStatement.java:152) at com.ibm.cubeservices.mdx.v5.V5RequestHandler.handleRequest(V5RequestHandler.java:59) at com.ibm.cubeservices.mdx.comms.Servlet.processMessage(Servlet.java:124) at com.ibm.cubeservices.mdx.comms.ComWorker.processQueryInputMsg(ComWorker.java:361) at com.ibm.cubeservices.mdx.comms.ComWorker.processInput(ComWorker.java:266) at com.ibm.cubeservices.mdx.comms.ComWorker.call(ComWorker.java:176) at com.ibm.cubeservices.mdx.comms.ComWorker.call(ComWorker.java:78) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) at java.util.concurrent.FutureTask.run(FutureTask.java:138) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) === END OF JAVA STACK TRACE === RSV-SRV-0042 Trace back:RSReportService.cpp(856): XQEException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(259): XQEException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(792): XQEException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(249): XQEException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(845): XQEException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(625): XQEException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(290): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): XQEException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(357): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179): XQEException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303): XQEException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(175): XQEException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137): XQEException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(357): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardRSQueryMgr.cpp(1164): XQEException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1324): XQEException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgr.cpp(1624): XQEException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(189): XQEException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147): XQEException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1145): XQEException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1102): XQEException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): XQEException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): XQEException: CCL_RETHROW: QFSQuery::Execute v2XQEConnector.cpp(255): XQEException: CCL_THROW: XQEConnector::send

[kenrisen]

I forgot to tell you, i put my last period on data security so i map with cognos group.
Is it correct put the last filter in data security?

[CognosPaul]

I'll check the FM tomorrow on my test machine.

The XQE errors from the DQM engine. It may be possible that my solution doesn't work on DQM/DMR. I implemented it on a purely relational system in 8.4 on a DB2 database.

It's resolving the expression correctly - at least it's attempting to call the filter [Trading Domestic - Profitability Figures].[GROUP1].

Questions to answer:

• What happens if you replace the macro with the filter reference?
• What happens if you replace the macro with the expression inside the filter?
• What happens when you run the package from QS?
• What happens if you remove the filter from the data security and attempt to use it inside a query in RS?
• Is the filter filtering a regular dimension or query/

There are a few other solutions. At worst comes to worst you could probably put another level of parameter maps that contain the entire filter expression.
Something like:
$FilterLookup{$GroupLookup{$account.personalInfo.userName}}

[kenrisen]

I created the package from the scratch (Please find the new package in attachment.) and I still got

XQE-PLN-0117
      Invalid expression involving item '[Trading Domestic - Profitability Figures].[group1]'.
     Details
DPR-ERR-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2012-05-09-00:02:04.101-#11



Questions to answer:
What happens if you replace the macro with the filter reference?

I still got the same error when I change the #sb($ParamMap2{$account.personalInfo.userName})# to [group1]

XQE-PLN-0117
      Invalid expression involving item '[Trading Domestic - Profitability Figures].[group1]'.
     Details
DPR-ERR-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2012-05-09-00:02:04.101-#11


What happens if you replace the macro with the expression inside the filter?

Can you explain more detail?
If you mean I need try to put my second filter ( [Trading Domestic - Profitability Figures].[Sales Region].[Sales Region].[Sales Region].[Sales Region]  contains #sq($soFilterLookup{prompt('FiscalYear','integer')})# )

the report will run correctly but it will apply to all user because we pass the third value that check which group filter is correct for the user

What happens when you run the package from QS?

XQE-PLN-0117
      Invalid expression involving item '[Trading Domestic - Profitability Figures].[group1]'.
     Details
DPR-ERR-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2012-05-09-00:07:49.940-#12



What happens if you remove the filter from the data security and attempt to use it inside a query in RS?

it's RUN!!!!!!OMG!!!! I will try to test first in my sample report with this way if the report run smoothly i will implement in my real report and post the result to this forum..:)

FYI...I didnt turn off the DQM in framework manager

BTW do you know why we got an error when we implement in the Framework Manager?

Is the filter filtering a regular dimension or query?
The filter will filter the regular dimension


If I applied [Trading Domestic - Profitability Figures].[Sales Region].[Sales Region].[Sales Region].[Sales Region]  contains #sq($soFilterLookup{$ParamMap2{$account.personalInfo.userName}})# in Framework Manager filter I got this error

XQE-GEN-0018
      Query Service internal error has occurred, please see the log for details.
     Details
DPR-ERR-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2012-05-09-00:42:39.560-#14

Paul, if i put the filter in RS, do you have any concern?

do you know the macro function to catch a prompt for SAP BW?if Framework manager can not catch the prompt, my idea I will create a prompt in RS with the prompt from SAP BW and textbox that will be used to define prompt from framework manager.
When User choose the fiscal year in sap BW prompt, javascript will insert the value to the textbox , so when the user submit they dont need to write the year in the textbox.
Do you have any other solution for this?

THANK YOU...:)

[kenrisen]

Paul can we use Cognos group rather than using personalInfo.userName
( [Namespace].#sb($ParamMap{$account.personalInfo.userName})# ) in parameter map?
because I have a lot of user, will be easier if framework can detect their Cognos Group.

[CognosPaul]

There's no simple way of looking up a single Group. Users tend to have multiple groups (Everyone, consumers, etc). There are macro functions you can use to retrieve them, but none of them are perfect to what you need. For example, the CAMIDListForType()  function returns:

Code Select Expand

#csv(CAMIDListForType('role'))#

Code Select Expand

'CAMID("::System Administrators")','CAMID(":Authors")','CAMID(":Query Users")','CAMID(":Consumers")','CAMID(":Metrics Authors")','CAMID(":Metrics Users")','CAMID(":Planning Contributor Users")','CAMID(":Controller Users")','CAMID(":Analysis Users")','CAMID(":PowerPlay Users")','CAMID(":Data Manager Authors")','CAMID(":Readers")','CAMID(":Express Authors")','CAMID(":Adaptive Analytics Users")','CAMID(":Statistics Authors")'

I could use grep to retrieve a specific role:

Code Select Expand

#join('',grep('Stat',CAMIDListForType('role')))#

Code Select Expand

'CAMID(":Statistics Authors")'

The CSVIdentityName functions may be useful for you, unfortunately they always returns comma separated values. For example,

Code Select Expand

#CSVIdendityNameList#

Code Select Expand

'Adaptive Analytics Users', 'Analysis Users', 'Anonymous', 'Authors', 'Cognos', 'Consumers', 'Controller Users', 'Data Manager Authors', 'Everyone', 'Express Authors', 'Metrics Authors', 'Metrics Users', 'Planning Contributor Users', 'PowerPlay Users', 'Query Users', 'Readers', 'Statistics Authors', 'System Administrators'


You can create a param map, call it groupLookup, and put in Statistics Authors as key and A as the value.

Code Select Expand

#CSVIdentityName(%groupLookup)# will return 'A'. Notice the single quotes, this prevents it from being useful, but we can do another layer of param maps, groupLookup2, which as 'A' (including the quotes, as the key and Group1 as the value.

Code Select Expand

#$groupLookup2{CSVIdentityName(%groupLookup)}# will now return Group1 without the quotes, which can then be used in any macro function.

The downside of this, besides being overly complicated, is that if the user has two or more groups that match the first param map, it won't work. The second lookup will attempt to make a lookup using both of them, #$groupLookup2{'A','B'}#

[kenrisen]

Hi Paul..

Can I create a paramater map table to assign 1 key to multiple value?
ex:

Key	Value
2011	'SO1','SO2','SO3'
2012	'SO1','SO2'

[CognosPaul]

What you're suggesting would work fine. Remember param maps return fragments, not strings. So [Region] in (#$lookUp{timestampMask($current_timestamp,'yyyy')}#) would resolve to:
[Region] in ('SO1','SO2')

If your values were SO1, SO2 (without the quotes) you could do something like
[Region] in (#
csv(split(',',$lookUp{timestampMask($current_timestamp,'yyyy')}))
#)

[bbtresoo]

What you're suggesting would work fine. Remember param maps return fragments, not strings. So [Region] in (#$lookUp{timestampMask($current_timestamp,'yyyy')}#) would resolve to:
[Region] in ('SO1','SO2')

If your values were SO1, SO2 (without the quotes) you could do something like
[Region] in (#
csv(split(',',$lookUp{timestampMask($current_timestamp,'yyyy')}))
#)

Hi Cognos Paul,

I came across this topic and u look very comfortable with parameter session and all. That's why I come to you.

I created a paramater map and here is the filter I wrote #sq($User_Look_up{$account.personalInfo.userName})# = 368564 or [PV_Positions].[DIM_ACCOUNT].[ACCT_KEY]  = #sq($User_Look_up{$account.personalInfo.userName})#

The syntax seems correct , I don't get any error but when I run the query subject here is what I get as error XQE-GEN-0002 An unexpected exception occured:  Infinite or Nan

Any ideas from what that might be coming?

thank you

[CognosPaul]

The SQ function will wrap the value with quotes, thus explicitly turning the User_Look_up into a string. 368564, and probably [PV_Positions].[DIM_ACCOUNT].[ACCT_KEY], are integers. By comparing a string to an integer, you're forcing an implicit cast. I'm not sure how the DQM engine will handle implicit casting like this, and if the User_Look_up is returning a string, like 123abc, then it will definitely fail.

Does User_Look_up have a default value? Make sure it's numeric. Next, try the filter without the SQ function.

[bbtresoo]

now it works time to time even with sq function. anyway now I put[PV_Positions].[DIM_ACCOUNT].[ACCT_KEY]  = cast(#sq($User_Look_up{$account.personalInfo.userName})#,int) 
because of ur feedback and it works. do you know what can I use besides sq function , because  User_Look_up only doesn't work??

I have another problem, in my function  User_look_up I have 2 columns User key and ACCT_key(account Key).  for example this user key 5063 can access 3 differents accounts key 382759 , 382961 ,382964

but when I hard code the user key as 5063( FM Top Menu actions -> sessions parameters) and run the query item where I defined the filter above mentionned I only get the last account 382964 instead of 3,  one line instead of 3 lines. It doesn't make any sense. I've been tearing my hair up for a week end !!! tahnk you for ur feedback.

[CognosPaul]

parameter map lookups always return a single value. There are a few ways around this though.

1. Pivot the source data.
    This basically involves converting the data to look exactly what you need. key would be 5063 and value would be '382759, 382961, 382964'. If it's done in ETL, or with a well written view, this is probably the best solution for you.
2. CSVIdentityName
    This is a macro function which will look up all the values in a parameter map that matches pieces of the users identity. A good example would be limiting access based on country. You would create a series of roles, each with the country name, and assign users to that. The function would look up the parameter map for each role and return the results as comma separated strings.
3. Redesign the model to set up the lookup table as a dimension.
    You would join this to your accounts table, and force a join to be created with a filter.

I think options 1 and 3 are the best for your situation. 2 is more for assigning security on a higher level, you don't want to create a separate role for each account.

[bbtresoo]

parameter map lookups always return a single value. There are a few ways around this though.

1. Pivot the source data.
    This basically involves converting the data to look exactly what you need. key would be 5063 and value would be '382759, 382961, 382964'. If it's done in ETL, or with a well written view, this is probably the best solution for you.
2. CSVIdentityName
    This is a macro function which will look up all the values in a parameter map that matches pieces of the users identity. A good example would be limiting access based on country. You would create a series of roles, each with the country name, and assign users to that. The function would look up the parameter map for each role and return the results as comma separated strings.
3. Redesign the model to set up the lookup table as a dimension.
    You would join this to your accounts table, and force a join to be created with a filter.

I think options 1 and 3 are the best for your situation. 2 is more for assigning security on a higher level, you don't want to create a separate role for each account.

Thank you it's more clear now.

1.Pivot the source data
=> could u be more specific ? u're talking about converting data , the cast I did isn't sufficient enough? what would u do? just briefly

3. Actually my look up table is a dimension named User List All Accounts  related to DIM Accounts
   there's a join between these two tables    DIM User List Account.Account_key= DIM Accounts.Account_key  , When I force this join and try to add User List Account.User Key = #$account.personalInfo.userName#     the original join disappears automaticaly. In other words how do u do to force a join to be created with a filter.
  correctly ? thanks again for your feedback.

[CognosPaul]

By pivoting the data, I mean to alter the values in the table so each key would appear only once. It's not directly related to casting. Do a google search for: sql rows to comma separated list. Implement the best solution for your database, and you'll end up with a table that looks like:

Key       Value   
5063  |  382759, 382961, 382964
5062  |  382758, 382960, 382963
5061  |  382757
5060  |  382758, 382960, 382963, 382758, 382960,


Where are you trying to set up the filter for the third solution? If you try it in the final query, the join should be there. Is it possible that the two tables are from different databases?

[bbtresoo]

By pivoting the data, I mean to alter the values in the table so each key would appear only once. It's not directly related to casting. Do a google search for: sql rows to comma separated list. Implement the best solution for your database, and you'll end up with a table that looks like:

Key       Value   
5063  |  382759, 382961, 382964
5062  |  382758, 382960, 382963
5061  |  382757
5060  |  382758, 382960, 382963, 382758, 382960,


Thank you very much everything is ok now

Where are you trying to set up the filter for the third solution? If you try it in the final query, the join should be there. Is it possible that the two tables are from different databases?