Best practices for making a rolling report by month and year

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
I have a need to show a summary report with Months on the X Axis. This report shows labor hours by month. As jobs can last more than a year, we have a need to show these summaries also by year/month. Instead all of the labor hours for January 2018 and 2019 are being summarized in one column. 

Are their any best practices for concatenating or keeping the report from doing this?
Photo of Rob Salaj

Rob Salaj

  • 180 Points 100 badge 2x thumb

Posted 4 months ago

  • 0
  • 1
Are you saying that you only want the report to have 12 months wide and you have it also doing row groupings by year?
Photo of Rob Salaj

Rob Salaj

  • 180 Points 100 badge 2x thumb
My Rows are by cost code and phase which i need by job. My columns are by Month today. I need to add year to the columns too, as i need the report to continue to expand. Since you can only have one grouping column, i am stuck. i am thinking a concatenation field may help but the text format causes other sorting issues. for example it shows 1-2018, then 1-2019, 10-2018, 11-2018, as opposed to a monthly order. 
So just make a field to use for your column grouping that calculates to YYYY-MM

Year & "-" & PadLeft([Month],2, "0")


Photo of Rob Salaj

Rob Salaj

  • 180 Points 100 badge 2x thumb
Perfect! Thanks
That PadRight was a new feature on Sunday. It could be be done before, but now it's easier.