Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
using filters in logical layer (bi Adminitration)
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!
Answers
-
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.
0 -
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
0 -
Yes I didn't mean "take a petri dish, dump the UDML inside and incubate under a lamp" :-P
0 -
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!
0 -
haha Thanks!
0 -
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!
0 -
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
0 -
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...
0 -
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?
0 -
NULL != '' != 'null' != 0
0
