Skip to main content

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!

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

Hi Nisansala,

 

Yes, you understood correct. 

 

Thank you!

Disna.

 

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:

 

 

 

 

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!

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.

Terms & ConditionsCookie settings