about sql

[qwertyu1]

Hello all,

I am using crystal xi and oracle 9.

I got a sql like following :

select r.PRIM_TIMBER_TYPE ptt,
tt.TIMBER_TEXT ptx,
count(r.RECON_ID),
sum(r.ACRES_AMT) Acres,

TO_CHAR(sum(r.ACRES_AMT)/TO_NUMBERP53_RECON_ACRES,'999999999')*100,'990.99') P,

TO_CHAR(sum(r.ACRES_AMT)/TO_NUMBERP53_FORESTED_ACRES,'999999999')*100,'990.99') P2

from fr_recon r, fr_timber_type tt

where r.FR_PROP_CODE = 0_PROPERTY
and
r.PRIM_TIMBER_TYPE = tt.TIMBER_TYPE
and
tt.FOREST_FLAG = 'F'

group by r.PRIM_TIMBER_TYPE, tt.TIMBER_TEXT




note:
sql for :53_RECON_ACRES

(select sum(r.ACRES_AMT)
from fr_recon r
where r.FR_PROP_CODE = 0_PROPERTY)



sql for:53_FORESTED_ACRES

(select sum(r.ACRES_AMT)
FROM fr_recon r, fr_timber_type tt
where r.PRIM_TIMBER_TYPE = tt.TIMBER_TYPE
and tt.FOREST_FLAG = 'F'
and r.FR_PROP_CODE = 0_PROPERTY
and nvl(instr(r.STAND_PREFIX_CODE,'R'),0) = 0
and nvl(instr(r.STAND_PREFIX_CODE,'Y'),0) = 0
and nvl(instr(r.STAND_PREFIX_CODE,'Z'),0) = 0)


sum(r.acres_amt) this is the total amount for all prop_codes.

how i proceed for report. actually i can able to get the below fields on report.

r.PRIM_TIMBER_TYPE ptt,
tt.TIMBER_TEXT ptx,
count(r.RECON_ID),
sum(r.ACRES_AMT) Acres,

for last 2 fields i confused.
anybody can tell how can i get last 2 fields on report.

 

[synapsevampire]

You can place each in it's own subreport (use Insert->Subreport) since the intent is not to join the data.

-k

 

[qwertyu1]

where should i place subreports?
do i need to link subreports to mainreport?

 

[synapsevampire]

Since you don't want to link the data, you wouldn't want to link the subreports.

I'm somehwat surprised that you wouldn't just go ahead and do this instead of asking me where you want them to display, as I don't know where you want them, you do, so you'll decide where to put them, right?

It's probably either the report header or report footer, right clicking either and selecting insert section below to place each in it's own section within.

-k

 

[qwertyu1]

hello all,

I tried subreport and i placed in groupheader, its giveing
acres amt for that perticular group.

i would like to c my report like below:

ttype tname recin_id acres %acres %acres

A aspen 124 20000 20 10
BH redwine 55 12000 15 33
CA blackwine 127 674 10 15
D deeryard 230 120 13 17
.
.
c oak 12 235 12 15


how i can get , please help me.
thanks

 

[Madawc]

If you want all of the detail, put the subreport in the report header or report footer.

Also take a look at Crosstabs, Insert > Cross-Tab. A crosstab in the report header or footer can summarise data for the whole report.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10