TS. Pivot Tables

Owned by iarmstrong
Nov 30, 2021
7 min read

You may create Pivot table type report by choosing this option from Add Report dropdown.

Schedule > Reporting > Add Report > "Pivot Report"

Overview and Pivot tab

Pivot table comes in really handy as a quick way to make an interactive summary from many records. Among other things, it can automatically sort and filter different subsets of data, count totals or calculate average. Another benefit of using pivot tables is that you can set up and change the structure of your summary table simply by dragging and dropping the source table's columns.

In the top right part of application you see the list of all project elements to work with, organised by levels or folders.

Drag and drop parameters to display in your custom report to related fields from the top right part of the Reporting tab to the bottom one. They will be automatically displayed in the main viewport accordingly.

Reporting tab fields

You may also customise right part form layout by pressing top right icon and selecting desired layout of areas and fields as shown below.

By default “Field Section and Areas Section Stacked” layout is set. You may change it at any time to suit your display preferences.

In a bottom part of this right section you see four areas where you can drag and drop required fields, it has an automated sorting function preventing you from dropping wrong fields into wrong areas. To remove a field from an area simply drag it out and drop in any spot where it is marked by the Black Cross sign.

You can always manually change the layout of your pivot table by dragging fields to the desired locations between existing columns.

Press the filter icon to open its dropdown and select options to display and organise them in your table.

Press Configure Format button to open Configure Pivot Grid Appearance window where you may add more custom settings, such as text font, size, style, colors and format for each element of a table.

Right click on a field in a report to see options to edit format or Show Totals option.

Press the Copy Image button to make a screenshot of your table, which will be copied to the clipboard and can be pasted directly to your reports or presentation.

Below in this section see a number of common pivot tables for error-checking your project setup. 

Destination Check

Create a pivot table to validate the destination logic setup: 

  1. Go to Schedule tab > Reporting tab > Add Report button > "Add Pivot Report". 

  2. Click in the name field and rename to "Destination Check".

  3. Drag "Activity Type" into the Filter Area and set the filter to show "Mining only". 

  4. Drag "Mining_wetTonnes" into the Data Area.

  5. Drag "Destination.Top" into the Columns Area.

  6. Drag "Source Parcel" into the Row Area.

  7. Drag "Destination Logic Rule" into the Row Area.

Use this table to identify if any Destination Rules are sending material to the wrong location.

Step Logic Check

Create a pivot table to validate the step logic setup:

  1. Go to Schedule tab > Reporting tab > Add Report button > "Add Pivot Report". 

  2. Click in the name field and rename to "Step Logic Check".

  3. Drag "Activity Type" into the Filter Area and set the filter to show "Mining only". 

  4. Drag "Mining_wetTonnes" into the Data Area.

  5. Drag "Fleet" into the Column Area.

  6. Drag into the Row Area:

    1. "Source.OpenPit",

    2. "Destination.Top",

    3. "Step Logic Rule".

Use this table to identify if any Step Rules are using fleets in the wrong areas.

Bench Progression Check

Create a pivot table to visualise the bench progression by pit and stage:

  1. Go to Schedule tab > Reporting tab > Add Report button > "Add Pivot Report". 

  2. Click in the name field and rename to "Bench Progression".

  3. Drag "Source.OpenPit" into the Filter Area and set the filter to show "OpenPit only". 

  4. Drag "Mining_wetTonnes" into the Data Area.

  5. Drag "Period" into the Column Area.

  6. Drag into the Row Area: Source.Mine/Pit/Stage/Bench.

Use this table to confirm that bench turnover is within practical limits per period.

Cycle Time Check

Create a pivot table to validate the average cycle times being used by trucks.

  1. Go to Schedule tab > Reporting tab > Add Report button > "Add Pivot Report". 

  2. Click in the name field and rename to "Cycle Time Check".

  3. Drag "Activity Type" into the Filter Area and set the filter to show "Mining only". 

  4. Drag "Truck" into the Row Area.

  5. Drag "Source.Mine/Pit/Stage" into the Row Area.

  6. Drag "Destination.Name" into the Column Area.

  7. Drag "HaulageResult.Times.Total" into the Data Area.

Use this table to confirm the average cycle times match with historical numbers.

Note that the HaulageResult fields return the instantaneous property of the haul cycle. For instance, "HaulageResult.FuelBurn.Total" is the fuel burn of a single return trip on that haul, not the actual fuel burned for this schedule transaction (which may represent a fraction of a haul). The same goes for "HaulageResult.Distance", "HaulageResult.Times" and so on. 

To calculate the actual time or fuel used, you must take the sumproduct of the property with "HaulageResult.NumberOfTrips".

Loader Rate Check

Create a pivot table to validate the production rates being used by the excavation equipment:

  1. Go to Schedule tab > Reporting tab > Add Report button > "Pivot Report". 

  2. Click in the name field and rename to "Loader Rate Check".

  3. Drag "Activity Type" into the Filter Area and set the filter to show "Mining only". 

  4. Drag "Loader" into the Row Area.

  5. Drag "MutexParcel.Haulage" into the Row Area.

  6. Drag "HaulageResult.LoaderProductionRate.WTPH" into the Data Area.

Use this table to confirm the loader rates match with planning parameters.

Chart tab 

Once you have finished setting up the table in the Pivot tab, you can go to the Charts tab and view the data graphically. For example, a graphical representation of the Loader Rate Check table created in the Pivot tab would look like shown in the figure below.

You can also create and edit charts in this tab. To do this, simply drag and drop the required fields into the applicable areas, just as you do when generating table reports. The data populated will be displayed in the chart, and also automatically applied to the table in the Pivot tab.

Hover the cursor over a graph column to display detailed information.

Click the Configure Format button to open the Series Colors dialog, where you can change the colors assigned to the columns.

  • You can select colors manually from the Color column dropdown, or

  • create a gradient by clicking the Generate Gradient button at the top.

Press OK to confirm and return to the chart.

In the lower right Charts Options panel, you can select the type of chart display through the Chart Type field dropdown.

Tick Show Labels, if you wish to display custom labels on the chart.

Tick Show Column as Series, if you wish to display columns as series, as shown below.

You can also copy the chart image to the clipboard by clicking the Copy Image button in the right corner of the toolbar.