Solved

NVL in Business Reporter

  • 12 January 2021
  • 5 replies
  • 286 views

Badge +5

Hi All,

 

Is it possible to use NVL function in Advanced filter in Business reporter?  

I got a customer requirement to have the ‘Branch’ (code_b) parameter to 

Leave blank for all, select one or multiple in a BR report.

Do you have any suggestions to achieve this in BR?

 

what I tried is 

  • I defined ‘Branch’ as an optional parameter.

 

  • and used it in the Advanced filter as shown below.

 

  • it was giving the below error

     

Thank you!

icon

Best answer by addbjorns 15 January 2021, 10:15

View original

This topic has been closed for comments

5 replies

Badge +4

Good! :grinning:

And your way of solving this with a mixture of single/multiple values points is more flexible. Just the thing for a report I am working on.

Badge +5

Yes, your approach was correct.  I was able to achieve what I wanted.

what I did was

  • Define &Account as multi value parameter
  • Define &NewAccount Functional parameter as explained above
  • Use advanced filter with (DIM_ACCOUNT.CODE IN &Account or DIM_ACCOUNT.CODE LIKE &NewAccount)

Thank you!

Badge +4

Hi - I am also tring to figure  out how to do things the “SQL way”, and my first try would have been something like your example, or (DIM.CODE LIKE ‘&Param OR &Param IS NULL). Neither worked. I also tried to define fixed string parameter &pct with a value of % to  use in the NVL, but that didn’t help either.

 

It seems that the functions have to be used within a function parameter. Here I have used the standard input parameter Account and the NVL simple function to get the desired output:

 

 

 

 

Badge +5

Hi Nisansala,

 

Yes, you understood correct. 

 

Thank you!

Disna.

 

Userlevel 4
Badge +8

Hi Disnam,

Can you further explain what do you mean by ‘Leave blank for all, select one or multiple in a BR report.’?  Does that mean that the parameter value should be null when you want to select all the branches? 

Best Regards,

Nisansala