Horizontal Reporting (Transpose)

Hi!

I'm currently having some issues generating a report from smartsheets that enables be to do some additional reporting away from the product to enable external sharing (due to data, and legal reasons).

I'm looking at building a milestone report however the current report that is being generated in smartsheets is more of a data dump than a report. Currently my report looks like this.

Project 1/Milestone 1/finishdate/

Project 1/Milestone 2/finishdate/

Project 1/Milestone 3/finishdate/

Project 2/Milestone 1/finishdate/

Project 2/Milestone 2/finishdate/

Project 2/Milestone 3/finishdate/

Ideally i need this to be structured slightly different which will allow me to complete multiple reporting as mentioned above.

Project Name/Milestone 1/Milestone 2/Milestone 3

Project 1/Finishdate 1/Finishdate 2/Finishdate 3

Project 2/Finishdate 1/Finishdate 2/Finishdate 3

Is there any easy way to achieve this without editing multiple project plans?

Thanks

Find more posts tagged with

Reports

Project Management

Comments

Jim Hook

Jason, what you're trying to do is quite challenging in Smartsheet. It sounds like your source data for the Smartsheet report accesses sheets that use a separate row for each project/milestone combination. Assuming that the raw data format on the source sheets cannot be changed to something like Project/Milestone 1/ Milestone 2/ ... the standard Smartsheet report won't do it for you since reports can't do any sort of computations.

I do something similar to compress weekly timekeeping data. It comes to me by project/person/role/hours and our project tracking and costing process charges the same rate for different people working on the same role which happens frequently. I developed an algorithm that identifies the first instance of project/role in a long list of input records and then picks-up the hours charged by each person throughout the rest of the input records, essentially like milestone 1, 2, 3, etc. in a single row. If it's not the first instance of project/role in the input list everything is left blank in the rest of that row. Weekly I copy the compressed data, paste it into an Excel database and sort out the blank rows leaving only the compressed data behind. It's a complex algorithm but it works great eliminating about 1/3rd of the original rows of data charges each week.

If possible, I recommend that you see if it's possible to list each project milestone in a single row rather than try to cope with the complexity of compressing multiple project rows with different milestones into the same row for your report. If you did that the standard Smartsheet report would work just fine.

Sign in to join the conversation!

Quick Links

Trending discussions

Viewers and Permissions after new pricing model
I have provisional users who, under the legacy model, were unlicensed users with editing permissions for workspaces. I’ve now changed their role to "viewers" and assigned editing permissions to specific workspaces, aiming to maintain the same editing experience as before. However, they cannot edit fields within tasks or…
Automation for Daily Reminders After X Days Past Due
I'm creating an offboarding checklist sheet which sends out automation notifications to relevant parties in each department. Notifications are sent out when the termination goes live, one week after the termination date (if that department's tasks still aren't checked off as done), and two weeks after the termination date…
Sharing sheets
Does sharing a sheet with edit or view rights immediately require the shared user to have a paid license, effectively making them a group member? This seems to be occurring. In contrast, can I confirm that sharing a link using the 'publish' feature will not require a license since the user is only accessing that specific…