Really simple XLS from XML datasource - Jasper

Question

I have a very simple XML datasource structured like this:

<datasource>
    <row>
        <column>Some text 1</column>
        <column>Some text 2</column>
        <column>Some text 3</column>
    </row>
    <row>
        <column>Some text 4</column>
        <column>Some text 5</column>
        <column>Some text 6</column>
    </row>
    <row>
        <column>Some text 7</column>
        <column>Some text 8</column>
        <column>Some text 9</column>
    </row>
</datasource>

And I want to create a very simple XLS report formatted like this:

-------------------------------------------
| Some text 1 | Some text 2 | Some text 3 |
-------------------------------------------
| Some text 4 | Some text 5 | Some text 6 |
-------------------------------------------
| Some text 7 | Some text 8 | Some text 9 |
-------------------------------------------

I have created a report with the XPath query:

/datasource

And two fields:

$F{row}
$F{column}

with expressions:

row
row/column

respectively.

I have added a crosstab element to the Detail band and have made a few attempts to configure it with different variations of the fields defined above for the row and column grouping options. But the best result I have been able to get is the 1st column value (i.e. "Some text 1" value in the above example).

My questions:

1. How can I configure the crosstab element to iterate over each row element in my datasource and push the column values into the right cells as per the example above?
2. Is the crosstab element the most suitable element for this type of task?

Answer 1

Ok so I have figured it out and I really cannot pin point any one source as a good point of reference for this stuff. Jasper's documentation and community contribution is for the most part sparse and dated. I digress. Here is what I did to get it working for iReport 4.5.0 and Jasper 4.5.0:

1. Create a blank A4 report in iReport.
2. Edit the document query to select all 'row' elements using XPath syntax (i.e. /datasource/row)
3. Now create a sub dataset by right clicking the document property from your Report Inspector view (i.e. the one with the same name as your .jrmxl file) and selecting Add dataset.
4. In the wizard select "create new dataset from a connection or datasource"
5. Select the same XML datasource as your master report and if you have fields or groups you want to pass across select them in the corresponding wizard windows that follow. But the main thing is to select your datasource.
6. Now you will need to edit the query to select on your column elements. Before doing this, to make life easier, I edited my XML datasource to include a "count" attribute in each row element. You can create your own incrementing count if you like but for me editing the datasource document was the easiest.
   1. So next thing I did was create a field in my master report called "rowCount" and gave it the description expression "@count" so that it would store the count attribute value from each row element.
   2. After that I set up a parameter in my sub dataset with the same name.
   3. Then I configured my sub dataset query to read "/datasource/row[@count="$P{rowCount}"]/column" so that all column elements were selected for each row.
   4. Next I created a field in the sub dataset called columnValue with the description expression: "text()" to store the text from each column element.
7. After configuring the sub dataset I dragged a crosstab object into my detail band which prompted the crosstab wizard to show.
   1. In the wizard I selected the sub dataset as its datasource and selected the same connection as the master report.
   2. For the Row and Column group values I just chose $P{rowCount} and $V{COLUMN_COUNT} respectively.
   3. Most importantly I configured the "Data" section to point to my columnValue field and select the measure as "nothing" rather then count or any other calculation. Lastly click finish to create the object.
8. Next step was to right click the crosstab object in the Report Inspector and select "crosstab data" which bring up a data config window. In here I:
   1. Selected "data is pre-sorted"
   2. In the Dataset Run section at the bottom, in the "Connection/Datasource exp" tab I selected "Use Connection Expression" with the value $P{REPORT_CONNECTION}
   3. In the "Parameters" tab I added:
      • XML_DATA_DOCMENT
      • REPORT_CONTEXT
      • XML_DATE_PATTERN
      • XML_NUMBER_PATTERN
      • XML_LOCALE
      • XML_TIME_ZONE
      • rowCount
   4. I left the "Parameters Map exp" blank.
9. The last couple of things to do from this point is to hack the XML to remove the row and column group markup which entails removing the following elements from inside the "crostab" JRXML element (including all sub elements of the below):
   • crosstabRowHeader
   • crosstabTotalRowHeader
   • crosstabColumnHeader
   • crosstabTotalColumnHeader
10. Lastly of all wrap your $P{rowCount} row group bucket expression with java.lang.Integer.parseInt so that it can process the rowCount as an integer.

So that's it. For me this is what I need to solve the crosstab / XML datasource / sub dataset conundrum!