OBIEE Analysis- Involving Union - Result Column - Calculated Formula

Cameron Loepker

Hello Board,

OBIEE Version: Oracle Business Intelligence 12.2.1.3.0

Encountered an analysis that needs updated with two additional columns. Went into said analysis and found that it was performing a union, fine. When I add the SUM(0) to one of the union's datasets, and SUM(column) to the other dataset, I am getting an error that says it does not recognize 'SAW_15' - which I assume is an alias for the new column I am trying to create.

Is there a way to get the report to recognize that new column?

How do I create the logic that goes in the 'result column' similar to the other already existing calculated column which houses this logic inside the formula:

ifnull(sum(saw_8 by saw_1,saw_13),0)+ifnull(sum(saw_9 by saw_1,saw_13),0)+ifnull(sum(saw_10 by saw_1,saw_13),0)+ifnull(sum(saw_11 by saw_1,saw_13),0)

This looks like some internal BI code, but unsure how to get this for new column as well as make the new column play nice with existing columns.

one note: the column that I want to add is already in the first calculated column, but will be doing different logic to the new addition.

please let me know if this does not make sense.

thanks,

Cameron

[Deleted User]

Hi Cameron,

UNIONs are always less than optimal approaches. Why do you need that union? Is the data really completely disparate or is it just because it comes from different stars which haven't been conformed correctly in the RPD?

Jerry Casey

Hi Cameron,
That's a message I've seen many times.  When you add a new column to the component parts of a union, the header for the new column appears in the right-most position, outside the calculated "Results" columns. 
You then have to move that header to the inside of the "Results" columns that contain the "saw_" syntax.
If your new column is the 10th column in the component queries, it has to be in the 10th position in the header line.

I'm not disagreeing with Christian, but it sounds like a very familiar situation.

[Deleted User]

You're right of course, Jerry. It's just that a system is more sane the less unions it has

Cameron Loepker

Hi Christian,

thanks for your timely response. I am not entirely sure which scenario it is as this point. I have a hunch that the RPD was not conformed correctly. Am going to dig in a bit further and see if i can figure that out.

Cameron Loepker

Thanks, Jerry.

Will follow that and make a copy of the report and try to implement. Will come back and let you know my findings.

thanks very much.

Jerry Casey

Sanity would put me out of a job

Cameron Loepker

Not having anyluck yet.

maybe answering this portion could help me understand the result column origins for the existing measure column.

So when I add each component column to each data set of the union, and come back to the headline columns , i don't see my new column set as a measure (no gold bar- only blue and white) - so that is what leads me to hit 'Add Result Column' where a window pops up which wants a formula. I have no idea how to code this formula.. in the other existing measure the formula is : ifnull(sum(saw_8 by saw_1,saw_13),0)+ifnull(sum(saw_9 by saw_1,saw_13),0)+ifnull(sum(saw_10 by saw_1,saw_13),0)+ifnull(sum(saw_11 by saw_1,saw_13),0).

this looks generated? or did someone just know what they were doing and manually code this?

Jerry Casey

Those columns are written as part of the analysis.

The saw_x syntax allows you to reference the columns of the union without typing out the column formulas (which may differ from one leg of the union to another, anyway).  "saw_o" is the first column on the left, then they're sequentially numbered to the right.  It's actually a very powerful tool.

If I take this simple union:

I can get the Max amount by Activity from both legs by adding a Result Column:

If I want to add Region to the Analysis, I add it to the end of both legs, and the header looks like this:

That's when I have to drag the header for the new column to its correct position, or I'll get a "non-existent" column"  error:

Your first error message is specifically generated from a failure to do this. 

The subsequent error must be a different message?

The Results column formulas become incorrect if the column order is changed in any way that effects the saw_x references.

Cameron Loepker

Would there be any confusion if you are using the same column for the measure in two different columns? in the first location there is a plain SUM, with a filter on the whole data set limiting by a certain revenue code. in the column I am trying to create, I was planning on putting a different revenue filter code on just that particular column, and the more and more i think about it, am thinking those filters will not play nice with on at the dataset level and one at the column level - with same column in mind for both purposes.

Jerry Casey

What you describe sounds like the filters could conflict.  You can use two separate "formula filters" (not sure what they are officially called) on the same measure in two separate columns.  By that I mean the FILTER function available within the column formula editor.
In your case, you can have one column:  FILTER(MeasureA USING RevCode='XXX')  and another: FILTER(MeasureA USING RevCode='YYY').  The results will still be pre-limited by any inline filters (in the "Filters" window at the bottom of query) you have on "RevCode".