| Author |
Message |
andrewsimon
Newbie
Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
|
  
Topic: Monthly Report
Posted: 20 Jul 2011 at 8:59pm |
I have an time attendance events table with punch in and punch out timings. when i generate the report,with date range parameter,the report showing only the dates ,having events. But the client wants a full month report,which should show all dates for that month and,if the person has no events on a particular date,should show as absent.And should show the weekends(fri & sat ) off. Any way to implement this.Bcz my table is not containning full month dates. Thank you in advance.
Edited by andrewsimon - 20 Jul 2011 at 9:00pm
|
|
bbbbb
|
 IP Logged |
|
|
|
sharona
Senior Member
Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
|
Posted: 21 Jul 2011 at 2:14am |
you can create a formula to calculate the days in a month
check this link to give you some insight on how to do that
then create a group by that formula.
then add your data below the group
you may need to go to
file
report options
check the null values boxes
|
|
sharona
|
| IP Logged |
|
andrewsimon
Newbie
Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
|
Posted: 21 Jul 2011 at 2:54am |
|
|
|
bbbbb
|
| IP Logged |
|
sharona
Senior Member
Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
|
Posted: 21 Jul 2011 at 2:56am |
|
you can search the web for a formula to see if you can find one.
|
|
sharona
|
| IP Logged |
|
andrewsimon
Newbie
Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
|
Posted: 21 Jul 2011 at 3:46am |
I tried to find out a formula regarding ths. But there is no way for displaying all dates without having no records in the table.Any help....
|
|
bbbbb
|
| IP Logged |
|
IdoMillet
Groupie
Joined: 26 Oct 2007
Location: United States
Online Status: Offline
Posts: 99
|
Posted: 21 Jul 2011 at 3:56am |
|
Create a Calendar table with one row for each date.
Outer join from it to your transaction table.
Edited by IdoMillet - 21 Jul 2011 at 3:56am
|
|
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
www.MilletSoftware.com
|
| IP Logged |
|
sharona
Senior Member
Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
|
Posted: 21 Jul 2011 at 4:15am |
|
//Main formula
WhileReadingRecords;
Local DateVar Start := {StartDate}; // place your Starting Date here
Local DateVar End := {EndDate}; // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;
Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);
Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );
Weeks + Days - Hol
//Holiday Listing formula to go into the report header of the report.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0
replace start date and end date with your parameters
|
|
sharona
|
| IP Logged |
|
andrewsimon
Newbie
Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
|
Posted: 23 Jul 2011 at 9:41pm |
Thank You sharona.I tried the above formula.But i'm not getting the dates in between the start date and enddate.I'm only getting the number of buisness days. I think, from the post, you got my requirement.
Edited by andrewsimon - 23 Jul 2011 at 9:41pm
|
|
bbbbb
|
| IP Logged |
|
andrewsimon
Newbie
Joined: 28 Jun 2011
Online Status: Offline
Posts: 26
|
Posted: 24 Jul 2011 at 1:01am |
I tried this formula, local datetimevar aDate := {?startDate};
local stringvar dateList:="";
local numbervar xx:={?endDate}-{?startDate};
local numbervar i:=0; for i:=1 to xx+1 do
(
dateList:=dateList+", "+left(totext(aDate),10); //,"mm/dd/yyyy");
local datetimevar aDate:=dateadd("d",1,aDate);
);
dateList; I'm getting dates as 1/7/2010,2/7/2010,3/7/2010..etc. but i want to connect it to my datefield which is already grouped.
|
|
bbbbb
|
| IP Logged |
|
|
|
