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 edit column formula to apply filter and save for reuse?

User_60BGL

I want to create a series of columns with filtering for my colleague's easy use.

I know that filters can be saved, but I would like to explore how to set the filter in a column using a formula, as this would be more convenient and understandable.

For example, for Legal Employer, I want to create a column for Active Legal Employer that displays Legal Employer name that Legal Employee Active Status = Active. I want to achieve this using a column formula and save it to a shared folder for reuse.

I tried using the FILTER function, but it requires at least one measure attribute, which doesn’t cover all cases.

Is there any way to achieve filtering using a column formula?

Many thanks.

Find more posts tagged with

Report

Filter

column

OTBI

Accepted answers

All comments

Shankar-Oracle

Try the following and let me know, if this works for you:

First create an analysis with the below query. I saved this as LE_Filter:

SELECT
"Workforce Management - Worker Assignment Real Time"."Legal Employer"."Name",
"Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status",
DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status")
FROM "Workforce Management - Worker Assignment Real Time"
WHERE
(DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status") = 'A')

This analysis contains a filter on legal employer status (for active). Save the analysis.

Create another analysis with the columns of your choice. Put a filter on "Legal Employer"."Name". On the filter dialog, for Operator choose "is based on results of another analysis". For "Saved Analysis" column, select LE_Filter, for Relationship column select "is equal to any", and for "Use values In Column" field "Legal Employer Name". Check the results. I tested this with an analysis with the following query:

SELECT "Workforce Management - Worker Assignment Real Time"."Legal Employer"."Legislation", "Workforce Management - Worker Assignment Real Time"."Legal Employer"."Name", "Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status"FROM "Workforce Management - Worker Assignment Real Time"

After adding the filter, I see the following logical query in the "Advanced" tab of the respective analysis:
SELECT 0 s_0, "Workforce Management - Worker Assignment Real Time"."Legal Employer"."Legislation" s_1, "Workforce Management - Worker Assignment Real Time"."Legal Employer"."Name" s_2, "Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status" s_3, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Legal Employer"."Legislation") s_4, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status") s_5FROM "Workforce Management - Worker Assignment Real Time"WHERE("Legal Employer"."Name" IN ( SELECT saw_0 FROM (SELECT "Legal Employer"."Name" saw_0, "Legal Employer"."Status" saw_1, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status") saw_2 FROM "Workforce Management - Worker Assignment Real Time" WHERE DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Legal Employer"."Status") = 'A'
) nqw_1 ))

gclampitt

Or just a simple CASE statement

Case

when Legal Employee Active Status = 'Active'

then Legal Employer name

End

User_60BGL

@Shankar-Oracle

Thank you! I could follow the first step you provided, but I encountered some problem in step 2.

I created a new analysis and added Legal Employer Name and assignment count. However, I could not find the items in filter dialog e.g. operator, saved analysis, etc.

May you please show the screenshots or navigation? Also, I would like to ask if this filter will stay with the column, and can I save the column for reuse?