Grouping Data Inside of a Table Example
This example begins with a table similar to the one created in the Report Workflow Tutorial. This example will demonstrate how to group the existing downtime report by equipment, collect downtime totals, and introduce some formatting techniques.
In the Data panel, create a Data Source that has a Timestamp, Equipment, Downtime, and Cause. Shown below is the text needed for a Static CSV datasource used for this example.
Code Block language py title Data Source for Equipment Downtime Report
T_stamp, Equipment, Downtime, Cause "Jan 20, 2017 17:55", "Labeler", 50, "Out of labels" "Feb 20, 2017 18:40", "Filler", 120, "Overflow" "Feb 28, 2017 12:45", "Palletizer", 21, "Misalignment" "Feb 12, 2017 20:13", "Labeler", 98, "Stuck labels" "Jan 21, 2017 18:15", "Conveyor Line", 27, "Backup" "Feb 25, 2017 16:22", "Filler", 2, "Scheduled Maintenance" "Feb 13, 2017 19:19", "Conveyor Line", 21, "Scheduled Maintenance" "Jan 20, 2017 15:30", "Palletizer", 241, "Misalignment"
In the Design panel, drag a Table component to your report.
- With the Table selected, drag the Data data source to the Data Key under the Configure Table tab of the Property Inspector.
- Drag the each of the data keys (i.e., Equipment, Cause, Downtime, and T-Stamp) to any of the columns in the table row.
- Click on the Preview panel to see what the report looks like. You'll notice that all your data is there, but it's a little hard to read because it isn't very well organized.
- Next, let's have the table sort each row by equipment by grouping all the equipment together. Go to the Property Editor, and in the Configure Table, click the
- Go to the Preview panel, you will see that the report is now sorting by equipment name.
- Let's remove the Equipment Name from each row and add a Header. Go to the Design panel and cut '@Equipment@' from the Data Details row and paste it in the Equipment Details row. You can even bold it in the Edit Text tab to make it standout.
- While the table component is selected, go to the Configure Table tab in the Property Inspector and select the Equipment item in the grouping list and check both the Header and Summary boxes. Then select the Data group and select the Header and Summary checkboxes for it.
- Next, make the Equipment header an unstructured row and add text as a title for your report. Unstructuring the row allows you to easily center the title of your report.
- Now add header text for each of the Data Details columns (Cause, Downtime, and Date) by typing into the Data Header row.
- Set the Show Calculations checkbox at the top of the Key Browser. Now you can drill down into the Downtime column and drag the '@total.downtime@' to both the Data Summary and Equipment Summary rows. You can add any text outside of the @ symbols, so update your cell to say '@total.downtime@ minutes'.
- Lastly, do the same in the Equipment Summary row. This time we will make it slightly different by adding the word total to the beginning of the cell: 'Total: @total.downtime@ minutes'.
In any Summary row, '@total.Downtime@' is a sum of all downtime at that level of grouping. IE: in the Data Summary row it is the total downtime grouped by equipment. In the Equipment Summary row, '@total.Downtime@' is the sum of all downtime for all equipment groupings.
- Click over to the Preview panel to check out your report. If you want to make any changes, go back to the Design panel to update your report. Notice that the 'total' data key respects both groupings.
Separating Groupings using Page Breaks
In the Configure Table tab of the Property Inspector, there is Page Break option that can be set to create breaks between each Grouping. Each new instance of that level of grouping creates a new page in the report. In the example above, we could add a page break in-between each grouping of Type, which would further delineate each grouping of data. This is especially useful if you are adding charts or other images at the beginning of each group.