Need help on Union report

Question

I am trying to build an union report from two subject areas as shown below -

Invoice NumberOrder TypeInvoice Quantity17020004NULL400017020004ITA 1001 Order0

"Invoice Number" is common in both the subject areas while "Invoice Quantity" comes from first subject area and "Order Type" from the other.

I am passing NULL as dummy value for "Order Type" and 0 for "Invoice Quantity".

Currently I am seeing two rows per "Invoice Number" while i want to see only one. Can anyone please suggest a way around for this?

souvik88_570 · Answer

Thanks all for the suggestions.

I made changes to RPD and brought "Order Type" column in first subject. I don't need a union report any more.

Thomas Dodds · Answer

USE Drives Design ... create a subject area that has the data already conformed and related from the BMM layer.  Then you don't need a union and the Order Type attribute is not an issue and SUM(Quantity) works as expected.

Martin van Donselaar · Answer

Not by using a union query.

What are your models like? You need a conformed Invoice dimension because that is what you need join the 2 data sources on.

Please provide details on your data model so we can give some more detailed advise.