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):
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.