Topic
  • 11 replies
  • Latest Post - ‏2012-10-23T18:14:46Z by SantoshGaga
hauge9
hauge9
277 Posts

Pinned topic Cognos - Rounding Up Issue

‏2010-05-11T19:17:19Z |
Has anyone else gone through this, or heard of the issue. Basically Cognos uses "half even" rounding, so it doesn't round .5 up, but rather goes up or down to the next even number.

I have users who need to always have .5 rounded up in many reports. According to Cognos support there is no way to accomplish this.

I find it hard to believe that many people haven't had the same need and that there is no solution.

Anyone have any insight?

Thanks in advance.
Updated on 2012-10-23T18:14:46Z at 2012-10-23T18:14:46Z by SantoshGaga
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos - Rounding Up Issue

    ‏2010-05-12T01:30:41Z  
    Depending on your database, you may have a rounding function which provides greater control over the way in which the rounding is applied. For example, the DB2 round function goes to the next greatest integer (when positive) if you have 0.5 of the degree or rounding precision specified. This rounding behaviour of the DB2 function can be controlled through database settings. You could apply this function to the aggregated values for your query to get a controlled rounding behaviour.

    Alternately, you could check for an exact 0.5 of a decimal place in the aggregated value and round up as necessary. Using an expression with a Calculated aggregate type:
    IF ([Value] - floor([Value]) = 0.5) THEN (ceiling([Value])) ELSE (floor([Value]))
    
    Updated on 2014-03-25T08:30:57Z at 2014-03-25T08:30:57Z by iron-man
  • hauge9
    hauge9
    277 Posts

    Re: Cognos - Rounding Up Issue

    ‏2010-05-12T13:11:56Z  
    Depending on your database, you may have a rounding function which provides greater control over the way in which the rounding is applied. For example, the DB2 round function goes to the next greatest integer (when positive) if you have 0.5 of the degree or rounding precision specified. This rounding behaviour of the DB2 function can be controlled through database settings. You could apply this function to the aggregated values for your query to get a controlled rounding behaviour.

    Alternately, you could check for an exact 0.5 of a decimal place in the aggregated value and round up as necessary. Using an expression with a Calculated aggregate type:
    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">IF ([Value] - floor([Value]) = 0.5) THEN (ceiling([Value])) ELSE (floor([Value])) </pre>
    Thanks. The main downside I see to those methods is they change they round the actual value, not just the display. It sounds like by applying it at aggregate level, all numbers at all levels will be rounded after summing non-rounded? That makes it not so bad, but still if you exported to Excel you would only get rounded numbers at the detail level (not just less precision displayed).

    Another solution I found was to add ".0000000001" to numbers where the last decimal is 5. This is ugly, but it seems like it would work ok. The big hassle would be adding a formula to check for this to every single numeric field in my packages.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos - Rounding Up Issue

    ‏2010-05-12T13:46:24Z  
    • hauge9
    • ‏2010-05-12T13:11:56Z
    Thanks. The main downside I see to those methods is they change they round the actual value, not just the display. It sounds like by applying it at aggregate level, all numbers at all levels will be rounded after summing non-rounded? That makes it not so bad, but still if you exported to Excel you would only get rounded numbers at the detail level (not just less precision displayed).

    Another solution I found was to add ".0000000001" to numbers where the last decimal is 5. This is ugly, but it seems like it would work ok. The big hassle would be adding a formula to check for this to every single numeric field in my packages.
    To round to the nearest whole number I use...
    floor([Value] + 0.5)
    


    To round to 2 decimal places I use...
    floor( ([Value] + 0.005) * 100) / 100
    


    To round to 3 decimal places I use...
    floor( ([Value] + 0.0005) * 1000) / 1000
    

    etc...

    Also, if you do not want to round at the detail level you can create summary calculations for the subtotals and/or report total and then apply the rounding there.
    Updated on 2014-03-25T08:30:41Z at 2014-03-25T08:30:41Z by iron-man
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos - Rounding Up Issue

    ‏2010-05-17T14:30:29Z  
    Hauge9 - Did you ever work this out? I checked out the scenario on my system and the Cognos HTML did the half-even rounding (which I never noticed before) but when I exported to excel the numbers were rounded up. I am on Cognos 8.2 and I exported to 2002.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos - Rounding Up Issue

    ‏2010-05-17T14:31:52Z  
    Hauge9 - Did you ever work this out? I checked out the scenario on my system and the Cognos HTML did the half-even rounding (which I never noticed before) but when I exported to excel the numbers were rounded up. I am on Cognos 8.2 and I exported to 2002.
    see attached...
  • hauge9
    hauge9
    277 Posts

    Re: Cognos - Rounding Up Issue

    ‏2010-05-17T15:04:10Z  
    see attached...
    We still haven't decided how we're going to deal with this. The best solution to accomplish the round up in Cognos seems to be adding ".0000000001" to every number that ends in .5. Ideally on the Framework side (so it's once per field and not per report), but still a huge hassle to put this code and maintain it in several spots (and whenever new fields are added). Not sure if this is a realistic long-term solution.

    The SQL code I have that will do this looks like this:
    actual_dollar_value + decode(substr(actual_dollar_value, length(actual_dollar_value), 1), 5, .0000000000001, 0)

    I had a few emails back and forth with Cognos support about this. They maintain that there is no possible workaround other than using a formula to adjust the number. No way to ever get it to display other than half even rounding. They also maintained that no other customers have complained about this or put in calls about it....I would be very surprised if we are the only company with a concern. To me Excel not matching HTML/PDF is a decent issue. Plus I would think it's fairly common to want numbers to round up for display as that's what most people expect.

    Anyways, we are going to see if our business will go along with using the half/even rounding. Otherwise probably the solution I mentioned above...
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos - Rounding Up Issue

    ‏2010-05-18T01:09:32Z  
    • hauge9
    • ‏2010-05-17T15:04:10Z
    We still haven't decided how we're going to deal with this. The best solution to accomplish the round up in Cognos seems to be adding ".0000000001" to every number that ends in .5. Ideally on the Framework side (so it's once per field and not per report), but still a huge hassle to put this code and maintain it in several spots (and whenever new fields are added). Not sure if this is a realistic long-term solution.

    The SQL code I have that will do this looks like this:
    actual_dollar_value + decode(substr(actual_dollar_value, length(actual_dollar_value), 1), 5, .0000000000001, 0)

    I had a few emails back and forth with Cognos support about this. They maintain that there is no possible workaround other than using a formula to adjust the number. No way to ever get it to display other than half even rounding. They also maintained that no other customers have complained about this or put in calls about it....I would be very surprised if we are the only company with a concern. To me Excel not matching HTML/PDF is a decent issue. Plus I would think it's fairly common to want numbers to round up for display as that's what most people expect.

    Anyways, we are going to see if our business will go along with using the half/even rounding. Otherwise probably the solution I mentioned above...
    If you go with adding a very small fraction to numbers ending with 0.5 then make sure you are doing it on the aggregated values (as a calculation with the aggregate type set to Calculated). This should reduce some of the concerns about data skewing when aggregating across large sets of records.
  • hauge9
    hauge9
    277 Posts

    Re: Cognos - Rounding Up Issue

    ‏2010-05-18T14:12:43Z  
    If you go with adding a very small fraction to numbers ending with 0.5 then make sure you are doing it on the aggregated values (as a calculation with the aggregate type set to Calculated). This should reduce some of the concerns about data skewing when aggregating across large sets of records.
    Good to know. So if you put a formula like that above as the field and then set it to Calculated, it won't apply that formula until the highest level?
  • My Technology Space
    My Technology Space
    1 Post

    Re: Cognos - Rounding Up Issue

    ‏2012-10-12T11:33:50Z  
    • hauge9
    • ‏2010-05-18T14:12:43Z
    Good to know. So if you put a formula like that above as the field and then set it to Calculated, it won't apply that formula until the highest level?
    I have a number that is 34 digits long. I just want cognos to render it as such
    eg: 12345677934353485746546895460412312323
    but it does a round off and renders it on the screen like this.
    eg 12345677934353500000000000000000000000

    From report studio, the value is a direct pull from the db and I haven't done any modifications.

    It works if i change it to to_char before rendering. But i want to display it as a number only.

    Can someone tell me what I can do to achieve this?
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos - Rounding Up Issue

    ‏2012-10-12T12:13:27Z  
    I have a number that is 34 digits long. I just want cognos to render it as such
    eg: 12345677934353485746546895460412312323
    but it does a round off and renders it on the screen like this.
    eg 12345677934353500000000000000000000000

    From report studio, the value is a direct pull from the db and I haven't done any modifications.

    It works if i change it to to_char before rendering. But i want to display it as a number only.

    Can someone tell me what I can do to achieve this?
    First, for new questions it would be better to start your own thread rather than force new questions into an existing thread.

    Second, Cognos 8 has an effective limit of about 15 significant digits when working with large numbers due to the floating point storage format. If you want larger number support you find this functionality in the latest version of Cognos 10.
  • SantoshGaga
    SantoshGaga
    28 Posts

    Re: Cognos - Rounding Up Issue

    ‏2012-10-23T18:14:46Z  
    • hauge9
    • ‏2010-05-17T15:04:10Z
    We still haven't decided how we're going to deal with this. The best solution to accomplish the round up in Cognos seems to be adding ".0000000001" to every number that ends in .5. Ideally on the Framework side (so it's once per field and not per report), but still a huge hassle to put this code and maintain it in several spots (and whenever new fields are added). Not sure if this is a realistic long-term solution.

    The SQL code I have that will do this looks like this:
    actual_dollar_value + decode(substr(actual_dollar_value, length(actual_dollar_value), 1), 5, .0000000000001, 0)

    I had a few emails back and forth with Cognos support about this. They maintain that there is no possible workaround other than using a formula to adjust the number. No way to ever get it to display other than half even rounding. They also maintained that no other customers have complained about this or put in calls about it....I would be very surprised if we are the only company with a concern. To me Excel not matching HTML/PDF is a decent issue. Plus I would think it's fairly common to want numbers to round up for display as that's what most people expect.

    Anyways, we are going to see if our business will go along with using the half/even rounding. Otherwise probably the solution I mentioned above...
    Hauge9 - I had the exact same issue and I added up 0.05 0r 0.005 or 0.005 depending on the fact that how many digits I want in the rounded number after decimal. I use truncate funtion to truncate the number to the desired number of digits after decimal. and for the summary line do not use the spiked column values rather take it from ths data source and select 'calculate' for the agreegate function under properties-dataItems. This should work fine.