To report on venue dark days, meaning a count of the number of days without any events happening (regardless of if there are any room bookings or not), you can use the below statement for EZWriter. You can use this to create an EZWriter Source and an EZWriter Template that can then be added to your dashboard.
EZWriter Statement
Dark Days Count
Select
count(EV001_ISO_DATE) as 'Dark Days'
From
EV001_MSTR_DATE_SCHED
WHERE
EV001_YEAR = 2016 /*adjust Year if required*/
and (select count(*) from EV200_EVENT_MASTER where EV200_EVT_START_DATE >= EV001_ISO_DATE and EV200_EVT_END_DATE <= EV001_ISO_DATE and EV200_ORG_CODE = @ORGANIZATION) = 0
Event Days Count
Select
count(EV001_ISO_DATE) as 'Event Days'
From
EV001_MSTR_DATE_SCHED
WHERE
EV001_YEAR = 2016 /*adjust Year if required*/
and (select count(*) from EV200_EVENT_MASTER where EV200_EVT_START_DATE >= EV001_ISO_DATE and EV200_EVT_END_DATE <= EV001_ISO_DATE and EV200_ORG_CODE = @ORGANIZATION) <> 0
Creating the EZWriter Source
1. From the Main Menu, click the EZWriter Data Sources link. The EZWriter Data Sources screen opens.
2. Click the Add button.
3. On the Add EZWriter Data Source, enter the necessary information:
- Description - Name of the EZWriter Data Source.
- Active - Check the check box.
- Note - Enter any internal notes for the EZWriter Data Source.
4. Select the Header tab.
5. Copy and paste the Dark Days Count statement from above into the text box.
6. Click OK.
7. Repeat steps 2-6 using the Event Days Count statement.
Creating the EZWriter Template
1. From the Main Menu, click the Email and Merge Templates link. The Email and Merge Templates screen opens.
2. Click the arrow next to the Add button.
3. Select Add EZWriter Template.
4. On the Add EZWriter Template screen, enter the necessary information:
- Description - Name of the EZWriter Template
- Data Source - Select the data source created for Dark Days in the Creating the EZWriter Source.
- Active - Check the check box.
4. Select the Header tab.
5. In the text box of the Header tab, enter the text for the template.
6. Enter * followed by the name of the EZWriter Source. For example, if you named the EZWriter Source Dark Days it is *Dark Days.
7. Click OK.
8. Repeat steps 2 - 7 for the Event Days Count template.
Displaying on the Dashboard
1. From the Global Navigation Bar, click the Home button.
2. In the lower right corner of the screen, click the Edit Dashboard link. The Edit Dashboard screen opens.
3. On the Gadgets tab, click the Add button. The Add Gadget screen opens.
4. On the Add Gadget screen, enter the necessary information:
- Description - Name of the gadget.
- Height - Height, in pixels, of the gadget display.
- Gadget Type - Select EZWriter.
- Format Template - Select the template created in the Creating the EZWriter Template section.
5. Click OK.
6. Repeat steps 3 - 5 for the Event Days Count.
Comments
2 comments
Thank you for this! One typo I noticed. In the event days count statement there should be a closing ) instead of 0 after @ORGANIZATION
0 upvotes
Thank You, Svend - well spotted. I have correct the typo!
0 upvotes
Please sign in to leave a comment.