This will export the resource workload to an Excel sheet or a CSV text file, per day/week/month in a specified period.
Only the resources (or groups) where a resource type has been specified will be included in the workload calculaton. If a resource type is set on a resource group the workload for all sub resources (and sub groups and their sub resources etc.) within that group will be summed.
Notice that by default the resource type which is set on the resource to include must have it’s “bit flag” no. 15 set (see parameter ‘resourceTypeBitFlagNo’ below).
Example parameter configuration
Example output (Excel)
Month by month in three months.
| Resource | Resource type | Month | Avail |
| Cleaning | Type 1 | Nov-10 | 0 |
| Construction group | Type 1 | Nov-10 | 0 |
| Construction2 | Type 1 | Nov-10 | 0 |
| Cooling | Type 1 | Nov-10 | 0 |
| Heating | Type 1 | Nov-10 | 800 |
| Laboratory | Type 1 | Nov-10 | 0 |
| Painting | Type 1 | Nov-10 | 0 |
| Painting2 | Type 1 | Nov-10 | 0 |
| Painting3 | Type 1 | Nov-10 | 4560,5 |
| Painting4 | Type 1 | Nov-10 | 0 |
| Tech-Secr | Type 1 | Nov-10 | 133 |
| Welding group1 | Type 1 | Nov-10 | 1862 |
| Prod-DK | Type 2 | Nov-10 | 10660 |
| Prod-Ext | Type 2 | Nov-10 | 6560 |
| Supervisor | Type 2 | Nov-10 | 0 |
| Welding group2 | Type 2 | Nov-10 | 1640 |
| Welding2 | Type 2 | Nov-10 | 0 |
| Cleaning | Type 1 | Dec-10 | 0 |
| Construction group | Type 1 | Dec-10 | 0 |
| Construction2 | Type 1 | Dec-10 | 0 |
| Cooling | Type 1 | Dec-10 | 0 |
| Heating | Type 1 | Dec-10 | 820 |
| Laboratory | Type 1 | Dec-10 | 0 |
| Painting | Type 1 | Dec-10 | 0 |
| Painting2 | Type 1 | Dec-10 | 0 |
| Painting3 | Type 1 | Dec-10 | 4703 |
| Painting4 | Type 1 | Dec-10 | 0 |
| Tech-Secr | Type 1 | Dec-10 | 137 |
| Welding group1 | Type 1 | Dec-10 | 1918 |
| Prod-DK | Type 2 | Dec-10 | 10985 |
| Prod-Ext | Type 2 | Dec-10 | 6760 |
| Supervisor | Type 2 | Dec-10 | 0 |
| Welding group2 | Type 2 | Dec-10 | 1690 |
| Welding2 | Type 2 | Dec-10 | 0 |
| Cleaning | Type 1 | Jan-11 | 0 |
| Construction group | Type 1 | Jan-11 | 0 |
| Construction2 | Type 1 | Jan-11 | 0 |
| Cooling | Type 1 | Jan-11 | 0 |
| Heating | Type 1 | Jan-11 | 760 |
| Laboratory | Type 1 | Jan-11 | 0 |
| Painting | Type 1 | Jan-11 | 0 |
| Painting2 | Type 1 | Jan-11 | 0 |
| Painting3 | Type 1 | Jan-11 | 4343,5 |
| Painting4 | Type 1 | Jan-11 | 0 |
| Tech-Secr | Type 1 | Jan-11 | 126,5 |
| Welding group1 | Type 1 | Jan-11 | 1741 |
| Prod-DK | Type 2 | Jan-11 | 10140 |
| Prod-Ext | Type 2 | Jan-11 | 6240 |
| Supervisor | Type 2 | Jan-11 | 0 |
| Welding group2 | Type 2 | Jan-11 | 1560 |
| Welding2 | Type 2 | Jan-11 | 0 |
Week by week in three weeks.
| Resource | Resource type | Week | Avail |
| Cleaning | Type 1 | 2010-46 | 0 |
| Construction group | Type 1 | 2010-46 | 0 |
| Construction2 | Type 1 | 2010-46 | 0 |
| Cooling | Type 1 | 2010-46 | 0 |
| Heating | Type 1 | 2010-46 | 180 |
| Laboratory | Type 1 | 2010-46 | 0 |
| Painting | Type 1 | 2010-46 | 0 |
| Painting2 | Type 1 | 2010-46 | 0 |
| Painting3 | Type 1 | 2010-46 | 1023,5 |
| Painting4 | Type 1 | 2010-46 | 0 |
| Tech-Secr | Type 1 | 2010-46 | 30 |
| Welding group1 | Type 1 | 2010-46 | 420 |
| Prod-DK | Type 2 | 2010-46 | 2405 |
| Prod-Ext | Type 2 | 2010-46 | 1480 |
| Supervisor | Type 2 | 2010-46 | 0 |
| Welding group2 | Type 2 | 2010-46 | 370 |
| Welding2 | Type 2 | 2010-46 | 0 |
| Cleaning | Type 1 | 2010-47 | 0 |
| Construction group | Type 1 | 2010-47 | 0 |
| Construction2 | Type 1 | 2010-47 | 0 |
| Cooling | Type 1 | 2010-47 | 0 |
| Heating | Type 1 | 2010-47 | 180 |
| Laboratory | Type 1 | 2010-47 | 0 |
| Painting | Type 1 | 2010-47 | 0 |
| Painting2 | Type 1 | 2010-47 | 0 |
| Painting3 | Type 1 | 2010-47 | 1030 |
| Painting4 | Type 1 | 2010-47 | 0 |
| Tech-Secr | Type 1 | 2010-47 | 30 |
| Welding group1 | Type 1 | 2010-47 | 420 |
| Prod-DK | Type 2 | 2010-47 | 2405 |
| Prod-Ext | Type 2 | 2010-47 | 1480 |
| Supervisor | Type 2 | 2010-47 | 0 |
| Welding group2 | Type 2 | 2010-47 | 370 |
| Welding2 | Type 2 | 2010-47 | 0 |
| Cleaning | Type 1 | 2010-48 | 0 |
| Construction group | Type 1 | 2010-48 | 0 |
| Construction2 | Type 1 | 2010-48 | 0 |
| Cooling | Type 1 | 2010-48 | 0 |
| Heating | Type 1 | 2010-48 | 180 |
| Laboratory | Type 1 | 2010-48 | 0 |
| Painting | Type 1 | 2010-48 | 0 |
| Painting2 | Type 1 | 2010-48 | 0 |
| Painting3 | Type 1 | 2010-48 | 1030 |
| Painting4 | Type 1 | 2010-48 | 0 |
| Tech-Secr | Type 1 | 2010-48 | 30 |
| Welding group1 | Type 1 | 2010-48 | 420 |
| Prod-DK | Type 2 | 2010-48 | 2405 |
| Prod-Ext | Type 2 | 2010-48 | 1480 |
| Supervisor | Type 2 | 2010-48 | 0 |
| Welding group2 | Type 2 | 2010-48 | 370 |
| Welding2 | Type 2 | 2010-48 | 0 |
Day by day in three days.
| Resource | Resource type | Day | Avail |
| Cleaning | Type 1 | 15 Nov | 0 |
| Construction group | Type 1 | 15 Nov | 0 |
| Construction2 | Type 1 | 15 Nov | 0 |
| Cooling | Type 1 | 15 Nov | 0 |
| Heating | Type 1 | 15 Nov | 40 |
| Laboratory | Type 1 | 15 Nov | 0 |
| Painting | Type 1 | 15 Nov | 0 |
| Painting2 | Type 1 | 15 Nov | 0 |
| Painting3 | Type 1 | 15 Nov | 217 |
| Painting4 | Type 1 | 15 Nov | 0 |
| Tech-Secr | Type 1 | 15 Nov | 6,5 |
| Welding group1 | Type 1 | 15 Nov | 91 |
| Prod-DK | Type 2 | 15 Nov | 520 |
| Prod-Ext | Type 2 | 15 Nov | 320 |
| Supervisor | Type 2 | 15 Nov | 0 |
| Welding group2 | Type 2 | 15 Nov | 80 |
| Welding2 | Type 2 | 15 Nov | 0 |
| Cleaning | Type 1 | 16 Nov | 0 |
| Construction group | Type 1 | 16 Nov | 0 |
| Construction2 | Type 1 | 16 Nov | 0 |
| Cooling | Type 1 | 16 Nov | 0 |
| Heating | Type 1 | 16 Nov | 40 |
| Laboratory | Type 1 | 16 Nov | 0 |
| Painting | Type 1 | 16 Nov | 0 |
| Painting2 | Type 1 | 16 Nov | 0 |
| Painting3 | Type 1 | 16 Nov | 223,5 |
| Painting4 | Type 1 | 16 Nov | 0 |
| Tech-Secr | Type 1 | 16 Nov | 6,5 |
| Welding group1 | Type 1 | 16 Nov | 91 |
| Prod-DK | Type 2 | 16 Nov | 520 |
| Prod-Ext | Type 2 | 16 Nov | 320 |
| Supervisor | Type 2 | 16 Nov | 0 |
| Welding group2 | Type 2 | 16 Nov | 80 |
| Welding2 | Type 2 | 16 Nov | 0 |
| Cleaning | Type 1 | 17 Nov | 0 |
| Construction group | Type 1 | 17 Nov | 0 |
| Construction2 | Type 1 | 17 Nov | 0 |
| Cooling | Type 1 | 17 Nov | 0 |
| Heating | Type 1 | 17 Nov | 40 |
| Laboratory | Type 1 | 17 Nov | 0 |
| Painting | Type 1 | 17 Nov | 0 |
| Painting2 | Type 1 | 17 Nov | 0 |
| Painting3 | Type 1 | 17 Nov | 223,5 |
| Painting4 | Type 1 | 17 Nov | 0 |
| Tech-Secr | Type 1 | 17 Nov | 6,5 |
| Welding group1 | Type 1 | 17 Nov | 91 |
| Prod-DK | Type 2 | 17 Nov | 520 |
| Prod-Ext | Type 2 | 17 Nov | 320 |
| Supervisor | Type 2 | 17 Nov | 0 |
| Welding group2 | Type 2 | 17 Nov | 80 |
| Welding2 | Type 2 | 17 Nov | 0 |
Parameter descriptions
(* = configuration required)
applyFiltering
If true the exported workload will reflect the workload graph settings and the filter settings in the Gantt chart.
Default false
applyResourceEfficiency
If true resource efficiency is factored in to workload calculations. The workload hours overlapping a period are multiplied by the efficiency of the resource.
Efficiency is never factored with steps that are configured to ignore efficiency. setup, for instance, usually ignores resource efficiency.
This setting completely overrides the setting on individual resources about resource efficiency being factored in to workload hours.
availableHoursText
Specify the text to be printed in the header cell for the available hours column.
The default is ‘(avail)’.
dayDateFormat
Specify the date format to use if ‘D’ is specified in ‘periodType’.
Default is ‘d MMM’, i.e. 3 Aug.
For additional examples se Formatting dates
dayText
Specify the text to be printed in the header cell for the period column, when ‘periodType’ is specified to ‘D’.
The default is ‘Day’.
decimalSeparator
The decimal separator used when exporting numbers, i.e. ‘,’ or ‘.’ (comma or period).
If left blank the default decimal separator corresponding to language preferences are automatically selected.
excludeFinishedWorkload
Specify true to ignore workload already finished on operations in progress.
Default false.
fileNameCSV *
Directory path including filename to the export file can be specified.
This must be specified if “outputAsCSV” is true.
Example: c:\workloadOverview.csv
fileNameExcel *
Directory path including filename to the export file can be specified.
Example: c:\workloadOverview.xls
includeOperationStates
Only relevant if ‘includeOperations’ is enabled.
Define the operation states to include in the report. Default is 20, 30, 40. See also ‘usedHours1StatusList’.
includeOperations
If this is enabled operations are added to the report. One row per operation, after each resource. At least 2 columns are always shown when this is enabled – ‘Operation’ and ‘Operation hours’. More columns can be added, see parameter ‘insertOperationColumns’.
This is only supported if ‘outputAsCSV’ is false.
insertOperationColumns
Here a list of layout items (fields/columns) can be defined. The same fields as in the field chooser in ROB-EX tables can be used.
For a list of the possible fields see the variable list. In the field chooser the id of a field is also shown.
Default is: order_name, order_customer_name, opr_state, opr_start_time_readable, opr_end_time_readable
insertResourceColumns (from v7.0)
To see the list of possible resource layout items, see the resource layout items list. In the field chooser, the id of a field is also shown.
If you delete all of the layout item ids from the insertResourceColumns column, it’ll go back to the default layout item ids. Default layout item ids are:
resource_name[name=Resource], resource_group_name, resource_resourcetype_name
How to use:
The string is structured with layout item ids, separated by comma “,”. Each layout item can have attributes set to it.
The entered string can contain attributes for either Header Cells or Data Cells. You can create a list of attributes by encapsulating the list with square brackets (“[” and “]”). The left square bracket opens the list of attributes and the right bracket closes it. You separate attributes with a semicolon “;”.
Example: resource_name[name=Resource;replace=StringToReplace:NewString], resource_group_name
Useable Attributes:
Attributes for Header Cells:
- name = Overwrites the name of the layout item. ROB-EX automatically gets the layout item name from language files, structured as a “friendly name”. If you want a specific name for the column, use this attribute.
Attributes for Data Cells:
- replace = Searches for a chosen string and replaces if with a chosen replacement string – assignment is given with a colon (eg. StringToReplace:NewString)
- numberFormat = Formats the number from a given syntax (eg. ###.## = 987.65, ### = 988)
monthDateFormat
Specify the date format to use if ‘M’ is specified in ‘periodType’.
Default is ‘MMM-yy’, i.e. Aug-10.
For additional examples se Formatting dates
monthText
Specify the text to be printed in the header cell for the period column, when ‘periodType’ is specified to ‘M’.
The default is ‘Month’
numberFormat
The format of the numbers. #.## means for instance 10,57 (rounded from 10,56739). A hash symbol (#) indicates the max number of decimals, in this case it’s two. The format ‘0.00’ indicates exactly two decimals – i.e. 10,50.
The default is ‘#.##’. This is only relevant if ‘outputAsCSV’ is true.
onlyFinishedHours
If ‘true’ is specified only the finished hours will be reported for operations in progress. In this case parameter “excludeFinishedWorkload” has no effect.
Default is ‘false’.
outputAsCSV
If ‘true’ is specified the output format will be in CSV instead of XLS.
Default is ‘false’.
period
The quantity of days, weeks or months – dependent of what is specified in ‘periodType’.
periodType
‘D’, ‘W’ or ‘M’ can be specified. For instance if ‘W’ is specified and ‘4’ is specified in ‘period’ the workload will be calculated week by week in four weeks.
resourceText
Specify the text to be displayed in the resource header column.
The default is ‘Resource’.
resourceTypeBitFlagNo
If this is specified the resource types which is set on the resources to include must have the specified bit no. set.
This is done in “Edit->Resource” dialog. Select the “Resource types” tab and check the “Optional sub category” shown below.
Possible values are 8, 9, 10, 11, 12, 13, 14, 15. The default is 15.
resourceTypeText
Specify the text to be displayed in the resource type header column.
The default is ‘Resource type’.
separator
Specify the character used to delimit the columns of the generated CSV file.
The default is ‘;’ (semicolon).
sheetName
Specify the name of the Excel sheet to put the data in.
Default is ‘Workload Overview’
start
The start date from where the workload calculation should be performed.
The keyword “this week” corresponds to Monday morning at 00:00 in the current week. It is possible to add and subtract by specifying for instance ‘this w-3w+2d’ (or ‘this week – 3weeks + 2days’) (also see period parameter). Please notice that ‘m’ corresponds to month and NOT minute.
Supported notation: “now” or “this week” (not “this month”), y/year(s), m/mon/month(s), w/week(s), d/day(s), h/hour(s), min/minute(s) and s/sec/second(s).
The default is ‘this week’.
If ‘M’ is specified in ‘periodType’ the calculation will be performed from the first day in the month specified in this field. For instance if ‘this week + 2 days ‘ are specified – resulting in for instance the 18th of Nov, the calculation will be performed from the 1st of Nov at 00:00 in the morning.
If ‘W’ had been specified in ‘periodType’ the calculation would be performed from Monday the 15th of Nov at 00:00 in the morning.
At last if ‘D’ is specified in ‘periodType’ the calculation will be performed from the 18th of Nov from 00:00 in the morning.
Thus specifying hours, minutes or seconds will not give any effect.
usedHours1StatusList
Specify a comma separated operation state list to include in the workload calculation. This is used in the ‘Used1’ column.
Example: If ’20, 40’ is specified only operations with state planned or started is included in the workload calculation.
Possible states:
15 Reserved/Forecast
20 Planned
30 Start possible
40 Started
50 Paused
60 Cancelled
70 Completed
If nothing is specified all states except ‘70’ are included.
usedHours2StatusList
Specify a comma separated operation state list to include in the workload calculation. This is used in the ‘Used2’ column. If nothing is specified all states except ‘70’ are included.
usedHours3StatusList
Specify a comma separated operation state list to include in the workload calculation. This is used in the ‘Used3’ column. Leave blank if no calculations are whished in this column.
usedHours4StatusList
Specify a comma separated operation state list to include in the workload calculation. This is used in the ‘Used4’ column. Leave blank if no calculations are whished in this column.
usedHoursText1
Specify the text to be printed in the ‘Used 1’ column
The default is ‘(used1)’.
usedHoursText2
Specify the text to be printed in the ‘Used 2’ column
The default is ‘(used2)’.
usedHoursText3
Specify the text to be printed in the ‘Used 3’ column
The default is ‘(used3)’.
usedHoursText4
Specify the text to be printed in the ‘Used 4’ column
The default is ‘(used4)’.
usedQuantity1StatusList
Specify a comma-separated list of operation states to include in the quantity calculation. This is used in the ‘usedQuantityText1’ column. If left blank, the column will not be shown.
usedQuantity2StatusList
Specify a comma-separated list of operation states to include in the quantity calculation. This is used in the ‘usedQuantityText2’ column. If left blank, the column will not be shown.
usedQuantity3StatusList
Specify a comma-separated list of operation states to include in the quantity calculation. This is used in the ‘usedQuantityText3’ column. If left blank, the column will not be shown.
usedQuantity4StatusList
Specify a comma-separated list of operation states to include in the quantity calculation. This is used in the ‘usedQuantityText4’ column. If left blank, the column will not be shown.
usedQuantityText1
Specify the text to be printed in the header for the ‘used quantity 1’ column
The default is ‘UsedQty1’.
usedQuantityText2
Specify the text to be printed in the header for the ‘used quantity 2’ column
The default is ‘UsedQty2’.
usedQuantityText3
Specify the text to be printed in the header for the ‘used quantity 3’ column
The default is ‘UsedQty3’.
usedQuantityText4
Specify the text to be printed in the header for the ‘used quantity 4’ column
The default is ‘UsedQty4’.
weekDateFormat
Specify the date format to use if ‘W’ is specified in ‘periodType’.
‘yyyy-ww’ will result in ’2010-28’.
The default is ‘yyyy-ww’.
For additional examples se Formatting dates
weekText
Specify the text to be printed in the header cell for the period column, when ‘periodType’ is specified to ‘W’.
The default is ‘Week’.
Post your comment on this topic.