Grouping in reports based on 4 variables

RayB

Hi Community

Trying to work out best way to produce a report based on 4 variables…which is just one more than the max in reports. I can't be the first one to have this conundrum and I would be keen to understand how others have resolved it. Columns, in the hierarchy I would like to report are:

Goals/Strategies/Sub-strategy/Measure

There is no linkage between any of them - selecting a goal does not define a strategy. Similarly for sub-strat and measure. I'm sure there must be a way by using helper columns and am hoping someone has solved beforehand 🙂

Kelly Moore

Hey @RayB

Could you add a helper column to the sheet that is a concatenation of all 4 columns then group by the new helper column? You should be able to hide this column in the report, I think.

=Goals@row+Strategies@row+[Sub-Strategy]@row+Measure@row

Would this work?
Kelly

RayB

…forgot to add in the post that the report would group projects based on the four variables

Kelly Moore

Hey @RayB

Could you add a helper column to the sheet that is a concatenation of all 4 columns then group by the new helper column? You should be able to hide this column in the report, I think.

=Goals@row+Strategies@row+[Sub-Strategy]@row+Measure@row

Would this work?
Kelly

RayB

ha! so simple and so obvious when someone points it out 😁 I suspect your solution will work perfectly ! Thanks Kelly - off to give it a go.