Create an ad hoc multi-year line graph

[beverleym]

I am using JasperReports Server Professional 7.1 (web interface)

I'm trying to create an AD HOC VIEW line graph that has one line for each year of my date field, similar to the example at the top of  https://www.pivot-table.com/2014/05/14/compare-years-in-pivot-chart/ but instead I'm getting a single line for the entire time series, more like the example shown on that web page under "Create a pivot chart".

I have mine grouped by Month, but I'm still only getting a single line and the X axis has January 2016 at the far left and October 2019 at the far right.  What I want is January at the left and December at the right, and a line for 2016, a different line for 2017, etc.

The layout I have currently which is the closest I can get is:

• Chart Type: Line graph
• Columns: A calculated field counting the number of results
• Rows: my date field, grouped by month
• Rows data slider all the way at the right

Any assistance would be greatly appreciated.

[beverleym]

After posting the above, I found "Time Series" but it won't allow me to select Month, only Day or less.  I need to compare 10 years of data so Day will be very messy .. and it still seems to want to do it as a series of days from first to last rather than multiple lines.

[danoldenkampgmail.com]

Put year in the chart series.

 

Add year to your dataset.  (use an expression to extract)

Edit the chart and select the Year as the series, the month as the category, and your calculated field as the value.

[beverleym]

@danoldenkamp I thought I had done that, but perhaps I hadn't done all the things at the same time.

 

What I've got now is a calculated Field for year with formula = Year("Ignition Date/Time") in the Columns section, along with a calculated Measure for Num with formula = 1.  In the Rows section, I have another calculated Field for month with formula MonthNumber("Ignition Date/Time").

 

This seems to be pretty close to what I was looking for.  I was hoping for "January" rather than 1, but if I use the MonthName formula instead, it puts them in alphabetical order so it starts with April and August and ends with September.  So I guess I can do a concatenate of both to do 1-January etc. 

[beverleym]

Nope.  that doesn't help my sort issue.  Now I get 1-January, then 10-October etc.  Sigh.

[beverleym]

Ugly final formula.

Concatenate(IF(MonthNumber("Ignition Date/Time") <= 9, '0', ''), MonthNumber("Ignition Date/Time"), '-', MonthName("Ignition Date/Time"))

 

Is there a better way??

[beverleym]

what I've got so far.....

 

Screen shot should be below but it's not showing up.... you can also see it at https://ibb.co/6FZ97qg

[C-Box]

What about using the month numbers as category and just use an expression for the category labels?

(e.g.

new SimpleDateFormat("MMMM").format(java.sql.Date.valueOf(LocalDate.of(2019,$F{YourMonthNumberField},1)))  [/code]

So this will probably sort chronological but render the month names. ;-)

hth + regards

C-Box

[beverleym]

@C-Box
I sure wish I could comment directly on the answers.

 

I don't think I can use that Java code in the web interface.  There aren't categories, etc.  Only Columns and Rows.  See screen shot link in previous comment.