Formatting Duration Fields in Excel Pivot Table Reports
The best way to create reports for Duration fields is using the Excel Reports Module
Many Excel Reports use Pivot tables or Pivot Charts. These need to be formatted correctly. Set up your excel report to include the report Type, Dates and Data you require then Generate the Report Template (as shown below)
For more assistance on how to create Excel Reports see the Lessons on Excel Reports. These types of reports can be quite complex to create, so if you are not confident using Excel ask your distributor for assistance.
In the generated Template, on the second Worksheet (that has the data export) right click on your Duration fields and see how the fields are formatted. This is the EXACT cell formatting you need to apply to the Duration fields you use in a pivot table or pivot chart.
1: Right click on the Duration field or Duration calculation column. For this example, we are selecting the 10m sprint duration.
2: Click on Format cells
3: See the image in the step below
Now look at the format of the cells. Duration Formats are not Numbers, but in the original data that is exported from the Excel file, format the fields as Numbers. You
The [ ] around the highest duration ensures that if you are summing durations in a pivot table, then the answer will calculate and show in the ss.SSS format, even if the total goes about 60 seconds. If you were summing 400m split times for an 800m, without the [ ] around the ss then a time of 46.89sec and a 48.11sec would sum to 1:35.000(mm.ss.SSS), which would display as 35.00 (in a ss.SSS format and you would not see the minutes). With the [ ] the result would show as 93.000 seconds.
On the First Worksheet, add in the pivot table and remember to choose the "All fields" from the "Use in Formula" (On the Formulas Tab)
After you select to Insert a Pivot Table, follow the steps below:
1: Click on the cell selection for the Table/Range
2: Click on the Formulas Tab
3: Click on "Use in Formula"
4: Select the All_ name to be able to use all of the fields from your form
5: Click "OK"
Now add in the fields into the Pivot Table. You will notice that the duration field cells are NOT formatted correctly when they are added
You need to select the Pivot table columns that are duration fields and then right mouse click and select Format Cells. Then apply the correct custom format (ensure it is the same as the format of the exported data in the data worksheet) e.g [ss].000 .
You MUST select a custom field with the [ ] around the duration to ensures that if you are summing durations in a pivot table, then the answer will calculate and show all of the duration field correctly. If you use a ss.SSS format (without the [ ]) and the sum or average goes above 60 seconds, then ONLY the seconds will show and the minutes field will be hidden. E.g 1:44.555 in a ss.SSS format will ONLY show the seconds field of 44.555. If you add in the [ ] around the ss then it will display as 104.555. Similarly, if you were summing 400m split times for an 800m, without the [ ] around the ss then a time of 46.89sec and a 48.11sec would sum to 1:35.000(mm.ss.SSS), which would display as 35.00 (in a ss.SSS format and you would not see the minutes). With the [ ] the result would show as 93.000 seconds.
The cells will be formatted correctly. Update any other duration cells that you have in your pivot (this example shows the [mm]:ss.SSS being formatted)
Now, right mouse click on the Pivot Table and select Pivot Table options
Ensure you select Refresh data when opening the file (or the data will not update) and click OK
You MUST click on Refresh data when opening the file.
If you have formatted your cell width click on the Layout & Format and deselect "Autofit column widths on update" so that they do not collapse on opening.
Delete the Data on the Worksheet tab (as shown here) and Save the Excel worksheet. Then upload the template back into the Excel Reports Page on the system (see the step below).
Delete the data from the worksheet, but not the column headings. Save the file.
Choose the file and upload it.
1: Now upload the template that has the pivot table in it by clicking Choose File
2: Locate the file from your downloads, or from where you saved the file
3: Click Upload to upload the template to the site
4: Test the report formatting (shown in the step in the image below)
Test that the template is formatted correctly by clicking the Test button. This will generate a test Excel Report in your downloads from the template you just uploaded. Open the download and check the formatting is correct.
Ensure the formatting is being retained and that the file layout is correct. If it is not, update the original template and reupload it and test it again.
After you have tested the report, you must click "Save". Remember, you cannot edit it again once you Save it and click out of this page so ensure it it formatted correctly (the pivot table, page layout etc).
The Report name and structure will now be available to run from the Excel Reports Page
0 Comments
Add your comment