Newb questions

[PaulP2]

I am migrating reports from Crystal 11 to Cognos 8.4, after one week of training. I am having a lot of trouble, and I can't seem to find resources, other than forums, that will help with the detailed syntax questions that keep coming up.

For example, why does this sample code test ok

case
when
(substr([Revenue Summary].[Calendar].[Current Fiscal Period],5,2) in ('05','06','07','08','09','10','11','12')) THEN '1' ELSE '2'
end

but this code does not?

if
(substr([Revenue Summary].[Calendar].[Current Fiscal Period],5,2) in ('05','06','07','08','09','10','11','12')) THEN '1' ELSE '2'

In the second case, I get an error labeled QE-DEF-0261 QFWP.

In short, I think I need to use if-then-else instead of case, as I have been told that I can't use ANDs and ORs in case statements. So what is the correct syntax for if-then-else statments?

 

[blom0344]

You are using native function 'substr' instead of cognos 'substring'! Do not mix native with Cognos

Case expressions do work and needn't be recompiled for database use. Stick to case expressions, which are far more readable when nesting is required!!

Ties Blom

 

[PaulP2]

Thank you very much.

On a more general note, is there any written resource that will cover these kinds of syntax questions? The few books out there don't seem to get very good reviews.

Paul

 

[PaulP2]

To follow up, here is an actual Case statement, where I am getting an error, apparently on the "substr" part of the code.

Background: we use financial periods instead of months. The field [Current Fiscal Period] is a 6-character field in the format 201107 for the current period. Here is the case statement:

case
when substr([Revenue Summary].[Calendar].[Current Fiscal Period],5,2) in ('05','06','07','08','09','10','11','12')
then
case
when [Revenue Summary].[Calendar].[Fiscal Period]=[Revenue Summary].[Calendar].[Current Fiscal Period]-2 then [Revenue]
when [Revenue Summary].[Calendar].[Fiscal Period]=[Revenue Summary].[Calendar].[Current Fiscal Period]-3 then [Revenue]
when [Revenue Summary].[Calendar].[Fiscal Period]=[Revenue Summary].[Calendar].[Current Fiscal Period]-4 then [Revenue]
else 0
end
when substr([Revenue Summary].[Calendar].[Current Fiscal Period],5,2) in ('01','02')
then
case
when [Revenue Summary].[Calendar].[Fiscal Period]=[Revenue Summary].[Calendar].[Current Fiscal Period]-90 then [Revenue]
when [Revenue Summary].[Calendar].[Fiscal Period]=[Revenue Summary].[Calendar].[Current Fiscal Period]-91 then [Revenue]
when [Revenue Summary].[Calendar].[Fiscal Period]=[Revenue Summary].[Calendar].[Current Fiscal Period]-92 then [Revenue]
else 0
end
when substr([Revenue Summary].[Calendar].[Current Fiscal Period],5,2) = '03'
then
case
when [Revenue Summary].[Calendar].[Fiscal Period]=[Revenue Summary].[Calendar].[Current Fiscal Period]-2 then [Revenue]
when [Revenue Summary].[Calendar].[Fiscal Period]=[Revenue Summary].[Calendar].[Current Fiscal Period]-91 then [Revenue]
when [Revenue Summary].[Calendar].[Fiscal Period]=[Revenue Summary].[Calendar].[Current Fiscal Period]-92 then [Revenue]
else 0
end
when substr([Revenue Summary].[Calendar].[Current Fiscal Period],5,2) = '04'
then
case
when [Revenue Summary].[Calendar].[Fiscal Period]=[Revenue Summary].[Calendar].[Current Fiscal Period]-2 then [Revenue]
when [Revenue Summary].[Calendar].[Fiscal Period]=[Revenue Summary].[Calendar].[Current Fiscal Period]-3 then [Revenue]
when [Revenue Summary].[Calendar].[Fiscal Period]=[Revenue Summary].[Calendar].[Current Fiscal Period]-92 then [Revenue]
else 0
end
else 0
end

What I am trying to do here is to capture revenue from a 3-period span from 2 to 4 periods ago.

The error I get is

UDA-SQL-0219 The function "substr" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.

Here is a similar but much shorter statement that works fine:

case
when (substr([Current Fiscal Period],5,2) <> '01')
then
case
when ([Fiscal Period]=[Current Fiscal Period]-1)
then [Revenue]
else 0
end
else
case
when ([Fiscal Period]=[Current Fiscal Period]-89)
then [Revenue]
else 0
end
end



Paul

 

[blom0344]

Evidently, the non-cognos 'substr' function fails to be recompiled when local processing is required. For some reason the query itself can not be executed against the database in one go. When Cognos decides to split parts of the query then it will also require local processing.

My advice: only use native SQL functions if no Cognos alternative can be used. A full set of functions is available when writing your dataitem expression. You will find that Cognos uses substring (as opposed to substr)

Ties Blom

 

[kushal96]

Try putting parantheses like so:

if (substr([Revenue Summary].[Calendar].[Current Fiscal Period],5,2) in ('05','06','07','08','09','10','11','12')) THEN ('1') ELSE ('2')

 

[MrTy]

Is there anyway in a calculated measure (or other item) to compare members of a set or hierarchy? For example, I need a waterfall report:

CASE
WHEN [RowFiscalPeriod] <= [ColFiscalPeriod] THEN [Actual]
ELSE [Forecast]
END

The columns are all the months of the designated fiscal year and the rows are the months up to the current fiscal period.

Thanks in advance for any help.

Ty