I'm using the calendar report described at to show time off scheduled by employees. The table has a first day off and last day off. If the absence is only one day, both dates are the same. Otherwise the dates cover a range. I am trying to show the appointment on each weekday in the range. Any ideas on how I can make this work?
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 derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Show Multi-Day Appointments in Crystal Calendar Report by lbass 1
- Thread starter magiez
- Start date
-
1
- #2
That link has been updated to:
First create a formula in the main report {@dayofyear}:
(datepart("ww",{xl.Date},dayofweek(date(year({xl.Date}),1,1)),crfirstJan1)-1)*7 +
dayofweek({xl.Date},dayofweek(date(year({xl.Date}),1,1)))
Add this formula as a link to each subreport. Then go into each subreport, and create separate formulas for week number, month, and year, one that references the start date and one, the end date. Also create a SQL expression, e.g., {#DOY-Start}, for each:
{fn dayofyear(`table`.`start`)}
and change the selection formula to the following:
//{@Monday}:
{@Weekno-Start} <= {?Pm-@weeknumber} and
{@Weekno-End} >= {?Pm-@weeknumber} and
{%Month-Start} <= {?Pm-%Month} and
{%Month-End} >= {?Pm-%Month} and
{%Year-Start} <= {?Pm-%Year} and
{%Year-End} >= {?Pm-%Year} and
{%DOY-Start} <= {?Pm-@dayofyear}+1 and
{%DOY-End} >= {?Pm-@dayofyear}+1
Note that the 1 should be removed for Sunday, and should be changed to 2 for Tuesday, 3 for Wednesday, etc., since {Pm-@dayofyear} will always show the dayofyear for Sunday of the specific week.
You should check to make sure that the dayofyear function in your database will match the formula that is used for the Excel spreadsheet by placing the {?PM-{@Dayofyear} and {%DOY-Start} on a Sunday where the start date is a Sunday and making sure they match. You can adjust the formulas as necessary.
-LB
First create a formula in the main report {@dayofyear}:
(datepart("ww",{xl.Date},dayofweek(date(year({xl.Date}),1,1)),crfirstJan1)-1)*7 +
dayofweek({xl.Date},dayofweek(date(year({xl.Date}),1,1)))
Add this formula as a link to each subreport. Then go into each subreport, and create separate formulas for week number, month, and year, one that references the start date and one, the end date. Also create a SQL expression, e.g., {#DOY-Start}, for each:
{fn dayofyear(`table`.`start`)}
and change the selection formula to the following:
//{@Monday}:
{@Weekno-Start} <= {?Pm-@weeknumber} and
{@Weekno-End} >= {?Pm-@weeknumber} and
{%Month-Start} <= {?Pm-%Month} and
{%Month-End} >= {?Pm-%Month} and
{%Year-Start} <= {?Pm-%Year} and
{%Year-End} >= {?Pm-%Year} and
{%DOY-Start} <= {?Pm-@dayofyear}+1 and
{%DOY-End} >= {?Pm-@dayofyear}+1
Note that the 1 should be removed for Sunday, and should be changed to 2 for Tuesday, 3 for Wednesday, etc., since {Pm-@dayofyear} will always show the dayofyear for Sunday of the specific week.
You should check to make sure that the dayofyear function in your database will match the formula that is used for the Excel spreadsheet by placing the {?PM-{@Dayofyear} and {%DOY-Start} on a Sunday where the start date is a Sunday and making sure they match. You can adjust the formulas as necessary.
-LB
Actually, I think you might be able to leave out the month and weekno formulas/expressions in the subreports, because the dayofyear formula/expression already take these into account. You can try the following:
//{@Monday}:
{%Year-Start} <= {?Pm-%Year} and
{%Year-End} >= {?Pm-%Year} and
{%DOY-Start} <= {?Pm-@dayofyear}+1 and
{%DOY-End} >= {?Pm-@dayofyear}+1
-LB
//{@Monday}:
{%Year-Start} <= {?Pm-%Year} and
{%Year-End} >= {?Pm-%Year} and
{%DOY-Start} <= {?Pm-@dayofyear}+1 and
{%DOY-End} >= {?Pm-@dayofyear}+1
-LB
- Thread starter
- #4
- Thread starter
- #5
I have put the formulas in as shown above. However, I have one appointment that is scheduled for a Friday through to the next Friday, and it is showing on the three days prior to the appointment in addition to the actual dates of the appointment. I have not been able to narrow down what is causing this to happen.
Mary Anne Giezentanner
Mary Anne Giezentanner
- Thread starter
- #6
LB - I am not clear on how to link the formula {@dayofyear} to each subreport. Can you tell me how this should link?
Mary Anne Giezentanner
Mary Anne Giezentanner
In the main report, go to edit->subreport links and select the formula from the list on the left and move it to the right. Allow it to link to some field in the selected sub and then go into the subreport->report->selection formula->record and change the auto-generated link to the selection formula in my previous post.
I can't really trouble shoot your previous post without knowing exactly what you did and what the exact results were.
-LB
I can't really trouble shoot your previous post without knowing exactly what you did and what the exact results were.
-LB
- Thread starter
- #8
It looks like I do have the links done correctly. Attached is my cr as well as a print out in pdf format showing the results. There are appointments showing on the first line for June that should not start until 06/03 and continue into the next week.
Mary Anne Giezentanner
www.winsystems.com
Mary Anne Giezentanner
www.winsystems.com
- Thread starter
- #9
Sorry, here is the pdf file of the results
Mary Anne Giezentanner
www.winsystems.com
Mary Anne Giezentanner
www.winsystems.com
I retested this and it worked properly. I can't see anything wrong with your report, but I cannot see the content of your SQL expressions to check them, so am not sure whether you implemented these correctly. So I would double check them, and also within the subreports, insert another detail section and add the following to make sure they match:
{?pm-@dayofyear}+n {%dayofyear}
...where n is the number you are adding to the {?pm-@dayofyear} in your subreport selection formula.
I think you will need to add conditional suppression formulas to suppress each subreport like this to eliminate data from days that should be blank at the beginning of the month, but this will still not address the issue of data appearing in valid days:
maximum({@Mon},{@Weeknumber}) = 0 //for Mon sub, etc.
You still need to take the earlier two steps--check expressions and compare dayofyear values.
-LB
{?pm-@dayofyear}+n {%dayofyear}
...where n is the number you are adding to the {?pm-@dayofyear} in your subreport selection formula.
I think you will need to add conditional suppression formulas to suppress each subreport like this to eliminate data from days that should be blank at the beginning of the month, but this will still not address the issue of data appearing in valid days:
maximum({@Mon},{@Weeknumber}) = 0 //for Mon sub, etc.
You still need to take the earlier two steps--check expressions and compare dayofyear values.
-LB
- Thread starter
- #11
LB-
The report is including the blank days in the day of the year count even when those subreports are conditionally suppresed. This is what is causing the appointments to show on those days. Any thoughts on how I can correct that?
Mary Anne Giezentanner
The report is including the blank days in the day of the year count even when those subreports are conditionally suppresed. This is what is causing the appointments to show on those days. Any thoughts on how I can correct that?
Mary Anne Giezentanner
- Thread starter
- #13
LB-
Sorry about my last post. I am still having trouble getting the appointments to show correctly on the June calendar. For example, I have someone who scheduled days off from 06/03 to 06/10, and another person scheduled one day off on 06/03. The person with multiple days off, Jeff has appointments showing from 06/01 instead of starting on 06/03. The other employee with the single day appointment on 06/03 doesn't have one showing at all. However, these show correctly on the first version of the calendar report that is just showing their time off start date.
I did the two checks you recommended, and the day of year/start days seem to be matching.
Mary Anne Giezentanner
www.winsystems.com
Sorry about my last post. I am still having trouble getting the appointments to show correctly on the June calendar. For example, I have someone who scheduled days off from 06/03 to 06/10, and another person scheduled one day off on 06/03. The person with multiple days off, Jeff has appointments showing from 06/01 instead of starting on 06/03. The other employee with the single day appointment on 06/03 doesn't have one showing at all. However, these show correctly on the first version of the calendar report that is just showing their time off start date.
I did the two checks you recommended, and the day of year/start days seem to be matching.
Mary Anne Giezentanner
www.winsystems.com
Please place firstdayoff and lastdayoff in a detail_b section in the subreports for June 1 to June 5 or so. Also add both:
{?pm-@dayofyear}+n {%dayofyear}
...to the report footer of each. Then please post the results in an attached Crystal Report.
-LB
{?pm-@dayofyear}+n {%dayofyear}
...to the report footer of each. Then please post the results in an attached Crystal Report.
-LB
- Thread starter
- #15
Your link is to the design mode of one of the subs. I need to see the main report in preview mode AFTER you have added those fields to all subs, so I can see where the problem is for Jeff. I can't run the report, but I'd prefer to see the actual report saved with data so I can see formulas, etc.
Please also verify in another report that his date range off is in fact 6/3 to 6/10.
-LB
Please also verify in another report that his date range off is in fact 6/3 to 6/10.
-LB
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question