record selection formula that uses a calculated value

[TB1977]

I need to
Read a database of CICS transactions
For those transactions that were used on the priorday and for those Txs consuming more CPU seconds than their calculated normal values (Average CPU + standard deviation)
I want to print their daily CPU values for each day to show the daily growth of that Tx.

The final report will show the daily historical CPU usage for only those records where the prior days CPU usage is above its normal value. I have this working in a DB2 SQL query.

How do I :

1:Read all dates from database for each transaction - to calculate standard deviation and mean. Pass#1
(cannot use a subreport because subreports are ran in Pass#2)

2:Select Tx data where prorday's value is above the normal value calculated in step 1.
(cannot use a record selection because Pass#1 has already finished).

3:For records passing test in step 2, print data from each day from step 1 that was used to calculate standard deviation.

 

[lbass]

If you have a working SQL Query, try copying it into the Add command area and use that as your datasource. Database->database expert->your datasource->add command (above the table list). This assumes you have version 9.0 or above.

-LB

 

[TB1977]

I'm sure glad the cut and paste works when adding my DB2 SQL -- it has about 80 lines and 5 subselects. Only a couple of slight differences in syntax. But it works!! I didnot know you could add SQL this way. I will be using this method in the future.

Thank you very much.