Search code examples
csvjasper-reports

How to use the CSV query language?

In JasperSoft Studio or iReport, how do you query on csv data? It has an option to do CSV query language but I cannot find any proper documentation or instructions on how it works.

I need to do group by and add conditional parameters without the need to transfer it first into a database.

Solution

  • The JRCsvQueryExecuter lets you sort and filter data from a cvs file

    The sort command can be done on single or multiple fields.

    <sortField name="name"/>    
<sortField name="city" order="Descending"/>

    The filtering is done by the filterExpression

    <filterExpression><![CDATA[$P{IncludedStates}.contains($F{state}) ? Boolean.TRUE : Boolean.FALSE]]></filterExpression>

    You can not use a normal sql statement but with these 2 properties, you are fairly close to order by and where. Specially since jasper reports have build in support for sum, avg (through variables) and the group by through groups.

    <group name="YourGroup">
    <groupExpression><![CDATA[$F{fieldToGroupOn}]]></groupExpression>
   ...the group bands ..
</group>

    You can find a full running sample in the jasper reports distribution under demo\samples\csvdatasource\reports\CsvQueryExecuterReport.jrxml, this is the sample reference