PS. Pivot Tables
- iarmstrong
A pivot table is a table of statistics that summarizes the data from the database.
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.
You can create the pivot tables reports for different activities and agents, by choosing “Pivot” option from the relevant period/agent/activity menu of the Add Report button.
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.
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:
Go to Schedule tab > Reporting tab > Add Reports button > “Movements” option > “Mining” activity > “Pivot” option.
Click in the name field and rename to "Destination Check".
Drag “Source.Parcel” into the Row Area and filter out blank parcels.
Drag “Misc.DestinationRule” into the Row Area.
Drag “Destination.Name” into the Column Area.
Drag “Mining_wetTonnes” into the Data Area.
Drag “Destination.Top” into the Columns Area.
Use this table to identify if any Destination Rules are sending material to the wrong location.
Average Cycle Time Check
Create a pivot table to validate the average cycle times being used by trucks.
Go to Schedule tab > Reporting tab > Add Reports button > “Movements” option > “Mining” activity > “Pivot” option.
Click in the name field and rename to "Cycle Time Check".
Drag “Haulage.Truck” into the Row Area and set the filter to exclude blanks.
Drag “Source.Reserves” into the Row Area.
Drag “Source.Name” into the Row Area.
Drag “Destination.Top” into the Column Area.
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".
Bench Advance Check
Create a pivot table to visualise the bench progression by pit and stage:
Go to Schedule tab > Reporting tab > Add Reports button > “Movements” option > “Mining” activity > “Pivot” option.
Click in the name field and rename to "Bench Advance".
Drag “Source.Top” into the Filter Area and filter out everything except for “Reserves”.
Drag “Source.Pit/Stage/Bench/Flitch” fields into the Row Area.
Drag “Period.Name” into the Column Area.
Drag “Mining_wetTonnes” into the Data Area.
Use this table to confirm that bench turnover is within practical limits per period.
Loader Rate Check
Create a pivot table to validate the production rates being used by the excavation equipment:
Go to Schedule tab > Reporting tab > Add Reports button > “Movements” option > “Mining” activity > “Pivot” option.
Click in the name field and rename to "Dig Rates".
Drag “Agent.Name” into the Row Area and filter out “PlantAgent”.
Drag “MutexParcel.Haulage” into the Column Area.
Drag “HaulageResult.LoaderProductionRate.WTPH” into the Data Area.
You can also copy the chart image to the clipboard by clicking the Copy Image button in the right corner of the toolbar.
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 Dig Rates 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.