Can a report contain a query string or prompt?

RDarmstadt

I'm looking for a way for a user running a report to input a query response that is then used as the filter in a report. For example, a report lists all projects assigned to a variety of sponsors. Can I somehow create a prompt so that each time the report is run that the end user gets a prompt for which sponsor they'd like to see the list of projects for? I know that it is possible to create a report for every sponsor that hardwires the response in the report filter, but would prefer something dynamic so I don't have to have many multiple versions of the same report just with a different filter for sponsor.

Genevieve P.

Hi @RDarmstadt

The Filter function in a Report works differently than filters in a sheet - as you've noted, Reports have this hardwired into their build. The filter is what defines that specific Report item and the rows that it will bring through from one or multiple sheets.

This means that there isn't a way to use a query string to change what the Report filters, since that would change how the Report was created (something only Admins or Owners can configure).

In this instance, yes, you would need to build out multiple Reports for each type of filter, then use these unique URLS per-filter. Keep in mind that users shared to a Report will also need to be shared to the underlying sheet to view rows.

If they're shared to the sheet, then I would suggest using the Sheet URL to automatically apply a filter, since you can do this via query string.

If you only want to show specific columns from the source sheet, you could set up a secondary sheet that uses cell-links or formulas to copy data across, then share out this sheet with filter query strings applied instead.

Another alternative to create dynamic filters would be to use the premium add-on, Dynamic View.

Cheers,

Genevieve

Andrée Starå

Hi @RDarmstadt

I hope you're well and safe!

Unfortunately, it's not possible now, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment.

Here's a possible workaround or workarounds

• I recently developed a solution for a client where I used a form for selection in a Dashboard, submit, and refresh to see the updated information in the report included in the Dashboard.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Genevieve P.

Hi @RDarmstadt

The Filter function in a Report works differently than filters in a sheet - as you've noted, Reports have this hardwired into their build. The filter is what defines that specific Report item and the rows that it will bring through from one or multiple sheets.

This means that there isn't a way to use a query string to change what the Report filters, since that would change how the Report was created (something only Admins or Owners can configure).

In this instance, yes, you would need to build out multiple Reports for each type of filter, then use these unique URLS per-filter. Keep in mind that users shared to a Report will also need to be shared to the underlying sheet to view rows.

If they're shared to the sheet, then I would suggest using the Sheet URL to automatically apply a filter, since you can do this via query string.

If you only want to show specific columns from the source sheet, you could set up a secondary sheet that uses cell-links or formulas to copy data across, then share out this sheet with filter query strings applied instead.

Another alternative to create dynamic filters would be to use the premium add-on, Dynamic View.

Cheers,

Genevieve

RDarmstadt

Thank you both for taking the time to respond!

Andrée Starå

@RDarmstadt

Happy to help!

Terri1019

Hello. I see this thread was from a few years ago. Have there been any updates to Smartsheet to make this possible? I am looking at a similar issue where I would like a prompt so the user could select the "Responsible" view by which to view the report. (Instead of creating multiple reports for each of the different "Responsible" options).

Thanks much for any updates!

Best,

Terri

Genevieve P.

Hi @Terri1019

Can you explain more about your process and what you want to do? Is there a reason why selecting filters in a View-only sheet won't work for your collaborators?

Terri1019

Hi Genevieve,

We are putting "Continuous Improvement Tracker" reports on a dashboard and do not want the users to be able to see all of the rows - just the ones that apply to their role. (Some of them will contain sensitive information). So we were hoping that a prompt would come up when they click on the report on the dashboard that would allow them to choose the correct Responsible role (Disbursement Specialist vs. Disbursement Director, etc.) They can't use the "current user" filter because the "Responsible" column will have only positions, not people. (And some positions have more than one person in the role.)

Does that make sense? Thank you!

Genevieve P.

Hey @Terri1019

Thanks for clarifying your process!

In this instance you'll need to create multiple, separate Dashboards and only share out the Dashboard with the relevant information to the user who is allowed to see that data.

Terri1019

Thank you!