Search code examples
jasper-reports

Number each nested table inside a master table on a Jasper report

So i have the following issue. In my database I have a table named 

bp_minutes_app.

This table stores records of minutes from committee meetings. Each minute can consist of many files. The database design regarding minutes is the following:

The table bp_minutes_attachments is connected via a foreign key to the bp_minutes_app and holds the following information:

description, createdby

The table

 bp_files

is connected via a foreign key to the

bp_minutes_app

and holds the file along other information. We are interested in taking only the name and id of the file (the latter in order to create a hyperlink to the file, but this is out of scope of the present question).
Final remark. All those tables are linked to the master 

bp_full

table that is joined only to get the id of each specific report.

In my report I want to print on a table all minutes and the accompanying information (file name, description, createdby, submission date), but make a separate entry for each minute. In order to achieve this, I first created a table that uses the following dataset:

SELECT bp_minutes_app.id
FROM bp_minutes_app
    INNER JOIN bp_full ON
    bp_minutes_app.bp_full_app_id = bp_full.id
WHERE bp_full.id = $P{id}

With this dataset I retrieve each minutes record. In my example, I get two records with ids 39 and 40. Inside this table I nested another table to retrieve information for each file in each minutes record. The dataset that I used is the following:

SELECT bp_minutes_app.submission_date,
    bp_minutes_attachments.createdby,
    bp_minutes_attachments.description,
    bp_files.id,
    bp_files.name
FROM bp_minutes_attachments
    left JOIN bp_minutes_app ON
    bp_minutes_attachments.bp_minutes_app_id = bp_minutes_app.id
    AND bp_minutes_app.id = $P{mid}
    left JOIN bp_full ON
    bp_minutes_app.bp_full_app_id = bp_full.id
    left JOIN bp_files ON
    bp_minutes_attachments.bp_file_id = bp_files.id
WHERE
    bp_full.id = $P{id}

The id parameter is used again to take the specific report and the mid parameter to take a specific minute. On parameter specification mid is declared as follows:

mid = $F{id}

, where id is the bp_minutes_app.id.

Following this setup I managed to get nested tables for each minute (forgive me for the greek headers):

enter image description here

What I want to do is number each subtable from 1...n, according to the number of minutes/ subtables. How can I achieve this? I tried to create a suitable variable but it was not met with success. I want to somehow "save" in a parameter / variable the count of bp_minutes_app records and manipulate this number for each nested table header, instead of just numbering each table with the corresponding id.

Sorry for the long post but I am new to Jasper and I wanted to be clear about the steps I have taken and what I want to achieve.

Solution

  • Alot of sql not much jrxml in your post so I start by guessing.

    Have you tried something like this in the jrxml:

    <variable name="counter" class="java.lang.Integer" calculation="Count">
    <variableExpression><![CDATA[$F{id}]]></variableExpression>
</variable>