Solved

NVL2 BR Function Parameter - Example?

  • 18 August 2022
  • 5 replies
  • 75 views

Userlevel 1
Badge +4

Hi All,

 

Can anyone please show an example of using NVL2 function parameter in a BR report?  

I tried to use NVL but could not get what I wanted. And trying to use NVL2 function parameter, but  not sure what should I enter for the first argument (ValueIfNull).

 

please see the image.

&CodeB is a standard parameter for DIM_CODE_B.CODE

The requirement is to get all CodeB values including NULL when the &CodeB leaves blank and the selected CodeB values when &CodeB has a value.

Example:

when &CodeB = say '45' , the report will only show the records with code_b = 45

when &CodeB leaves blank (NULL) then the report will show the records where the code_b of any value plus the code_b IS NULL

 

 

Thank you!

icon

Best answer by Sanjaya 1 September 2022, 10:06

View original

5 replies

Userlevel 1
Badge +3


It is possible to use the “ALL” parameter value functionality for this purpose.

https://docs.ifs.com/techdocs/foundation1/045_administration_aurena/255_br_and_a/040_Services/010_manage_BRA_services/010_BRA_System_Parameters/default.htm

When “ALL” (the configured value) is entered as the parameter value, values including NULL will be shown.

Userlevel 1
Badge +4

Thank you Sanjaya!

Badge +1

Hi Disna, 

Can I ask you which operator (IN, = or LIKE)  do you use in advanced filters with this standard and functional parametrs? 

 

Thank you  

 

Martin Gestinger

Userlevel 1
Badge +4

Hi Martin,

 According to Sanjaya and the referenced documentation, we do not need to use NVL2 function to satisfy my requirement to some extent. Only thing is we need to enter ‘ALL’ as the parameter value but not leave it as blank which I wanted.

I just created a standard parameter &CodeB (Single value) and used LIKE operator to use wildcards , but if not you can use just = operator in the filter.

But make sure this BR System parameter (Enables the All parameter value functionality)  should be set to TRUE as in the screen shot.
 

 

 

 

Key ‘ALL’ in the parameter value

let me know if this is clear.

Disna.

Badge +1

Hi Disna, 

Thank you for your comment. Your approach looks nice and elegant. But I see now you are in APP 10 and I try to recreate something similar in IFS Cloud 21R1 and there is no window for BR System parameter where is ALL define and could be used in BR. But using % in functional parameter with NVL solve this as well. 

 

Thank you for your added details. 

 

Martin 

Reply