Displaying all missing dates in a range

[Former Member]

Hi,

  I followed this link http://scn.sap.com/community/crystal-reports/blog/2011/12/13/displaying-all-missing-dates-in-a-range... and created a function for displaying the dates missing from range which is passed as parameter values.

Here what happens is that, my report will show first the values which are already in db in that range, and then the missing dates.

for each every record, my second section for missing dates is showing.How to include the missing dates with dates have data

for eg, if i select 01-feb-2014 to 20-feb-2014, and in db, data is there for 05-feb and 10-feb

05-feb-2014 ------- data from db

now the missing dates from second section

then

10-feb-----data from db

now the missing dates from second section

Please help

See

[JWiseman]

hello See,

this is a function that i built...i will have to modify it to meet that requirement...i'll get back to you later with the revised function...should be a day or so as i'm at a conference.

thanks,

jamie

[JWiseman]

hi See,

this is not actually something that would be changed in the custom function but in your display formula itself. please see the syntax below...

     datevar thisdate:= date({Orders.Order Date});
     if not onlastrecord then datevar nextdate:= date(next({Orders.Order Date}));

     if onlastrecord and thisdate < {?EndDate}
         then nextdate := {?EndDate}+1;

     Days_Between (thisdate, nextdate, 'MMM. d, yyyy');

you would need to use something like the above in order for this to work in your situation. the syntax is such as when you're on the last record of the report, then a Next() on a field would be Null.

if you change the above syntax to use your DateField and your End Date parameter, then it will display any missing dates between your last known date and the end date parameter if necessary.

-jamie

[Former Member]

Hi Jamie,

  Thanks a lot for your help.But still i am confused.

I send the start date and end date as the parameter , but how to print these missing dates in between the data from db.

     datevar thisdate:= date(({?DateStart}));

     if not onlastrecord then datevar nextdate:= date(next({?DateStart}));

     if onlastrecord and thisdate < {?DateEnd}

         then nextdate := {?DateEnd}+1;

     Days_Between (thisdate, nextdate, 'dd-MMM-yy');

But again it is printing the date which is there from db. Also this missing date is coming as a different section. Now the section coming in between record is gone. But first the dates from db, then the missing dates. I want to show a single section with watever data from db and missing dates sort in order.

If i select 01-Feb-2014 to 28-Feb-2014 and my data from db is only for 27-feb-2014, it should display from 01-feb-2014 to 28 feb-2014 and 27feb-2014 will be the data from db.

I hope you understood my question.

[abhilash_kumar]

Hi See,

Have you tried creating a lookup table (in the database) that has all the dates?

You can also create a lookup table in Excel and join it in the report to the existing table however, such a configuration will significantly slow down the report.

With a Lookup table that contains a column with all the dates, you can Join it to the existing table (Left Outer Join) on the Date fields.

Now, in the report, wherever you use the Date field from the existing table change it to use the Date field from the Lookup table. This includes Groups, Record Selection formula etc.

If your current record selection formula looks like this:

{ExistingTable.Datefield} IN {?StartDate} TO {?EndDate}

Change it to:

{LookupTable.DateField} IN {?StartDate} TO {?EndDate}

-Abhilash

[Former Member]

Hi Abhilash,

  I didnt tried that method by creating look up table. I tried the above method of using custom function.Any idea about that?

Seeism.

[JWiseman]

please see the attachment...extract the contents and change the .txt extension to .rpt.

note that in the formula used for the date display, in Details C, that if the end date parameter is greater than the last date in the db, that those dates are still displayed.

-jamie

Custom Function - Display Missing Date Values2.txt.zip

[JWiseman]

p.s. please note that all this method does is display missing dates in a formula...you can't use these missing dates in crosstabs and charts. in those cases you need to use a different method...please see the post here for different methods on creating date data.

[Former Member]

Hi Jamie,

I could not open the file. When i tried to open the file using vs 2010 crystal reports, it is saying

"Crystal reports  compound file %1 was produced with a newer version of storage..failed to open the file.."

Then i tried cystal reports 10, developer edition and again it shows....

"Failed to open document..."

Please help...

Sorry for the late reply..As I am in UAE, we have friday,saturday off....

Seeism

demo.txt.zip

[JWiseman]

i tested the link and there is nothing wrong with the file itself. i am using cr 2013 though.

however, as mentioned before, change the formula that you are using to display the missing dates to syntax similar to below:

datevar thisdate:= date({Orders.Order Date});
if not onlastrecord then datevar nextdate:= date(next({Orders.Order Date}));

if onlastrecord and thisdate < {?EndDate}
    then nextdate := {?EndDate}+1;

Days_Between (thisdate, nextdate, 'MMM. d, yyyy');

[Former Member]

See if this helps..

Crystal Reports - all dates in range: sql, table or procedure - Spiceworks