XQE-V5-0005 Identifier not found

[kmedwards76]

In Framework, I am trying to create a weekly calculation in my business layer that has this:

#strip( substitute( sq(1) , 'total([Business Layer].[Weekly Account Solution].[Cost Incremental]  ) / total([Business Layer].[Weekly Account Solution].[Revenue]  )' ,CSVIdentityName(%biSecureCostMetrics  ) ),'''') #

But when I validate, I get this error: XQE-V5-0005 Identifier not found '[Business Layer].[Weekly Account Solution].[Cost Incremental]'.

What's odd is that Cost Incremental is most definitely available and present, and I have even tested the Cost Incremental field in the business layer and the data layer and received results.

There is already a monthly version of this calculation and it validates fine. But, again that was set up by someone else and I'm just trying to mimic their process to introduce the weekly data. All the Weekly Query Subjects in the Business Layer have validated successfully. Co-worker is stumped as well and verified I was doing everything correctly.

Thanks,

[bus_pass_man]

i.
Go into the xqe directory and open the xqe log file.   Search for the error.  There might be more information there.

ii.
Humour me a bit.

Expand Weekly Account Solution and select it.
Confirm that there's no leading or trailing spaces in its name.
Select Cost Incremental and do the same.

iii.
An alternate technique for that would be to drag the objects into the expression and substitute them for what you have.

iv.
Another technique to try would be build the expression up bit by bit.   That might not be necessary as the problem seems to be in the replacement string parameter.   My guess is that there's either leading or trailing spaces, a typo, or a case mix up.   I'm outlining it just so that the technique would be illustrated.

Create another calculation with just total([Business Layer].[Weekly Account Solution].[Cost Incremental]  ) / total([Business Layer].[Weekly Account Solution].[Revenue] in it and test it. 

If that works then put it into the substitute function with a mock value rather than the csvidentitylist stuff.  This would control for a problem with the function.  You might also want to try a value which is expected not to substitute your replacement string.   

I can't help on the csvidentitylist stuff as I'm crap at that.

At this point you will be here:

substitute( sq(1) , 'total([Business Layer].[Weekly Account Solution].[Cost Incremental]  ) / total([Business Layer].[Weekly Account Solution].[Revenue]  )' ,CSVIdentityName(%biSecureCostMetrics  ) ).  Test that.

At that point, if it works, you would have isolated the problem to the strip macro function.

[CognosPaul]

I agree that the first step should be building the expression without the macro, just total()/total().

Is object security enabled? Does Weekly Account Solution have the object security defined?

Also, what happens if you publish it, does it work in reporting? I've seen a few cases where FM will fail a validation on something that works perfectly.

[kmedwards76]

I agree that the first step should be building the expression without the macro, just total()/total().

Is object security enabled? Does Weekly Account Solution have the object security defined?

Also, what happens if you publish it, does it work in reporting? I've seen a few cases where FM will fail a validation on something that works perfectly.

Paul,
Does this look remotely familiar to you?

LOL

-Kaylee

[kmedwards76]

i.
Go into the xqe directory and open the xqe log file.   Search for the error.  There might be more information there.

ii.
Humour me a bit.

Expand Weekly Account Solution and select it.
Confirm that there's no leading or trailing spaces in its name.
Select Cost Incremental and do the same.

iii.
An alternate technique for that would be to drag the objects into the expression and substitute them for what you have.

iv.
Another technique to try would be build the expression up bit by bit.   That might not be necessary as the problem seems to be in the replacement string parameter.   My guess is that there's either leading or trailing spaces, a typo, or a case mix up.   I'm outlining it just so that the technique would be illustrated.

Create another calculation with just total([Business Layer].[Weekly Account Solution].[Cost Incremental]  ) / total([Business Layer].[Weekly Account Solution].[Revenue] in it and test it. 

If that works then put it into the substitute function with a mock value rather than the csvidentitylist stuff.  This would control for a problem with the function.  You might also want to try a value which is expected not to substitute your replacement string.   

I can't help on the csvidentitylist stuff as I'm crap at that.

At this point you will be here:

substitute( sq(1) , 'total([Business Layer].[Weekly Account Solution].[Cost Incremental]  ) / total([Business Layer].[Weekly Account Solution].[Revenue]  )' ,CSVIdentityName(%biSecureCostMetrics  ) ).  Test that.

At that point, if it works, you would have isolated the problem to the strip macro function.

i. attached is section of that log, i couldn't find anything more telling of what is going on it thought.

ii. I had already checked the trailing/leading space issues yesterday, and even did so again today.

iii. I had already done this yesterday as well.

iv. this validated: total([Business Layer].[Weekly Account Solution].[Cost Incremental]  ) / total([Business Layer].[Weekly Account Solution].[Revenue]

this did NOT validate: substitute( sq(1) , 'total([Business Layer].[Weekly Account Solution].[Cost Incremental]  ) / total([Business Layer].[Weekly Account Solution].[Revenue]  )' ,CSVIdentityName(%biSecureCostMetrics  ) ) and yields the following error: XQE-V5-0017 V5 syntax error found for data item 'expr' of query 'fmQuery', invalid token "%" found after "substitute( sq(1) , 'total([Business Layer].[Weekly Account Solution].[Cost Incremental]  ) / total([Business Layer].[Weekly Account Solution].[Revenue]  )' ,CSVIdentityName(".

**ETA** I am on Version 11.1 Framework Manager

[kmedwards76]

I agree that the first step should be building the expression without the macro, just total()/total().

Is object security enabled? Does Weekly Account Solution have the object security defined?

Also, what happens if you publish it, does it work in reporting? I've seen a few cases where FM will fail a validation on something that works perfectly.

The object security is the same for both the monthly and weekly. See attached.

[bus_pass_man]

Progress of a sort.

What happens if you mock in values rather than the CSVIdentityName(%biSecureCostMetrics  ) stuff?  Put in a value which you want the csvidentity stuff to resolve to.   This would control for a problem with the function.  You might also want to try a value which is expected not to substitute your replacement string.   Also see if you can substitute a value rather than the %biSecureCostMetrics stuff (mocking that part as well rather than the entire thing).

Since I'm rubbish at CSVIdentityName, I can't say anything intelligent about that as I don't know the syntax. 

The query engine doesn't like the %.  The stack trace certainly thinks that the object reference is not valid but the error is tripping over that.

[kmedwards76]

Hey, I had been copying out the code previously and deleting the existing data items  when I created the weekly IOR calc. I decided to just type it in from scratch and drag over the data item, and it worked. I guess copying and pasting it meant there might have been some sort of reference or something to the existing query subject/data item. I have no clue, but I think it's working now. Thanks for the help!