How add additional data to fields (SOLVED)

Hi all,
Can anybody give right direction in solving such problem.
I have query (MySQL) which returned as result table with fields: "year", "month", "day", "hour", "someValue_1", . . . , "someValue_4".
My customer wants that I added HTML5 Chart (StackedColumn) to report (I work with Pro-version).
And this chart must always have 24 samples (on X-Axis) , but it's problem, because there are some empty values, i.e. we have hour  like this 1, 2, 3, 5, 8, 10
My questions are:
1) Which method should I use to add empty rows to table, i.e. if there is not hour number 3, I have to add row which hour = 3 and all values = 0;
2) Can I into scriptlet load ALL values ALL rows(fields) in local variable?  And then change it?
Best regards, Andrew
andrqxa's picture
Joined: Apr 6 2012 - 5:16am
Last seen: 6 years 7 months ago

3 Answers:

I have found the solution. May be it will help somebody.

  1. In main report created parameter NAME_OF_MY_PARAMETER with type of net.sf.jasperreports.engine.JRDataSource

  2. Created scriptlet 

  3. In scriptlet beforeReportInit() method received value of  REPOT_CONNECTION parameter

    Connection conn = (Connection) this.parametersMap.get("REPORT_CONNECTION").getValue();

    Run query which I needed and received result (ArrayList in my cause)

    Processed result and got ArrayList which I needed (i.e. added additional rows)

  4. override setData method in scriptlet

    public void setData(Map parameters, Map fields, Map variables, JRFillGroup[] groups) {
        super.setData(parameters, fields, variables, groups);
        JRFillParameter parameter = (JRFillParameter) parameters.get("NAME_OF_MY_PARAMETER");
  5. call setData in beforeReportInit method of scriptlet

  6. In main report created subreport and as a DataSource of it set parameter NAME_OF_MY_PARAMETER

  7. in subreport left its query as empty (or as select 1)

  8. added field to subreport which had type and name which I need

  9. created chart which I needed

andrqxa's picture
Joined: Apr 6 2012 - 5:16am
Last seen: 6 years 7 months ago

I recommend that you create another table which will have empty rows missing in your customer's table. One way to do this is to use Jaspersoft ETL to extract rows from your customer table and to insert into your table. Another method is to create a bean object and insert necessary rows in the bean object and use this bean as your data source instead of jdbc.

hozawa's picture
Joined: Apr 24 2010 - 4:31pm
Last seen: 4 years 3 months ago

Check If you can use UNION Query


Select year, month, day, hour, someValue_1 from Table1


Select 0, 0, 0,hour,0 from TableStaticHour


--Ajay Patil

ajay_1311's picture
Joined: May 17 2012 - 11:27pm
Last seen: 1 week 1 day ago