Skip to Content

Crosstab not showing decimal points

I hear it's a known bug in Crystal Reports, but I also hear there is a workaround. Basically, I am trying to show 2 decimal points from the yearly difference percentage calculation below:

`if GridValueAt(CurrentRowIndex, GetColumnPathIndexOf( {?year1} ), CurrentSummaryIndex) =0

then 0

else

(

(

( GridValueAt(CurrentRowIndex, GetColumnPathIndexOf( Totext( (tonumber({?year1}) + 1 ), 0, "" ) ), CurrentSummaryIndex)

-

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf( {?year1} ), CurrentSummaryIndex) )

/

GridValueAt(CurrentRowIndex, GetColumnPathIndexOf( {?year1} ), CurrentSummaryIndex)

)

*100.0

)`

I've tried totext(x,2), round(x, 2) and a few more tricks in the format field sections; all to no avail.....

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Dec 21, 2017 at 01:13 PM

    Hi Kenshin,

    1) Edit Calculation Formula you entered for the difference column

    2) Comment or remove the code inside and just type in 0 (zero).

    3) Right-click the Calculated Column's Header cell > Calculated Member > Edit ColumnValue formula.

    Make sure there is some text in there to identify that column by name. For e.g. 'Difference'

    4) Right-click one of the summary cells > Format Field > Common tab > Click the formula button beside 'Display String' and use this code:

    if GridRowColumnValue("Database_Field_Used_as_Column", CurrentColumnIndex) = "Difference" then
    (
      if GridValueAt(CurrentRowIndex, GetColumnPathIndexOf( {?year1} ), CurrentSummaryIndex) =0
         then '0'
      else
      (
         totext(
                (( GridValueAt(CurrentRowIndex, GetColumnPathIndexOf( Totext( (tonumber({?year1}) + 1 ), 0, "" ) ), CurrentSummaryIndex)
                -
                GridValueAt(CurrentRowIndex, GetColumnPathIndexOf( {?year1} ), CurrentSummaryIndex) )
                /
                GridValueAt(CurrentRowIndex, GetColumnPathIndexOf( {?year1} ), CurrentSummaryIndex))
                *100.0
                ,2) & "%"
      )
    )
    else
    totext(CurrentFieldValue,0)

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Hi, Abhilash.

      I know it's unrelated to this, but would you know how to convert this into a loop function? I now have to format the report so that the calculations are done for the x number of years... I thought I only had to figure this out for 5 years, but not any more... Ugh!

  • Dec 21, 2017 at 12:58 PM

    see Ken Hamady's newsletter topic "Percentages in a Cross-Tab":
    http://kenhamady.com/newsletter/news0407.shtml

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks for the suggestion. I looked over it, but i don't think it specifies the decimal points? My calculation is using values from two columns, and crosstab automatically truncates decimal values and shows them as zeroes. I need the report to show the exact decimal values...