How add additional data to fields (SOLVED)

[andrqxa]

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

[hozawa]

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.

[ajay_1311]

Check If you can use UNION Query

E.g.

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

Union

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

 

--Ajay Patil

[andrqxa]

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();[/code]

       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");    parameter.setValue(JRMeasureDataSourceFactory.getDataSource(measures));}[/code]

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