How to create and customise the online reports, forms and templates and change the tags?

Reports and Forms are simple documents where you can control the layout/look of any data which is reported on. You use Filters to control the data in your reports, and the report is just how the data layout. Tags are inserted into the report, which are replaced with real data once the report is run, and all the available tags you can insert are shown at the bottom of the report editor when you are editing a report. You can edit reports from your Account Settings page, then press Edit Reports.

Reports can be complex and include a large a number of events or just a simple worksheet form with information taken from a single event. Below is an example of a list of events with some summery information.





Editing Reports



It is recommended you start with a sample that closely matches the layout you need then it can be copied and modified to fit your needs, rather than starting from an empty page and building the report from new.


Report Sections


You can style the page as you need but the data is built on 2 main sections and the tags are only used in these sections:

1, The ResourceLoop section can only be included once per report and will be repeated for each Resource being reported on, or selected in your Resource filter. Tags in this section generally start with SI-R (a Schedule it resource). Reports can just include this section if no event data is need, for example this report will create list of resources with their name and email address. This is a Resource Only report.

{{ResourceLoop}}
{{SI-R-Name}}, {{SI-R-EmailAdd}}
{{ResourceLoopEnd}}


2, The EventLoop section can only be included once per report and will be repeated for each Event being reported on, or selected in your Event filter. Tags in this section generally start with SI-E (a Schedule it event). Reports can just include this section if no resource data is need, for example this report will create a list of events with their title and start date. This is an Events Only report.

{{EventLoop}}
{{SI-E-Title}}, {{SI-E-DateStart}}
{{EventLoopEnd}}



It is possible to join these 2 main section and have a report that list resources with some details, and includes the events for that resource. In this example the report will have a section for each resource and under the resource name will be a list of all events tagged to that resource, below the list of a events will be a total of all the events hours. This is a Resource and Event report.


{{ResourceLoop}}
{{SI-R-Name}}
{{EventLoop}}
{{SI-E-Title}}, {{SI-E-DateStart}}
{{EventLoopEnd}}
SI-R-TotalResourceHours}}
{{ResourceLoopEnd}}




Report Editing

Our editor allows you to change the style and layout of your report templates just like a standard text document. To add extra data fields, set your cursor position where you want to add it, then pick from the list of available data fields shown at the bottom of the editor, then press Insert to insert the select data tag at your cursor position.

All Resource tags must be in-between the text {{SI-Resourceloop}} and {{SI-ResourceloopEnd}}. All Event tags must be in-between the text {{SI-Eventloop}} and {{SI-EventloopEnd}}.







Report Filters


When running any report the filter you have applied in your current view will be used. If you Save a filter, you can also have this automatically selected when you run the report. For example you could have a filter that only shows Holiday events for 2019 and a report layout with just the information needed for your Holiday reporting, selecting this Holiday report will auto select the Holiday filter. This allows you to fully customize the data shown in each report down to a subset of resources and events.

To save a filter, choose Search/Filter from the Schedule menu, set your filter criteria, and press Save. The name you give your Filter can then be selected in the report editor for any report.





Report Tags



It is best to use the Report Editor to insert the correct report tags, but they be typed manually if you are aware of the data you need. Only a small subset of these tags are available in email and other custom templates.

Global Fields
{{SI-ReportDateStart}} = Report Date Range Start (yyyy-mm-dd)
{{SI-ReportDateStartYYYY}} = Report Date Range Start Year (yyyy)
{{SI-ReportDateStartMM}} = Report Date Range Start Month (mm)
{{SI-ReportDateStartDD}} = Report Date Range Start Date (dd)
{{SI-ReportDateEnd}} = Report Date Range End (yyyy-mm-dd)
{{SI-ReportDateEndYYYY}} = Report Date Range End Year (yyyy)
{{SI-ReportDateEndMM}} = Report Date Range End Month (mm)
{{SI-ReportDateEndDD}} = Report Date Range End Date (dd)
{{SI-ReportDays}} = Report Date Range in Days
{{SI-CurrentDateTime}} = Date and Time when Report Created

Resources Fields (Place inside the Resource Loop Section)
{{SI-R-Name}} = Full Name
{{SI-R-EmailAdd}} = Email Address
{{SI-R-PicImage}} = Photo
{{SI-R-Groups}} = Group IDs
{{SI-R-GroupNames}} = Group Names
{{SI-R-Data1}} = Data Field 1 (first field from Resources Details tab)
{{SI-R-Data2}} = Data Field 2 (second field from Resources Details tab)
{{SI-R-Data3}} = Data Field 3 (third field from Resources Details tab)
{{SI-R-Data4}} = Data Field 4
{{SI-R-Data5}} = Data Field 5
{{SI-R-Data6}} = Data Field 6
{{SI-R-Data7}} = Data Field 7
{{SI-R-Data8}} = Data Field 8
{{SI-R-Data9}} = Data Field 9
{{SI-R-Data10}} = Data Field 10 (Notes)
{{SI-R-ColorText}} = Color Text
{{SI-R-ColorBack}} = Color Background
{{SI-R-Location}} = Location (Location field from Resources Details tab)
{{SI-R-Price}} = Price/Cost
{{SI-R-PriceType}} = Price/Cost Type
{{SI-R-SkillsAllNames}} = List of Labels/Skills for the resource
{{SI-R-AvailableWorkingHours}} = Total Resource Working Hours Available
{{SI-R-AllAvailableHours}} = Total Resource Hours Available
{{SI-R-TotalResourceEvents}} = Total Resource Events Count
{{SI-R-TotalResourceDays}} = Total Resource Days Booked
{{SI-R-TotalResourceHours}} = Total Resource Hours Booked
{{SI-R-TotalResourceCosts}} = Total Event Costs Per Resource
{{SI-R-TotalResourceAutoCosts}} = Total Resource Costs Per Resource

Events Fields (Place inside the Event Loop Section)
{{SI-E-ID}} = Event ID
{{SI-E-DateStartEnd}} = Start & End Date & Time (text)
{{SI-E-DateStart}} = Start Date & Time (text)
{{SI-E-DateStartISO2}} = Start Date & Time (ISO yyyy-mm-ddThh:mmZ)
{{SI-E-DateStartISO}} = Start Date & Time (SQL yyyy-mm-dd hh:mm)
{{SI-E-ShortDateStart}} = Start Date (text)
{{SI-E-DateStartYYYY}} = Start Date (yyyy)
{{SI-E-DateStartMM}} = Start Date (mm)
{{SI-E-DateStartDD}} = Start Date (dd)
{{SI-E-DateStartWeekNum}} = Start Date Week Number (US)
{{SI-E-DateStartWeekNumISO}} = Start Date Week Number (ISO 8601, UK)
{{SI-E-ShortTimeStart}} = Start Time (hh:mm)
{{SI-E-DateStartHH}} = Start Hours (hh)
{{SI-E-DateStartII}} = Start Minutes (mm)
{{SI-E-DateStartWD}} = Start Weekday Name
{{SI-E-DateEnd}} = End Date & Time (text)
{{SI-E-LastWorkEnd}} = End Date & Time of last working minute (text)
{{SI-E-DateEndISO2}} = End Date & Time (ISO yyyy-mm-ddThh:mm00Z)
{{SI-E-DateEndISO}} = End Date & Time (SQL yyyy-mm-dd hh:mm)
{{SI-E-ShortDateEnd}} = End Date (text)
{{SI-E-ShortLastWorkEnd}} = End Date of last working minute (text)
{{SI-E-DateEndYYYY}} = End Date (yyyy)
{{SI-E-DateEndMM}} = End Date (mm)
{{SI-E-DateEndDD}} = End Date (dd)
{{SI-E-ShortTimeEnd}} = End Time (hh:mm)
{{SI-E-DateEndHH}} = End Hours (hh)
{{SI-E-DateEndII}} = End Minutes (mm)
{{SI-E-DateEndWD}} = End Weekday Name
{{SI-E-DurationM}} = Duration in Minutes
{{SI-E-DurationH}} = Duration in Hours
{{SI-E-DurationD}} = Duration in Days (hours/24)
{{SI-E-DaysAffected}} = Number of days affected
{{SI-E-DateAdded}} = Date Added to Database
{{SI-E-DateModified}} = Date Last Modified in Database
{{SI-E-Title}} = Title
{{SI-E-Notes}} = Notes (simple text - multi line)
{{SI-E-SingleNotes}} = Notes (simple text - Single Line)
{{SI-E-FormattedNotes}} = Notes (with formatting)
{{SI-E-LocationName}} = Location Name
{{SI-E-LocationAddress}} = Location Address
{{SI-E-EmailAdd}} = Email Address
{{SI-E-Completed}} = Complete
{{SI-E-Priority}} = Priority
{{SI-E-ColorText}} = Color Text
{{SI-E-ColorBack}} = Color Background
{{SI-E-Series}} = Series ID
{{SI-E-ParentID}} = Parent ID
{{SI-E-Price}} = Price/Costs from an Events Finance Section
{{SI-E-AutoPrice}} = Price/Costs from Resource Price/Price Type Settings
{{SI-E-OwnersNamesALL}} = Names of all Tagged Resources
{{SI-E-NamesIn-????-Group}} = Names of all Tagged Resources from a specific Group (change ???? for group ID NUMBER)
{{SI-E-OwnersCount}} = Count all Tagged Resources
{{SI-E-CountIn-????-Group}} = Count all Tagged Resources from a specific Group (change ???? for group ID NUMBER)
{{SI-ResLoop}} {{SI-Show(????)}} {{SI-R-Name}}, {{SI-R-Data1}}, {{SI-R-Data2}}... Invited State = {{SI-R-EventState}} {{SI-ResLoopEnd}} = Show Extra Resource Section with Details (change ???? for group or resource ID NUMBER)
{{SI-E-Custom1}} = Custom 1 (multi line)
{{SI-E-Custom2}} = Custom 2
{{SI-E-Custom3}} = Custom 3
{{SI-E-Custom4}} = Custom 4
{{SI-E-Custom5}} = Custom 5
{{SI-E-Custom6}} = Custom 6
{{SI-E-Custom7}} = Custom 7
{{SI-E-Custom8}} = Custom 8
{{SI-E-Custom9}} = Custom 9
{{SI-E-SingleCustom1}} = Custom 1 (as a single line)
{{SI-E-SingleCustom2}} = Custom 2
{{SI-E-SingleCustom3}} = Custom 3
{{SI-E-SingleCustom4}} = Custom 4
{{SI-E-SingleCustom5}} = Custom 5
{{SI-E-SingleCustom6}} = Custom 6
{{SI-E-SingleCustom7}} = Custom 7
{{SI-E-SingleCustom8}} = Custom 8
{{SI-E-SingleCustom9}} = Custom 9
{{SI-E-Signature}} = Signature Data
{{SI-E-SplitResourceName}} = Group Name (in Split/Normalized Reports Only)
{{SI-E-SplitGroupNames}} = Resource Name (in Split/Normalized Reports Only)
{{SI-E-ResPending}} = Number of resources marked as Pending
{{SI-E-ResAccepted}} = Number of resources marked as Accepted
{{SI-E-ResDeclined}} = Number of resources marked as Declined
{{SI-E-ResAttended}} = Number of resources marked as Attended
{{SI-E-ResAbsent}} = Number of resources marked as Absent
{{SI-E-FinanceLoop}} Date = {{SI-F-Date}}, Item = {{SI-F-Description}}, Amount = {{SI-F-Amount}}
{{SI-E-FinanceLoopEnd}} = Finance Section with Items (all finance fields)
{{SI-E-F-Date}} = Finance Item Date
{{SI-E-F-Description}} = Finance Item Description
{{SI-E-F-Amount}} = Finance Item Amount
{{SI-E-OwnersNamesRAW}} = Meta data of all Tagged Resources

Report Summary Fields (Place at the report end)
{{SI-TotalReportEvents}} = Total Number of Events
{{SI-TotalReportResources}} = Total Number of Resources
{{SI-TotalReportDays}} = Total Number of Days from All Events
{{SI-TotalReportHours}} = Total Number of Hours from All Events
{{SI-TotalReportResourceAllHours}} = Total Number of All Hours Available from Resources
{{SI-TotalReportResourceAvailableHours}} = Total Number of Working Hours Available from Resources
{{SI-TotalReportEventCosts}} = Total Costs from All Events
{{SI-TotalReportResourceCosts}} = Total Costs from All Resources





Report Formulas


Whilst there are a number of built-in tags to give total hours, number of resources tagged etc, you can include mathematical formulas of your own to calculate values based on your report data. Once the report is created and all tags replaced with your data, then any calculations are run.

Some examples:

To get the events percent completed value as a fraction of 100 rather then a whole percentage you divide the events percentage value by 100. This is the tag to use:

SI-Calc{{{SI-E-Completed}} / 100}

To get the events price in pence/cents you can multiple the price by 100. This is the tag to use:

SI-Calc{{{SI-E-Price}} * 100}

Whole numbers only... (Other math functions round, floor, ceil, cos, sin, tan, scrt, log, abs)

SI-Calc{round({{SI-E-Price}})}


If you are storing the number allowed holidays in a resources details (information box 4) you can use a report (filtered for just Holidays) and show the days remaining. It takes the number found in the resources details field 4 and minuses the total events in the report for that resource. This is the tag to use:

SI-Calc{{{SI-R-Data4}} - {{SI-R-TotalResourceEvents}}}


Calculate a total cost of an event based in its hours duration and any fixed cost value you want to include in your report. This is the tag to use:

SI-Calc{{{SI-E-DurationH}} * 75.50}


Calculate the average number of events per day based on the days being reported on:

SI-Calc{{{SI-TotalReportEvents}}/{{SI-ReportDays}}}



Show the rate/cost of an event based on a resource price(from a resource in group 282) and event duration:

SI-Calc{({{SI-ResLoop}} {{SI-Show(282)}} {{SI-R-Price}} {{SI-ResLoopEnd}} * {{SI-E-DurationH}})curr}


If there are multiple resources in the same group then add each price together...

SI-Calc{({{SI-ResLoop}} {{SI-Show(282)}} {{SI-R-Price}} + {{SI-ResLoopEnd}} 0)curr}


...then add this cost to an internal store...

SI-Calc{store-add(SI-ResLoop}} {{SI-Show(282)}} {{SI-R-Price}} {{SI-ResLoopEnd}} * {{SI-E-DurationH}})}

...then at the end of the resources events show the total, less any event costs/adjustments...

SI-Calc{store-get+{{SI-R-TotalResourceCosts}}}

Pull a cost from a resource and store it:

SI-Calc{store-add({{SI-ResLoop}}{{SI-Show(12345)}} {{SI-R-Price}}{{SI-ResLoopEnd}})}

Total Costs: SI-Calc{(store-get+{{SI-TotalReportEventCosts}})curr}



Return difference between total booked hours and available hours only if the value is more than zero...

SI-Function[if(({{SI-R-TotalResourceHours}}-{{SI-R-AvailableWorkingHours}})>0){({{SI-R-TotalResourceHours}}-{{SI-R-AvailableWorkingHours}})}else{0}]



Last updated on Jan 16, 2018
Schedule it Ltd
/faq/10807/how-to-create-and-customise-the-online-reports-forms-and-templates-and-change-the-tags

Subscribe to our newsletter and receive our free scheduling tips and articles to help you improve the planning in your business. Unsubscribe anytime.

GET STARTED RESOURCES
Login Support and Help
Register Blog
Features Privacy Policy
Online Pricing Terms and Conditions
On-Prem Pricing Industry Sectors
Latest Version Reviews and Feedback
Live Webinar Status
APPS POPULAR SEARCHES
Web App Team Scheduling
iOS App Employee Planning
Android App Customer Scheduling
Zapier App Engineer Scheduling


Start Your Free Trial Today



Copyright © 2003-2020 Schedule it Ltd. All rights reserved.
Schedule it on LinkedIn   Schedule it on Twitter   Schedule it on YouTube   Schedule it on Facebook
Join Us - Live Webinar