Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Pivot table in BI Publisher

244
Views
5
Comments

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

BIP_PIVOT_FORMULA_ONLY_COUNT.JPG

Comments

  • Wade Wilson
    Wade Wilson Rank 4 - Community Specialist

    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.

    pivot.png

  • Alvaro Couto
    Alvaro Couto Rank 4 - Community Specialist

    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

     

    Issue_PivotTable_BIP.JPG

  • Wade Wilson
    Wade Wilson Rank 4 - Community Specialist

    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

  • Gail Langendorf-Oracle
    Gail Langendorf-Oracle Rank 4 - Community Specialist

    (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)  END

    And 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) END

     

  • DavidHarrison
    DavidHarrison Rank 1 - Community Starter

    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