Automating which columns are displayed in a report

Issa M

Is there any way to automate which columns are displayed in a report based on data entered into a worksheet? I suspect the answer is no but I'd like to confirm.

I'm envisioning a scenario where I have a chart on a dashboard that displays financials by month, year, and quarter. It would be great if I could create an update request with a checkbox for each option (i.e., month, year, quarter) and if, for example, month were checked, the report would only show monthly expense columns. If year were checked, only yearly expense columns would appear in the report and so on.

I want this report tied to a chart on a dashboard that only shows the expense columns included in the report. What I'm envisioning is something similar to how Report Builder allows you to filter rows based on some specified criteria except for columns.

Thanks for any input!

Andrée Starå

Hi Issa,

Unfortunately, it's not possible at the moment to update/hide columns in a report, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

As a possible workaround, it could maybe work updating and showing the information as you describe, but the other columns would still show, but be empty.

What do you think?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

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

L_123

This is achievable but my solution isn't exactly optimal.

What I have done historically is use a separate sheet with a webform with the filtering options on the webform, and with the webform posting to the top of the sheet.

Then you can use a helper column on the main sheet (i would just use a checkbox column) that uses cross-sheet formula to check the most recent submission from the webform and check the box if that row meets the criteria. Use the helper column to filter your report and hide the column behind the scenes.

Next you can create a dashboard and add the report to it. Pretty it up and put a url on it that goes to the form. Set the form to go to the dashboard after submission. (I would set it to go to public link so I wouldn't have to give direct access to the dashboard, and share the form url to the users, but that is personal preference).

Why isn't this optimal?

1. Server lag. Sometimes you have to refresh the dashboard in order for the data to update. ( I would put a note on the dashboard to refresh if the data doesn't match the filtering criteria to reduce support time)
2. Extra sheet required
3. Sharing can be somewhat complicated if you don't have a resource developed for sharing links depending on your users. (This is something that can also be done on smartsheet, but may be something you need to develop)

Issa M

Thank you, @Andrée Starå . I will submit an enhancement request.

I'm going to try out what @L@123 is suggesting and see how that works as well.

Thanks to both of you! I may return with more questions.

Andrée Starå

@Issa M

You're more than welcome!

Let me know how it goes!

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

Brett Robinson

@Issa M @Andrée Starå

I had a similar issue but was able to come up with a solution that maybe can be leveraged here. It requires you to flip the columns and rows though, which can be a little weird. I'm not sure how nicely this would work on financial, but maybe there is a way to link the current sheet to a new one in order to do the row/column flip for the graph but still have the data entry sheet operate like a traditional income statement. It would allow you to flip the graph between Months and quarters (see below).

I utilized a sheet that had a trigger in a dropdown to select from, which ran a formula in a checkbox column that told a report which rows to pull. The report ran the dashboard widget.

Green box is the trigger to switch the chart from month to quarter.

Formulas in the checkbox column is =IF([Quarter or Month]$1 = "Month", 1) on the month rows and =IF([Quarter or Month]$1 = "Quarter", 1) on quarter rows

Quarter rows also have this formula in the revenue column =IF(ISBLANK(Revenue2), "", SUM(Revenue2:Revenue4))

In theory, you could automate the values that populate in the primary column so that the values would change based on today's date (I've done this in the past to create rolling chart graphic, but its not easy). You could then use the value in the primary column to pull data from a more traditional financial sheet using vlookup or index/match.

Another thought to get the rolling 12 month/quarter effect may be to add a date column in and have the report filter by the date? I've never tired that (probably would have made life a lot easier)

Checkbox runs the report criteria (now labeled filter in the screenshot above), which kicks the data up to the graphs below

Andrée Starå

@Brett_Robinson

Thanks for sharing!

I do something similar for other needs but hadn't thought of it for this use case.