Technical Questions: Running total in cross tabs shows 0 instead of RT

Print Page | Close Window

Running total in cross tabs shows 0 instead of RT

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22672
Printed Date: 25 Apr 2024 at 1:32pm

Topic: Running total in cross tabs shows 0 instead of RT

Posted By: RoadRunner
Subject: Running total in cross tabs shows 0 instead of RT
Date 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!

Replies:

Posted By: gsaunders
Date 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.

Posted By: RoadRunner
Date 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.

Posted By: gsaunders
Date 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.

Posted By: gsaunders
Date 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.

Print Page | Close Window