Skip to Content
0

Crosstab not showing decimal points

Dec 21, 2017 at 12:49 PM

53

avatar image

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.....

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Abhilash Kumar
Dec 21, 2017 at 01:13 PM
1

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

Show 1 Share
10 |10000 characters needed characters left 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!

0
Ido Millet Dec 21, 2017 at 12:58 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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...

0