Oracle Transactional Business Intelligence

Products Banner

Pivot table in BI Publisher

131
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

  • 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

  • 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

  • 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

  • (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

     

  • 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