Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Need help on Union report

Received Response
41
Views
6
Comments
souvik88_570
souvik88_570 Rank 4 - Community Specialist
edited Aug 13, 2024 5:53PM in Oracle Analytics Cloud and Server

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

Invoice NumberOrder TypeInvoice Quantity
17020004NULL4000
17020004ITA 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?

Answers

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    dont know exactly what do you want to show a only one Row, because your attribute (Order Type) has different values. if you want to show the sum by the Invoice Number, show use a Pivot Table Object. honestly dont know exactly what do you behind your requirement

  • Martin van Donselaar
    Martin van Donselaar Rank 6 - Analytics Lead

    Well, of course you will see two rows, one with order type NULL, the other with order type  "ITA 1001 Order" (exactly as in the table you are showing).

    But maybe I do not understand your issue?

    You need to conform the Invoice dimension to be able to properly join these facts together.

  • souvik88_570
    souvik88_570 Rank 4 - Community Specialist

    Thanks for your responses.

    The NULL value you see in "Order Type" column in the first row is a dummy value i have passed in the column formula, as this column is not available in first subject area.

    My requirement is to show the data in the following format -

    Invoice Number

    Order Type

    Invoice Quantity

    17020004ITA 1001 Order4000

    Is this achievable?

  • Martin van Donselaar
    Martin van Donselaar Rank 6 - Analytics Lead

    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.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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.

  • souvik88_570
    souvik88_570 Rank 4 - Community Specialist

    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.