8 Replies Latest reply on Jul 3, 2013 5:20 AM by Bobby468

# Problem Regarding a COlumn In Oracle Reports 10g

Hi everybody,

I am having a simple problem with a field in Oracle Reports 10g . Actually the requirement is we need to have 5 columns the values for the 1st three columns were getting from the query and the 4th column is the average of the 3columns of that record and teh 5th column is the sum of the averages of the previous 3 records of the 4th column . For example

a          b          c          d((a+b+c)/3)          e

---       ----        ----         ----                      ----

1          1          1          1                         0

2          2          2          2                         0

1          2          3          2                         1

Where D and E are place holder columns

The probelm is that if all a=0,b=0,c=0  then d should be also 0 it is displaying it as 0 but it is storing internally some value which s equal to teh value of d in the previous Record . which i came to with teh help of SRW.Message .

Thanks,

Best Regards

• ###### 1. Re: Problem Regarding a COlumn In Oracle Reports 10g

Hi ,

you can retrieve  column d in sql instead of placeholder.. But what is the functionality of column e? .. If it has some logic behind it , use placeholder for e ..

WITH D AS(select 1 A, 1 B, 1 C
from dual
union
select 2 ,2,2
from dual
union
select 1,2,3
from dual
union
select 0,0,0
from dual
)
SELECT  A,B,C ,(A+B+C)/3 D
FROM   D
;
A,B,C,D
0,0,0,0
1,1,1,1
1,2,3,2
2,2,2,2

• ###### 2. Re: Problem Regarding a COlumn In Oracle Reports 10g

Yes E is a placeholder column The value of E is equal to the sum of the averages of the previous 3 values of D which is nothing but the Last 3 Moving Averages of D column

• ###### 3. Re: Problem Regarding a COlumn In Oracle Reports 10g

ok.. Is your placeholder of E working fine? Or you find any issues with that.. If so please post your code...

Regards,

Archana

• ###### 4. Re: Problem Regarding a COlumn In Oracle Reports 10g

Nope it is not working properly Externally it is not displaying nay value but internally it is storing some value . Th eproblem is coming if all the three columns such as  a,b,c were not having any values . A,b,c, were being calcuated from the query d,e were calculated based upon the values of a,b,c. D is calculated as the average of the Current values of a,b,c and E is calcualted as the Current and last 2 values of D

• ###### 5. Re: Problem Regarding a COlumn In Oracle Reports 10g

Cannot identify the error from your explanation.... Check out the way you used to return value if a,b,c is null and the usage of your placeholder..

what is the code behind d.. ?

Regards,

Archana

• ###### 6. Re: Problem Regarding a COlumn In Oracle Reports 10g

hi

You can get the fourth column from below query

select a,b,c, (a+b+c)/3  average1,  from table

But regarding 5th column value - please clarify how do we identify  the previous rows.  Provide your  sample  full structure ,  so that a fix can be identified.

Check and provide your  information

• ###### 7. Re: Problem Regarding a COlumn In Oracle Reports 10g

This is the code for teh caluculation of D&E COLUMNS . I am getting teh values of A,B,C from the query D is nothing but the average of the values of a,b,c IF :D IS NOT NULL THEN :T := NVL(:T,0)+1; ST := NVL(ST,0)+:D; :SDST := NVL(:SDST,0)+(:D*:D); :CUMUL_AVG := ST/:T; :E := (NVL(:D,0)+NVL(:A,0)+NVL(:B,0))/3;   :B := :A;   :A := :D;   IF :T > 2 AN:D :D IS NOT NULL THEN :STAN:D_:DEV := SQRT( ( (:T*:SDST)-(ST*ST) )                         / (:T*(:T-1))                   );   END IF;  END IF;

• ###### 8. Re: Problem Regarding a COlumn In Oracle Reports 10g

This is the code for teh caluculation of D&E COLUMNS . I am getting teh values of A,B,C from the query D is nothing but the average of the values of a,b,c IF :D IS NOT NULL THEN :T := NVL(:T,0)+1; ST := NVL(ST,0)+:D; :SDST := NVL(:SDST,0)+(:D*:D); :CUMUL_AVG := ST/:T; :E := (NVL(:D,0)+NVL(:A,0)+NVL(:B,0))/3;  :B := :A;  :A := :D;  IF :T > 2 AN:D :D IS NOT NULL THEN :STAN:D_:DEV := SQRT( ( (:T*:SDST)-(ST*ST) ) / (:T*(:T-1))); END IF;  END IF;