Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Technical Questions
Message Icon Topic: Running total in cross tabs shows 0 instead of RT Post Reply Post New Topic
Author Message
RoadRunner
Newbie
Newbie
Avatar

Joined: 06 Aug 2014
Online Status: Offline
Posts: 8
Quote RoadRunner Replybullet Topic: Running total in cross tabs shows 0 instead of RT
    Posted: 26 Sep 2018 at 3:41am
Hi,
I have a crosstab with rows that are grouped by month with a total number and I also have to run a Cumulative total or running total for this total number
The report does the correct calculation in the running total but if one month there is no value, the running total in the RT column will show 0 for that month. But if any other Month after this zero value month has a value the report will show the correct running total.
What I need to see is the previous cumulative running totals and not 0 for these zero months
Thanks for any advice or any formula on how to correct this issue.
Thanks,
Please find below an example:
          Jan 1 2 3
running total:1 2 3
        Feb 5 0 2
running total:6 0 5
        March :7 1 5
running total:13 3 10

For Feb I need to see the running total as 2 rather than 0
Thank You!
IP IP Logged
gsaunders
Newbie
Newbie


Joined: 09 Apr 2012
Online Status: Offline
Posts: 20
Quote gsaunders Replybullet Posted: 05 Nov 2019 at 11:00am
Hello,

Did you ever find a solution... this is the exact problem I am running into at the moment.

Thanks.


Edited by gsaunders - 05 Nov 2019 at 11:00am
IP IP Logged
RoadRunner
Newbie
Newbie
Avatar

Joined: 06 Aug 2014
Online Status: Offline
Posts: 8
Quote RoadRunner Replybullet Posted: 05 Nov 2019 at 10:36pm
I did find a solution. I added the running total to the cross tab. Then I added an embedded summary column and for this columns calculation formula used the following:

local numbervar i;
local numbervar temp;
for i:=1 to CurrentRowIndex do
temp:=temp + GridValueAt (i, CurrentColumnIndex, 0);
GridValueAt (0, CurrentColumnIndex, 1)+ temp

Then I suppressed the field showing the running total. I shrunk this column so the user would not have known it was there. I had to suppress the column grand totals in the crosstab as the embedded summary gave the wrong value there but this was OK for the purpose of the report I was writing.

Hope this helps.

Edited by RoadRunner - 05 Nov 2019 at 10:37pm
IP IP Logged
gsaunders
Newbie
Newbie


Joined: 09 Apr 2012
Online Status: Offline
Posts: 20
Quote gsaunders Replybullet Posted: 06 Nov 2019 at 9:14am
So I already have the running total field.
I used that for the crosstab.
I did remove grand totals as that was useless due to cumulative / running totals.

Following what you have I am trying following code:

local numbervar i;
local numbervar temp;
temp := 0;
for i:=1 to CurrentRowIndex do
    temp := temp + GridValueAt(i,CurrentColumnIndex,0);
GridValueAt(0,CurrentColumnIndex,1)+temp

But getting error:
"Unable to compute the return type because the Embedded Summary is referring to itself"

And it has a highlight on the 1 in the last line.  I changed it to 0 and that got rid of error.

For some reason I am not getting what I expect so I will have to play with it some.  But this should get me there.

Mine has months as the row on left and cost type as the column.

Thanks.
IP IP Logged
gsaunders
Newbie
Newbie


Joined: 09 Apr 2012
Online Status: Offline
Posts: 20
Quote gsaunders Replybullet Posted: 06 Nov 2019 at 10:09am
Well... I was able to get it to work by doing the following:

<<
local numbervar i;
local numbervar temp;
temp := 0;
for i:=0 to CurrentRowIndex do
    temp := temp + GridValueAt(i,CurrentColumnIndex,0);
temp;
>>

I could not use a running total, but just the actual values.

BUT... a side affect is the stinking cells get bigger and even suppressing and shrinking the size of everything as best as possible it is still bigger than if there was a single summary wasting space.

So I am punting and creating a stored procedure that injects a 0 value for all months so we don't have this issue.  That way the cross tab just works natively with the data and no worries with trying to get the visual tool to account for this.

Thanks for your reply as it definitely helped and learned something new.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.