Subreport With Data From SQL Query Which Is In JSON Format

Issue Description

User wants to create a subreport with data from main report SQL query which is in JSON format.

Resolution

To create such report we would need to create to reports: main report (MainReport.jrxml) and subreport(SubReport.jrxml). Main have ordinary SQL Query which gets data from DB Table. This query can be represented by the following statement:

SELECT "json_text" , "id"
FROM 
(VALUES 
('[{ 
''dateFrom'': ''01.12.2016'', 
''dateTo'': ''01.12.2016'', 
''someOptionalNumber'': 12 
},{ 
''dateFrom'': ''01.12.2016'', 
''dateTo'': ''01.12.2016'' 
}, { 
''dateFrom'': ''01.12.2016'', 
''dateTo'': ''01.12.2016'', 
''someOptionalNumber'': 11 
}]', 1)
) s("json_text" , "id")

The structure of main report is simple: a Title and a Subreport element in Summary band

Subreport element should contain several parameters: 'JSON_INPUT_STREAM' and 'net.sf.jasperreports.json.date.pattern'.  'JSON_INPUT_STREAM' is needed to process JSON String from the main report field and pass it to subreport.  'net.sf.jasperreports.json.date.pattern' is used as a pattern for dates in subreport. The code for subreport element:

<subreport>
    <reportElement x="-1" y="0" width="545" height="320" uuid="b11b1e89-b446-44a6-aa9e-c12fdba2d428"/>
    <subreportParameter name="JSON_INPUT_STREAM">
        <subreportParameterExpression><![CDATA[new ByteArrayInputStream($F{json_text}.getBytes("UTF-8"))]]></subreportParameterExpression>
    </subreportParameter>
    <subreportParameter name="net.sf.jasperreports.json.date.pattern">
        <subreportParameterExpression><![CDATA["d.m.y"]]></subreportParameterExpression>
    </subreportParameter>
    <subreportExpression><![CDATA["Blank_A4.jasper"]]></subreportExpression>
</subreport>

Subreport should have query language 'JSON':

<queryString language="json">
        <![CDATA[]]>
</queryString>

Please make sure, that subreport contains all actual fields, which are in JSON String (in this case: "dateFrom", "dateTo", "someOptionalNumber"):

Now we can publish main report to JasperReports Server to check the result. As you can see, JSON String was passed to subreport and parsed correctly by JSON executer:

mainreport.jrxml

subreport.jrxml