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

How to implement the below SQL in RPD

3174977

Hi All,

I have a requirement where I need to have two columns for my reports that are not available in the views/tables that we are using . In order to have those columns in the report we need to follow the below SQL. How can the below SQL logic can be used in RPD or the reports.

Please suggest me..

select d.project_wid,

(select sum(p.HRS) plan FROM dw_PLAN_TIME p where p.project_wid=d.project_wid) total_plan_hrs

,(select sum(a.HRS) plan FROM Dw_Pa_Hrs a where a.project_wid=d.project_wid) total_PA_hrs

from dw_project_d d

Thanks !

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

Hi,

Your SQL can't be used in the RPD just because if you use it you are making the whole concept of RPD useless (and paying an OBIEE license when SQL Developer could do the same for free).

Yours is probably the most simple model you can have: dw_project_d is conformed dimension and dw_plan_time and dw_pa_hrs are 2 fact tables (can also be included in the same fact logical table using 2 logical table source).

Set SUM as aggregation and you are done ...

3174977

Hi Gianni,

Please see the below . I think the same SQL logic is already implemented right? so whatever hours we pick from dw_plan_time and dw_pa_hrs tables satisfy that SQL condition ?

Am I missing something ?

[Deleted User]

Your model looks just fine. Both facts join to your dimension table so now you can put aggregation rules on the measures and then analyze them by dimensional attributes.

Proper execution is obviously also the creation of a dimensional hierarchy for the logical dimension itself and the subsequent setting of content levels for all sources.

3174977

Thanks Christian & Gianni,

One more question similar to this.. There is another SQl logic which needs to be implemented in OBIEE. How can the below SQL be transformed..

SELECT T.PROJECT_NAME,

T.TASK_NAME,

T.EMPLOYEE_NAME,

T.ENDING_DATE,

T.HRS,

SUM(T.HRS) ACTUAL,

0 FORECAST,

'A' STATUS,

(select SUM(T1.HRS)

FROM XXESC_PA_TIMECARDS T1

where t.PROJECT_NAME = t1.PROJECT_NAME

and t.TASK_NAME = t1.TASK_NAME

and t.EMPLOYEE_NAME = t1.EMPLOYEE_NAME

GROUP BY t1.PROJECT_NAME, t1.TASK_NAME, t1.EMPLOYEE_NAME) Total_Actual,

(select SUM(F1.HRS)

FROM XXESC_FORECAST_TIME F1

where t.PROJECT_NAME = F1.PROJECT_NAME

and t.TASK_NAME = F1.TASK_NAME

and t.EMPLOYEE_NAME = F1.EMPLOYEE_NAME) Total_Forecast

FROM XXESC_PA_TIMECARDS T

GROUP BY PROJECT_NAME, TASK_NAME, EMPLOYEE_NAME, ENDING_DATE, HRS

In this SQL I am clear on how to get the Actual hours..which means we need to get sum of all hours that are there in XXESC_PA_TIMECARDS table..And Total_Actual & Total_Forecast hours we are getting them only when project name, task name & employee name are equal ? Is my understanding correct ..If so how can this be implemented.

Thanks a lot !

Gianni Ceresa

Similar as before, you model your tables making the joins based on your conditions between xxesc_pa_timecards and xxesc_forecast_time, for the "Total_Actual" you can add a "by" in the formula.

3174977

Hi Gianni,

I have one more doubt.I have added a new view to my existing model. When I just use the columns from that view it is giving data..But when I combine the columns of that view with the other columns from other tables the data from that view columns are becoming null.Why is this happening ?

Any suggestions please.

Thanks !!

[Deleted User]

LTS content levels are not set correctly.

Also: a view? Yuck.

3174977

Hi Christian,

I have set up the LTS content but that is not working..View is created in OBIEE . In physical layer I am not giving any join conditions. In BMM layer I have added in LTS to my existing fact table. I tried giving join conditions in physical layer .But the joins are not making any difference.I don't know if I am missing something.

Thanks !

[Deleted User]

Your fact must be joined to *something* dimensional. Otherwise it's jsut a standalone table/view and even that needs to have a degenerate dimension in order for fact measures to be aggreagated along dimensional attributes

3174977

Hi Christian,

I have the fact joined to the dimension in both physical & BMM layer..Please see the below..Am I missing anything..

This is the Business diagram..

Please help me with suggestions..Thank you !!