about missing field

[qwertyu1]

Hello all

I am using crystal xi and oracle 9.
please see the below sql which is from show sql query.

SELECT
DISTINCT "FR_TIMBER_SALE_GENERAL"."FR_PROP_CODE",
"FR_TIMBER_SALE_GENERAL"."SALE_ESTABLISHED_DATE",
"FR_TIMBER_SALE_RECON"."PROPOSED_ACRES_AMT",
"FR_PROPERTY_XREF"."FR_PROP_NAME"

FROM

("X13659"."FR_TIMBER_SALE_RECON" "FR_TIMBER_SALE_RECON"
INNERJOIN "X13659"."FR_TIMBER_SALE_GENERAL" "FR_TIMBER_SALE_GENERAL"

ON "FR_TIMBER_SALE_RECON"."TIMBER_SALE_ID"="FR_TIMBER_SALE_GENERAL"."TIMBER_SALE_ID")

INNER JOIN "X13659"."FR_PROPERTY_XREF" "FR_PROPERTY_XREF" ON "FR_TIMBER_SALE_GENERAL"."FR_PROP_CODE"="FR_PROPERTY_XREF"."FR_PROP_CODE"


WHERE "FR_TIMBER_SALE_GENERAL"."FR_PROP_CODE"=5873

I have a one group on
"FR_TIMBER_SALE_GENERAL"."SALE_ESTABLISHED_DATE"

i placed 2 fields in group header those are
"FR_TIMBER_SALE_GENERAL"."SALE_ESTABLISHED_DATE",
"FR_TIMBER_SALE_RECON"."PROPOSED_ACRES_AMT"(summary field)

when i ran the report i am getting data as below:

established date acres amt
1992 100
1993 256
1995 124
1996 34
1997 125

my question is?
i need 1994 also even if the data acres amt is zero.
i need to show continues years on chart expert.

please help me how should i can get the 1994 on the report.
thanks.

 

[hilfy]

The join to FR_TIMBER_SALE_GENERAL needs to be changed to an Outer join FROM FR_TIMBER_SALE_GENERAL to FR_TIMBER_SALE_RECON. (It looks to be the other way around right now...) This will ensure that you always have the record with the established data available for the report.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[qwertyu1]

Thanks, I tried as you said but the result is same.

 

[lbass]

One way of handling this is to use running totals where you specify the year in the evaluation formula area and reset never. Place the running totals in the report footer with text boxes identifying the year.

-LB

 

[qwertyu1]

i tried using running total like below:
i placed FR_TIMBER_SALE_RECON"."PROPOSED_ACRES_AMT",
field as summary ,
at evalute place using formula , i placed year field
and at reset i checked never option.

i placed this in report footer its not giveing any value.

do i need to do any thing more , please tell me.
thanks.

 

[synapsevampire]

What formula did you use?

Honest, we don't know...

-k

 

[qwertyu1]

sorry, here is the formula

YEAR({FR_TIMBER_SALE_GENERAL.SALE_ESTABLISHED_DATE})

 

[synapsevampire]

That formula doesn't do anything in the evaluate use a formula of a running total, it must evaluate something...

Anyway, I routinely show people how to create arrays to insert fake data, which is your dilema.

Right click the groupheader andselect insert section below.

Place your fields in the lower groupheader.

upper groupheader formula;
whileprintingrecords;
stringvar Output:="";
numbervar x;
If year(previous({table.date})) <> year({table.date})-1 then
for x := year(previous({table.date}))+1 to year({table.date})-1 do(
Output:= Output & {table.date} & chr(13)
);
Output

Now right click the upper groupheader sectionand select format section->X2 next to suppress and place:

year(previous({table.date})) = year({table.date})-1

-k

-k

 

[qwertyu1]

i did the same thing as you said above, the reslut is like this:

03/03/1995 12:00:00AM
i got this in the place of missing year field. but i need for year 1994, how can i make changes.

 

[lbass]

If you wanted to use the inserted running totals, you would use a formula like the following in the evaluation section of the running total:

year({FR_TIMBER_SALE_GENERAL.SALE_ESTABLISHED_DATE})= 1994

You would change the year value for each running total.

-LB