I think I have a syntax problem and I'm not sure how to fix it.
I'm filling in a Calculation Definition to restrict when the user gets to see the Dollar amount.
Model -> Transformation Layer -> Query Subject -> Query Item -> Calculation Definition.
This is valid when I click the check mark to test for errors.
--------------------------------------
IF(#sq($account.parameters.SECURITY_FL)# ='N')
THEN
([DB-Pool].[GL Detail].[Detail Amount])
ELSE
(IF ( ([TL-Pool].[GL Detail].[Organization ID]) in
('org1, org2, org3')
)
THEN ([DB-Pool].[GL Detail].[Detail Amount])
ELSE (0)
)
--------------------------------------
This gives an error about [XQE-V5-0017 V5 syntax error found after ... in (select ".]
IF(#sq($account.parameters.SECURITY_FL)# ='N')
THEN
([DB-Pool].[GL Detail].[Detail Amount])
ELSE
(IF ( ([TL-Pool].[GL Detail].[Organization ID]) in
(#sq(select [TL-Pool].[ORG_SEC_LOOKUP].[ORG_ID] from [TL-Pool].[ORG_SEC_LOOKUP] )#)
)
THEN ([DB-Pool].[GL Detail].[Detail Amount])
ELSE (0)
)
So I tried making it a string with #sq...
And this gives a "ZQE-GEN-0018 Query Service internal error has occurred."
IF(#sq($account.parameters.SECURITY_FL)# ='N')
THEN
([DB-Pool].[GL Detail].[Detail Amount])
ELSE
(IF ( ([TL-Pool].[GL Detail].[Organization ID]) in
(#sq(select [TL-Pool].[ORG_SEC_LOOKUP].[ORG_ID] from [TL-Pool].[ORG_SEC_LOOKUP] )#)
)
THEN ([DB-Pool].[GL Detail].[Detail Amount])
ELSE (0)
)
I'm sure I'm missing something obvious about how to embed an SQL query in the definition. Can anyone make any suggestions?
Thanks,
Paul
Quote from: FixItNow on 01 Nov 2018 12:39:52 PM
I think I have a syntax problem and I'm not sure how to fix it.
I'm filling in a Calculation Definition to restrict when the user gets to see the Dollar amount.
Model -> Transformation Layer -> Query Subject -> Query Item -> Calculation Definition.
This is valid when I click the check mark to test for errors.
--------------------------------------
IF(#sq($account.parameters.SECURITY_FL)# ='N')
THEN
([DB-Pool].[GL Detail].[Detail Amount])
ELSE
(IF ( ([TL-Pool].[GL Detail].[Organization ID]) in
('org1, org2, org3')
)
THEN ([DB-Pool].[GL Detail].[Detail Amount])
ELSE (0)
)
--------------------------------------
This gives an error about [XQE-V5-0017 V5 syntax error found after ... in (select ".]
IF(#sq($account.parameters.SECURITY_FL)# ='N')
THEN
([DB-Pool].[GL Detail].[Detail Amount])
ELSE
(IF ( ([TL-Pool].[GL Detail].[Organization ID]) in
(#sq(select [TL-Pool].[ORG_SEC_LOOKUP].[ORG_ID] from [TL-Pool].[ORG_SEC_LOOKUP] )#)
)
THEN ([DB-Pool].[GL Detail].[Detail Amount])
ELSE (0)
)
So I tried making it a string with #sq...
And this gives a "ZQE-GEN-0018 Query Service internal error has occurred."
IF(#sq($account.parameters.SECURITY_FL)# ='N')
THEN
([DB-Pool].[GL Detail].[Detail Amount])
ELSE
(IF ( ([TL-Pool].[GL Detail].[Organization ID]) in
(#sq(select [TL-Pool].[ORG_SEC_LOOKUP].[ORG_ID] from [TL-Pool].[ORG_SEC_LOOKUP] )#)
)
THEN ([DB-Pool].[GL Detail].[Detail Amount])
ELSE (0)
)
I'm sure I'm missing something obvious about how to embed an SQL query in the definition. Can anyone make any suggestions?
Thanks,
Paul
Hi,
If you're creating a calculation in your FM model, you need to use legal syntax for Cognos calculations. SQL isn't legal at this level in the model. If you really want to mess around in SQL, you'll need to do this in the definition of the relevant underlying data source query subject. Normally best practice would be to leave the SQL as "Select * from <table>" though, in order to attain query minimization and prevent metadata callbacks.
Can you explain what you are trying to achieve? There's probably a better way than coding SQL.
Cheers!
MF.
End Goal:
I have a session parameter account.parameters.SEC_ORG_GRP_CD that specifies which group a user belongs to and thus which organizations (departments) a user can see.
It is linked to a lookup table that shows GROUP and the related organizations (1 to many). There can be overlap in what the various groups can see.
PUB, 001
PUB, 001.23
PUB, 002.33
ENG, 001
ENG, 004.22
ENG, 004.23.99
ADM, 003
ADM, 003.01
ADM, 003.02
etc.
So the question: "is the ORG_ID in the current record an ORG_ID that the current user is allowed to see?" If so, then dollars, else 0.
The user needs to see the record regardless (description, etc.), but not the money.
select [TL-Pool].[ORG_SEC_LOOKUP].[ORG_ID]
from [TL-Pool].[ORG_SEC_LOOKUP]
where account.parameters.SEC_ORG_GRP_CD = [TL-Pool].[ORG_SEC_LOOKUP].[ORG_GROUP]
The list of groups to departments is dynamic, so I can't hard code the values.
I tried a dynamic parameter map and couldn't get that to work either. (map is ValidOrgList in query below).
I looked at the map, and it's pulling all the items I expect.
In the query, it looks like the map keeps returning a single value, so unless the current record has the first value in the list, it's failing...
IF(#sq($account.parameters.SECURITY_FL)# ='N')
THEN
([DB-Pool].[GL Detail].[Detail Amount])
ELSE
(IF ([TL-Pool].[GL Detail].[Organization ID]
in (#sq($ValidOrgList{
$account.parameters.ORG_SEC_GRP_CD })#)
)
THEN ([DB-Pool].[GL Detail].[Detail Amount])
ELSE (0)
)