Calculating a Running Total for suppressed rows?
Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22083
Printed Date: 29 Apr 2024 at 12:53am
Topic: Calculating a Running Total for suppressed rows?
Posted By: DarrylR
Subject: Calculating a Running Total for suppressed rows?
Date 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. |
Replies:
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |