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

Analysis measure based on external filter

Received Response
111
Views
18
Comments
2»

Answers

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    I should have mentioned that in going to a union query, it is frequently necessary to use a pivot table instead of a table view.

    In the example shown above, your first 3 columns would be in the ROW drop area.  Exclude the 'Test' column because it splits the records.

    You can then add your bar chart within the pivot table view.

    pastedImage_0.png

  • Matthias_VC
    Matthias_VC Rank 3 - Community Apprentice

    Okay,

    I think I'm in an older version as my layout is slightly different.

    However if I add a Pivot Table it gives me the same error as before

    PivotTable.JPGtoo many rows.JPG

    And the problem is: once you have an error you can't change the visual or anything. So I'm wondering if this is a Catch-22 situation, or in which order I should execute it to get the desired result...

    Kind regards,

    Matthias

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    That may be because you are still trying to open the problematic table in your Compound layout.

    Are your query filters in both parts of the union limiting the results correctly?  Do you need another filter in the "NOT IN the Subquery" query?

    I don't see any WHERE statements in the SQL you've shown, so it looks unfiltered.

    You can test the separate legs of the union by adding a "1=0" filter to opposite one.

    That space limitation error can usually be fixed by moving an attribute column into either the table prompt or pivot table prompt area, in the absence of other issues, and with adequate size-limit configurations

  • Matthias_VC
    Matthias_VC Rank 3 - Community Apprentice

    Okay,

    Filtering helped in trying to create a pivotTable.

    I was able to get the following result:

    PivotTable.JPG

    With following SQL query:

    SELECT saw_0, saw_1, saw_2, saw_3, saw_4, COUNT(DISTINCT saw_2 by saw_0, saw_1, saw_4) saw_5
    FROM (

         (SELECT "General - (Project)"."Project Object Id" saw_0,  "General - (Project)"."Project Name" saw_1,  "General - (Activity)"."Activity Id" saw_2,  "General - (Activity)"."Activity Status" saw_3, CONCAT("General - (Activity)"."Activity Status", ' original') saw_4
               FROM "Primavera - Activity History"
               WHERE
                   ("General - (Activity)"."Activity Status" IN ('In Progress', 'Not Started'))

                   AND ("General - (Project)"."Project Object Id" IN
                         ( SELECT saw_1 FROM (SELECT "General - (Project)"."Project Name" saw_0, "General - (Project)"."Project Object Id" saw_1
                         FROM "Primavera - Activity History" WHERE "General - (Project)"."Project Name" IN ( '... juli 2019')) nqw_1 ))     

                   AND ("General - (Activity)"."Activity Id" IN

                         ( SELECT saw_0 FROM (SELECT "General - (Activity)"."Activity Id" saw_0,"General - (Project)"."Project Name" saw_1
                         FROM "Primavera - Activity History" WHERE "General - (Project)"."Project Name" = '... juli 2019') nqw_1 ))

         ) UNION ALL

         (SELECT "General - (Project)"."Project Object Id" saw_0, "General - (Project)"."Project Name" saw_1, "General - (Activity)"."Activity Id" saw_2, "General - (Activity)"."Activity Status" saw_3, CONCAT("General - (Activity)"."Activity Status", ' new') saw_4
               FROM "Primavera - Activity History"
               WHERE
                   ("General - (Activity)"."Activity Status" IN ('In Progress', 'Not Started'))

                   AND ("General - (Project)"."Project Object Id" IN
                         ( SELECT saw_1 FROM (SELECT "General - (Project)"."Project Name" saw_0, "General - (Project)"."Project Object Id" saw_1
                         FROM "Primavera - Activity History" WHERE "General - (Project)"."Project Name" IN ( '... juli 2019')) nqw_1 ))

                   AND ("General - (Activity)"."Activity Id" NOT IN

                         ( SELECT saw_0 FROM (SELECT "General - (Activity)"."Activity Id" saw_0,  "General - (Project)"."Project Name" saw_1

                        FROM "Primavera - Activity History" WHERE "General - (Project)"."Project Name" = '... juli 2019') nqw_1 ))

         )

    ) t1 ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4

    Which looks kind of like what I need. Only to receive this setting I've had to put "Activity ID" in the Prompt section. If I move it back to the Excluded section it generates the same error as before:

    A REPORT totalling function must be in a leaf query block.

    pastedImage_0.png

    Also, if I now try to generate a column graph it gives the following error:

    pastedImage_96.png

    So, yeah I have to drop at least one column. But as soon as I drag ANY column in the "exclude" part it gives the previous error.

    State: HY000. Code: 42038. [nQSError: 42038] A REPORT totalling function must be in a leaf query block

    Ideas?

  • Matthias_VC
    Matthias_VC Rank 3 - Community Apprentice

    Wait, found the answer online.

    I've had to put "Aggregation Rule" to None...

    pastedImage_1.png

    Hmmm now start over and just add month...

  • Matthias_VC
    Matthias_VC Rank 3 - Community Apprentice

    Okay finally, Mission accomplished!

    Thank you Jerry, for helping me go through this. Otherwise I would have undoubtably given up!

    pastedImage_0.png

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    I just did this simplified version of your analysis so you could see what I'm talking about.
    It is based on a list of 11 Project IDs.  The first one listed is "Proj A".  This is a pivot table with a nested chart:

    pastedImage_0.png

    This is the union.  No external (saw_x) columns were needed. "Proj A SubQ" is a simple query that lists the Activities in Project A (023625.00).

    pastedImage_1.png  pastedImage_2.png

    The pivot looks like this:

    pastedImage_3.png

    The SQL looks like this:

    SELECT

       saw_0,

       saw_1,

       saw_2

    FROM ((SELECT

       "Expenditures"."WIN_DOT" saw_0,

       "Expenditures"."Activity" saw_1,

       'In Proj A' saw_2

    FROM "FACT2 Expenditures"

    WHERE

    ("Expenditures"."WIN_DOT" IN ('023625.00', '022603.00', '021745.00', '022610.00', '022608.00', '021666.00', '018959.00', '021736.00', '021874.00', '021870.00', '022236.00')) AND ("Expenditures"."Activity" IN

    (

    SELECT saw_1 FROM (SELECT

       "Expenditures"."WIN_DOT" saw_0,

       "Expenditures"."Activity" saw_1

    FROM "FACT2 Expenditures"

    WHERE

    "Expenditures"."WIN_DOT" = '023625.00'

    ) nqw_1

    ))

    ) UNION (SELECT

       "Expenditures"."WIN_DOT" saw_0,

       "Expenditures"."Activity" saw_1,

       'Not in Proj A''' saw_2

    FROM "FACT2 Expenditures"

    WHERE

    ("Expenditures"."WIN_DOT" IN ('023625.00', '022603.00', '021745.00', '022610.00', '022608.00', '021666.00', '018959.00', '021736.00', '021874.00', '021870.00', '022236.00')) AND ("Expenditures"."Activity" NOT IN

    (

    SELECT saw_1 FROM (SELECT

       "Expenditures"."WIN_DOT" saw_0,

       "Expenditures"."Activity" saw_1

    FROM "FACT2 Expenditures"

    WHERE

    "Expenditures"."WIN_DOT" = '023625.00'

    ) nqw_1

    ))

    )) t1 ORDER BY saw_0 DESC

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    Great!  Your answer hadn't hit my inbox when I posted the above.

    Cheers!