Impromptu 7 - Division - Remainder not appearing

[dipat55]

I’m having difficulties displaying calculated decimals fields on the report. Can you please help?

When creating new calculated fields on an existing or new report, the calculated fields do not display the correct values. (It is not display the remained of the value)

For example:

Calculation 1

Field 1 = 7811, Field 2 = 2585, Calculation is = (Field 1 - Field 2) / Field 1 = 0.669 (Correct value)

Calculation 2

Field 1 = 10, field 2 = 8, Calculation is = Field 1 / field 2 = 1.25 (Correct Value)

On the report – Calculation 1 it appears as 0.00
Calculation 2 it appears as 1.00

Is there a way we can make the actual value appear on the report?

The column format is 0.00

 

[DoubleD]

What version of Impromptu are you running, and what datasource are you running against?

 

[dipat55]

Using Cognos Impromptu Administrator ver 7.0.702.0
The datasource is Oracle 8i (8.1.5 or 8.1.6)

Thanks

 

[DoubleD]

This could have to do with your datatypes in Oracle. The calculation should be happening on the db. Are your fields Integer datatypes or something else. If they are set as integers, and your column format is set to 0.00, and you are really only doing the straightforward calculation as stated previously, then I am at a loss.

 

[astra1]

Hi,

Try exporting the data in excel and see if you are getting the correct results!!!
Reply if you are getting correct result

Vish

 

[dipat55]

The report executes properly in Cognos Impromptu 6.0, using the same data, catalog and report. When executing in Impromptu 7, all the calculation fields are at 0 or 1.00.

The catalog and report were upgraded and verified. All looks good. (There is no corruption that I could see)

On the Oracle database ver 8.1.5, the two column fields are
Field 1, Number (12) Default 0 not null
Field 2, Number (12) Default 0 not null

In Query, Profile, Query columns the fields 1 and 2 are interpreted as Packed Decimal, Length = 5, Precision = 9 and Scale = 0
The calculated field is Packed Decimal, Length=65, Precision = 129 and Scale = 0.

The calculated field column format is at 0.00.

I've trying exporting to PDF, Excel and HTML with no success.

Hope this helps a little in your investigation.
Thank you for all your help so far!
Happy hunting!

 

[DoubleD]

The conversion utility from 6 to 7 is not exactly perfect.
Have you tried recreating the report from scratch in version 7?

 

[dipat55]

YES, I've re-created the report using the same catalog with no success.
I've also re-created the catalog using the same database and the report with no success.

I've used a MSAccess database (NWIND.MDB from Microsoft and created a test report and guess what, it works....

I'm not too sure where to go from here. I've hit the wall. Please help!

 

[BruceReed]

I don't know if this will work, but it might be worth a shot- try rounding the numbers which will force the data type from decimal to double.

For example :Round-near(field1/ field 2, 2) to get to 2 decimals.

Good luck
Bruce

 

[dipat55]

Thank you for the tips and your help!

So far, we changed the Microsoft ODBC for Oracle driver ver 2.573.5303.00 to the Oracle ODBC Driver ver 8.01.06 and all works well. Some data is getting lost between the Microsoft ODBC for Oracle driver and Impromptu 7.0.

Is anyone else using Oracle database with a Microsft ODBC for Oracle driver? What version are you using?

Thanks

 

[puppyquestion1]

We had the exact same problem with Impromptu 7.1 using calculated fields. It seems whenever we were dividing integer by integer, rather than getting a real (decimal) result, we ended up with a 0 (zero) value.

Cognos Support couldn't fix the problem and submitted it to their programming staff as a bug report. In our calcs, we had to change all integers values to decimals for this to work properly. And the integers we were using were VALUES (basically row counts divided by one another to get percentages) we set using Impromptu, NOT FROM our database.

Personally, I think this is a MAJOR bug they should address. Integer/integer divides producing real results should be the least of a customer's worries about software, especially at Cognos prices.

 

[dipat55]

Thank you puppyquestion1

I'm glad to see I wasn't alone in this.
I'm still perusing this with Cognos and hope to have a fix very soon.
Thanks

 

[dipat55]

Hello puppyquestion1

Is it possible for you to send me the bug reference number for that specific bug?

Thank you

 

[griffindm]

dipat55,

Any way you could shift to direct SQLNet access instead of MS ODBC for Oracle? I know the problem does not occur there (at least not very often - In 5 years I've seen this only 1-2 times, usually involving division using summary results. I could do a programatic work-around, but it wasn't a show-stopper for us).

-puppyquestion1: Definitely post the bug number if possible so others can track the progress of the fix.

Thanks,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[puppyquestion1]

Sorry all, but the support rep never gave me a bug report number. I just put in a call referencing our old call number to see if I can get the bug's number.