Crystal Report question - Date

[Former Member]

Good Morning,

I am new to report writing in Crystal Reports. However I have linked tables together and have the report that I sort of need.

My report has the following fields Last name, First Name, Date, Absent code.

My question is I only store dates that the employee is absent. I would like to add the other dates as present for the date range I enter. Usually done year by year.

I am not sure how to do that.

For example

What I currently have:

Joe Doe 11/2/2018 Absent

Joe Doe 11/4/2018 Absent

Joe Doe 11/19/2018 Absent

What I need:

Joe Doe 11/1/2018 Present

Joe Doe 11/2/2018 Absent

Joe Doe 11/3/2018 Present

Joe Doe 11/4/2018 Absent

Joe Doe 11/5/2018 Present

Joe Doe 11/6/2018 Present

Joe Doe 11/7/2018 Present

Joe Doe 11/8/2018 Present

Joe Doe 11/9/2018 Present

Joe Doe 11/10/2018 Present

Joe Doe 11/11/2018 Absent

Thank You

Jeremy

[abhilash_kumar]

I agree with Shawn's advice.

You'd need a "Calendar" table that contains a row for each day. You'd then LEFT JOIN from this Calendar Table to the Other table that has the Employee's date entries.

And then replace all occurrences of the Date field on the report (Selection Formulas, Report Fields etc) with the field from the Calendar table.

-Abhilash

[former_member203619]

There are a couple ways to do this - however it can get very complicated very quickly. The simplest way is to have another table which has an entry for every single date and then link the two tables together. This is by far the easiest and least difficult way to accomplish what you are looking for.

If you cannot do this, you can possibly create a formula that will loop through the fields, or have an array of subreports - however that is well outside my SDK area of expertise. Possibly someone in the Crystal Report design are may be able to help more.

Shawn