Month over Month Reporting

[BridgetHa]

We're trying to organize a report to help show our busier times of the year. To do this, we'd like a report to group data by month over the course of 3 years. For example, we'd want March 2017, 2018 and 2019 grouped together followed by April 2017, 2018, 2019 etc., all in one report. Since we haven't been able to get a Workfront report to do this for us, we've been exporting data to Excel - see the attached example for reference. Does anyone know how to get a Workfront report to format like this? We'd love to be able to avoid exporting to Excel if we can. Bridget Harrod

[AnthonyIm]

Hi Bridget - what object type are you reporting on? Tasks, hours, assignments? Since Workfront does it charts in alphabetical order, we need to find a place to put a custom field that says something like 01 - 2017, 01 - 2018, etc. so that it will do your grouping in order. Anthony Imgrund FCB

[BridgetHa]

Hi Anthony, We're reporting on Project entry date. Let me know if that helps, or if you need more to go off of. I appreciate any help you're able to give! Thanks much. Bridget Bridget Harrod

[AnthonyIm]

Okay, so on the Project Custom form, add a calculated field with this CONCAT(MONTH(Entry Date)," - ",YEAR(Entry Date)) The only problem I can't figure out is how to get the leading zero for Jan - Sept. I tried the trick in Excel TEXT(Entry Date,"mm") but that didn't work. Hoping someone can help you with that issue, but at least you got part of the way there. :) Anthony Imgrund FCB

[JaneSt1]

CONCAT("ABC",RIGHT(YEAR(Entry Date),2),"0",MONTH(Entry Date),"-",Reference Number) Jane Stavinoha

[AnthonyIm]

I don't think that wouldn't work for Bridget because it would put the year first. So all the 2017's would be grouped together. She is looking for grouping all the January's together (Jan 2017, Jan 2018, Jan 2019, etc.) We need the leading zero though for Jan-Sept otherwise she would get Jan, Oct, Nov, Dec, Feb, March, April, etc. Anthony Imgrund FCB

[JaneSt1]

Hi Anthony, I know I have done this before, but can't find the exact right code... It is something like IF(LEN(STRING(MONTH(Entry Date),0))>1,STRING(MONTH(Entry Date),0),CONCAT("0",STRING(MONTH($$TODAY),0))) Jane Stavinoha