I have a question about customizing or adjusting the x –axis in Jaspersoft for charts. Currently Jaspersoft defaults to Alphabetic order to list my X-axis labels. I need to adjust this to be based on time so I can show a trend over time. I know the issue has to do with my label names and the fact that Jaspersoft does not understand that the values are suppose to be dates and not text. (See chart below for issue)
Attempts to fix:
Change the data type to a date in sql statement
Creating a calculated field and changing the the data type to date in Jaspersoft.
My question,
Is there a way to adjust the axis manually to reflect the intended trend based on time ?
If there isn’t a quick fix, What would the change have to be for Jaspersoft to understand I’m trying to trend based on time. (Starting in 2016-present)
Other notes:
1.I've built a domain
2.I'm using sql statement to build a custom table within the domain. The sql looks like this:
Select
Case
when date(a.trandate) between '2015-08-01' and '2015-08-31' then 'Aug 2015'
when date(a.trandate) between '2015-09-01' and '2015-09-30' then 'Sep 2015'
when date(a.trandate) between '2015-10-01' and '2015-10-31' then 'Oct 2015' else null end as Month
,Case
when date(a.trandate) between '2015-08-01' and '2015-08-02' then 'Jul 2015 - Week 4'
when date(a.trandate) between '2015-08-03' and '2015-08-09' then 'Aug 2015 - Week 1'
when date(a.trandate) between '2015-08-10' and '2015-08-16' then 'Aug 2015 - Week 2'
when date(a.trandate) between '2015-08-17' and '2015-08-23' then 'Aug 2015 - Week 3'
when date(a.trandate) between '2015-08-24' and '2015-08-30' then 'Aug 2015 - Week 4'
when date(a.trandate) between '2015-08-31' and '2015-09-06' then 'Sep 2015 - Week 1'
when date(a.trandate) between '2015-09-07' and '2015-09-13' then 'Sep 2015 - Week 2'
when date(a.trandate) between '2015-09-14' and '2015-09-20' then 'Sep 2015 - Week 3'
when date(a.trandate) between '2015-09-21' and '2015-09-27' then 'Sep 2015 - Week 4'
when date(a.trandate) between '2015-09-28' and '2015-10-04' then 'Sep 2015 - Week 5'
when date(a.trandate) between '2015-10-05' and '2015-10-11' then 'Oct 2015 - Week 1'
when date(a.trandate) between '2015-10-12' and '2015-10-18' then 'Oct 2015 - Week 2'
when date(a.trandate) between '2015-10-19' and '2015-10-25' then 'Oct 2015 - Week 3'
when date(a.trandate) between '2015-10-26' and '2015-11-01' then 'Oct 2015 - Week 4' else null end as Week
,sales
,GP $
,GP %
From table_name
Where
etc
,etc
Group by
Case Statements for Month
Case Statement for Week
