Query Generator Help please

[chrisvff]

I would like to run a query that I can specify a date range of sales orders and specify a min profit percentage.

for example

1.9.18 to 25.10.18 %7 (THIS NUMBER CAN CHANGE)

this will show any sales orders with individual lines or products that were discounted below the 7% gross profit threshold.

Then I will be able to edit the induvial lines on the special prices for business partners in order to get the profit percent above 10%

The special prices for business partners do not have the option to show me the % profit after discount, as far as I can see.

as always, any help would be highly apricated

[former_member545090]

Hi,

Put Below one in Where Conditon Block:

Where Tablename.FieldName=[%0] and Tablename.FieldName=[%1] and Tablename.FieldName=[%2]

here from %0 and %1 field name is date like doc date or tax date.

and for %2 field name is Discount .

Regards,

Tejas P

[chrisvff]

ended up using this

SELECT CardCode, CardName, ItemCode, Dscription, DocNum, CASE WHEN T1.GrossBuyPr<>0 THEN ROUND(GrssProfit/GrossBuyPr* 100,2) ELSE ROUND(100,2) END AS GrossProfitPer FROM RDR1 T1 LEFT OUTER JOIN ORDR T2 ON T2.DocEntry = T1.DocEntry
WHERE CASE WHEN T1.GrossBuyPr<>0 THEN ROUND(T1.GrssProfit/T1.GrossBuyPr* 100,2) ELSE [%0] END <= [%0] AND T2.DocDate >= [%1] AND T2.DocDate <=[%3]