Reporting and Analytics for ERP

Get Involved. Join the Conversation.


    Soumya Parhi
    Conditional Columns based on Parameter
    Topic posted May 16, 2019 by Soumya ParhiGold Trophy: 10,000+ Points, last edited May 16, 2019, tagged OTBI, Reports, Sample Reports, Tip 
    98 Views, 2 Comments
    Conditional Columns based on Parameter
    Report Layout change based on Parameter

    Hi ,

    I am using the "Receivables - Payment Schedules Real Time" subject area I want to have a parameter of Entered Currency / Functional Currency, based on which my report should either show Document Currency, Entered Amount Due Remaining (for Entered Currency) or Ledger Currency, Accounted Amount Due Remaining (for Functional Currency) but not all.





    • Aaron Leggett


      Have you tried using a presentation variable with hardcoded values and passing that to the column?


      1) define a presentation variable in dashboard prompts, such as @{CurrencyType}, with a hardcoded list consisting of Document Currency and Ledger Currency

      2) Write a custom column formula, such as: 

      CAST ( CASE
          WHEN '@{CurrencyType}{Document Currency}' = 'Document Currency' THEN "Document Currency"."Entered Amount Due Remaining"
          WHEN '@{CurrencyType}{Document Currency}' = 'Ledger Currency'   THEN "Ledger Currency"."Accounted Amount Due Remaining"
          ELSE NULL
      END AS CHAR(20) )

      Name the column header 'Amount' or something universal. You can also replicate this to have dynamic column headers. Replace the "Document Currency"."Entered Amount Due Remaining"/"Ledger Currency"."Accounted Amount Due Remaining" fields with whatever fields you are using. 

      3) Put the Report and Dashboard Prompt onto a Dashboard, run it and test - hopefully it should work. 




    • Manish Pesswani


      Try using the "column selector" view.

      This will provide a list of column names at run time to the user.

      Based on the column selected it will show the results.