table of contents


Aggregates by Equipment Category

(tap image to enlarge)

This MS Excel utility will aggregate the Activities, Work Requests, and Equipment Categories. Plotting the data into a chart that looks like this image.

The below process will need to be executed each time you would like to generate a fresh output of the data.

Versions:

  • March 20, 2022 - 1.0, Initial Release

  • March 25, 2022 - 2.0, additions of Hydros and Equipment Complete.

  • March 26, 2022 - 3.0, changes to how Hydro test complete is determined

To execute the MS Excel utility, follow the below steps.

Step 1 - Download the template MS Excel workbook from here.

Step 2 - Download the following data from the Wall Chart you would like to plot.

  1. Activities data as an MS Excel Workbook.

    • This can be found at Administrator Tile->Select Turnaround Card-> Select Wall Chart-> Equipment in Scope tab-> Download Activities Excel workbook in upper right corner.

  2. Work Requests as an MS Excel Workbook.

    • This can be found at Inspector Tile-> Select Wall Chart-> Download Work Requests Excel workbook in upper right corner.

  3. Equipment data as an MS Excel Workbook.

    • This can be found at Administrator Tile-> Select Equipment Card-> Download Equipment Excel workbook in upper right corner.

      NOTE: For the Equipment download, you will delete all Equipment from the MS Excel workbook that is not related to the Wall Chart.

Step 3 - In this step you will copy and paste the download data into the “Aggregates by Equipment Category” template. The “Aggregates by Equipment Category” download template workbook already has sheets in it, you will not need to add new sheets, but use the existing sheets. Follow the below steps to copy and paste the downloaded MS Excel sheets (from Step 2) into the template.

  1. Paste the downloaded Activities data into the Activities sheet.

    • Ensure you paste the data starting in cell A1 on the sheet.

  2. Paste the downloaded Work Requests data into the Work Request sheet.

    • Ensure you paste the data starting in cell A1 on the sheet.

  3. Paste the downloaded Equipment data into the Equipment sheet.

  4. Ensure you paste the data starting in cell A1 on the sheet.

    NOTE: If the Equipment data has not changed from the last time you produced the report, there is no need for refresh the data to the “Aggregates by Equipment Category” MS Excel template.

(tap image to enlarge)

Step 4 - Now that you have the data pasted into the template “Aggregates by Equipment Category”, you can run the macro to generate the data.

This is performed by tapping the “Execute Process” command button on the “Setup and Create” tab.

(tap image to enlarge)

Once you receive a message stating the process is complete and how many Equipment records were processed, you’ll notice on the Equipment sheet columns were added with aggregates. This newly added columns of data will be used to generate the pivot chart and associated graph on the “Output” Sheet.

NOTE: When you tap “Execute Process”, the prior data in the columns are cleared before it is repopulated.

(tap image to enlarge)

Step 5 - The final step is to confirm the Pivot Table and associated Graph is correct. To perform this step, follow the below steps.

  1. Select the “Output” sheet.

  2. Place your cursor on any cell within the Pivot table. When you perform this step, the “PivotTable Analyze” ribbon should appear in MS Excel.

  3. Tap the “Refresh” icon to refresh the pivot table to the latest data.

  4. You are complete, the data has been plotted within the Pivot Table and associated Graph.

    NOTE: The MS Excel template has a predefined range for the Pivot Table based on sample data. You may beed to increase, or decrease, the Pivot Table row range to ensure it analyzes all of your data.


Plot a project “S” curve based on Activities

(tap image to enlarge)

This MS Excel utility will generated a project “S” curve based on predicted vs actual activity completion.



Versions:

  • August 04, 2022 - 1.0, Initial Release

To execute the MS Excel utility, follow the below steps.

Step 1 - Download the template MS Excel workbook from here.

Step 2 - Download the Activities from the Wallchart Administrative tile and save the MS Excel file.

(tap image to enlarge)

Step 3 - Copy and paste the Activities downloaded into the MS Excel Activities sheet, starting with the A1 cell. Note: If there are legacy activities from an older project, please ensure they are removed.




(tap image to enlarge)

Step 4 - On the “Setup and Create” sheet, set the Start and End Date for the project. This will create the “Y” axis of the Pivot char and “X” axis of the chart.





(tap image to enlarge)

Step 5 (Optional) - There may be activities, such as report writing, defined in the data you want to ignore. You can define these activities to be ignored by filling in this information on “Setup and Create”.





Step 6 - On the “Setup and Create” tap the “Execute Process” button to execute the process. The VBA code will run and create the data starting in Column “T” on Activities sheet.

(tap image to enlarge)

Step 7 - To generate the graph, a Pivot table is used. In this step, you will need to ensure the Pivot table source rows match the data generated in Step 5.





(tap image to enlarge)

Step 8 - The final step is to refresh the Pivot table to reflect the data generated. At this stage, the graph should reflect the data.