Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Baan calendar using crystal
- Thread starter njain76
- Start date
Dear njain,
Since I do not have this product I cannot give an exact recommendation.
Assumptions: Holidays are stored in a table.
If working days and hours are stored in a table, that is easiest otherwise go to the Crystal KB and do a search for Business Hours, you should find a formula that will accomodate your business days and hours.
However, the app I do reporting for has a holiday table. The table holds the length of the holiday (days) and the startdate of the holiday. Not knowing how yours is structured makes it difficult for me to give you an exact formula.
For the Holiday, what I do is create a sql expression (Crystal 8.5):
(Select sum(([length]*24),0)from Holiday where startdate >= [Open Date] and startdate <= (case when [status] = 'O' then getdate() else [Closed Date] end))
I do a similar calculation for working hours on holidays which in my db is stored in a separate column.
You can do a similar query which accomodates your table structure. The above is a correlated subquery as it references fields in the view in the main select statement of Crystal.
When you add a sql expression to a report it adds it to the main select statement which you can view doing a Database/Show SQL Query.
I hope this information was helpful. If not, hopefully someone with this app will reply.
regards,
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Since I do not have this product I cannot give an exact recommendation.
Assumptions: Holidays are stored in a table.
If working days and hours are stored in a table, that is easiest otherwise go to the Crystal KB and do a search for Business Hours, you should find a formula that will accomodate your business days and hours.
However, the app I do reporting for has a holiday table. The table holds the length of the holiday (days) and the startdate of the holiday. Not knowing how yours is structured makes it difficult for me to give you an exact formula.
For the Holiday, what I do is create a sql expression (Crystal 8.5):
(Select sum(([length]*24),0)from Holiday where startdate >= [Open Date] and startdate <= (case when [status] = 'O' then getdate() else [Closed Date] end))
I do a similar calculation for working hours on holidays which in my db is stored in a separate column.
You can do a similar query which accomodates your table structure. The above is a correlated subquery as it references fields in the view in the main select statement of Crystal.
When you add a sql expression to a report it adds it to the main select statement which you can view doing a Database/Show SQL Query.
I hope this information was helpful. If not, hopefully someone with this app will reply.
regards,
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #4
- Status
Similar threads
- Locked
- Question