report with a sql - statement with a return value from another sql - statement before

[sv-gr]

 

Hello,

 

i want to create a report that works like this: 

 

1. query against table1 using value0 (arbitrary initial value) -> 

2. return value1 -> 

3. query against table2 using value1 -> 

4. return of value2 -> 

5. display/print value2 /make pdf with value2...

 

After bringing in the the value0 at step 1  the following should run automatically till the end at step 5.

 

i am able to do this:

 

 

1. query against table1 using value0 (arbitrary initial value) -> 

2. return value1 -> 

3. display/print value1 /make pdf with value1...

 

I am also able to create reports which contains subreports where i pass the Parameters to bring in

there the value0 for example. A java application brings in that value0. i can use ireport 5.6.0 or jaspersoft studio 6.2.0.

I tried to pass the generated value1 per parameter in the query but it is still NULL when i execute this.

 

The parameter seems to be used before it is filled with the needed value1.

 

The following is not possible because the jdbc-driver i have to use doesnt support this:

 

- no "join"

- no "union"

- only one "select" per query

 

How can i create a report which does the 5 steps above?

 

greetz

sg

[javier.ggi90]

What you can do is the following:

1. Create a report and get value1 into a variable using the process you listed
2. Create a subreport and pass variable1 as a parameter into a subreport.
3. Use the parameter as a condition in the subreport's query and obtain value2.
4. Use value2 in whatever way you need it.

[sv-gr]

can you give me a hint how to do this?

I guess i have to manipulate the parameters and the variables via the Report Inspector at the left and the Properties at the right (ireport 5.6.0)

The way you described is bringing the value1 from a parameter to a variable and then back to a parameter. This i dont know how to do, please tell me.

I dont know why i need condition, because i get exactly one value1, and this value1 want use for the second query.

greetz

[javier.ggi90]

You need to ask for a parameter, this should be your "value0". Let us assume your parameter is called Parameter1.

With this you can obtain "value1" as you said. If this is a field you can create a new variable (Variable1) with the expression $P{value1}. Make sure you create the variable correctly assigning data type accordingly.

After this you should create a subreport element and map it to your second report. In the subreport's properties (as you mention, in the right bottom part) there is an option to map parameters (refer to the answer in this question: http://community.jaspersoft.com/questions/816003/how-pass-main-reports-parameter-subreport). An example would be something like this: Create a parameter in your second report called Parameter2, map the variable "Variable1" to this new parameter clicking in new and assigning the values. You need to make sure you use the exact same names otherwise it will not work.

Finally you can use Parameter2 in your subreport in the new query to retrieve value2 and do whatever you need with it.