Sort by and IIF Functions  

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?