Problems with Cognos Impromptu Catalog Calculation

[ambady]

Hi Everybody,

First time I am logging into this forum and finds really helpfull.

I am facing a problem in Impromptu. I created some lengthy calculations in Catalog Level (Calculations are with many ranges. For example:- if a>0 and a<10 then 'Range 0' else if a>10 and a<20 then 'range 1' ...... up to 'Range 21').

When i was trying to add this Calculated field in to the Report, It was not responding.

So I modified the COGNDMOR.ini file as per the advice from cognos. I changed the Case_to_decode = F. After this there was good improvement in the performance. But today, when I tried to add another lengthy calculation, the report is not running.

Can any one help me??

Thanks in advance.
- Biju

 

[griffindm]

Hi ambady,

The problem with this type of calculation on Oracle is that Oracle (thru version 8.1.5 at least) has very limited support for the SQL CASE WHEN statement. This statement is very useful in working with multiple range statements. Impromptu handles the If-then-else statement locally unless you set the auto decode on, which makes for some very convoluted decode statements with multiple nesting, which kills performance.

SQL Server doesn't have this problem, but that doesn't help you in this case.

I do a lot of manipulation at the database level. As Oracle will accept a CASE WHEN statement in SQLPlus, you might want to create a SQL View that uses this syntax, and then just include the view into your catalog. I've never had to try this, so I don't know for certain that it will work. I do know that Oracle will support the syntax in a select via SQLPlus.

The syntax would be:

CREATE VIEW viewname AS
SELECT col1,col2,col3, CASE WHEN COL4 BETWEEN 0 AND 10 THEN '0', ELSE WHEN COL4 < 20 THEN '1', ELSE '2' END &quot;COLNAME5&quot; FROM TABLE tablename WHERE filter;

If you can embed all the data you need in the view, including the calculated columns, Impromptu will give you a much faster report as well.

Hope this helps. Respond if you need clarification.

Regards,

Dave Griffin ;-)

 

[griffindm]

If anyone is interested, I was able to create the view noted above on Oracle 8.1.6 in SQLPlus. This means that if you have a lot of standard 'range' calculations, you can embed the range logic into an Oracle view and gain the benefit of database processing rather than local processing for these types of expressions.

Regards,

Dave Griffin ;-)