We have a large chart of accounts group by categories (revenue, Motor Vehicles etc) and our organization structure is nested with portfolios, centres and programs around 350 to 400 levels. I would like to create reports that automatic grouping by account or levels before distribution to managers.
Reports exported from Adaptive add leading blanks in the levels. Any ideas how to group them automatic ?
Andrea,
ReportWORQ doesn’t have anything today to provide grouping functionality but I wouldn’t mind opening a discussion with the community about a new RW Formula that accomplish this. With the RW formulas we try to stay datasource agnostic by allowing the user to provide the condition (calculation) to determine when and how the formula should operate. For example, instead of ReportWORQ arbitrarily trying to automatically provide zero suppression, we provide a RWSUPPRESS formula where you get to decide the condition and whether the row/col is hidden or deleted.
With this in mind I could see grouping being necessary at multiple depths with each grouping either collapsed or expanded by default, for rows or columns.
Visually we may want to achieve any one of these 3 examples:
In our design we need to consider the different ways ReportWORQ might load the data that needs grouping. Some report types may already have all of the possible rows and cols defined, and suppression will remove some entities. In this scenario, the ReportWORQ formula isn’t really needed because you can just add the grouping in Excel ahead of time. Therefore, this formula is more appropriate for things like Planning Analytics Dynamic Reports, RWSQL formulas and AIModeledExtract formulas, where the row/column set is imported into the grid and pre-work can’t be done in Excel.
Everything below applies to columns as well, but let’s assume we’re focused on rows for now.
To accomplish this ReportWORQ will need to know when to start a grouping, how many rows/cols to include in the grouping, whether the grouping is on rows or cols and whether to expand or collapse by default.
My initial thought is a formula like this:
RWGROUP(identifier, axis, visible)
Identifier is any string or integer used to begin a grouping. The rows beneath this row would be grouped together until a contiguous RWGROUP formula with the same identifier or a blank row is
encountered (a row without a RWGROUP formula). If there’s only 1 row with that identifier then the there is no grouping.
Axis is either “rows” or “columns” to tell us the direction and Visible is “show” or “hide” for the default expansion.
Now let’s update our example to use this formula. I’ve hard coded level as an integer, but this could have been another calculation which identifies the depth in the hierarchy, such as substring of a label code.
Following the rules for the identifier above:
Rows 3 through 9 would be grouped because row 2 (Earth) started a grouping with identifier “1” and then a “1” was not encountered again until reaching a blank in row 10.
Rows 4 through 6 would be grouped because row 3 (USA) started a grouping with identifier “2”. Since another “2” was encountered in row 7 (Australia), the grouping stopped one row prior at row 6.
Rows 8 through 9 would be grouped because row 7 (Australia) started a grouping with identifier “2”. and like above another “2” was not encountered before running into the blank in row 10, which terminated this grouping.
All of the rows with identifier “3” would have created a single row grouping, which is non-sensical, so no grouping is applied to those rows.
This design appears to work for multiple depths and would allow for an arbitrary formula which would be auto-generated in a datatable or dynamic report to determine grouping based on a condition. However this would not have worked if we didn’t want to nest groups. So I would add an optional forth argument to the formula to change the behavior so that a group is terminated as soon as the identifier changes. This would allow us to support grouping multiple depths as a hierarchy or just a list of groups.
I’m curious if anyone has ideas to reduce the complexity or other things that should be considered?
Thanks,
-Andy
2 Likes
Thanks Andy. I think this idea will work for us. Been 1,2 or 3 the number of spaces in from of each label. That will give you the level of the nesting.
Thanks Andrea
1 Like