A real puzzler - displaying database 'gaps'...... 1

[robmason10]

Synopsis:

Report is to disply MISSING time sheet entries - i.e. when an employee does not enter time for a day then the report will show it. The report is generated from a start and end date (parameters in select exp). I have an ALMOST excellent solution from the CR website (shown beloew)....however using this solution (below) it only displays where there are gaps 'mid-period' not from the ?start date or to the ?enddate. i.e. if a range is for the month of april and entries do exist for 10th and 15th the report will show 'gaps' for 11,12,13,14 and 16 but not for 1,2,3....17,18,19....etc.

anyone help me out with this one? - Thanks in advance....

Solution from CR

To display all dates in a date range even though some dates may not exist in the database, follow these steps:

1. Right click the Details section and from the fly out menu select 'Insert Section Below'. You now have a Details A section and a Details B section.

2. Place your date field into the Details A section. Right click the date field to select it and from the fly out menu select 'Format Field'.

3. From the 'Format Editor' select the 'Common' tab and then select the 'Suppress if Duplicated' check box.

4. Format the Details A section and select the 'Suppress Blank Section' check box.

5. Create a basic syntax formula similar to the following and insert it into the Details B section:

' @date
' thisdate and nextdate will be used to determine if gaps exist in the database
dim thisdate as date
dim nextdate as date

'output will be used to create the on-screen formula display
dim output as string

thisdate = {?startdate} + 1
nextdate = nextvalue({?startdate})

do while nextdate - thisdate > 0
output = output + totext(thisdate, "MM/dd/yyyy&quot + chr(10)
thisdate = thisdate + 1
loop

formula = output

' output is creating a string running total to create the dates which do not exist
' the chr(10) in output creates a new line for each new date created

6. Right click @date and from the fly out menu select 'Format Field'. From the 'Format Editor' select the 'Common' tab and then select the 'Can Grow' check box.

7. Right click the Details B section and from the fly out menu select 'Format Section'. From the 'Section Expert' select the 'Suppress Blank Section' check box.

When you preview your report, all dates display, whether or not there is any corresponding data in the database.

 

[synapsevampire]

I always suggest creating a period table, and doing a left outer from it to your table, that way you have all dates, and in this case, you might just select where is null.

This solution seems to start from the @startdate, but will not include the @enddate.

You might just add something to the group footer to handle a check for if the last date < @date, and then issue the same sort of loop.

-k

 

[robmason10]

Thanks - unfortunately I cannot add a period table - the database cannot be touched. As for the 'enddate' issue - I will deal with that after I have sorted this one....

 

[ctwilliams]

I am trying to write a report very similar to this one. If anyone has any new info on how to do this please post!

 

[chelseatech]

There are a series of articles a year or so ago in Crystal Clear covering various methods to handle &quot;Missing Data&quot;.

The key to the solution is using Previous and Next functions on some formulas.

If {table.Datefield}<> Previous({table.datefield})+ 1 then
// write some code to say the day is missing;


Editor and Publisher of Crystal Clear

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

 

[synapsevampire]

As Chelsea mentioned, and the original poster made reference to, there are several articles on the web, and I've had to do it in the past due to not being able to add in tables, but I urge people to resolve the problem long term by taking a data warehouse approach and adding in a period table.

-k

 

[lbass]

Here's a method for creating dates for every date within a parameter period, even if the start date is less than the first database date and the last database date is less than the end date. This is based on an adaptation of a formula that SynapseVampire developed in an earlier thread to display consecutive numbers. It's a little complicated, but does the job (at least when I tested it). This assumes that you want to create a complete set of dates within a group on EmployeeID.

Start by creating 4 detail sections, and format each section by checking &quot;suppress blank section.&quot;

Create two parameters {?start} and {?end} which are date type parameters, and use these in your record select statement:

{table.date} >= {?start} and
{table.date} <= {?end}

Create a reset formula {@resetcounters} and place it in the group (EmplID) header and suppress:

whileprintingrecords;
numbervar counter1 := 0;
numbervar counter2 := 0;

In detail_a place the following formula (@?starttofirst date} and format the field to &quot;can grow&quot;:

whileprintingrecords;
datevar Start := {table.date};
datevar End := next({table.date});
numbervar X;
numbervar Counter1 := counter1 + 1;
stringvar OutPut :=&quot;&quot;;

if counter1 = 1 and
{?start} - start < 0 then
for X := 0 to (start-1 -{?start}) do(
OutPut := Output+ totext(({?start}+X),&quot;MM/dd/yyyy&quot+&quot;*&quot; +chr(13)
);
Output;

In detail_b place the {table.date}.

In detail_c place the following formula {@btwdateswingrp} and format the field to &quot;can grow&quot;:

whileprintingrecords;
datevar Start := {table.date};
datevar End := next({table.date});
numbervar X;
Stringvar OutPut :=&quot;&quot;;

if End-start > 1 and
{table.EmplID} = next({table.EmplID}) then
for X := 1 to (End-2 - Start+1) do(
OutPut := Output+ totext({table.date}+X,&quot;MM/dd/yyyy&quot+&quot;*&quot; +chr(13)
);
Output;

In detail_d place the following formula {@lastdateto?end} and format the field to &quot;can grow&quot;:

whileprintingrecords;
datevar Start := {table.date};
datevar End := next({table.date});
numbervar X;
numbervar counter2 := counter2+1;
Stringvar OutPut :=&quot;&quot;;

if {?enddate} - {table.date} > 0 and
counter2 = count({table.EmplID}, {table.EmplID}) then
for X := 1 to ({?enddate} - {table.date}) do(
OutPut := Output+ totext({table.date}+X,&quot;MM/dd/yyyy&quot+&quot;*&quot; +chr(13)
);
Output;

The asterisk marks each missing date which has been added.

-LB