cancel
Showing results for 
Search instead for 
Did you mean: 

Using distinct count in Crystal crosstab

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Thank you a lot Abhilash!

Answers (0)