Oracle Analytics and AI

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

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

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.

Wolfrm

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

Thomas Dodds

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.

Wolfrm

Thank you, Thomas, but I do not have administrator privileges.

Michael Verzijl

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?

Wolfrm

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.

Michael Verzijl

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:

Screen_ 20160920 14.36.14.jpg

Modelling this correctly in your environment would be much better though.

Thomas Dodds

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.

Wolfrm

Thanks for replying, Thomas, but your critique helps me in no way to produce the report I need to produce.

Gianni Ceresa

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.