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
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Custom column with a filter that selects a single row
Usually what one receives from OBIEE is this:
| Name | Address |
|---|---|
| John | address1 |
| address2 | |
| address3 |
Would it be possible to create three custom columns that each return a single one of those rows? So I would have three columns, each returning a single address record?
Something like this:
| Name | Address1 | Address2 | Address3 |
|---|---|---|---|
| John | address1 | address2 | address3 |
Answers
-
If you have a way to identify row 2 from row 1 and 3 yes.
So do you have a way to identify which one is row 1, which one is row 2 and which one is row 3?
As these aren't really measures I'm not sure if FILTER(... by ....) works, but for sure you will be able to model it in your RPD.
If you had to write it by hand in SQL, how would you write it? Just do the same in OBIEE, build it in a way to generate the same kind of query.
0 -
I don't have a direct way available in the database to identify each row but I'm using RCOUNT to 'rank' the addresses in a custom column like this: RCOUNT("Address" BY "Client ID"). This creates a custom column which has a number corresponding to each address:
Client ID Name Address rank Address 1234 John 1 address1 2 address2 3 address3 0 -
Use of information drive physical data model design ... build a view/table that puts the address components into their own columns. The database is much better at doing this than the BI or Presentation server.
0 -
Thank you, Thomas, but I do not have administrator privileges.
0 -
This can be a solution, but will probably end up in mixed address parts.
For example you will get: Amsterdam, Netherlands but also on other rows Netherlands, Amsterdam. Does this have business value for you?
0 -
Okay, so how could I use this to filter the column?
Re- business value: it might, but I would have to try it and see the results first.
0 -
For example below formula can be used:
Address 1 = min(case when rank("ADDRESS_FIELD) = 1 then "ADDRESS_FIELD" end by "USERNAME")
Address 2 = min(case when rank("ADDRESS_FIELD) = 2 then "ADDRESS_FIELD" end by "USERNAME")
The following output will then be provided:
Modelling this correctly in your environment would be much better though.
0 -
You don't need administrator rights, you need to run a BI project to make the change. Who's building BI content without use of information requirements? IT departments that foist BI content on the business are doomed to fail - your posts prove it yet again.
BUSINESS Intelligence - not PROGRAMMER Intelligence.
The fact star you have is deficient (your dimension is not properly formed and attributed) - your use case cannot be met by the model and thus you are forced to do things inefficiently or not at all.
0 -
Thanks for replying, Thomas, but your critique helps me in no way to produce the report I need to produce.
0 -
Take it more like a mid-long term advice ...
A bit along the lines of : give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime.
0
