system
January 26, 2017, 12:15pm
1
hi,
I have a cross tab table with the following data: Debt Credit Payment
I want to add another column that would calculate what percentage is the (Tot Amt paid) of the (Tot Debt recd minus Tot credit given)
I’ve tried creating a field formula and entering it in the cross tab but that didn’t work.
Any help much appreciated.
K
juguete (BOB member since 2017-01-26)
system
January 26, 2017, 2:59pm
2
You’ll need to use advanced formulas in the crosstab. To do this:
Right-click on the cross-tab and select “Advanced Calculations” and then “Embedded Summary”.
Create a new summary and then click on “Edit Embedded Summary”.
To remove the possibility of having a 0 in the denominator, which will cause an error, you’ll have to do something like this in the formula:
If (GridValueAt(CurrentRowIndex, GetColumnPathIndexOf('Tot Debt Recd'), CurrentSummaryIndex) -
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf('Tot Credit Given'), CurrentSummaryIndex)) > 0 then
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf('Tot Amt Paid'), CurrentSummaryIndex) %
(GridValueAt(CurrentRowIndex, GetColumnPathIndexOf('Tot Debt Recd'), CurrentSummaryIndex) -
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf('Tot Credit Given'), CurrentSummaryIndex))
else 0This uses the “Grid Value” functions that are only available in the formula editor when you’re working with a crosstab.
I don’t guarantee that you won’t have to tweak this, but it should get you started.
-Dell
hilfy (BOB member since 2007-04-16)
system
January 26, 2017, 4:36pm
3
hi,
thanks for this but I don’t seem to have the Advanced Calculations option when I right click on the cross tab.
perhaps we’ve two different versions.
juguete (BOB member since 2017-01-26)
system
January 26, 2017, 4:55pm
4
Which version are you using?
-Dell
hilfy (BOB member since 2007-04-16)
system
January 27, 2017, 10:34am
5
hi, sorry I thought I mentioned it before. I have version 10.
juguete (BOB member since 2017-01-26)
system
January 27, 2017, 2:16pm
6
Then I don’t think there’s going to be much you can do for this. I believe the advanced calculations were added in 2008 (or possibly XI).
If you’re using version 10, are you doing this from within Visual Studio 2008 - this is the version of Crystal that came with that version of VS.
-Dell
hilfy (BOB member since 2007-04-16)
system
January 27, 2017, 4:33pm
7
oh I see. no I’m not sure Visual Studio. I might look into upgrading to newer version then.
juguete (BOB member since 2017-01-26)
(BOB member since 2007-04-16)