Categories
- All Categories
- 104 Oracle Analytics News
- 9 Oracle Analytics Videos
- 14.3K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 58 Oracle Analytics Trainings
- 60 Oracle Analytics Data Visualizations Gallery
- 5 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Pivot table in BI Publisher

Summary
Can't change Formula value to 'No Formula'
Content
Hello guys,
I am struggling to change the function of measure section of my pivot table in BI Publisher. In OTBI, pivot table worked correctly. Using the same fields in BI Publisher, for some reason BIP automatically assumes COUNT function instead of default or NO function in field formula. Please see the images. I don't want BIP to count, I want BIP to show the Element Entry values as I can see in OTBI.
Can someone please help me to understand what I am doing wrong?
Thank you in advance,
Alvaro Couto
Comments
-
Hey Alvaro,
Maybe check your data format. If it's alpha, the system won't be able to perform numeric operations. You'll be able to quickly tell by the icon under the data source section. Please see attached image.
Thanks.
0 -
Hello Wade,
Please see my screen. Yes, the field is as Alpha-Numeric. What shoud I do? Do I need to convert it? Casting? This column in pivot table is the Screen_Entry_Value field from FUSION.PAY_ELEMENT_ENTRY_VALUES_F. It is originally an alpha column but it contains Date, Earning, etc. In OTBI Pivot Table worked sucessufully.
Could you please help me one more time?
Thank you,
Alvaro Couto
0 -
Hey Alvaro,
Yes, you will need to do this in the data model SQL.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions191.htm
0 -
(This answer is for OTBI not BIP.) For Input Value in OTBI, I have to perform a logic like this for numeric:
Case when "Input Value"."Input Unit of Measure Code" = 'M' then CAST ("Element Entry Value"."Displayed Input Value" as Numeric) when "Input Value"."Input Unit of Measure Code" = 'I' then CAST ("Element Entry Value"."Displayed Input Value" as Numeric) when "Input Value"."Input Unit of Measure Code" = 'N' then CAST ("Element Entry Value"."Displayed Input Value" as Numeric) ENDAnd logic like this for character:
Case when "Input Value"."Input Unit of Measure" = 'Character' then CAST ("Element Entry Value"."Displayed Input Value" as CHAR) when "Input Value"."Input Unit of Measure" = 'Day' then CAST ("Element Entry Value"."Displayed Input Value" as CHAR) when "Input Value"."Input Unit of Measure" = 'Date' then CAST ("Element Entry Value"."Displayed Input Value" as CHAR) when "Input Value"."Input Unit of Measure" = 'Time' then CAST ("Element Entry Value"."Displayed Input Value" as CHAR) when "Input Value"."Input Unit of Measure" like 'Hours' then CAST ("Element Entry Value"."Displayed Input Value" as CHAR) END0 -
Hi All
I found this old post that is similar to an issue I am having in BI Publisher.
I am trying to build a pivot table in BI Publisher to show colleague phone numbers and to display the Primary Phone Types field as a column rather than a row. I have converted the phone number to a character but I only have Count or Count Distinct formula options so I cant force it to display correctly.
Does anyone have any ideas how I can get around this please? Let me know if you need any further details.
Thanks
David
0