Automating which columns are displayed in a report

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!

Tags:

Answers

  • Andrée Starå
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 06/24/20

    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
    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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Brett Robinson
    edited 12/18/20

    @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å
    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.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.