Public

The focus of this chapter will be around how use grouping in visualisations

---

In this chapter we will explain the thought process and steps you can take to use subtotals to 'Group' your reports visualisation.

Visualisations in Enhanced reporting are very powerful. You can colour code, rename columns, and apply ‘Grouping’ (collapsing subtotals) to help structure the data to gain insight quickly.

In this post, we’re going to construct a report which shows the number of currently sourced (or not) jobs per Brand.

3.1 - Choose the Job explore

Start by confirming a known example of an open job you want to report on with your business and locate the record in the system, making a note of the value that will uniquely identify this record such as the job number on the job card. For this example make note of the job number as well as the Brand or Business unit.

For this example choose the Jobs explore as this is the best explore for reporting on job related information.

3.2 - Add job card fields to your report

In this step, we're going to add the fields that we want to include in our report, this will include Dimensions, Measures (because subtotally/grouping requires measures within the report) and define our filters.

3.2.1 Add Dimensions

Start by adding the following dimensions to the report. Please note that your dimension names may vary with your configuration or requirements, please adjust as required.

1. Job > Organisational Hierarchy: Brand
2. Job: Is Currently Sourced (Yes/No)
3. Job: Job No.
4. Job > Source Opening Date: Opening Date

3.2.2 Add measures

Next we will add in some measures to the report, measures are important as they will enable us to use the 'Subtotals' option in the data tab which will be key later on.

1. Job: Total Jobs
2. Custom measure 'Count of job: Job No'
   1. Find Job no in the the field lookup list
   2. Click the cog
   3. Choose 'List
      This creates a new 'Custom Measure' which is a count of the Job No returned by the report's filters.

3.2.3 Add Filters

Next, we'll filter down the results so it's easier to work with. We can use a specific job no which you setup in step 1, but I'll be using

1. Job: Created Date: Matches (advanced) 1 years

I'm using this because in our test environment, we don't source jobs very frequently, and it's a manageable list for this purpose.

3.3 - Enable Subtotals & Run

Now this is going to be fun!!

In the data section, check the 'Subtotals' box on the top left corner.

Now click Run so that the visualisation prepopulates with the dimensions and measures we've added above.

The data tab will contain all rows of data. and the visualisation does as well, but as you can see, it has applied some grouping to the ‘Is currently sourced' column.

3.4 - Refine the Visualisation

Now, it's time to refine the visualisation, to make the insights easier to glean quickly.

The visualisation has automatically included each of the columns included in the data section.

There are some data points that don't actually add value for within the visualisation, but add 'noise' to it making things harder to see what I want.

3.4.1 - Hide from visualisation

Time to hide the Job No and Total Jobs columns from the visualisation only. They will stay in the data section, but not be included in the visualisation.

1. Data section
2. Click the cog against the relevant field [e.g. Job No]
3. Choose Hide from visualisaton

Once completed for both fields, the visualisation is getting simpler

3.4.2 - Order the columns within the visualisation

We now have only 4 columns within our visualisation.

The next thing to do is order the columns in the way that you'd like your data 'grouped'.

I'll be using the order of Brand, Is currently sourced, list of Job No, Opening date

This way, when subtotals are enabled it will 'group' by brand and is currently sourced with subtotals showing.

3.4.3 - Applying the Grouping

Now it's time to take full advantage of visualisation options to 'Collapse' the subtotalled dimensions so that they may better visualise the grouping.

1. Click the cog in the visualisation section
2. Click to the Series tab
3. Expand Is Currently Sourced (Yes / No)
4. Enable the Collapse Subtotal option
5. The visualisation will then collapse the values in the column showing the numbers of 'No' and 'Yes' for that Brand.

3.4.4 - Using the grouping when viewing the report

Now that we've collapsed the subtotals for Is currently sourced we can see at a glance the number of jobs for the AEC brand which are / are not sourced.

To see which specific jobs have been sourced to ABC brand (ed. super original name), we can click the 'Yes (8)' to expand it out.
We then see a nice little list of the Job No & the Opening Date against each job. This is a benefit of using the List of Job no instead of using the Job No dimension within the visualisation itself.

Once the report is saved, should you share it with other users, they will be able to view the visualisation and click to expand the groupings to assess the data that they are interested in.

Here we have a report which very quickly enables users to see key insights at a brand level regarding the jobs which are currently sourced, and if they wish to expand the subtotals, they can even see the specific jobs and the relevant opening dates.

This is a really cool way to employ enhanced reporting to share insight with your users quickly.

3.5 - Advanced - customising date format & including source closing date

This output is great, but I’d really like to do 2 more things;

• Change the formatting of the date &
• Include the closing date of the sourcing as well so that I can plan ahead.

When Subtotals is enabled in the visualisation, it will provide a subtotal for each dimension included in the visualisation. What I’d really like for my report, is that the Job No, Opening Date & Closing date will appear on a single line, without grouping.

Do achieve this, I'm going to use a 'Table Calculation' to return the dates as measure instead as these are shows on a single row under the grouped dimension. Table calculations are also where I can adjust the format of the date to be in Month, Day, Year instead of it's current default format.

To use a table calculation, you need to have the dimension the calculation is based on included as a column in the report.

3.5.1 - Adding in new fields

I don't yet have the closing date for the jobs included in my report, let's add that in now.

1. Edit the report
2. Pick Job > Source Closing Date: Closing Date from the field picker on the left

3.5.2 - Custom Table Calculations

Create custom Table Calculations for Opening and Closing dates

1. Opening date
   1. Clear the field search bar
   2. Expand the Custom Fields section
   3. Click New
   4. Choose Table Calculation (this is the only type which supports the required formula)
      
      
      
      
   5. Name the Table Calculation [Opening Date C]
   6. Enter the formula [diff_days(to_date("1899-12-30"),${job.opening_date})]
   7. Set the custom format [MM/dd/yyyy] - Note, this is case sensitive, mm = minutes, while MM = Month
   8. Click Save
2. Closing date
   1. New Table Calculation (this is the only type which supports the required formula)
   2. Name the Table Calculation [Opening Date C]
   3. Enter the formula [diff_days(to_date("1899-12-30"),${job.closing_date})]
   4. Set the custom format [MM/dd/yyyy] - Remember the formatting is case sensitive.
   5. Click Save

The 2 new table calculations are now in my data section of the report. While the location in the data section is fixed, we can drag the columns into wherever we would like in the visualisation.

3.5.3 - Hide the original columns

Hide the original dimensions from the visualisation [Opening date and closing date]

1. Click the cog against each field in turn
2. Choose Hide in visualisation
   
   

Unfortunately, our visualisation doesn't include a subtotal of the is currently sourced column any more. This is because the visualisation no longer has a dimension to the right of is currently sourced by which to split out the data.

3.5.4 - Not enough dimensions

Now that we've lost the subtotals for is currently sourced (as it's the final dimension in the visualisation) we need to include another dimension for it to subtotal (group) by.

Never fear, as this is pretty easy to overcome.

We can add in a new dimension, and I'm going to use Job Status

1. 1. Add Job: Job Status dimension to the data section
   2. Click Run
   3. Job Status is automatically added to the visualisation

1. Drag Job Status between List of Job no & Opening Date C in the visualisation
   
   
   
   
2. Click Run

Now we have achieved a really nice visualisation of the data which incorporates meaningful grouping with Subtotals, as well as formatting the dates according to our preference.

3.6 - Conclusion

Together, we've worked through how to use Enhanced Reporting's 'Subtotals' to apply 'Grouping' to the visualisations of our report. Our stakeholders will now be able to get the high level insight (number of total number of jobs) per brand split out by Is currently sourced (yes/no).

If you've followed along through the advanced section, you'll have also seen how to employ Table calculations to change the format of a date from the Default.

One key learning for me from this was also that you can only collapse the subtotals (group) of the dimensions that are not the rightmost dimension in the visualisation. I mean, it makes sense, but while I was busy building the report, I missed it the first time. Fortunately, it's pretty simple to overcome and even lead me to ensure that some more meaningful information was made available to my stakeholders.

---