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.
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
Best answer by SanjayaView original
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.
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.
let me know if this is clear.
Can I ask you which operator (IN, = or LIKE) do you use in advanced filters with this standard and functional parametrs?
Thank you Sanjaya!
It is possible to use the “ALL” parameter value functionality for this purpose.
When “ALL” (the configured value) is entered as the parameter value, values including NULL will be shown.