on 07-23-2012 11:56 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Hi
I too have similar issue with the calculated member field, how to check if specific value exists for a group in DB?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.