Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Loop through parameters/SQL statements
Loop through parameters/SQL statements [message #726979] Tue, 20 September 2011 01:49 Go to next message
fixed-term.raimond.eisele is currently offline fixed-term.raimond.eisele
Messages: 2
Registered: September 2011
Junior Member
Good morning,

I'm having a problem with the following task:

My first report calculates some data based on a SQL database which contains timing tests.
The user enters the testid as report parameter which is used in the Data Set SQL statement to get/calculate the values.

Quote:
SELECT *, st_mittelwert-st_min AS "st_diff_min_mw", st_max-st_mittelwert AS "st_diff_max_mw"
FROM (
SELECT st_device_groupnumber, MIN("st_waittime") AS "st_min", MAX("st_waittime") AS "st_max", AVG("st_waittime") AS "st_mittelwert", STDDEV("st_waittime") AS "st_stddev", MEDIAN("st_waittime") as "st_median"
FROM step
WHERE execution_id IN (
SELECT execution_id
FROM execution
WHERE testlauf_id = ? --<---------Parameter
)
AND st_device_text LIKE 'MP_SI%'
--ORDER BY st_device_nodenumber ASC, execution_id
GROUP BY st_device_groupnumber
ORDER BY st_device_groupnumber
)
AS "zwischentabelle"


This is working great.

But now I have to create an additional (chart)report which should contain an overview of 9 different tests. The IDs of the 9 tests should be entered by the user. So I'll get 9 different parameters. But how can I tell the SQL statement that it should use the first parameter for the first request, the second one for the second request......


I'm quiet sure that there is a simple solution, but I couldn't find it...

I'd be thankful for any hints/pointers.

Rai

[Updated on: Tue, 20 September 2011 01:52]

Report message to a moderator

Re: Loop through parameters/SQL statements [message #727194 is a reply to message #726979] Tue, 20 September 2011 11:13 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Rai,

The ? in the sql are order specific, so the first will be the first
parameter, second the second parameter etc. How will the user select
the 9 parameters? Multi-select List box? If you were hard coding the 9
test ids how would you write the query? The reason I ask this is there
may be a better way to simplify the number of parameters in the query
using some script.

Jason

On 9/20/2011 1:49 AM, fixed-term.raimond.eisele wrote:
> Good morning,
>
> I'm having a problem with the following task:
>
> My first report calculates some data based on a SQL database which
> contains timing tests.
> The user enters the testid as report parameter which is used in the Data
> Set SQL statement to get/calculate the values.
>
> Quote:
>> SELECT *, st_mittelwert-st_min AS "st_diff_min_mw",
>> st_max-st_mittelwert AS "st_diff_max_mw"
>> FROM (
>> SELECT st_device_groupnumber, MIN("st_waittime") AS "st_min",
>> MAX("st_waittime") AS "st_max", AVG("st_waittime") AS "st_mittelwert",
>> STDDEV("st_waittime") AS "st_stddev", MEDIAN("st_waittime") as
>> "st_median"
>> FROM step
>> WHERE execution_id IN (
>> SELECT execution_id FROM execution
>> WHERE testlauf_id = ? --<---------Parameter
>> ) AND st_device_text LIKE 'MP_SI%'
>> --ORDER BY st_device_nodenumber ASC, execution_id
>> GROUP BY st_device_groupnumber
>> ORDER BY st_device_groupnumber
>> )
>> AS "zwischentabelle"
>
>
> This is working great.
>
> But now I have to create an additional report which should contain an
> overview of 9 different tests. The IDs of the 9 tests should be entered
> by the user. So I'll get 9 different parameters. But how can I tell the
> SQL statement that it should use the first parameter for the first
> request, the second one for the second request......
>
>
> I'm quiet sure that there is a simple solution, but I couldn't find it...
>
> I'd be thankful for any hints/pointers.
>
> Rai
Re: Loop through parameters/SQL statements [message #727380 is a reply to message #727194] Wed, 21 September 2011 02:28 Go to previous message
fixed-term.raimond.eisele is currently offline fixed-term.raimond.eisele
Messages: 2
Registered: September 2011
Junior Member
Hi Jason,

thanks for your reply.

I've removed the parameter completely from the Query.
Now I'm using a short 3-line script in the chart to select the data to display.

Rai
Previous Topic:Move Rows across Groups
Next Topic:ClassNotFoundException: org.eclipse.core.runtime.CoreException
Goto Forum:
  


Current Time: Fri Aug 29 01:26:35 EDT 2014

Powered by FUDForum. Page generated in 0.16621 seconds