Report Design
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Report Design
Message Icon Topic: Calculating a Running Total for suppressed rows? Post Reply Post New Topic
Author Message
DarrylR
Newbie
Newbie


Joined: 13 Sep 2016
Location: United States
Online Status: Offline
Posts: 1
Quote DarrylR Replybullet Topic: Calculating a Running Total for suppressed rows?
    Posted: 14 Sep 2016 at 7:04am
I created the report depicted in the attached image in Crystal Reports for VS 2008:



The report displays data for one month, which is selected using the runtime parameter {?ReportDate}. Data for the other months available in the database is hidden by suppressing the Details section for all months except the selected one. The report displays the selected month's Popsicle and Soda sales, estimated (budgeted) Popsicle and Soda sales for the selected month, prior year sales, Year to Date (YTD) sales, Last 12 Month (LTM) sales, and an estimate of sales for the full calendar year.

The prior year, YTD, and LTM values are correctly calculated using Running Totals. However, the Running Total used to calculate the estimated sales for the calendar year is only calculated correctly if I display the selected month along with the remaining months of the year.

Here are the relevant Running Total Evaluation formulas and the Suppression condition:

Details section suppression condition:
DateValue ({vw_CommonDates.CommonDate}) <> {?ReportDate}

Running Total evaluation formulas:

Prior Year:
DateValue({vw_CommonDates.CommonDate}) = DateAdd("yyyy", -1, {?ReportDate})

YTD:
DateValue({vw_CommonDates.CommonDate}) >= DateValue(Year({?ReportDate}), 1, 1) AND DateValue({vw_CommonDates.CommonDate}) <= {?ReportDate}

LTM:
DateValue({vw_CommonDates.CommonDate}) >= DateAdd ("m", -11 , {?ReportDate}) AND DateValue({vw_CommonDates.CommonDate}) <= {?ReportDate}

*Budgeted Sales for remainder of year:
DateValue({vw_CommonDates.CommonDate}) > {?ReportDate} AND DateValue({vw_CommonDates.CommonDate}) <= DateValue(Year({?ReportDate}), 12, 31)

*This Running Total only works if I change the Details section suppression condition to: DateValue ({vw_CommonDates.CommonDate}) < {?ReportDate}, which displays the selected month along with the remaining months of the year. Otherwise, the Running Total returns null.

Is there a way to calculate this Running Total without displaying the other months of the year?
Thanks,
Darryl R.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 03 Oct 2016 at 6:15am
Try doing something like this:

1. Create a formula that looks like this:
if DateValue({vw_CommonDates.CommonDate}) > {?ReportDate} AND DateValue({vw_CommonDates.CommonDate}) <= DateValue(Year({?ReportDate}), 12, 31) then
{field to summarize} else 0

2. Do a regular sum of this field for the appropriate report group (or grand total) instead of using a running total.

-Dell
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.