How do I multiply the results of a column from one analysis with a result of a column from another a

3423849

Hello

For technical reasons, there are two columns I can't have in the same analysis.  However, I need create a column in another analysis that multiplies the two values together.

I have gone to the 'Edit Column Formula' diaglog but I cannot figure out a way to pick the results of the other analysis.  The column itself in the other analysis is already a calculated item so I can't just pick it from the list of available columns.

Is anyone able to help or advise if this is possible?

Many thanks

Michael.

FTisiot

For technical reasons, there are two columns I can't have in the same analysis.  However, I need create a column in another analysis that multiplies the two values together.

that's not a good way of starting your description... if I have to follow your reasoning:

1st sentence says: I can't have ColA and ColB in the same analysis

2nd sentence says: I need an analysis with both....

....those are in contradiction...

what are the technical reasons preventing you to have both?

You should have the option of adding column coming from different subject areas within the same report.

If the columns are coming from different SA and different BM your quickest option is a UNION.

But... before going to the UNION i would try understanding WHY the two columns can't be in the same report

Gianni Ceresa

Aren't your technical reasons that you didn't setup content level correctly to define the conformed dimensions etc. ?

Because if you think at your need from a row point of view you see that there is something that can't work: you want colA from an analysis next to colB from another one, but things are put one next to each other at a row level, so there must be rules saying how a row with with both colA and colB must work. That's generally what content levels, conformed dimensions and multiple facts give you as a result.

3423849

Thank you for your reply.

I should say that I am not involved in the technicalities of the data warehouse behind the scenes or how the respositories have been built; my role is to develop analsyes and dashboards with the available subject areas/columns to replicate the information that is currently available in another system we will be decommissioning.  I have attended the 'Building Analyses and Dashboards' course but that was 9 months ago so my knowledge from that time is a bit sketchy.

Column A and Column B cannot be in the same analysis because the analysis that containts Column B also includes Column C and D, and Column C and D aren't 'compatible' with Column A, so Column A shows no results when you include Column C and D (in an ideal world I would be able to 'force' Column A to show the results, regardless of what other columns are in the same analysis).

So to get a value in Column A, I need to put Column A in an analysis on it's own.  However, I still need to use the values from Column A in the analysis that containts Column B, C and D and Column E needs to be the formula (Column E*Column A).  But because I can't put Column A with Column B, C or D, I cannot then add Column E, which I need!

Apologies if I have not explained myself very well. This is the first time I have developed reports, and the first time I have developed reports in OBIEE, so I'm basing everything on the building analsyes and dashboards course I attended as well as material I can find online.  There's a good chance I could be looking at this the wrong way!

Best wishes

Sherry George

One possible way to achieve this will be calling a database function (with passing the required params) to get colA value in the analysis containing colB.

3423849

Hi Sherry

This was something I had thought of but I could not figure out how to do it.  It would be good if I could give the value of Column A a name or reference that I can then use in the column forumla of Column F in my second analysis, so it ends up as (Column F*TheResultOfColumnA).

Best wishes

Sherry George

Since I do not know exactly what your model and report is, I'm limited in my answer. What I'm suggesting is, in your second analysis which has colB, colC, colD create a column and use 'Evaluate' function in the formula to call the database function that you have created which will return the value of colA.