Skip to Content
0
Jul 25, 2016 at 10:04 AM

Crystal Reports: How To Set Date Parameter to either Minimum or Maximum in a query

384 Views

Hi Experts,

Our Client is running SAP Business One version for HANA. I had been given a query of Opening & Closing Balance in an SCN post I had done last year. I have tweaked it a bit.

When I ran the query in SAP B1, it worked well. However, when I tired to add it to Crystal Reports command, it brought an error because my query had date parameters (From Date, ToDate & vGroup). So I created/ Declared the 3 parameters in the Parameter list and incoporated them in the query.

Where can I set the minimum value for FromDate and Maximum value for ToDate in the query? See Query Below:

Select a."ItemCode", max(a."Dscription"), sum(a."Opening Balance") as "Opening Balance", sum(a."IN") as "IN", sum(a."OUT") as "OUT",max(a."Price") as "Price", ((sum(a."Opening Balance") + sum(a."IN")) - Sum(a."OUT")) as "Closing" from "TEST".OITM I1

Left JOIN (Select N1."ItemCode", N1."Dscription",N1."Price", (sum(N1."InQty")-sum(N1."OutQty")) as "Opening Balance", 0 as "IN", 0 as "OUT"

From "TEST".OINM N1

Where (N1."DocDate") < {?FromDate}

Group By N1."ItemCode",N1."Dscription",N1."Price"

Union All

Select N1."ItemCode", N1."Dscription",N1."Price", 0 as "Opening Balance", sum(N1."InQty") as "IN", 0 as "OUT" From "TEST".OINM N1

Where N1."DocDate" >= {?FromDate} and N1."DocDate" <= {?ToDate} and N1."InQty" >0

Group By N1."ItemCode",N1."Dscription",N1."Price"

Union All


select N1."ItemCode", N1."Dscription",N1."Price", 0 as "Opening Balance", 0 as "IN", sum(N1."OutQty") as "OUT" From "TEST".OINM N1

Where N1."DocDate" >= {?FromDate} and N1."DocDate" <={?ToDate} and N1."OutQty" > 0

Group By N1."ItemCode",N1."Dscription",N1."Price"

) a

ON a."ItemCode"=I1."ItemCode"

Where I1."ItmsGrpCod" = {?vGroup} and I1."ManBtchNum" = 'Y'

Group By a."ItemCode"

Order By a."ItemCode"

Kind Regards,

Pascale