Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    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
    Title:
    Conditional Columns based on Parameter
    Summary:
    Report Layout change based on Parameter
    Content:

    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.

    Thanks,

    Soumya

    Comment

     

    • Aaron Leggett

      Soyma, 

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

      E.g.

      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. 

      Thanks

      Aaron

       

    • Manish Pesswani

      Hi,

      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.