Hi friends,
I need to create crosstab in Crystal for group distinct count distributed by month.
Iam using Numer variable to calculate the total. But this formula is not available in crosstab expert . Is there any way to use distinct count formula in crosstab?
This is a report:
GH1: January 2017
Apple
Banana
Apple
Distcount=2
GH2: February 2017
Pear
Pear
Distcount=1
This is the crosstab:
Jan-17.... Feb-17 .... Total
2........... ..1 ............... 3
I would appreciate any help on this.
Hi Alisia,
Here's what you need to do:
1. Right-click the Total Column header > Calculated Member > Insert Child.
2. A Blank Column with zero values is inserted at the start.
3. Right-click the blank white space to the top left of the crosstab > Advanced Calculations > Calculated Member > For 'Insert Evaluation', select 'After'.
4. The blank column should now move to the end just before the original Total Column.
5. Right-click the blank column's header cell > Calculated Member > Edit Header Formula and type in:
"Total"
6. Right-click one of summary cells in this calculated column > Select Calculated Member > Edit Calculation Formula > Use this code:
local numbervar i; for i := 0 to CurrentColumnIndex-1 do ( local numbervar t := t + GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex); ); t;
7. Right-click the original Total Column at the end > Select Column Grand Totals > Suppress Grand totals.
-Abhilash
Thank you a lot Abhilash!