Skip to Content
Former Member
Jul 19, 2007 at 06:57 PM

List all dates in a range at the group level


Post Author: allendo

CA Forum: Formula

I have a report that runs against a single source table with a couple of columns and it is currently grouped by date. The user selects that start and end dates as parameters upon running the report. Overall, the report looks something like below:

Date Sum of Sales Avg % of Whatever======= ========== ==============7/1/2007 $355,691.00 35%7/2/2007 $561,237.00 34%7/5/2007 $399,111.00 15%7/6/2007 $834,261.00 20%

Notice that we have the dates 7/1, 7/2, 7/5, and 7/6 because they were pulled directly from the date field in the table. Now here is the issue, the user wants to also show dates that are not in the table so the report would actually look as below:

Date Sum of Sales Avg % of Whatever======= ========== ==============7/1/2007 $355,691.00 35%7/2/2007 $561,237.00 34%7/3/2007 $0.00 0%7/4/2007 $0.00 0%7/5/2007 $399,111.00 15%7/6/2007 $834,261.00 20%

Notice the addition of 7/3 an d7/4 even though they are not in the db itself. What I have been attempting to do is create a formula that will evaluate each date in a date range and display regardless of whether or not it is in the db. What I have currently done without success follows:

global DateVar dtCurDate;local DateVar dtPrevDate;

dtPrevDate := dtCurDate;dtCurDate := {Sales.CreateDate}; //Current record date from sales table

//Display the correct dateif (dtCurDate <> (dtPrevDate + 1)) then dtCurDate := (dtPrevDate + 1); else dtCurDate Of course this is not displaying the correct date and I think I am completely off on this. Any ideas here on how to accomplish the above? Thanks for your help!

Allen D.