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

using filters in logical layer (bi Adminitration)

Alex1

Hello experts.

I have a table with some columns, this table has a Logical Table Source. I know that if I apply filter in logical table source in where section I can filter data that I want.

My question is if I can filter only one column of that table. For example:

I have a column with different Centers "Center Column". I would like to create a measure that only show values of specific center:

"Amount Filtered by Center A" some kind of filter inside with only values when is Center A

"Amount Filtered by Center B" some kind of filter inside with only values when is Center B

Is possible to config this?

Thanks!

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

Second LTS; basically a clone of the first one but with a WHERE clause. And then only map the column you want filtered. All other columns remain mapped only on the original LTS

edit: And before someone yells "this can be done differently": you can also just make 2 measures using CASE WHEN statements.

Gianni Ceresa

Christian Berg wrote:... basically a clone of the first one ...

Which translate into: right click on the LTS and choose "duplicate", then set the mappings as Christian said

[Deleted User]

Yes I didn't mean "take a petri dish, dump the UDML inside and incubate under a lamp" :-P

Alex1

Thanks @Christian Berg for your answers.

I'll try about second LTS. By the way could you give an example about duplicate measure and create a CASE WHEN statement?

Thanks!

Alex1

haha Thanks!

[Deleted User]

CASE WHEN "Dim - Centers"."Center Name" = 'Center A' then "Data_Table"."Amount" else NULL End

By the way "Data_Table" is a horrible name for a logical fact!

Gianni Ceresa

The example of "duplicate measure with a CASE WHEN" is : right click on the logical column and select "duplicate", then edit the source of that column to add a formula (so instead of being just a reference to a physical column you go into the expression editor). The formula is just : CASE WHEN <your condition> THEN "the reference of the physical column giving the value" END .

This column will return you values only when the condition is met.

How to select between the LTS or the CASE WHEN? Think at how your physical query will be impacted. With a LTS it add a WHERE condition to the query, which means it returns only the rows matching the condition. With the CASE WHEN it will not have a WHERE condition, so it apply the CASE WHEN on all the rows of the physical source returning NULL (as there is no ELSE in the CASE WHEN) for rows not matching the condition.

So it can have an impact on performance depending on a bunch of factors (what else do you have in the analysis, indexes etc.).

edit: of course it takes longer to write some "blabla" around and not just a CASE WHEN like Christian did

[Deleted User]

One day you will become a master yourself, young padawan!

That is if you don't switch to the dark side a kill a horde of younglings...

Gianni Ceresa

Well ... sometimes I write a bit faster, just enough to hijack your replies and add some extra (not requested) info all around

I would just say I personally prefer to not have the " ELSE null " as just the default behaviour or any normal database around, so it's written for nothing (but maybe somebody will replace the "null" with a '' thinking it's the same, and there issues start ...).

PS: you didn't think I would let you the last word, did you?

[Deleted User]

NULL != '' != 'null' != 0