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

OTBI - Edit column formula to include a filter

Ruby Hui

Is it possible to edit the column formula so that it will only show values with specific criteria?

More specifically, I'm trying to add in AOR Responsibility information and would like to bring in the names of the AOR representatives for only 3 Responsibility Types.

Currently, because we have several responsibility types, it duplicates the rows of the rest of the data in the report. We have been using Simple Logical SQL to bring in three separate columns using unions.

The hope is I can edit the formula in Representative Name to show only the name for Responsibility X in one column, add the Representative Name column again and show a different name for responsibility Y, and add the Representative Name column again showing another name for responsibility Z.

Thank you!

Find more posts tagged with

Reporting

Filters

OTBI

Accepted answers

Raghavendra Raghunath-Oracle

Hi @Ruby Hui Alternatively you can do that using case statements. i.e. you will have 3 columns with 3 different headers. for ex: Benefits Rep, HR Rep, Payroll Rep. In the column formula you will display the name only if it matches the corresponding name i.e. Case When Rep Name = 'HR Rep' THEN employee name else null end

However, with this approach, i suppose the data will display in different rows, give it a try

Raghu

All comments

Ashraf

Hi, i am facing a sought of similar issue in OTBI, where

I only need to pull Representative Name of responsibility type 'HR Representative'.

if, a person is not having a responsibility type of 'HR Representative', that person is not getting pulled in my report.

Is there a way to pull these person who don't have responsibility type of 'HR Representative' as well as those who have responsibility type of 'HR Representative'?

Thanks.

Raghavendra Raghunath-Oracle

Hi @Ruby Hui can you illustrate using a spreadsheet? refer the attached spreadsheet

AOR.xlsx

Raghu

Ruby Hui

Hi @Raghavendra Raghunath-Oracle

I've attached the spreadsheet updated with what I'm looking for.

Thank you for looking into this!

Regards,

Ruby

AOR.xlsx

Raghavendra Raghunath-Oracle

Hi @Ruby Hui unzip and unarchive the attached catalog. Modify the query to use your representative names.

Raghu

AOR Analysis.catalog.zip

Ruby Hui

Thank you Raghu,

So to confirm, we would have to do so through a pivot table view and can not edit the formula to show only the names for a specific representative type within a data table view?

Regards,

Ruby

Raghavendra Raghunath-Oracle

However, with this approach, i suppose the data will display in different rows, give it a try

Raghu

Ruby Hui

Thanks @Raghavendra Raghunath-Oracle

You're right, as it will display in different rows.

This was all a roundabout way of seeing if we can pull in AOR to different types of reports without producing multiple rows.

The way we are currently doing it is to use logical SQL and use a join to any existing otbi analysis that we may need to add AOR to. I was just curious if there was a way we could do it directly in the analysis itself.

Thanks again,

Ruby

Melek Cinar

Hello @@Raghavendra Raghunath-Oracle,

Resource Assignment Cost Split Report.docx

We have resource assignment report and our aim is to join Project Resource Assignment and Worker Assignment, in order to calculate each employee ‘s project assignment percentages each month and split payroll cost to projects accordingly. We have included the XML and SQL codes of the report in the document, along with detailing how we want the computation to be. Your suggestion would be greatly appreciated and very helpful.

Thank you.

Regards,

Melek