about summery field

[qwertyu1]

hi all,

iam using crystal xi and oracle 9i.

i have database tables like fr_recon and fr_stand_prefix.

i have databsae fields like : fr_recon.acres_amt(number5)
fr_recon.stand_prefix(varchar4)
and fr_stand_prefix.stand_prefix(varchar1)

iam trying to get the total amout of acres for each stand_prefix.
but the problem is ,i got two fields like fr_recon.stand_prefix and fr_stand_prefix.stand_prefix from both tables. i joined these tables using stand_prefix.

i have data in fr_STAND_PREFIX.standprefix as 'A','B' upto 'Z'.where as in other field i.e fr_recon.stand_prefix
as 'A','B','AZT','AXY','BX','OX' like that.

i need to get the total amount for each stand_prefix that contains instrings also.

i tried formula like instr(fr_recon.stand_prefix),'A'>0 and placed in selectexpert.i got total acres amout for stand_prefix 'A'

but i need total acres amount for all stand_prefixs.and some have no stand_prefix.

please help me.

 

[lbass]

I don't follow what you are doing. Why are you using these other two links? What happened to the link I suggested? All of a sudden you have added two new tables that we have not discussed before, and I have no context for this.

-LB

 

[qwertyu1]

sorry for that,

actually reports contanis:

4 tables
1)FR_RECON
2)FR_PROPERTY_ITEM
3)FR_PROEPRTY_GROUP
4)FR_STNAD_PREFIX

only two parameter fields iam using from fr_property_item,fr_property_group.

those are
1}prop_code from fR_PROPERTY_ITEM
2)groupname from fr_property_name

when i select prop_code,and groupname, it should show
stand_prefix, stand_prefix_text and and total acres_amt.

i tried the same link u have suggested ,i got the above posted result.but i dint tried parameters till now.

 

[lbass]

Please explain how the main report is grouped and also show your record selection formula.

-LB

 

[qwertyu1]

my main report grouped based on
{fr_stand_prefix}.{stand_prefix}.

redord selection formula in main report:

{fr_property_item}.{prop_code}={?prop_code}
{fr_property_group}.{name} ={?name}

subreport selection formula:

{fr_recon.stand_prefix} like "*"+{?pm-fr_stand_prefix.stand_prefix}

please let me know is it clear or not.

 

[lbass]

Please clarify your joins again. Do you have a left join FROM fr_recon TO fr_stand_prefix? And then additional joins FROM fr_recon to property item and from property item to property group?

-LB

 

[qwertyu1]

here are the joins.

{fr_recon}.{stand_prefix_code}(+)=
{fr_stand_prefix}.{stand_prefix}



{fr_recon}.{prop_code}=
{fr_property_item}.{prop_code}



{fr_property_item}.{prop_id}=
{fr_property_group}.{prop_id}

 

[lbass]

Well, that looks like a right outer join which is not what you said earlier. Is this how the joins are displayed in the "Show SQL Query" area? Maybe you should copy that into the thread.

-LB

 

[qwertyu1]

hello this is show sql query


SELECT "FR_RECON"."FR_PROP_CODE",
"FR_RECON"."ACRES_AMT",
"FR_RECON"."STAND_PREFIX_CODE",
"FR_STAND_PREFIX"."STAND_PREFIX_TEXT"


FROM "X13659"."FR_RECON" "FR_RECON" LEFT OUTER JOIN "X13659"."FR_STAND_PREFIX" "FR_STAND_PREFIX"
ON
"FR_RECON"."STAND_PREFIX_CODE"=
"FR_STAND_PREFIX"."STAND_PREFIX"

ORDER BY "FR_RECON"."FR_PROP_CODE"

thanks

 

[lbass]

Okay, I still think the subreport approach is the way to go, as described in my post of 03 Nov 2006 14:09. The main report should use the link shown in your last post--which is a left join FROM the Fr_recon table TO the FR_stand_prefix table. You need to do this so that when you group on fr_stand_prefix, you will also get a null group--so that you can pick up nulls in the subreport (I think). Add the other two tables, and set the parameters up in the record selection formula of the main report:

{fr_property_item}.{prop_code} = {?prop_code} and
{fr_property_group}.{name} ={?name} and

The additional tables should also be in the subreport, linked to the fr_recon table, and the parameters should be set up in the record selection formula of the subreport. You changed my suggestion for the like statement. It should read:

{fr_recon.stand_prefix} like "*"+{?pm-fr_stand_prefix.stand_prefix}+"*" //it was missing the + before

The whole subreport record selection formula should look something like:

{fr_property_item}.{prop_code} = {?prop_code} and
{fr_property_group}.{name} ={?name} and
{fr_recon.stand_prefix} like "*"+{?pm-fr_stand_prefix.stand_prefix}+"*"

If you are unclear what acres are being included in the subreport totals, then unsuppress the detail section to verify the accuracy.

Be sure to go the subreport linking screen and link the parameters from the main report to the parameters in the subreport by using the dropdown in the lower left corner to select {?prop code}, not the default {?pm-?prop code}, and to select {?name}, not {?pm-?name}.

-LB

 

[qwertyu1]

Hello Ibass,

when i was trying refresh the main report as well as sub report its giveing error msg like:

[propting failed with folowing error msg:
'list of values failure:fail to get values.
[cause of error:failed connection]
failed to open the connection.
unkonown.rpt']

error sourcerompt.dll
error code:0x8004380d

what is this?

 

[lbass]

I don't really know. It sounds like it has something to do with the way you set up the parameters. Maybe start a new thread if a search using the error code and/or error description doesn't turn up the answer.

-LB

 

[qwertyu1]

hello ibass,

now i am able to enter the parameters.

can u tell me how i can get, total acres amt for each prop_name.

i am getting total acres amt for each_stand_prefix.this amount includes all prop_name.
but i need for each prop_name.


do i need to enter parameters in both reports or only in main report.

pls let me clear.

 

[lbass]

I explained that in a previous post. You need to use the parameters in both main and subreports in the record selection formula and then link the parameters to each other in the subreport linking screen.

-LB

 

[qwertyu1]

but in main report i con't get stand_prefix parameter.

in subreport iam getting in linking screen.

{FR_RECON.STAND_PREFIX_CODE} like "*" +{?Pm- FR_STAND_PREFIX.STAND_PREFIX}+"*"

how should i do?

 

[qwertyu1]

thanks i bass.

its wonderful, working.