nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Access dataset from script

bwinter

I would like to take the data retrieved from a dataset, manipulate that data and then create a new report based on the resulting data. I've been digging as hard as I can but can't seem to find a way to do this. Is it possible?

Here's the situation: I have a very complex data set with multiple joins across 3 tables (for example: project, state and project_state_cross, where the cross table holds stateId, projectId). This is fine for a single parameter as I can just call a different dataset for each join group. However, I will have a dynamic multi-select input because the report must include details for many projects and because of the multiple many-to-many relationships, I end up with many rows for a single project, which is no good because I end up with this:

project id: 1
state: UT

project id: 1
state: WY

When What I need is:

project id: 1
states: UT
WY

What I think I need to do is to get the data from the dataset with the multi-select parameter and then manipulate that data into a POJO and shove the POJOs into a report and render that report. That seems to address the problems of not knowing how many rows there will be because I will know how many unique project ids are in a dataset and it allows me to be very flexible with the output, which is the goal of management.

I may be way off base here... I have been trying to do this in java with no luck so far.

Thanks in advance! I'm a total BIRT newbie and might be missing something very obvious.

Find more posts tagged with

Comments

kclark

Hi bwinter,

project id: 1
state: UT

project id: 1
state: WY

Is that what you end up with in your table? If it is then you should be able to using grouping to get the results you want.

bwinter

Here is the dataset query:

select distinct P.NAME as PROJECT_NAME,
P.ID as PROJECT_ID,
P.USEFUL_LIFE,
P.START_DATE,
P.END_DATE,
PROG.DESCRIPTION
from PROJECT P
left join PROJECT_PROGRAM_CROSS PPC on PPC.PROJECT_ID = P.ID
left join PROGRAM PROG on PPC.PROGRAM_ID = PROG.ID
where 0=0
/* BIND and P.ID in ($project_ids) */

This brings back the following result set:

Lower Detroit River Coastal Wetland Restoration and Enhancement 14717 31-MAY-06 30-MAY-13 Coastal Wetlands Act - 148555
bd late test project 147080 28-OCT-12 08-NOV-12 State Wildlife Grants - 148596
bd late test project 147080 28-OCT-12 08-NOV-12 State Wildlife Grants - 148597

When I put this into my layout, it looks like this:
Grant Application Report

PROJECT_NAME Lower Detroit River Coastal Wetland Restoration and Enhancement
PROJECT_ID 14717
PROGRAM
Coastal Wetlands Act - 148555

PROJECT_NAME bd late test project
PROJECT_ID 147080
PROGRAM
State Wildlife Grants - 148596

PROJECT_NAME bd late test project
PROJECT_ID 147080
PROGRAM
State Wildlife Grants - 148597

What I would like to see is this:

Grant Application Report

PROJECT_NAME Lower Detroit River Coastal Wetland Restoration and Enhancement
PROJECT_ID 14717
PROGRAM
Coastal Wetlands Act - 148555

PROJECT_NAME bd late test project
PROJECT_ID 147080
PROGRAM
State Wildlife Grants - 148596
State Wildlife Grants - 148597

How would I go about using grouping to make this happen? Sorry if it's an obvious answer. Been fiddling with this all week and am at wits end...

Clement Wong

It appears that you can add a Group section to your table, and set the "Group On" to PROJECT_ID. In the header of the Group section, you would have a Grid that would contain your Project Name and Project ID. The details of the table would be your PROG.DESCRIPTION.<br />
<br />
If you are not familiar with Grouping, you can see this example which includes a quick demo video and before/after report designs @<br />
<a class='bbc_url' href='http://www.eclipse.org/birt/phoenix/examples/reports/grouping/'>http://www.eclipse.org/birt/phoenix/examples/reports/grouping/</a>

bwinter

Thank you very much for this! I'll check it out immediately.