Attribute Outer Join

[138006]

Dear ALL,

Wanted a help regarding the following:

What I want is very simple. I have two entities customer and address.These two tables are joined through customer id(customer.customer_id = address.customer_id).

Now I want to show all customers in a Microstrategy report (my db is 8 1 7) and I want the address to be 'NA' for those customers who have not given their address. For those who have the address corresponding to them, I want the address attribute to be shown from the address table.

So I want something like:

SELECT
CUSTOMER.CUSTOMER_ID,
NVL(ADDRESS.ADDRESS,'NA')
FROM CUSTOMER, ADDRESS
WHERE CUSTOMER.CUSTOMER.ID = ADDRESS.CUSTOMER_ID(+);

Now, I have created an attribute called customer Id at MSTR and defined the join (Through checking the box). And I have another attribute called Address from Address table which is straightforward. My report will have these two attributes only.

And in the report I have changed the Report Data Option > Attribute Join Type > 'Preserve lookup table...w/o filter and w/ filter(neither of them worked)'. Also I have done 'Full outer join' support enabled in the project level VLDB properties.

But when I am dragging the customer id and the address, the SQL that is getting generated does not have the outer join.

What I am NOT able to do is the outer join. Can you please help me how to do this? I referred some of your technotes but they only deal with metric outer join, not attribute outer join. Please not that I do not want any metric in my report.

Can you let me know the steps to be preformed for this as I am struggling to do this simple thing make happen.

Thanks in advance.
Best Regards

 

[Z3]

My guess is you need to have some fact in the report. For some historical reason, MicroStrategy does not work as you wish when you don't have any metrics. So create a metric and hide it with OLAP services.