Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
UNAVAILABLE FUNCTIONS FROM BRIO TO OBIEE 12c
One problem I encountered while migrating our Brio report to OBI. There is ColMax function within Brio to return the largest value in a column of numbers.
e.g
ColMax (Amount, State) as Computed
State City Amount Computed
AZ Tucson 112 112
CA Burbank 240 240
CA Glendale NULL 240
NY New York 490 490
NY Rochester 180 490
Is there any way I can implement this in OBI?
Thanks
Answers
-
Can you define the exact behaviour and logic of "ColMax" ?
Because "return the largest value in a column of numbers" is just what MAX() gives you, but sounds too easy as an answer...
0 -
3744064 wrote:One problem I encountered while migrating our Brio report to OBI. There is ColMax function within Brio to return the largest value in a column of numbers.e.gColMax (Amount, State) as ComputedState City Amount ComputedAZ Tucson 112 112CA Burbank 240 240CA Glendale NULL 240NY New York 490 490NY Rochester 180 490
Let me see if I get this:
Do the 240 for CA and 490 for NY mean those are the maximum (MAX as Gianni said) for the hierarchy level NY which is the parent hierarchy level on top of Burbank/Glendale for CA and New York/Rochester for NY?
If so then you will need
a) Dimensional hierarchies with states and cities at miminum
b) a level-based measure with aggregation rule MAX tied to the dimensional level "State"
0 -
Or MAX(... by ....) as "quick-win"
0 -
Please let's not start him off on the worst way possible to do things and cram all logic into front-end objects, alright?
0 -
Yes, I need the max by a breakcol; but I can´t use that sintaxis in OBIEE . In this case MAX(Amount by State) but OBIEE does not allow that sintaxis. Do you know another solution? Thanks
0 -
If you have access to the rpd and your state is in a dimension hierarchy level then you can create a MAX version of the measure in question and pin it (drag it or copy it) to the level in question, then you will have max that is invariant for the state in question.
0 -
Or - you can use sections in your table / pivot table and have a Max function at each break, then you get both the max by state and the max (of all).
0 -
Sorry, thought I remembered you could use BY on the other aggregate functions also, but struggled to find a reference, but this one says you can do Max( by or Avg( by or you get the idea (by
0 -
Thanks Angel but OBIEE 12c doesn´t aloud the syntax (by)
0 -
Hate it when they do that - that was really useful functionality!!
You could also achieve similar by using the FILTER function in conjunction with your state and any other parameters filtering.
0
