nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

OBIEE (12.2.1.4) I need an alternative to using a "union" in an analysis

Tom D

This is my first post, hopefully I explain myself thoroughly. I'm using OBIEE 12.2.1.4.

This is a scenario which I've made up. My real life scenario is very similar to this. I'm having issues using "unions" in my analyis and getting a correct Grand Total %.

So here goes....

I have a database table with Attempts and Completions for time intervals of "week1" through "week10".

I have a dashboard prompt which is a multi-selectable choice list in which you can choose one or more weeks (week1 through week10), represented by a Presentation Variable called PVWeekGroupA.

I have another similar dashboard prompt which is a multi-selectable choice list in which you can choose one or more weeks (week1 through week10), represented by a Presentation Variable called PVWeekGroupB.

The values in these presentation variables can overlap, i.e., you can have "week1" in PVWeekGroupA and PVWeekGroupB.

In the dashboard prompt for Week Group A, I choose week1, week2 and week3.

In the dashboard prompt for Week Group B, I choose week4, week5, and week6.

Jones completed 85 of 130 in weeks 1 through 3 (Week Group A), and 50 of 74 in weeks 4, 5, and 6 (Week Group . For the season, he completed 145 of 270 (noted by All Weeks in the Week column)

Smith completed 150 of 200 in weeks 1 through 3 (Week Group A), and didn’t play in weeks 4 through 6 (Week Group . For the season, he completed 180 of 250 (noted by All Weeks in the Week column)

Green didn’t play in weeks 1 through 3 (Week Group A), and completed 30 of 45 in weeks 4 through 6 (Week Group . For the season, he completed 120 of 190 (noted by All Weeks in the Week column)

The output should look like this:

		Qtr 1			Qtr 2			Qtr 3			Qtr 4			Grand Total
Name	Week	Att	Cmp	%	Att	Cmp	%	Att	Cmp	%	Att	Cmp	%	Att	Cmp	%
Jones	All Weeks	70	35	50%	60	30	50%	65	35	54%	75	45	60%	270	145	54%
Jones	Week Grp A	20	12	60%	25	15	60%	35	22	63%	50	36	72%	130	85	65%
Jones	Week Grp B	22	15	68%	23	15	65%	19	14	74%	10	6	60%	74	50	68%
Smith	All Weeks	100	85	85%	50	30	60%	40	25	63%	60	40	66%	250	180	72%
Smith	Week Grp A	50	30	60%	50	40	80%	50	40	80%	50	40	80%	200	150	75%
Green	All Weeks	45	30	67%	45	30	67%	45	30	67%	55	30	55%	190	120	63%
Green	Week Grp B	10	7	70%	15	7	47%	10	8	80%	10	8	80%	45	30	67%

I can get this to work by using “unions” in the analysis. I need Grand Total %, and from what I experienced and read about, there is an OBIEE bug when trying to get that Grand Total % while using unions in the analysis.

So I’d like a solution where I don’t need to use “unions” in the analysis.

If you'd need more information, please let me know.

Thanks,

Tom

Find more posts tagged with

OBIEE

Accepted answers

All comments

Jerry Casey

Before abandoning the union query you built, I'm curious about whether it was a two-leg union or a three-leg union.

In analogous situations, I've union-ed one query for Grp A, one for Grp B, and a third for "All Weeks".

The "All Weeks" query contains its own calculations on the whole time period, so the row percentages are correct.

Alternatively, you can turn to column filters for a non-union query.

In that case you'll have 3 separate columns for GrpA, GrpB, and All Weeks X 3 (for attempts, completions, and %)

Grp A column formula would look like : FILTER(Count(table.Attempts) Using table.Weeks IN (@{GrpA}{1,2,3}) This may need added apostrophes, depending on the data types. It may also need a sum rather than a count, depending on your data.

You can figure out the other 8 columns from there.

Jerry

Tom D

Jerry,

This would be a 3-leg union. All Weeks w/ Grp A w/ Grp B. The "All Weeks" Grand Totals for Att. and Cmp. are correct, but the Grand Total % is incorrect every time.

It seems to always take the % that is in the very first Att/Cmp/% group. In my example, it shows 50% for Jones, 85% for Smith, and 67% for Green.

The same is true for Week Grp A and Week Grp B Grand Total %.

I would like to use column filters, but my users don't want to see those "extra" columns. They want to see "All Weeks", "Week Grp A", and "Week Grp B" in the same column.

Any ideas on how to do this in the column formula?

Thanks for your response,

Tom

Jerry Casey

Hi Tom,

Can you provide more information about your data? You've described your table as covering weeks 1-10, but you're results show quarters 1-4.

Are Quarter and Week coming from a structured time dimension?

You've described your desired results in detail, but not what you're working with.

Jerry

Tom D

Jerry,

Think in terms of a football game for my example.

There are 4 quarters per game, 1 game per week. In my example, week is the only element in a structured time dimension.

Let me know if this is not clear.

Tom

Jerry Casey

I had assumed the "Qtrs" were calendar quarter, so you can see how unclear it was for me.

Starting over,

If the whole example is a football season, then I have to assume a single record looks something like:

Week, Qtr, Name, Attempts, Completions.

Because your GrpA and GrpB can overlap, I believe it has to be a union of independent queries.

My own practice is to place an identifier column in the first position of each union "leg".

So, the Grp A query starts with a text column with the formula of 'Group A', and is filtered on table.Weeks IN (@{PVWeekGroupA}['@']{'week1','week2','week3'})

the Grp B query starts with a text column with the formula of 'Group B', and is filtered on table.Weeks IN (@{PVWeekGroupB}['@']{'week4','week5','week6'})

the 'All Weeks' query has no filter on the Weeks column.

Start with the All Weeks query to ensure your calculation produce the correct percentages. Use a pivot table to get the desired result format.

M De Fanti

Hi Tom,

if I understand correctly you have to calculate a sort of an attribute "week", not filtered measures.

With the filtered measures solution, you will not be able to obtain exaclty the same layout you show in your example.

You say that "the values in these presentation variables can overlap, i.e., you can have "week1" in PVWeekGroupA and PVWeekGroupB", I do not see any other solution of using union query if you want exactly this layout and you permit the overllapping of the 2 groups.

Regards

Massimo

Tom D

Jerry,

I started with the All Weeks query (no unions) and the Grand Total %'s were correct.

I added a union with GroupA and the Grand Total %'s are incorrect, as I suspected. I used the filter that you mentioned above. (table.Weeks IN (@{PVWeekGroupA}['@']{'week1','week2','week3'}) ).

The percentage total for Week 1 (and Week's 2 and 3, for that matter) is correct. It's only the Grand Total % that is incorrect. BTW, the Grand Total Attempts and Completes are correct.

In the '%' Column formula, i have:

My pivot table aggregation rule for the '%' is as follows:

So, I'm at a loss.

There are several bugs that I'm aware of:

OBIEE Union Query Bugs.PNG

Any other suggestions are appreciated!

Tom

Jerry Casey

If the separate queries in the union are independent of each other, the "All Weeks" percentages shouldn't have changed.

The main purpose of the text label column that I mentioned is to separate the results. So, if column 1 (let's call it Bin) contained values of 'All weeks',

'GrpA', and 'GrpB' in the 3 queries, you use Name, and then Bin to define the rows in the pivot table.

When you now mention "Grand Totals" are you referring to "All Weeks", or another bottom line for the whole table?

If you are expecting a bottom line with the correct percentages, you would add a fourth query to the union that included all names and all weeks.

Copy and paste the 'All Weeks' query, and in the in the name column, replace the formula with the text 'Grand Totals'. (Turn off any totals in the pivot table)

Use conditional formatting (When Name='Grand Totals', etc.) to make it look like a totals line.

Tom D

Ultimately, I would like a "Grand Totals" for name="Jones" (and the other Names), for All Weeks and each Week Group. (you can see this in my original post). I would have to select "Column Totals" in the pivot table.

And i would like a bottom line "Grand Totals" for each "Qtr". I was able to get that working thanks to your previous post (with the conditional formatting, etc). Thank you.

And I would like a Grand Totals for each Name. So there would be a Grand Totals line below the Jones entries, below the Smith entries and below the Green entries.

See below:

		Qtr 1			Qtr 2			Qtr 3			Qtr 4			Grand Total
Name	Week	Att	Cmp	%	Att	Cmp	%	Att	Cmp	%	Att	Cmp	%	Att	Cmp	%
Jones	All Weeks	70	35	50%	60	30	50%	65	35	54%	75	45	60%	270	145	54%
Jones	Week Grp A	20	12	60%	25	15	60%	35	22	63%	50	36	72%	130	85	65%
Jones	Week Grp B	22	15	68%	23	15	65%	19	14	74%	10	6	60%	74	50	68%
Jones Totals		70	35	50%	60	30	50%	65	35	54%	75	45	60%	270	145	54%
Smith	All Weeks	100	85	85%	50	30	60%	50	40	80%	60	40	66%	250	180	72%
Smith	Week Grp A	50	30	60%	50	30	80%	40	25	63%	50	40	80%	200	150	75%
Smith Totals		100	85	85%	50	30	60%	50	40	63%	60	40	66%	250	180	72%
Green	All Weeks	45	30	67%	45	30	67%	45	30	67%	55	30	55%	190	120	63%
Green	Week Grp B	10	7	70%	15	7	47%	10	8	80%	10	8	80%	45	30	67%
Green Totals		45	30	67%	45	30	67%	45	30	67%	55	30	55%	190	120	63%
Grand Totals		215	150	70%	155	90	58%	160	105	66%	190	115	61%	710	445	63%

Tom D

Massimo,

How about if I do not permit the overlapping of the groups? Can I still use the unions and get the Grand Total %'s working correctly/

Thanks,

Tom