Jump to content
Changes to the Jaspersoft community edition download ×

Unable to build trended dashboard chart with date value = Feb 2017,Mar 2017,Apr 2017 etc


ehab.anwar

Recommended Posts

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

 

 

converted-file.png.2427528347cea774315213bba42bc3c9.png

 

 

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Posted Images

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...