Use certain value of a result set

0

Hello together,

I'm kind of new to Jaspersoft - and to SQL and ... I'm new to all!

My problem:

To avoid many DB connections we try to get all our needed data with one SQL query at the beginning of the report (more precise: we have three database schemes, so I have three Datasets). We do a SELECT- with a WHERE-clause. In short, we get finally a result set like this:

result set A B C D
1 A1 B1 C1 D1
2 A2 B2 C2 D2
3 A3 B3 C3 D3
... A... B... C... D...
x Ax Bx Cx Dx

Is there a way to use/call certain values, e.g. B3, in my report as a return value (or in a pinch in a list element or something like that)?

 

For your information:

Yet we used to handle it in a very inconvenient way by creating a SQL query with a enormous SELECT and sub-SELECT-clause, respectively.

Until now we had in each dataset a very large SQL query to get all the data from the tables. It was a subselect SQL statement like

           SELECT
            (SELECT a FROM TabA WHEREAS A1),
            (SELECT b FROM TabA WHEREAS A2),
            ...
            (SELECT x FROM TabD WHEREAS D5)

There we got all all results in one single line so every value was specific. But this messy SQL clause is no option anymore because we have to know how many lines there would be and we had cells available to maybe get filled if there exist values. It looked like this when we said we have, e.g. max. 5, results.

result A1 A2 A3 ... A5 B1 ... B5 C1 ... C5 D1 ... D5
1 A1 A2 A3 A... A5 B1 ... B5 C1 ... C5 D1 ...

D5

Hope you'll understand my problem and can help me.

Thanks

ShibbyMuf's picture
Joined: Jun 14 2018 - 5:39am
Last seen: 5 months 4 days ago

2 Answers:

1

Where do you need your "certain values" exactly? In MainReport as a result from SubReport? Or outside your whole report in your (calling) java application?

It's not clear for me, what exactly you want to achieve with the result of column B in your third record!??!?!

If you just want to calculate something at MainReportLevel based on that certain third value in columnB from a SubReport, than you could use a ReturnVariable.

In your SubReport you could use a simple variable let's call $V{MyColumnBatRecord3}  - with an if-then-expression that "remembers" the value of column B in the 3. record:

$V{REPORT_COUNT} == 3? $F{fieldColumnB} : $V{MyColumnBatRecord3}

but as I said, it's not exactly clear for me, what you actually want to achieve.

hth + regards

C-Box

C-Box's picture
2202
Joined: Jul 19 2006 - 5:58pm
Last seen: 5 days 19 hours ago
0

Yeah, I want to use the variable in the main report. Didn't know that 'Im able to do kind of a loop with $V{REPORT_COUNT} == 3? $F{fieldColumnB} : $V{MyColumnBatRecord3}. You helped me a lot, thanks!

ShibbyMuf's picture
Joined: Jun 14 2018 - 5:39am
Last seen: 5 months 4 days ago
Feedback