How to Show Phone Numbers on Single Row in OTBI Analysis Table (no pivot)

kstreit

I have been trying to split the field "Person Phones"."Search Phone Number" (Under the Workforce Management - Person Real Time subject area) into separate fields for Phone (Home) and Phone (Work). I want to have just one line per person.

The only fields used in the report currently are Person Number, Phone (Home), and Phone (Work), but the phone numbers are showing on separate rows, rather than one line.

** I will need to add in several more fields from multiple subject areas, so I do not believe a Pivot Table will work as a workaround. **

For the home phone column, I am using the formula:
CASE "Person Phones"."Phone Type Code" WHEN 'H1' THEN "Person Phones"."Search Phone Number" END

For the work phone column, I am using the formula:
CASE "Person Phones"."Phone Type Code" WHEN 'STF_SLOC' THEN "Person Phones"."Search Phone Number" END

Can you please help me achieve one row per person, with Person Number, Phone (Home), and Phone (Work), etc?

Screenshots…

What I see:

vs What I want to see:

gclampitt

just add a max round your case statment
max(case when "Person Phones"."Phone Type" = 'Home Phone' then "Person Phones"."Phone Number" end)

gclampitt

Shankar-Oracle

Could you please share the logical SQL query from the "Advaned" tab of the respective analysis?

kstreit

Sure, here you go. Keep in mind this is just a test report for a larger report that needs those items. And I x'd out the person number.

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT
0 s_0,
"Workforce Management - Person Real Time"."Worker"."Person Number" s_1,
CASE "Workforce Management - Person Real Time"."Person Phones"."Phone Type Code" WHEN 'H1' THEN "Workforce Management - Person Real Time"."Person Phones"."Search Phone Number" END s_2,
CASE "Workforce Management - Person Real Time"."Person Phones"."Phone Type Code" WHEN 'STF_SLOC' THEN "Workforce Management - Person Real Time"."Person Phones"."Search Phone Number" END s_3
FROM "Workforce Management - Person Real Time"
WHERE
("Worker"."Person Number" = 'xxxxx')
ORDER BY 2 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST
FETCH FIRST 250001 ROWS ONLY

Shankar-Oracle

Based on a demo/Vision pod I checked on, following logical query is working for me, and I am getting one row per person:

SELECT 0 s_0, "Workforce Management - Person Real Time"."Worker"."Person Number" s_1, CASE "Workforce Management - Person Real Time"."Person Phones"."Phone Type Code" WHEN 'W1' THEN "Workforce Management - Person Real Time"."Person Phones"."Search Phone Number" END s_2, CASE "Workforce Management - Person Real Time"."Person Phones"."Phone Type Code" WHEN 'W2' THEN "Workforce Management - Person Real Time"."Person Phones"."Search Phone Number" ELSE "Workforce Management - Person Real Time"."Person Phones"."Search Phone Number" END s_3FROM "Workforce Management - Person Real Time"

kstreit

Thanks. How do I remove the ORDER BY clause from the Advanced tab? Sorry, new to this!

Shankar-Oracle

There is a way to play with the default "order by", but that option is not available from the front end.

We need to tweak with ORDERBY_SUPPORTED flag through the RPD (OBIEE repository file in the back end with extension .RPD. This is just referred to as RPD in the common parlance).

Please refer to section 14.6.8 of the below document:

https://docs.oracle.com/middleware/1221/biee/BIESG/deploylocal.htm#BIESG1774

kstreit

Yikes. Ok, thanks again. Sounds like that could have a big impact on performance, so not sure my team would want me changing that, lol.

kstreit

@gclampitt Thank you! That works in my simple report. Now I just need to get it to work in my more complex multi-subject area report. When I add put the max formula in there, it just comes back blank…but that is probably just user error, lol. I will keep at it!