Tile Reports
Instructions on how to create Tile Reports
Creating a Tile Report
Under the Admin menu option click on Tile Reports and Add New Report
Add the Tile Name, Tile Group (the group of tile reports that the tile should appear in.
Add if the count value is a percentage value or not
In Query Text Summary add the count of the fields required to be displayed. Please ensure this is the same From and Where clause as the Detailed Query.
In Query Text Details add the fields required to be displayed for the drill down from the summary count tile.
In all Where clauses please include any placeholders that have been created. e.g. where actiontype IN {{OnSite}}.
See Query Placeholders for details on how to create a placeholder.
In Column Groups add the name of the field/fields that the detail query will be grouped by and click Add.
In the Date Ranges field add the Date Ranges for which the tile will be run for.
A new tile is created for each date range selected. This automatically feeds into the place holders {{fromdate}} and {{todate}} from the SQL queries e.g. WHERE completeddate between {{fromdate}} and {{todate}}.
The Colour Bands is the target ranges that are set to determine the colour of the tile. If this is not required click Remove at each level and set the lower value to 0.
Leave "Is Published" to "No" until the report is working and is ready for release. It will not be visible to others until it is published
The tile and the details can be checked by clicking Preview. This gives an overview of the tile and the details.
If Preview doesn't work then there is an issue with the tile and the details entered needs to be checked. This is most likely in the SQL queries.
Once the report is working and ready for release, change the Is Published to Yes and Click Save.
The Primary tables used for tiles and reports are as follows.
Job Table (GSJOBFIL - usually aliased to J)
JobNo is the main key. BookedRef is the Client Order Number. PropertyKeyNo is unique ID which links to the Property Table. Clientcode is the client, contract and jobtype are self explanitory. Status is the job status. RecordedDate and CompletedDate are the recorded and completed dates of the job. Statustype is 'Classification'. And SalesValue / CostValue are useful for seeing financials at the job level.
Event Table (GSJOBDET - usually aliased to D)
This table contains all the events in the system. It links to the job through JobNo. Below are a selection of the fields. Jobeventkeyno is a unique identifer in this table and can be useful for linkage. NotifiedDate is logged date, AssignedDate is Attend On date, Required Date is event level target date. AssignedType is Designation Type. ActionRequired is Event Name. ProblemSummary is sometimes used for 'Trade'. Assignee_ID is engineers ID and links to the Engineers table. Status1 is event level status. ActionType is usually either 'On Site' or 'Internal'. We have CostValue and SalesValue at event level in this table. All the values from a certain job number should give the same total as the fields in the job table above.
Property Table (GSJOBPRO - usually aliased to P)
This is the table containing all the properties in the system. The PropertyID is sometimes known as the UPRN. PropertyKeyNo can be linked to the job or event table. CurrentlyActive lets us know if the property is currently active one. We have the address information. The name of the tenant associated with the property. We also can store landlord information in there (LLNAME, LLADDR1, etc)
Engineer Table (GSJOBENG - usually aliased to E)
This is information on the engineers that do the jobs. Assignee_ID can link to the events. Active shows if they are currently active in the system (ie with the company)
Job Items (GSJOBITM - usually aliased to I)
The contains information on all the job items (or SORs or LABOUR) used on a job/event. It has the cost and sales value for the item(s). Accountcode1 is the stock name and Descr1 gives a description. Qty_required is how many items were on the job. GSSTOCKRECNO can link to the stock table (GSSTOCK) where you can pull more detail out about the particular item. Items can be chargeable or non-chargeable usually depending on how they are configured.
Last updated