AUGForums.com

An Acumatica User Group

  • Forums
  • Podcast
  • Blog
  • Rolodex
  • Login
  • Start Here
  • Consulting
  • Courses
  • Register
Acumatica Forums

By using this website, you agree to our Terms of Use (click here)

  • Overview
  • Member List
  • All-Stars
  • Stats
  • Recent Posts
  • Help
Forums
AUG Forums
Acumatica Generic I...
Sort by and IIF Fun...
 
Notifications
Clear all

Sort by and IIF Functions  

    Last Post
RSS

NickSM
Posts: 28
 NickSM
August 25, 2020 12:16 am
(@nicksm)
21-50 Posts
Joined: 8 months ago

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.

IIF Function

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.

 

Sort By

Any thoughts? GI provided.

Top20CustSalesMTD.xml
Topic Tags
iif aggregate sorting
7 Replies
Dan Wilkins
Posts: 18
 Dan Wilkins     ★★ All-Star ★★
August 25, 2020 11:20 am
(@dwilkins)
6-20 Posts
Joined: 9 months ago

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])

Reply
NickSM
 NickSM
(@nicksm)
Joined: 8 months ago

21-50 Posts
Posts: 28
August 25, 2020 6:26 pm
Reply toDan WilkinsDan Wilkins

@dwilkins Hi Dan,

Thanks for your reply. The problem isn't the code, it is the Sort Order. you can use either of the above codes to get negative amounts for credits, but when you order by the same expression, it won't sort correctly. The second screenshot is what happens.

In normal SQL terms you would do

Order By SUM(CASE when ARTran.drcr='D' then -1 else 1 end *[ARTran.TranAmt])

As soon as you use SUM( ) in a Sort Order it show the figures, but not in the correct order.

Regards

Nick Savage-Mady

Reply
Tim Rodman
Posts: 2488
 Tim Rodman     ★★ All-Star ★★
August 28, 2020 11:39 pm
(@timrodman)
Over 200 Posts
Joined: 5 years ago

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.

Reply
NickSM
Posts: 28
 NickSM
August 30, 2020 5:53 pm
(@nicksm)
21-50 Posts
Joined: 8 months ago

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

Reply
NickSM
Posts: 28
 NickSM
September 2, 2020 7:45 pm
(@nicksm)
21-50 Posts
Joined: 8 months ago

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`

 

Reply
Tim Rodman
Posts: 2488
 Tim Rodman     ★★ All-Star ★★
September 2, 2020 11:57 pm
(@timrodman)
Over 200 Posts
Joined: 5 years ago

Excellent. Glad to hear it. What does the Trace look like now?

Reply
NickSM
 NickSM
(@nicksm)
Joined: 8 months ago

21-50 Posts
Posts: 28
September 3, 2020 1:09 am
Reply toTim RodmanTim Rodman

@timrodman Way better than above  😆

Reply
Tim Rodman liked
  All forum topics
  Previous Topic
Next Topic  
Related Topics
  • EXPORTING TO EXCEL FROM PIVOT TABLE DATA WHICH IS SORTED OUT ON ONE COLUMN
    2 years ago
Topic Tags:  iif (2), aggregate (1), sorting (3),
  Forum Statistics
11 Forums
1,656 Topics
7,693 Posts
4 Online
1,110 Members

Latest Post: Timecard reports/queries Our newest member: Janetteraab Recent Posts Unread Posts

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Online Members

 No online members at the moment

Acumatica Forums

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2021 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.

‹›×

    ‹›×