Horizontal Reporting (Transpose)

jasonhowling

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

Project 2/Finishdate 1/Finishdate 2/Finishdate 3

 

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

 

Thanks

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.