Oracle Communities

Oracle Analytics and AI

Child Item

Analysis measure based on external filter

I'm having an issue in Primavera Analytics(which uses OBIEE software as a basis)
It's a cloud implementation and I have no way of changing the Datamodel so I'd like to solve it with the Reporting Programs abilities only.

So what I'd like to have is a measure that looks something like this:

COUNT(DISTINCT "Primavera - Activity History"."General - (Activity)"."Activity Id")
WHERE "General - (Activity)"."Activity Id" IN
(SELECT "General - (Activity)"."Activity Id" FROM "Primavera - Activity History"

WHERE "General - (Project)"."Project Name" = @{varProject})

In other terms:

I have a visual where I'm trying to see how many "Activity IDs" the project has in common with the project I selected in a prompt elsewhere.

Simply filtering the entire visual is no problem. But I'd like to know the amount in common AND the amount NOT in common.

- Is it possible to have subqueries in the select part of the SQL?

- If necessary I'm able to generate the list in a visual. Can I transform the results of this visual into a presentation variable and so I only have to count those that are in the presentation variable? What is the size limit of a presentation variable?

Kind regards,

Matthias

Find more posts tagged with

Accepted answers

All comments

Jerry Casey

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.

Matthias_VC

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

too 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

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

Okay,

Filtering helped in trying to create a pivotTable.

I was able to get the following result:

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 - (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.

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

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

Wait, found the answer online.

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

Hmmm now start over and just add month...

Matthias_VC

Okay finally, Mission accomplished!

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

Jerry Casey

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:

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).

The pivot looks like this:

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

(

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

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

Cheers!