Grouping while 1 table connecting to other 2 tables with leftouterjoin

[rbarekere]

I have Table A, B and C. A to B and A to C linkage is using left outer join based on Project Key. So all projects listed in A will be displayed and common projects in B and c will fetch data.

Table B and C has project Phase. Now I need to create a group based on Project Key in GH1 and PRoject Phase in GH2.

I cannot add outer join for Project Phase between table B and C, that is the SAP BW restriction.


I tried to create GH2 based on formula

if {Proj_Phase_B}= {Proj_Phase_C} then
{Proj_Phase_B}
else if isnull({Proj_Phase_B}) and not(isnull({Proj_Phase_C})) then {Proj_Phase_C}
else if isnull({Proj_Phase_C}) and not(isnull({Proj_Phase_B})) then {Proj_Phase_B}

whenever Phase B = Phase C it works fine, problem appears when phase B is null it makes Phase C also null and all those rows are put then in the end.

I am damn sure that I am making a very simple mistake, need help.

-Raghu

 

[Turkbear]

Hi,
Try testing for the NULLs first -

if isnull({Proj_Phase_B}) and not(isnull({Proj_Phase_C})) then {Proj_Phase_C}
else if isnull({Proj_Phase_C}) and not(isnull({Proj_Phase_B})) then {Proj_Phase_B}
else
if {Proj_Phase_B}= {Proj_Phase_C} then
{Proj_Phase_B}





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rbarekere]

This is what happening. I think this is a SAP specific problem (guessing)

If for a GH1 value, Phase B has equal number of Phase C in Gh2, data is fine

if for a GH1 value, there is no Phase B and there are Phase C in GH2 then also data fine.

Problem when for a GH1 value if Phase B has 1 value and phase C has 5 value in GH2 then it suppresses the remaining Phase C value.

Measure data is coming from Phase C which i placed in GH2. Due to above observation value is suppressed but if u calculate a group total, total looks absoltely fine.

I have a working report using subreport technique, i tried the above one in order to improve the performance but no good

Thanks for the help

 

[lbass]

Depending upon your fields, you might want to consider using a command with a union all as in:

select 'B' as type, A.ProjKey, B.ProjPhaseB as ProjPhase
from A left outer join B on
A.ProjKey = B.ProjKey
union all
select 'C' as type, A.ProjKey, C.ProjPhaseC as ProjPhase
from A left outer join B on
A.ProjKey = B.ProjKey

This would place the projphase fields in one field called ProjPhase. You could then insert your groups on {command.projkey} and {command.projphase}. If you need to identify which table the phase is from, you can use the type field to identify it.

-LB

 

[rbarekere]

Thanks lbass.

It wont work in my scenario, because data source is SAP BW. Unfortunately it allows only "LEFT OUTER JOIN", and no full outer join.

 

[lbass]

Not sure what you are saying. I didn't suggest a full outer join--I suggested using a command with a union all. I'm not familiar with SAP BW. Can you confirm that you are using Crystal Reports?

-LB

 

[rbarekere]

I am using crystal report only. Concept will remain same but we donot have option of using SQL Expression or command line sql. Only way to achieve the Join is using the table link in database expert.

I couldn't proceed with joining table, instead I continued with subreport though it has performance implications.

Thanks for helping me.

 

[Turkbear]

Hi,
All recent versions of Crystal, as far as I know, have the option of using a command instead of tables - is your restriction company-imposed?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rbarekere]

It is the restriction imposed by product vendor SAP

Crystal report users MDX syntax instead of SQL syntax to connect to SAP BW (datawarehouse). So there is no SQL Expressions available while you connect crystal report to SAP BW