0 Replies Latest reply on Apr 3, 2020 10:16 AM by Omar Latif

    Smart reporting - Format date as MMM-YY and use it in a report filter

    Omar Latif
      Share This:

      Hi everyone

       

      Can I ask a really tricky question! i have spent ages trying to solve it

       

      I would like a report to display the count of incidents raised by month as below:

      Apr-19   200

      May-19  150

      Jun-19   365

      etc...

       

      I would also like to display this in a line chart, so the date (MMM-YY) will appear in the x-axis and the ticket counts in the y- axis.

      Now I can perform the above steps by creating two calculated fields, i.e. one for Year and another for month name. And then concatenate both in the report body to get MMM-YYY.

       

      However, here comes the challenging bit......

       

      I would like to drill through into a child report from here. So for example, in the table above for Jun-19 if a user clicks on the hyperlinked total '365' then it should open up a child report listing all those tickets. The problem is that I have to define these concatenated fields (MMM-YY) in the child report.

      But i cannot get the joined month and year i.e. MMM-YY into the filter section of the child report for the drill through link to work.

       

      Does anyone have any ideas on how to solve this? Do i need to create a calculated field (MMM-YY) at the view level? And if so then could someone kindly show me how to do it.

       

      Many thanks