Calculated Fields

One of the most powerful aspects of CEM is its ability to display information based on calculations. These calculations may be internal to CEM, or may retrieve information from external databases.

For most customers, calculated fields form the basis of a reporting regime.

Highlights

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Category

1. Show the number of assets for this category

(select count(*) from equipment where equipment.equipmentcategoryid = vequipmentcategorysce.equipmentcategoryid and equipment.recordstatus =0)

2. Show the total cost of all assets for this each category

(select sum(TotalPriceGross) from Equipment where Equipment.EquipmentCategoryId = vEquipmentCategorySce.EquipmentCategoryId and Equipment.RecordStatus < 8)

3. Show the number of jobs for each type of equipment

(select count(*) from Job where Job.EquipmentCategoryId = vEquipmentCategorySce.EquipmentCategoryId and Job.RecordStatus < 8)

Equipment

1. Show the number of jobs for each asset

(select count(*) from job where job.recordstatus = 0 and job.equipmentid = vequipmentsce.equipmentid)

2. Equipment age (in months)

DATEDIFF( month, PurchaseDate, GETDATE())

3. Cost of spare parts used in jobs for each asset

(select sum(costproduct) from job where job.recordstatus = 0 and job.equipmentid = vequipmentsce.equipmentid)

Job

1. How long ago was a job raised

DATEDIFF( minute, JobRequestDate, GETDATE())

2. Show the number of tasks for each job

(select count(*) from task where task.jobid = vjobsce.jobid)

Model

1. Show the number of assets for each model

(SELECT COUNT(*) FROM Equipment WHERE Equipment.EquipmentModelId = vEquipmentModelSce.EquipmentModelId AND Equipment.RecordStatus < 8)

2. Show the number of jobs for each model

(select count(*) from job inner join equipment on job.equipmentid = equipment.equipmentid where job.recordstatus = 0 and
equipment.equipmentmodelid = vequipmentmodelsce.equipmentmodelid)

3. Show the total job cost for each model

(select sum(TotalCostGross) from job inner join equipment on job.equipmentid = equipment.equipmentid where job.recordstatus = 0 and
equipment.equipmentmodelid = vequipmentmodelsce.equipmentmodelid)

 

 

 

Related resources

• You might also find some calculated field definitions on the Leeds user group web site.