Best Way of Being Able to Display Data Using a Current Sheet, Archive Sheet and Summary Reports

SteCoxy
SteCoxy ✭✭✭✭✭✭
in Smartsheet Basics

Hello,

I'm assisting some colleagues with a solution that helps track music session bookings. There are a number of summary reports set up, which the metrics are then displayed on a dashboard.

It's been set up for exactly a year, but they only want the current financial year's data to be displayed. They came to me asking to help them set up brand new sheets/reports/dashboard for this financial year.

Instead, I've recommended an archive function i.e. using the move row automation to an identical sheet, which would then mean keeping the same dashboard and reports would only display the most up to date data. What they'd also like though is to be able to compare and display last year's data in the dashboard.

I'm wondering if this is possible with the method that I've recommended (using perhaps a filter on the report to display the relevant data from previous years) or if having a new sheet each year is the best way to do this?

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @SteCoxy

    I personally would have separate sheets per-year as well.

    For the Dashboard it will depend on what you're already reporting... for example, you could either Copy/Paste a current widget and then change the source (so you have two pieces of data in two different widgets), or you could create a Metric Sheet with two sets of formulas in two columns (one for each year) so the data appears in the same chart.

    If you're wondering how to do this, would you be able to post a screen capture of the Dashboard and of the source Sheet? (But block out sensitive data).

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @SteCoxy

    I personally would have separate sheets per-year as well.

    For the Dashboard it will depend on what you're already reporting... for example, you could either Copy/Paste a current widget and then change the source (so you have two pieces of data in two different widgets), or you could create a Metric Sheet with two sets of formulas in two columns (one for each year) so the data appears in the same chart.

    If you're wondering how to do this, would you be able to post a screen capture of the Dashboard and of the source Sheet? (But block out sensitive data).

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • SkiPatrolScott
    SkiPatrolScott ✭✭✭✭

    @Genevieve P. Am I missing something with Sheets and Reports? I have a sheet for routes that will be used by patrollers. Some days there will be 5 routes some days as many as 39 routes. I would like to filter based on the date and then have information from that sheet displayed in a report and possibly displayed in a Dashboard. However, only the initial report data is shown, and unless I do a filter on the report, it is not updated.

    On the Dashboard, if I figure that out, the area does not adjust, so it looks like I would have to adjust the size of the box each time, or expand to what I think the max would be..

    So basically, I want my Report to display what is currently on my active Sheet.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @SkiPatrolScott

    It does sound like a Report will be best for you! You could set the filter in the Report to be "within the last X days" so that it always brings in the relevant rows, based on today's date.

    Screenshot 2022-11-14 at 11.20.35.png

    In regards to Widgets in a Dashboard, you are correct: they will not automatically adjust based on the data inside. If you're using a Report Widget and there are more rows than what can be displayed, you can scroll down within the widget to see the rest of the content.

    If this hasn't helped, it would be useful to see screen captures of your set-up, but please block out sensitive data.

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • SkiPatrolScott
    SkiPatrolScott ✭✭✭✭

    Thanks for your response @Genevieve P. we are finally getting some inputs, hoping I can create a widget that tabulates the number and type of incidents as they happen. So that the report would show 6 - Medical 4 - Courtesy 2 - Lift Stoppage... and as the numbers increase so would the totals... Will probably be back after I try to find a solution...

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @SkiPatrolScott

    If you're on a Business or Enterprise plan, you can use a Report (like above) but then Group and Summarize that report based on your Type of Incident. This then creates a summary row that can be mapped to a chart!

    Here's a free webinar with more information: Redesigned Reports with Grouping and Summary Functions

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video