By using this website, you agree to our Terms of Use (click here)
Hi,
I have a seemingly simple question in relation to a GI.
As the Invoice and Memos GI shows positive figures for both Credit Memos and Invoices, any GI totalling will give you a positive figure. This will wrong due to the negative values that the Credit Memo should. Now this isn't the problem, however I am open to suggestions about this as well.
I currently use IIF([ARTran.drcr]='D',-1,1)*[ARTran.TranAmt] with either a grouping of ARTran.CustomerID.
The problem arises when you try to sort into Descending Order using the above statement. Is you simply use SUM([ARTran.TranAmt]) it will be successfully wrong 😉. It will ignore the Credit Memos and elevate Customers that have load of credits. If you add the above argument in to Sort By, then it will then show the amount in an incorrect order.
Any thoughts? GI provided.
For my doc total in Invoice our GI, I use the following in the data field:
=IIf([ARInvoice.DocType]='CRM', [ARInvoice.CuryOrigDocAmt]*-1, [ARInvoice.CuryOrigDocAmt])
I've noticed strange behavior with sorting before and had to do random things to the formulas to try to get them to sort properly, sometimes successfully, sometimes not successfully.
Can you paste the SQL from the Trace? That's usually where I look for a clue as to ways to tweak the formula.
Hi @timrodman
Thanks for the reply.
Below is what happens when you put the =SUM(IIF([ARTran.DrCr]='D',-1,1)*[ARTran.TranAmt]) into the Sort By area. It throws an error 'Invalid use of the Group Function' first and then displays nothing. As you can see, it puts a Max on every field.
SELECT MAX( `ARTran`.`TranType`) AS `ARTran_TranType`, MAX( `ARTran`.`RefNbr`) AS `ARTran_RefNbr`, MAX( `ARTran`.`LineNbr`) AS `ARTran_LineNbr`, `ARTran`.`CustomerID` AS `ARTran_CustomerID`, SUM( `ARTran`.`CuryInfoID`) AS `ARTran_CuryInfoID`, `ARTran`.`FinPeriodID` AS `ARTran_FinPeriodID`, MAX( `ARTran`.`SalesPersonID`) AS `ARTran_SalesPersonID`, MAX( `ARTran`.`SubID`) AS `ARTran_SubID`, MAX( `ARTran`.`NoteID`) AS `ARTran_NoteID`, MAX( `FinPeriod`.`FinPeriodID`) AS `FinPeriod_FinPeriodID`, MAX( `FinPeriod`.`NoteID`) AS `FinPeriod_NoteID`, SUM( CASE WHEN `ARTran`.`DrCr` = 'D' THEN (- 1) ELSE 1 END * `ARTran`.`TranAmt`) AS `ARTran_Formula20640faf0d7a4a5e80f64f3faf1cc5f0`
FROM (
SELECT `ARTran`.`BranchID` AS `BranchID`, `ARTran`.`TranType` AS `TranType`, `ARTran`.`RefNbr` AS `RefNbr`, `ARTran`.`LineNbr` AS `LineNbr`, `ARTran`.`SortOrder` AS `SortOrder`, `ARTran`.`SOOrderType` AS `SOOrderType`, `ARTran`.`SOOrderNbr` AS `SOOrderNbr`, `ARTran`.`SOOrderLineNbr` AS `SOOrderLineNbr`, `ARTran`.`SOOrderLineOperation` AS `SOOrderLineOperation`, `ARTran`.`SOOrderSortOrder` AS `SOOrderSortOrder`, `ARTran`.`SOShipmentType` AS `SOShipmentType`, `ARTran`.`SOShipmentNbr` AS `SOShipmentNbr`, `ARTran`.`SOShipmentLineNbr` AS `SOShipmentLineNbr`, `ARTran`.`CustomerID` AS `CustomerID`, `ARTran`.`BatchNbr` AS `BatchNbr`, `ARTran`.`LineType` AS `LineType`, `ARTran`.`ProjectID` AS `ProjectID`, `ARTran`.`PMDeltaOption` AS `PMDeltaOption`, `ARTran`.`ExpenseDate` AS `ExpenseDate`, `ARTran`.`CuryInfoID` AS `CuryInfoID`, `ARTran`.`InventoryID` AS `InventoryID`, `ARTran`.`TaxID` AS `TaxID`, `ARTran`.`DeferredCode` AS `DeferredCode`, `ARTran`.`InvtMult` AS `InvtMult`, `ARTran`.`SiteID` AS `SiteID`, `ARTran`.`UOM` AS `UOM`, `ARTran`.`Qty` AS `Qty`, `ARTran`.`BaseQty` AS `BaseQty`, (CASE WHEN ( `ARTran`.`Qty` <> .0) THEN ( `ARTran`.`TranCost` / `ARTran`.`Qty`) ELSE .0 END) AS `UnitCost`, `ARTran`.`TranCost` AS `TranCost`, `ARTran`.`TranCostOrig` AS `TranCostOrig`, `ARTran`.`IsTranCostFinal` AS `IsTranCostFinal`, `ARTran`.`CuryUnitPrice` AS `CuryUnitPrice`, `ARTran`.`UnitPrice` AS `UnitPrice`, `ARTran`.`CuryExtPrice` AS `CuryExtPrice`, `ARTran`.`ExtPrice` AS `ExtPrice`, `ARTran`.`DiscPct` AS `DiscPct`, `ARTran`.`CuryDiscAmt` AS `CuryDiscAmt`, `ARTran`.`DiscAmt` AS `DiscAmt`, `ARTran`.`ManualDisc` AS `ManualDisc`, `ARTran`.`ManualPrice` AS `ManualPrice`, `ARTran`.`DiscountsAppliedToLine` AS `DiscountsAppliedToLine`, `ARTran`.`OrigGroupDiscountRate` AS `OrigGroupDiscountRate`, `ARTran`.`OrigDocumentDiscountRate` AS `OrigDocumentDiscountRate`, `ARTran`.`GroupDiscountRate` AS `GroupDiscountRate`, `ARTran`.`DocumentDiscountRate` AS `DocumentDiscountRate`, `ARTran`.`RetainagePct` AS `RetainagePct`, `ARTran`.`CuryRetainageAmt` AS `CuryRetainageAmt`, `ARTran`.`RetainageAmt` AS `RetainageAmt`, `ARTran`.`CuryTranAmt` AS `CuryTranAmt`, `ARTran`.`TranAmt` AS `TranAmt`, `ARTran`.`CuryTaxableAmt` AS `CuryTaxableAmt`, `ARTran`.`TaxableAmt` AS `TaxableAmt`, `ARTran`.`CuryTaxAmt` AS `CuryTaxAmt`, `ARTran`.`TaxAmt` AS `TaxAmt`, `ARTran`.`TranClass` AS `TranClass`, `ARTran`.`DrCr` AS `DrCr`, `ARTran`.`TranDate` AS `TranDate`, `ARTran`.`OrigInvoiceDate` AS `OrigInvoiceDate`, `ARTran`.`FinPeriodID` AS `FinPeriodID`, `ARTran`.`TranPeriodID` AS `TranPeriodID`, `ARTran`.`TranDesc` AS `TranDesc`, `ARTran`.`Released` AS `Released`, `ARTran`.`SalesPersonID` AS `SalesPersonID`, `ARTran`.`EmployeeID` AS `EmployeeID`, `ARTran`.`CommnPct` AS `CommnPct`, `ARTran`.`CuryCommnAmt` AS `CuryCommnAmt`, `ARTran`.`CommnAmt` AS `CommnAmt`, `ARTran`.`DefScheduleID` AS `DefScheduleID`, `ARTran`.`TaxCategoryID` AS `TaxCategoryID`, `ARTran`.`ReasonCode` AS `ReasonCode`, `ARTran`.`AccountID` AS `AccountID`, `ARTran`.`SubID` AS `SubID`, `ARTran`.`TaskID` AS `TaskID`, `ARTran`.`CommitmentID` AS `CommitmentID`, `ARTran`.`CostCodeID` AS `CostCodeID`, `ARTran`.`tstamp` AS `tstamp`, `ARTran`.`CreatedByID` AS `CreatedByID`, `ARTran`.`CreatedByScreenID` AS `CreatedByScreenID`, `ARTran`.`CreatedDateTime` AS `CreatedDateTime`, `ARTran`.`LastModifiedByID` AS `LastModifiedByID`, `ARTran`.`LastModifiedByScreenID` AS `LastModifiedByScreenID`, `ARTran`.`LastModifiedDateTime` AS `LastModifiedDateTime`, `ARTran`.`NoteID` AS `NoteID`, (SELECT `Note`.`NoteText`
FROM `Note` `Note`
WHERE ( `Note`.`CompanyID` IN ( 2, 1, 3) AND 32 = ASCII ( SUBSTRING( `Note`.`CompanyMask`, 1, 1)) & 32) AND ( `Note`.`CompanyID` IN ( 2, 1, 3) AND 32 = ASCII ( SUBSTRING( `Note`.`CompanyMask`, 1, 1)) & 32) AND `Note`.`NoteId` = `ARTran`.`NoteID`
LIMIT 1) AS `NoteText`, (SELECT COUNT( *)
FROM `NoteDoc` `NoteDoc`
WHERE ( `NoteDoc`.`CompanyID` IN ( 2, 1, 3) AND 32 = ASCII ( SUBSTRING( `NoteDoc`.`CompanyMask`, 1, 1)) & 32) AND ( `NoteDoc`.`CompanyID` IN ( 2, 1, 3) AND 32 = ASCII ( SUBSTRING( `NoteDoc`.`CompanyMask`, 1, 1)) & 32) AND `NoteDoc`.`NoteId` = `ARTran`.`NoteID`
LIMIT 1) AS `NoteFiles`, NULL AS `NoteActivity`, `ARTran`.`Commissionable` AS `Commissionable`, `ARTran`.`Date` AS `Date`, `ARTran`.`CaseID` AS `CaseID`, `ARTran`.`DiscountID` AS `DiscountID`, `ARTran`.`DiscountSequenceID` AS `DiscountSequenceID`, `ARTran`.`DRTermStartDate` AS `DRTermStartDate`, `ARTran`.`DRTermEndDate` AS `DRTermEndDate`, `ARTran`.`CuryUnitPriceDR` AS `CuryUnitPriceDR`, `ARTran`.`DiscPctDR` AS `DiscPctDR`, ( ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`GroupDiscountRate`)) + ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`OrigGroupDiscountRate`))) AS `GroupDiscountAmount`, ( ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`DocumentDiscountRate`)) + ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`OrigDocumentDiscountRate`))) AS `DocumentDiscountAmount`, ( `ARTran`.`DiscAmt` + CASE WHEN ( `ARTran`.`TaxableAmt` = .0) THEN `ARTran`.`TranAmt` ELSE `ARTran`.`TaxableAmt` END) AS `GrossSalesAmount`, ( CASE WHEN ( `ARTran`.`DrCr` = 'D') THEN (- 1.0) ELSE 1.0 END * CASE WHEN ( `ARTran`.`IsTranCostFinal` = 0) THEN `ARTran`.`TranCostOrig` ELSE `ARTran`.`TranCost` END) AS `Cost`, ( CASE WHEN ( `ARTran`.`DrCr` = 'D') THEN (- 1.0) ELSE 1.0 END * ( ( ( ( `ARTran`.`DiscAmt` + CASE WHEN ( `ARTran`.`TaxableAmt` = .0) THEN `ARTran`.`TranAmt` ELSE `ARTran`.`TaxableAmt` END) - `ARTran`.`DiscAmt`) - ( ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`GroupDiscountRate`)) + ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`OrigGroupDiscountRate`)))) - ( ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`DocumentDiscountRate`)) + ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`OrigDocumentDiscountRate`))))) AS `NetSalesAmount`, ( ( CASE WHEN ( `ARTran`.`DrCr` = 'D') THEN (- 1.0) ELSE 1.0 END * ( ( ( ( `ARTran`.`DiscAmt` + CASE WHEN ( `ARTran`.`TaxableAmt` = .0) THEN `ARTran`.`TranAmt` ELSE `ARTran`.`TaxableAmt` END) - `ARTran`.`DiscAmt`) - ( ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`GroupDiscountRate`)) + ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`OrigGroupDiscountRate`)))) - ( ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`DocumentDiscountRate`)) + ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`OrigDocumentDiscountRate`))))) - ( CASE WHEN ( `ARTran`.`DrCr` = 'D') THEN (- 1.0) ELSE 1.0 END * CASE WHEN ( `ARTran`.`IsTranCostFinal` = 0) THEN `ARTran`.`TranCostOrig` ELSE `ARTran`.`TranCost` END)) AS `Margin`, (CASE WHEN ( ( CASE WHEN ( `ARTran`.`DrCr` = 'D') THEN (- 1.0) ELSE 1.0 END * ( ( ( ( `ARTran`.`DiscAmt` + CASE WHEN ( `ARTran`.`TaxableAmt` = .0) THEN `ARTran`.`TranAmt` ELSE `ARTran`.`TaxableAmt` END) - `ARTran`.`DiscAmt`) - ( ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`GroupDiscountRate`)) + ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`OrigGroupDiscountRate`)))) - ( ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`DocumentDiscountRate`)) + ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`OrigDocumentDiscountRate`))))) <> .0) THEN ( ( ( ( CASE WHEN ( `ARTran`.`DrCr` = 'D') THEN (- 1.0) ELSE 1.0 END * ( ( ( ( `ARTran`.`DiscAmt` + CASE WHEN ( `ARTran`.`TaxableAmt` = .0) THEN `ARTran`.`TranAmt` ELSE `ARTran`.`TaxableAmt` END) - `ARTran`.`DiscAmt`) - ( ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`GroupDiscountRate`)) + ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`OrigGroupDiscountRate`)))) - ( ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`DocumentDiscountRate`)) + ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`OrigDocumentDiscountRate`))))) - ( CASE WHEN ( `ARTran`.`DrCr` = 'D') THEN (- 1.0) ELSE 1.0 END * CASE WHEN ( `ARTran`.`IsTranCostFinal` = 0) THEN `ARTran`.`TranCostOrig` ELSE `ARTran`.`TranCost` END)) / ( CASE WHEN ( `ARTran`.`DrCr` = 'D') THEN (- 1.0) ELSE 1.0 END * ( ( ( ( `ARTran`.`DiscAmt` + CASE WHEN ( `ARTran`.`TaxableAmt` = .0) THEN `ARTran`.`TranAmt` ELSE `ARTran`.`TaxableAmt` END) - `ARTran`.`DiscAmt`) - ( ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`GroupDiscountRate`)) + ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`OrigGroupDiscountRate`)))) - ( ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`DocumentDiscountRate`)) + ( `ARTran`.`TranAmt` * ( 1.0 - `ARTran`.`OrigDocumentDiscountRate`)))))) * 100.0) ELSE .0 END) AS `MarginPercent`, `ARTran`.`SubItemID` AS `SubItemID`, `ARTran`.`LocationID` AS `LocationID`, `ARTran`.`LotSerialNbr` AS `LotSerialNbr`, `ARTran`.`ExpireDate` AS `ExpireDate`, `ARTran`.`PlanID` AS `PlanID`, `ARTran`.`OrigInvoiceType` AS `OrigInvoiceType`, `ARTran`.`OrigInvoiceNbr` AS `OrigInvoiceNbr`, `ARTran`.`OrigInvoiceLineNbr` AS `OrigInvoiceLineNbr`, `ARTran`.`InvtDocType` AS `InvtDocType`, `ARTran`.`InvtRefNbr` AS `InvtRefNbr`, `FSxARTran`.`Source` AS `Source`, `FSxARTran`.`SOID` AS `SOID`, `FSxARTran`.`AppointmentID` AS `AppointmentID`, `FSxARTran`.`AppDetID` AS `AppDetID`, `FSxARTran`.`SODetID` AS `SODetID`, `FSxARTran`.`AppointmentDate` AS `AppointmentDate`, `FSxARTran`.`ServiceOrderDate` AS `ServiceOrderDate`, `FSxARTran`.`BillCustomerID` AS `BillCustomerID`, `FSxARTran`.`CustomerLocationID` AS `CustomerLocationID`, `FSxARTran`.`ServiceContractID` AS `ServiceContractID`, `FSxARTran`.`ContractPeriodID` AS `ContractPeriodID`, `FSxARTran`.`SuspendedSMEquipmentID` AS `SuspendedSMEquipmentID`, `FSxARTran`.`SMEquipmentID` AS `SMEquipmentID`, `FSxARTran`.`NewTargetEquipmentLineNbr` AS `NewTargetEquipmentLineNbr`, `FSxARTran`.`ComponentID` AS `ComponentID`, `FSxARTran`.`EquipmentLineRef` AS `EquipmentLineRef`, `FSxARTran`.`EquipmentAction` AS `EquipmentAction`, `FSxARTran`.`Comment` AS `Comment`, `FSxARTran`.`TranType` AS `FSxARTran_TranType`
FROM `ARTran` `ARTran`
LEFT JOIN `FSxARTran` `FSxARTran` ON ( `FSxARTran`.`CompanyID` = 3) AND `ARTran`.`TranType` = `FSxARTran`.`TranType` AND `ARTran`.`RefNbr` = `FSxARTran`.`RefNbr` AND `ARTran`.`LineNbr` = `FSxARTran`.`LineNbr`
WHERE ( `ARTran`.`CompanyID` = 3) AND ( `ARTran`.`BranchID` IS NULL OR `ARTran`.`BranchID` = 1)
) `ARTran`
INNER JOIN (
SELECT `FinPeriod`.`FinPeriodID` AS `FinPeriodID`, `FinPeriod`.`OrganizationID` AS `OrganizationID`, `FinPeriod`.`FinYear` AS `FinYear`, `FinPeriod`.`Descr` AS `Descr`, `FinPeriod`.`PeriodNbr` AS `PeriodNbr`, `FinPeriod`.`Status` AS `Status`, `FinPeriod`.`Active` AS `Active`, `FinPeriod`.`Closed` AS `Closed`, `FinPeriod`.`APClosed` AS `APClosed`, `FinPeriod`.`ARClosed` AS `ARClosed`, `FinPeriod`.`INClosed` AS `INClosed`, `FinPeriod`.`CAClosed` AS `CAClosed`, `FinPeriod`.`FAClosed` AS `FAClosed`, `FinPeriod`.`StartDate` AS `StartDate`, `FinPeriod`.`EndDate` AS `EndDate`, `FinPeriod`.`Custom` AS `Custom`, `FinPeriod`.`DateLocked` AS `DateLocked`, DATE_SUB( `FinPeriod`.`EndDate`, INTERVAL 1 DAY) AS `FinDate`, `FinPeriod`.`tstamp` AS `tstamp`, `FinPeriod`.`NoteID` AS `NoteID`, (SELECT `Note`.`NoteText`
FROM `Note` `Note`
WHERE ( `Note`.`CompanyID` IN ( 2, 1, 3) AND 32 = ASCII ( SUBSTRING( `Note`.`CompanyMask`, 1, 1)) & 32) AND ( `Note`.`CompanyID` IN ( 2, 1, 3) AND 32 = ASCII ( SUBSTRING( `Note`.`CompanyMask`, 1, 1)) & 32) AND `Note`.`NoteId` = `FinPeriod`.`NoteID`
LIMIT 1) AS `NoteText`, (SELECT COUNT( *)
FROM `NoteDoc` `NoteDoc`
WHERE ( `NoteDoc`.`CompanyID` IN ( 2, 1, 3) AND 32 = ASCII ( SUBSTRING( `NoteDoc`.`CompanyMask`, 1, 1)) & 32) AND ( `NoteDoc`.`CompanyID` IN ( 2, 1, 3) AND 32 = ASCII ( SUBSTRING( `NoteDoc`.`CompanyMask`, 1, 1)) & 32) AND `NoteDoc`.`NoteId` = `FinPeriod`.`NoteID`
LIMIT 1) AS `NoteFiles`, NULL AS `NoteActivity`, `FinPeriod`.`CreatedByID` AS `CreatedByID`, `FinPeriod`.`CreatedByScreenID` AS `CreatedByScreenID`, `FinPeriod`.`CreatedDateTime` AS `CreatedDateTime`, `FinPeriod`.`LastModifiedByID` AS `LastModifiedByID`, `FinPeriod`.`LastModifiedByScreenID` AS `LastModifiedByScreenID`, `FinPeriod`.`LastModifiedDateTime` AS `LastModifiedDateTime`
FROM `FinPeriod` `FinPeriod`
WHERE ( `FinPeriod`.`CompanyID` = 3) AND ( `FinPeriod`.`OrganizationID` = 0)
) `FinPeriod` ON ( `ARTran`.`finPeriodID` = `FinPeriod`.`finPeriodID`)
GROUP BY `ARTran`.`finPeriodID`, `ARTran`.`customerID`
ORDER BY MAX( SUM( CASE WHEN `ARTran`.`DrCr` = 'D' THEN (- 1) ELSE 1 END * `ARTran`.`TranAmt`)) DESC
LIMIT 100
Hi @timrodman,
I've figured how to get around the problem now and it is super simple.
previously I mention that using IIF([ARTran.drcr]='D',-1,1)*[ARTran.TranAmt] for the field would case the headache in the Sort Order tab. I found by looking at the Sales Profitability Report that there is in fact an extra field I didn't know about.
ARTran.NetSalesAmount
This solves my problem as the field runs the expression behind the scenes and you can use it in Sort Order without having to SUM( ).
As you can see below it run through a fair few parts in the Case statement, but the beauty is you don't have to code it.
( CASE WHEN ( `ARTran_ARTran`.`DrCr` = 'D') THEN (- 1.0) ELSE 1.0 END * ( ( ( ( `ARTran_ARTran`.`DiscAmt` + CASE WHEN ( `ARTran_ARTran`.`TaxableAmt` = .0) THEN `ARTran_ARTran`.`TranAmt` ELSE `ARTran_ARTran`.`TaxableAmt` END) - `ARTran_ARTran`.`DiscAmt`) - ( ( `ARTran_ARTran`.`TranAmt` * ( 1.0 - `ARTran_ARTran`.`GroupDiscountRate`)) + ( `ARTran_ARTran`.`TranAmt` * ( 1.0 - `ARTran_ARTran`.`OrigGroupDiscountRate`)))) - ( ( `ARTran_ARTran`.`TranAmt` * ( 1.0 - `ARTran_ARTran`.`DocumentDiscountRate`)) + ( `ARTran_ARTran`.`TranAmt` * ( 1.0 - `ARTran_ARTran`.`OrigDocumentDiscountRate`))))) AS `ARTran_NetSalesAmount`
Excellent. Glad to hear it. What does the Trace look like now?