|
Hi Everyone,
I am sorry if this is a duplicate of another question but I haven't been able to find the solution on this or other forums. I expect this is due to my inexperience with writing loop formulas. Happy to be directed to an existing solution.
In my report the user enters to and from dates producing a set of results. The results are "jobs" finished in the date range. The result has two columns commence date and complete date. I then want to add a column to display the number of working days between. Not accounting for holidays this was easy, I just subtracted the Sat and Sun count from the total datediff.
The part I cannot work out is how to subtract the number of weekday public holidays.
All public holidays are recorded in a separate db table which lists the date and the name i.e. 2013-12-25||Christmas Day etc.
I believe Ken Hamady has published a solution using an array however I am not sure how to produce the array from my db table or reference the table directly.
"http://www.kenhamady.com/form01.shtml"
Kind Regards,
bowja
|
|
in a stored procedure I can see this not being too difficult, but from inside a report...I'm not sure...perhaps with a subreport???
This is the general idea, which may not work...
in the subreport you have the table of holidays. Pass in the dates from the main report as parameters. in the subreport have a formula like:
if {table.holidayDate} in {@fromDate} to {@toDate} then
1
else
0
then in subreport groupfooter:
shared numbervar holidays:= sum(formulaName);
""//will hide the output if you want
back in the main report, where you calculate the days:
shared numbervar holidays;
existing logic - holidays
it nothing else, it's worth a try.
as a caveat, it will slow down the report, but all subreports do, which is why I try and avoid them, and sometimes that just can't be done.
|
IP Logged
