Creating a summary report with limited drill-down capability

KevinBradford

I have a prototype dashboard with a graph that drills down to a Summary Report. It works now, allowing drill-down to see summaries by 3 groups in the source sheet (Division, Organization and Backup OK), which is great. However, in the current iteration it allows the user to drill down to the detail level of the source sheet, displaying the EMail field (which is the primary column on the source sheet).

A decision has been made that this level of detail is no longer needed and PII like Email should not be displayed, so I would like to have the exact same report grouped by Division/Organization/Backup OK fields (and expandable/able to drill down by multiple Div/Orgs), but but prevent the Primary Email Column of the source from being displayed/available.

Email is not selected or available in the columns selection (I guess because it is primary in the Source sheet). There are 3 similar reports based on different columns in the source sheet, so I can't really make the Backup OK field primary in the source sheet.

What would be the best way to accomplish this? I have tried various things with little success...

Thanks!

Genevieve P.

Hi @KevinBradford

Since you're Grouping the Report, the Primary Column will always need to be displayed in order to see the Grouping titles.

There currently isn't a way to have the titles displayed over another column, but please feel free to suggest this to the Product team by creating an Idea in the Smartsheet Product Feedback and Ideas topic here in the Community.

If you can, I would suggest changing the data that's being displayed in the Primary Column. Then you could publish the Report to give view-access to your collaborators without sharing the underlying sheets and PII.

Cheers,

Genevieve

KevinBradford

Thanks, Genevieve. That might be a possibility, but I have several other similar reports that might be affected. I wonder about another possibility - is there a way I could create another sheet and use formulas and/or automation to maintain a synchronized list of all the unique combinations or columns Division, Organization, and Backup OK in the source Sheet? Then I could use a formula to summarize for each and use that sheet for all my reporting, without any reference to the primary column in the source sheet.... What do you think?

Thanks again!

Genevieve P.

Hi @KevinBradford

Yes, you could use formulas to create a Count per value selected! For example:

=COUNTIFS({Backup OK column reference}, "No Response")

See:

• Create cross sheet references to work with data in another sheet
• COUNTIFS Function
• SUMIFS Function

However keep in mind that these will only return the numbers associated with each metric, without providing the drill-down data of the exact rows associated with that value (the expanded rows below the Summary line in the Report).

Cheers,

Genevieve