Hello everyone,
Background - I'm currently working in Oracle Business Intelligence 12.2.1.2.0. I'm working at converting reports from Hyperion as it is near it's end of life. I'm rewriting hundreds of reports into OBI analyses. I don't have access to view RPD's and am working only in the presentation layer.
I'm running into a few problems as I try to mimic what had been done previously in Hyperion. Especially when it comes to computed items/column formulas/calculated measures.
For example - I have an employee/employee ID that has string values from a column/field. In Hyperion I could use the sum function and use the ID as a break point so I could essentially string "One, Two, Three, Four" together depending on how many values were linked to the employee. OBI doesn't have that option in the Sum function and CONCAT states that it is only for combining two strings. Is there a way for me to CONCAT strings and/or numbers by unique ID?
Also I have a lot of custom formulas where I look at string values and assign them numbers. I do this via CASE statements typically. I then have a need in a new column to sum those numbers. For example below - the case statement is displaying the correct numbers in the column on the left. I am able to then sum those numbers correctly (25) using sum in the reports tab via aggregation rule (see second picture). I then have a computed item that references that same case statement on the right. However there is some aggregation that is happening causing my numbers to duplicate depending on the scenario. In this case instead of the number being 25 the record is somehow aggregating four times and shows 100. Is there a way for me to ignore the aggregation ONLY for this measure or is there a way for me to reference the sum value (25) from the "Total" row and use it again in other formulas?
I'll take any advice and best practice tips I can get as I'm learning OBI on my own without any real training from my organization. Thanks for any help you can give me or any resources that people can point me in the direction of that help users like me in converting Hyperion queries.