I have a crystal report that is pulling data from our posted AR_InvoiceHistoryHeader and AR_InvoiceHistoryDetail files. I want to have it subtotal the quantities of each of the products and then print all items with a negative number on one page and all items with a positive number on another page and have both those pages sort in Item Number order. Below is the SQL statement:
SELECT "CI_Item"."ProductLine", "SY0_CompanyParameters"."CompanyName", "CI_Item"."DefaultWarehouseCode", "CI_Item"."ProcurementType", "AR_InvoiceHistoryDetail"."ItemCode", "AR_InvoiceHistoryDetail"."WarehouseCode", "AR_InvoiceHistoryHeader"."UDF_SO_BATCH_NO", "AR_InvoiceHistoryDetail"."QuantityOrdered", "AR_InvoiceHistoryDetail"."UnitOfMeasure", "AR_InvoiceHistoryDetail"."ItemCodeDesc", "AR_InvoiceHistoryDetail"."DropShip", "AR_InvoiceHistoryDetail"."Valuation", "AR_InvoiceHistoryDetail"."ProductLine", "AR_InvoiceHistoryHeader"."InvoiceDate", "AR_InvoiceHistoryHeader"."InvoiceNo", "AR_InvoiceHistoryHeader"."SalespersonNo"
FROM "AR_InvoiceHistoryHeader" "AR_InvoiceHistoryHeader", "AR_InvoiceHistoryDetail" "AR_InvoiceHistoryDetail", "SY0_CompanyParameters" "SY0_CompanyParameters", "CI_Item" "CI_Item"
WHERE (("AR_InvoiceHistoryHeader"."InvoiceNo"="AR_InvoiceHistoryDetail"."InvoiceNo") AND ("AR_InvoiceHistoryHeader"."HeaderSeqNo"="AR_InvoiceHistoryDetail"."HeaderSeqNo")) AND ("AR_InvoiceHistoryHeader"."InvoiceType"<>"SY0_CompanyParameters"."CompanyName") AND (("AR_InvoiceHistoryDetail"."ItemCode"="CI_Item"."ItemCode") AND ("AR_InvoiceHistoryDetail"."ItemType"="CI_Item"."ItemType")) AND "AR_InvoiceHistoryDetail"."WarehouseCode"='013' AND ("AR_InvoiceHistoryHeader"."UDF_SO_BATCH_NO"='09956' OR "AR_InvoiceHistoryHeader"."UDF_SO_BATCH_NO"='10010') AND "AR_InvoiceHistoryDetail"."ItemCode"<>'120-BFO' AND "AR_InvoiceHistoryHeader"."InvoiceDate">={d '2014-04-13'} AND "AR_InvoiceHistoryDetail"."ProductLine"<>'ZP'
ORDER BY "SY0_CompanyParameters"."CompanyName"
I have attached a copy of what I have so far but as you can see Item number 210 is showing on both pages and I want the sum of the quantity to determine what page it shows up on.
I have a 4 groups in the report to determine sorting:
1. Company Name
2. PlusMinus which is = Sgn({AR_InvoiceHistoryDetail.QuantityOrdered})
3. ProdLine which is the Product Line for the Item number (this just sorts items so they show in the right order)
4. AR_InvoiceHistoryDetail.ItemCode
Every time I change the PlusMinus to use Sgn(Sum ({AR_InvoiceHistoryDetail.QuantityOrdered}, {AR_InvoiceHistoryDetail.ItemCode})) I get the error: Group Specified on a non-recurring field. Details: @PlusMinus
Does anyone have any ideas how I can get this report to sum the item quantity first, then separate the negative quantities to one page and the positive quantities to another page and then sort each page on item code?
Any help would be greatly appreciated.
