How to Create a Total Column

JA1

I'm trying to create a "Total" column with the summation of two values from two different columns. Below is an example:

How can this be accomplished in OTBI? I tried using the SUM() functionality but I would receive a bizarre value. It was alluded to use the Pivot Table functionality but wasn't clear how to accomplish that either. I am using the Reported Time Cards Real Time Subject Area.

Thank you in advance!

Nathan CCC

But then you need to do nothing. The subject area already provides you with a column for that. User interface column "Total Hours" = subject area presentation column "Reported Time Cards"."Reported Hours" ?

Nathan CCC

To add 2 numbers together from 2 fact columns use operator "+".

for example

select all 0 s_0

, "Reported Time Cards"."Absence Hours" as fact_1

, "Reported Time Cards"."Labor Hours" as fact_2

, ifnull("Reported Time Cards"."Absence Hours",0.0)+ifnull("Reported Time Cards"."Labor Hours",0.0) as fact_total

from "Workforce Management - Reported Time Cards Real Time"

fetch first 7 rows only

JA1

Thank you @Nathan CCC .

Is there a way to accomplish this without SQL?

Nathan CCC

Hi JA1, No. Everything you do in OTBI to get data is SQL. All analytics and reports in OTBI query the databases using SQL. Either A) logical SQL like this to query using subject areas from the metadata repository database RPD either i) in an analysis in OTBI or ii) in a report in OTBI with a data model with a dataset using data source analysis or oracle bi ee (the RPD will convert this logical SQL to 1+ physical SQLs to get the answers) -or- B) physical SQL in a report in OTBI with a data model with a dataset with a data source query from one of the 3 application databases CRM, HCM, FSCM. So you MUST user SQL for all and any analytics and reports in OTBI.

But your user interface can help you. If you open your analysis on the criteria page, drag any presentation column, select edit formula, delete the existing presentation table presentation column, then you can use the f(x) button to help you do formulas, and the column button to select existing columns. So you can generate your desired SQL in this example without actually typing any SQL by clicking buttons in the user interface.

BTW Reported time is already the total of absence time + labor time. I am not sure why you want to add reported time + absence time. For example, row 1 was 0 absence hours + 80 not absence labor hours = 80 reported hours, row 2 is 8 absence hours + 75 not absence labor hours = 83 reported hours etc. If you add 83+8 then you are double counting absence hours which makes no sense to me?

Reported Hours is a calculation in the RPD Reported Hours = absence hours + labor hours

from view

view HWM_TM_RPT_ENTRY_V
https://docs.oracle.com/en/cloud/saas/human-resources/24b/oedmh/hwmtmrptentryv-3971.html

which is a union of group type 100 (labor) and 105 (absence) from tables
FROM HWM_TM_REC TimeEntryTimeRecordPEO,
HWM_TM_REC_GRP_USAGES TimeRecordGroupUsagePEO,
HWM_TM_REC_GRP DayTimeRecordGroupPEO,
HWM_TM_REC_GRP TimeCardTimeRecordGroupPEO

so as you can see if you view log in manage sessions the calculation is

Reported Hours =

nvl( TE_MEASURE_ABSENCE D1.c2 , 0)

+ nvl( (case when TE_UNIT_OF_MEASURE = HR (hours) then TE_MEASURE_LABOR else null end) , 0)

FYI Oracle has provided you with 2 "filtered" measures to give you either absence or labor time. But imagine it had not done so. You could have got the same result by create a pivot view table to pivot a yellow fact measure by a blue dimension attribute(s) which can have row and column totals. But for that you would need a fact that is already the total with an an attribute that says whether each fact is either absence time or not absence labor time. So you can use fact "Reported Hours" with an attribute to pivot back out into into "labor hours" group 100 and "absence hours" group 105. Not sure which attribute column says it is labor or absence. Have a look in your data lineage spreadsheet for this subject area at docs.oracle.com.

JA1

Thank you @Nathan CCC

We were trying to pull in the "Total" in order to replicate the Team Time Cards app in Oracle, so that is why I was pursuing the "Total" calculation.

JA1

You are correct. I was confused by the "Recorded" label within the app vs "Reported" label within OTBI.