Query generator is the tool which allows supporters and end-users to write query within SAP Business One without running SQL or HANA Studio. If you compare with Crystal Report, Query Generator comes with some limitations. No chance Re-naming parameter is can be given as an example.

In this article we will answer following question: how to add optional parameter..

As you know, we add “[%0]” with where clause after adding the field that we would like to filter. Let us give an example:

User requires to get sales order list inclueded Item details except canceled orders. Query result is 250.000 entries-lines. It takes some time running query. User is not happy with this performance issue. Generally, he gets report for specific Items but also he seldom needs to get full report.

The query that you need is similar with that:

SELECT T0."CardName", T1."ItemName", T1."LineTotal"  
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0."DocEntry"=T1."DocEntry"  
WHERE  T0."CANCELED"='N' AND T1."ItemName"=[%0] 

You need to use where clause like below script in order to make parameter optional:

WHERE T0."CANCELED"='N' (T1."ItemName"=[%0] OR T1."ItemName"='')

*Using brackets is important with OR usage.

Ismail Bedirhan ORHAN