cancel
Showing results for 
Search instead for 
Did you mean: 

List all dates in a range at the group level

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Post Author: allendo

CA Forum: Formula

Thanks Jagan, I was able to get what I needed with the formula above and thanks for the link too.

-Al

Former Member
0 Kudos

Post Author: Jagan

CA Forum: Formula

Found the website where I got this from:

http://www.chelseatech.co.nz/pubs.htm

There's a whole load of back issues of their newsletter that contain hints and tips for Crystal.

Former Member
0 Kudos

Post Author: Jagan

CA Forum: Formula

Here's an example of creating a list of the missing dates. Put in the group footer; tick the 'can grow' box; suppress blank section.

if {Sales.CreateDate}+1 <> next({Sales.CreateDate}) then {@Day List}

The Day List formula:

local numbervar d;local numbervar i;local stringvar s:="";

d:= next({Sales.CreateDate})-{Sales.CreateDate}-1; //Calculate the numer of missing daysfor i:= 1 to d do ( s:=s + totext({Sales.CreateDate}+ i,"dd-MMM-yyyy")+ chr(10) + chr(13));s

I'm sure you can tweek it to add the extra stuff you want in the format you want...