cancel
Showing results for 
Search instead for 
Did you mean: 

Crosstab - column totals using grid values

Former Member
0 Kudos

I have a crosstab in a report that lists months in columns and providers in rows:

Provider          Jan         Feb        Mar         Total

Allen               5               0          1               6

Donohoe          8               5          0               13

Haque             0               3           5               8

Patel              10              6           8              24

Other              1               4           1               6

Total               24             18          15

My first issue was that the bottom row total was sometimes off by 1, so I created a calculated member to add the columns for the bottom row total.  This works perfectly when all of the providers exist, but if one of the providers doesn't have any clients then my formula breaks:

GridValueAt(GetRowPathIndexOf("Allen"), CurrentColumnIndex, CurrentSummaryIndex)

+ GridValueAt(GetRowPathIndexOf("Donohoe"), CurrentColumnIndex, CurrentSummaryIndex)

+ GridValueAt(GetRowPathIndexOf("Haque"), CurrentColumnIndex, CurrentSummaryIndex)

+ GridValueAt(GetRowPathIndexOf("Patel"), CurrentColumnIndex, CurrentSummaryIndex)

+ GridValueAt(GetRowPathIndexOf("Other"), CurrentColumnIndex, CurrentSummaryIndex)

I've tried several different variations using 'if' statements to test for validity, but so far I've not found the solution.  Is there a better way to total each monthly column without referencing each provider by name?

I appreciate any and all assistance, my limited expertise has long since been exhausted.

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Deb,

Here's what you need to do:

1) First make sure the Row Grand Totals are printed at the bottom: Right-click the 'Total' Row > Row Grand Totals > uncheck 'Totals on top'. This will move the row totals to the bottom

2) Next, Right-click the 'Total' row of the Crosstab and select Calculated Member > 'Insert Child'

3) This will insert a blank row above the 'Total' row with all zero values

4) Right-click one of the cells with zero values and select Calculated Member > Edit Calculation Formula and use this code:

local numbervar i;

local numbervar tot;

while i <> currentrowindex do

(

    tot := tot + tonumber(gridvalueat(i,CurrentColumnIndex,CurrentSummaryIndex));

    i := i + 1;

);

tot;

5) Next, right-click the blank Row Header and select Calculated Member > Edit Header formula and type this:

"Total"

6) You can then suppress the Row Grand Totals by right-clicking the 'Total' Column > Row Grand Totals > Suppress Grand Totals

Let me know how this goes!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

Former Member
0 Kudos

Thank you, thank you.  Worked like a charm.

Former Member

Hi,

I am having a similar requirement in Crystal report cross tab like above but little change:

I need to display reprot as

A

B

C

D

E

Calculated Member Row (A+B+C+D+E)

calculated member row=

GridValueAt(GetRowPathIndexOf("A"), CurrentColumnIndex, CurrentSummaryIndex)+

GridValueAt(GetRowPathIndexOf("B"), CurrentColumnIndex, CurrentSummaryIndex)+

GridValueAt(GetRowPathIndexOf("C"), CurrentColumnIndex, CurrentSummaryIndex)+

GridValueAt(GetRowPathIndexOf("D"), CurrentColumnIndex, CurrentSummaryIndex)+

GridValueAt(GetRowPathIndexOf("E"), CurrentColumnIndex, CurrentSummaryIndex)

The above calculation is under group of Region.

The above values (A,B,C,D,E) are from database. Suppose if 'B' dosenot exist in database for specific group its saying" Argument #1 of 'GridValueAt is out of range.''

How to check whether specific value exists in database for a field?

Thanks,

Sahithya

Answers (1)

Answers (1)

0 Kudos

Hi

I too have similar issue with the calculated member field, how to check if specific value exists for a group in DB?