Automating Monthly Generation of Multiple Reports

KMH Spinner

Hello Smartsheeters! I have 14 reports that pull data from a master sheet that we need to report to an Important Entity each month. I'm hoping I can get out of the routine I have now, which is to save the batch of the prior month's reports as new and manually update the date parameters and the title for all 14 reports. For example, say I have a report called May Elephants that is pulling all the Elephants that were fed peanuts in May. I need to do the same for June, July, August… . Instead of updating these reports manually each month, is there a way to automate the process so May Elephants turns itself into June Elephants, June to July, etc.? There's a major caveat to this: We have to keep each month's reports in case there's a question about how the data was generated. (Slim chance, but possible.) Thank you!

Brian_Richardson

You cannot automate changes to the title of a report without using an API tool. You can however help yourself out.

Couple of options:

Option 1: Dynamic reports

I'm assuming you have some kind of Date field where people capture the date they fed peanuts. I'm going to call it "Fed Date" but replace that with the real column name in the formulas below.

To get a report that changes months, you can Add a column to your sheet called "Current Month?" as a checkbox column. Use the formula =IF(Month([Fed Date]@row) = Month(Today()), true). After entering the formula, right click and change it to a Column Formula.

Now set your reports to filter for "Current Month?" is checked. Your reports will show you the data just for the month that you're currently in (ie all July data if you ran it right now). When you export them (once a month probably at the end of the month) then they will have that month's information in them. Then all you need to do is rename the exports, and repeat at the end of the next month.

Note you could tweak the timing, if you plan to export the reports to show the prior month, maybe you're exporting on day 2 of August to show July data, then simply adjust the formula to flag data for the month prior to the current month =IF(Month([Fed Date]@row) = Month(Today())-1, true)

Option 2: Upfront fixed reports

Alternatively, you could do some painful upfront work to create 14 reports X 12 months up front, and just let them run. If you want them to work next year, use a column called "Month" with the formula =Month([Fed Date]@row) and then filter each report for the month number (ie 7 for the July report). Name each report for the months that you want, then your monthly job is simply exporting the reports that apply.

One tip for quick exports, if you open Browse and do not open the reports, you can right click each report and choose Export to Excel or Export to Google Sheets etc. That will run the export more quickly.

KMH Spinner

Thank you very much Brian! I'll experiment with these options.