displaying no existing data...

[3dthies]

I have the following database structure:

TIMESTAMP OFFERED ANSWERED ABANDONED
------------------------------------------------
2003/10/01 08:00:00 2 2 0
2003/10/01 08:15:00 3 3 0
2003/10/01 08:30:00 2 0 2
2003/10/01 08:45:00 1 0 1
2003/10/01 09:00:00 10 9 1

no existing data for 2003/10/02

2003/10/03 00:00:00 1 0 1
2003/10/03 00:15:00 1 1 0
2003/10/03 00:30:00 9 9 0
..
..
..and so on..

------------------------------------------------

As you can see the database produces an entry for every 15 minute intervall. To display the datas in a on day way I have to group it. So far so easy. For the date range I'm using a date parametric field (From/To).

If I start the report and take the date (from: 203/10/01 to 2003/10/3) the i get information for 2003/10/01 and 2003/10/03. the date 2003/10/02 will not be displayed because there is no existing data.

Is it possible to display the 2003/10/02 with zero entrys in the group?

 

[dgillz]

You need a table that has EVERY date in it, and then link from that table to the table you are currently working with. Then create a formula field that checks for nulls in your current table as follows:

If Isnull({Table.Datefield}) then "No Existing Data" else ""

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Ngolem]

hmmmmm....so you want the date and times but blanks or underscores for the details....so they can be filled in manually or something later...is that the idea?

You might be able to fake it in the group footer for the day....if the whole day of data is missing....it becomes REALLY complicated if you are just missing SOME daily data

What you can do is create the following formula in the Group footer for the for the group after day

eg.

Groups 1-?
Group Day
Group something
details
footer something
footer Day
footers for other groups

if there is no group after Day then you can do it as a detail section...Let us choose this option

So...you create a real detail section followed by a fake detail section

In the fake detail section you create the following formulas you need more than one since each line has 20 chars and 254 is your limit/formula...so for 24 hrs each hour requires 4 x 20 = 80 chars so we will do each formula for 3 hours...therefore in 24 hrs we require 8 formulas

Each formula would be in a separate detail subsection

//@fakeData_00-02hrs

WhilePrintingRecords;
numberVar position;
stringVar result := "";
numberVar NewDate ;
numberVar temp := 0;

//I have not checked here for missing data if going from
//December 31 to January 2...so this may have to be
//modified if this is a problem
if Day({table.timestamp} + 1) <> 1 then
NewDate := totext(year({table.timestamp},&quot;####&quot + &quot;/&quot; +
totext(month({table.timestamp},&quot;00&quot + &quot;/&quot; +
totext(Day({table.timestamp}) + 1,&quot;00&quot
else
NewDate := totext(year({table.timestamp},&quot;####&quot + &quot;/&quot; +
totext(month({table.timestamp})+ 1,&quot;00&quot+ &quot;/&quot; +
totext(Day({table.timestamp})+1,&quot;00&quot ;

for position := 1 to 12 do
(
if position = 1 then
(
temp := 0;
NewDate := NewDate + &quot; 00:&quot;;
)
else if position = 5 then
(
temp := 0;
NewDate := NewDate + &quot; 01:&quot;;
)
else if position = 9 then
(
temp := 0;
NewDate := NewDate + &quot; 02:&quot;;
)

result := NewDate + Totext(temp,&quot;00&quot + &quot;:00&quot; + chr(13) + chr(10);
temp := temp + 15;
);

result;

****************
//@fakeData_03-05hrs

WhilePrintingRecords;
numberVar position;
stringVar result := &quot;&quot;;
numberVar NewDate ;
numberVar temp := 0;

//I have not checked here for missing data if going from
//December 31 to January 2...so this may have to be
//modified if this is a problem
if Day({table.timestamp} + 1) <> 1 then
NewDate := totext(year({table.timestamp},&quot;####&quot + &quot;/&quot; +
totext(month({table.timestamp},&quot;00&quot + &quot;/&quot; +
totext(Day({table.timestamp}) + 1,&quot;00&quot
else
NewDate := totext(year({table.timestamp},&quot;####&quot + &quot;/&quot; +
totext(month({table.timestamp})+ 1,&quot;00&quot+ &quot;/&quot; +
totext(Day({table.timestamp})+1,&quot;00&quot ;

for position := 1 to 12 do
(
if position = 1 then
(
temp := 0;
NewDate := NewDate + &quot; 03:&quot;;
)
else if position = 5 then
(
temp := 0;
NewDate := NewDate + &quot; 04:&quot;;
)
else if position = 9 then
(
temp := 0;
NewDate := NewDate + &quot; 05:&quot;;
)

result := NewDate + Totext(temp,&quot;00&quot + &quot;:00&quot; + chr(13) + chr(10);
temp := temp + 15;
);

result;


you can see the pattern for the other formulas...

If you want underscores for the blank data you can add them but be careful about the character count...you may have to make another set of formulas to do this because of this.

in the conditional suppress for each fake data section
place the formula

WhilePrintingRecords;

not onLastRecord and
( next(day({table.timestamp})) = day({table.timestamp}) or
next(day({table.timestamp})) = day({table.timestamp}+ 1) ;

This might work....but only if you are missing only 1 day of data...more than that would be too complicated this way.

Hope this helps

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!