cancel
Showing results for 
Search instead for 
Did you mean: 

left outer join is working in bex, but not in crystal reports

Former Member
0 Kudos

good morning ladies and gentleman,

i have created a bex query that shows all employees from the master data table and links to the cats table to show absence hours...

in bex, all employees are showing and some of the employees have hours showing for sick time. this is known as a left outer join.

i have created a report in crystal with this bex query and it does not show rows from the master data that do not have a link to the cats table. only the rows that have a link to the cats table are showing up. (this is compared to a inner join)

the reason i need the master data from the employee table is that the users want to divide the total hours off(sick time) per department by the total employees in that department.

here is the sql query from crystal that was created automatically when i used the bex query to created the crystal report.

SELECT {Measures.4J8L2TQJ3P517ISQ1R4Y8UZ36, Measures.4J9PY3UANRIW2HW15YSOED92A, Measures.4J9TLIO0NDQROSA2YONCW6HB6}

ON COLUMNS,

NON EMPTY

CROSSJOIN(0EMPLOYEE.LEVEL01.MEMBERS, 0EMPLOYEE__0COMP_CODE.LEVEL01.MEMBERS)

DIMENSION PROPERTIES 0EMPLOYEE.50COMP_CODE, 0EMPLOYEE.50MAST_CCTR ON ROWS

FROM ZCATS_MC1/ZZCATS_MC1_Q001C

SAP VARIABLES 0I_DAYS INCLUDING 0CALDAY.20100502 : 0CALDAY.20100903

any thoughts?

thanks,

erik

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Check the direction of the link and be sure it goes from your master table to the cats table. If you let crystle link it for you it may be backwards.

Former Member
0 Kudos

thanks debi,

but in bex, you cannot change the select statement, you can only work within their screen options.

i have chosen to use the employee master data and then use fields from the cats table and in bex this is working.

it is when crystal uses this bex query where i think that the MDX driver does not have the functionality for a left outer join.

but, thanks anyway.

Former Member
0 Kudos

I am not familar with BEX but crystal can do this.

Former Member
0 Kudos

thanks for your help. i am working with bex query designer to create the query and then create a crystal report based on the bex query.

there is a link inbetween the two and it is called a mdx driver. also, there is a sap integration kit that links all of it seemlessly.

we are currently on integration kit 2.8 and there are two newer ones, 3.0 and 3.1. i am having our basis team download them so that i can review the fixes. it may fix this issue. if it does, i will be documenting it here.

thanks again .

Former Member
0 Kudos

Debi,

Erik is reporting from a data warehouse using MDX query language (MDX based queries against a OLAP database). It's a different animal that the type of reporting we're used to (SQL based queries against OLTP databases). Unfortunately, I don't know much about it either.

Erik,

You may want to check and see if there is a different driver available. Check to see which driver BEX is using. Also check to see if the database manufacturer has an updated driver.

Jason

Former Member
0 Kudos

as i stated above, i am having my basis person download the new integration kits. they will have updated mdx drivers that may allow this to happen.

Former Member
0 Kudos

i will move this to the integration area.

thanks all for your help.

Former Member
0 Kudos

the answer from sap service market place is to use a infoset.

create an infoset with 0employee and the cats infoprovider using the left outer join on 0employee.

create a new bex query and use the field time interval with today's date.

this has worked correctly.

thanks all.

0 Kudos

Hi Erik,

I am having the similiar requirement to create a bex report for cats who have not submitted. But using the field as "Time Interval" as Todays date in Filter is giving me the common records only.

Please can u give me more detail of the implementation of this bex query.

Thanks in advance

Regards

Dinesh

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi all,

I am facing performance problem with left outer join in crstal. According to requirement I have 2 bex queries need to be left outer join in crytal . I tested that Individual queries run fast at backend but when I am doing left outer join in crystal and running the report it takes long..........forever. can you please suggest any solutions to it. user will never accept it....plz

Thanks in advance,

Priyam

Former Member
0 Kudos

If I were to hazard a guess... I'd say that there was something entered in the Selection Expert of the report. Any selection criteria placed there will be applied to the entire result set even if the selection criteria only applies to fields coming from the "right" table.

HTH,

Jason

Former Member
0 Kudos

Thanks for the quick response.

What i see in the select record expert is "any record". there is no limitation to the selection of the data.

what i did find in oss is note #1386819 which talks about relational data to sap bw data using left outer join.

i am not sure if this is an answer or not, but it might be. what i don't understand about the note is, how do you link a sub report with all the employee data with a report that has incident data, and put it on the same page?

Former Member
0 Kudos

I don't have much experience reporting against DWs or writing DMX or MDX scripts so I can't really help there.

As for regular OLTP databases and ASNSI SQL. Anything you place in the Command window will get passed back to the db server just as it's written.

Former Member
0 Kudos

i have created an oss message to get help from sap.

hopefully it is an bobj integration fix in the next release. you would think that whatever you can create in a bex query would be passed to the crystal reports without exception.

thanks again.