Looking for some tips on how i would create a chart report that will help identifying trends of reports submitted by country over a period of time. I have fields for Country, Month and # of reports but i want to be able to select a month range and see the number of reports submitted by country within that time frame?
Line chart with a prompt on month?
Would creating a data item such as below and turning it into a prompt as well be useful?
trim(case when month([Date]) = 1 then 'January'
when month([Date]) = 2 then 'February'
when month([Date]) = 3 then 'March'
when month([Date]) = 4 then 'April'
when month([Date]) = 5 then 'May'
when month([Date]) = 6 then 'June'
when month([Date]) = 7 then 'July'
when month([Date]) = 8 then 'August'
when month([Date]) = 9 then 'September'
when month([Date]) = 10 then 'October'
when month([Date]) = 11 then 'November'
when month([Date]) = 12 then 'December'
end) ||' '|| cast(year([Date]), varchar(4))
If you need to display multiple dates, I would also add a month data item and a year data item to facilitate sorting when the report generates.
month([Date])
year([Date])
I do this for a few reports where I grab data across multiple months and years occasionally. I just make sure to sort on year descending followed by month descending from the two items above.