Creating Reports dynamically from info in another Sheet

Jeff Kline · 03/14/23

Is there a way to generate a report where one of the filters is adjusted dynamically based on either user input or based on the contents of a sheet?

I am rolling out a smallish scale Smartsheet project. I have created a series of reports. The reports are the same except for a single filter value that changes (the name of one of my staff). Currently I create these reports manually by copying one of the other reports, changing the name, then changing the filter. I am wanting to future proof the project. I am open to a number of ways of doing this. I just want the ways to be easy for a novice. I am new to smartsheet and have not explored templates and if that is an option I might use a template for the report.

One option would be to create an automation that when triggers prompts for a name of someone. It then generates a report using that name as one of the filters (associated with a specific column in the report). This is less than perfect, as it requires the user to create 5-6 "dynamic" reports per week or 2, and each time type in those five or 6 names.
A second way would be to create a sheet that lists the staff names. Then run an automation that creates a report for each name in the sheet (or only creates a new report when a name is added to the sheet). Essentially an automation that changes a single filter in a report template, the staff person's name. Since we only change the name of a staff member every few months this would be optimal. Old staff member reports could be deleted when they leave. Also new names could be added to the sheet with a simple form.

I am open to other suggestions. I just anticipate novice users in the future and while I can create a manual that creates step-by-step actions to modify a report (and in fact I will have a manual or maybe a dashboard, that has this info).

Thanks

Paul Newcome · 03/14/23

If you have their details in a Contact type column, you can filter the report by "Current User".

Jeff Kline · 03/14/23

https://community.smartsheet.com/discussion/comment/367124#Comment_367124

Paul that is how I set up the reports manually. They are filtered by the staff person's name. I would prefer to automatically create the report by:

Adding a name to a sheet, and dynamically creating a report that filters on that persons name
or create a sheet with staff names, and then periodically create a report for each staff member, based on all the same filters.

Since Templates seem to only be available for sheets, then this idea might not work. But I regular report can be used as a template in my scenario, I just need to adjust the name of who the report is filtered for.

Thanks!

Paul Newcome · 03/14/23

There should be an option when creating the filter that is specifically "Current User". This will see who is logged in and automatically filter the report based on rows that have that person listed in the contact column specified.

Jeff Kline · 03/16/23

Paul, that is a great idea. Unfortunately some of my recipients are not really users of Smartsheet so are not logged in. I want the report user to be able to open a generic version of the report, be prompted for a filter (a name in this case), type it in, then create a report filtered by that person's name. I do this in access as a way to future proof recipients of a report.

Thanks

Jeff

Lara Kelly · 04/10/23

@Jeff Kline Did you find resolution? I am in need of something similar but, would like the report to pull data based on sheet name. In the report point it to a different grid and pull the information vs creating multiple reports for each grid sheet.

Jeff Kline · 04/10/23

https://community.smartsheet.com/discussion/comment/371028#Comment_371028

Nope. AFAIK there are no solutions. Its not like Access where you can create forms that allow one to enter information or a query which will prompt someone to enter in certain information.

Creating Reports dynamically from info in another Sheet

Answers

Categories