Create a "This & Next Week Projects Report" in Smartsheet using RM advanced reporting

Archiduc
Archiduc ✭✭✭✭
edited 08/15/24 in Best Practice

Hello,

For a while now my teams have been missing a way to see their workload in the coming weeks. Taking screenshots of rm.smartsheet.com or giving them a licence to it are options, but it is either too manual or too costly for my taste.

A more realistic alternative was to export reports from RM, reimport the files in Smartsheet and manipulate them do create the wanted report. Now we're able to do all of this automatically using RM advanced reports and Data shuttle.

Here is a snapshot of what the end result looks like:

Screenshot 2024-07-29 at 12.55.39.png

The first step is to create the advanced reports in RM. My reports are time and fees in hours and I'm only interested in scheduled hours. Pick the filters for the teams you're interested in, and create an advanced report. You need to create onereport with the date filter for this week and another one for the next 30 days.

Once these reports are created you get two folders created and updated automatically everyday in Smartsheet.

Let's focus first on the Next 30 days sheet (PS: it's a good time to warn you to name everything well, as to not get lost later).

  1. In this sheet, create a new column formula "Project & Team Member" which concatenates the Project name and the assigned colleague name: =Project@row + " " + [Team Member]@row This will be your unique column for the data shuttle, so hopefully you do not have projects (sheets) with the same name
  2. Then create a new column called "Week Number" to get the week number with this column formula =VALUE(RIGHT(Week@row, 2))
  3. Create a new column called "This Week Number" with this column formula: =MIN([Week Number]:[Week Number])
  4. Create another column called "Next Week Number" with this column formula: =MIN([Week Number]:[Week Number]) + 1
  5. Finally create a column called "Next Week" with this column formula: =[Scheduled (hours)]@row / 35
  6. This formula transforms the scheduled hours into % of weekly allocation. You may change the 35 hours based on the weekly hours of your colleagues. You can even reference another table with INDEX/MATCH to change the number of hours based on your colleagues country for example.

Now go into the other advanced report and find the sheet with the data for Next week.

  1. Create a similar column "Project & Team Member" concatenating project and team member: =Project@row + " " + [Team Member]@row
  2. Create another one called "This Week" with this column formula: =[Scheduled (hours)]@row / 35

Next step is to create 2 data shuttle workflows. I advise to copy this week sheet first and empty it (and remove the column formulas), rename it as it will be your "final" sheet with the name you want.

  1. In this final sheet, create a new column called "Week Number" to get the week number with this column formula =VALUE(RIGHT(Week@row, 2))
  2. Create a new column called "This Week Number" with this column formula: =MIN([Week Number]:[Week Number])
  3. Create another column called "Next Week Number" with this column formula: =MIN([Week Number]:[Week Number]) + 1
  4. Take your Next 30 days sheet and create a data shuttle workflow from it. You want to create a new excel file from it, which you will drop into the copy (the final sheet) you just created. Make sure to remove the columns you don't need in the process and to set a schedule that works for you.
  5. When done, run the process a first time.
  6. Then go on your final sheet and create a new shuttle workflow. This one will populate the final sheet with the attachment created by the previous process. As mentioned earlier, the unique field will be your concatenation of Project+Team Member. I recommend making your Primary column the project name "Project" as it will make a more pleasing report to look at in the end. You will need to do this change in your final sheet before you run the last shuttle workflow. (You can do it after but will need to run it again). Make sure you do import all the columns we just created, except for "Next Week".
  7. Once the process is created, run it.
  8. Go in your final sheet, delete all the columns in the sheet which you did not need, if you unselected them when created the first shuttle workflow, they should be empty in the sheet, thus easy to find.
  9. Add 2 columns: This week and Next week.
  10. In both columns, use an Index/Collect column formulas to bring the data from the 2 sheets: Next 30 days and This Week. These are my formulas, but you will have to build and name the references yourself.
    1. This week =IFERROR(INDEX(COLLECT({LTC & IC Workload this week - This Week}, {LTC & IC Workload this week - Unique field}, [Project & Team Member]@row), 1), "")
    2. Next Week =IFERROR(INDEX(COLLECT({LTC & IC Workload next 30 days - Next Week %}, {LTC & IC Workload next 30 days - Week Number}, [Next Week Number]@row, {LTC & IC Workload next 30 days - Unique value}, [Project & Team Member]@row), 1), 0)
    3. Notice that in this second formula, we're adding a criteria that the week number is the one of next week.
  11. Finally, because the matching column is Project Team member, it can happen that some rows from other weeks sneak in. To filter them out, create a new column "Week Check" with a column formula: =IF(OR([Week Number]@row = [This Week Number]@row, [Week Number]@row = [Next Week Number]@row), "true", "false")

We're almost done! Now you can add whatever column you would like your colleagues to fill. In my case I had a dropdown RAG column with conditional formatting, a comment column, and a column to tell me if the % of estimated workload by my colleague differs.

When you're done, create a report that references this sheet,

  • Add the columns Primary (which should be the project name), This week, Next week, and whichever other columns you created.
  • Add filters that the % of This OR Next week should not be blank AND that Week Check should not be false.
  • Group by Email (and whichever else you find pertinent, Role and Manager in my case).
  • Add the Sum of This Week and Next Week records in summary
  • Finally sort however you wish. Make sure your colleagues have editing rights over the sheet that the report refers too so they can edit these added columns.

You may find that there are some other ways of building this. Where you build some of the columns or what sheet you copy to make your final sheet can be different. Perhaps it is better to use another unique field in the shuttle and filter the rows. If you find easier ways to build this, please contribute.

If I'm being honest, it's a bit too much to recreate the Resource Management legacy view, which was easy to read, accessible to everyone, and exportable in excel directly. I wish we had this again. But at least now I've got this alternative, directly in Smartsheet, and with editable fields.

Tags:
· Share on FacebookShare on Twitter

Comments

Categories