I've enjoyed/suffered coding Crystal for too many years. It's time for it to die. ;-)
Sorted Maximum Outstanding Balances in the ABC Company data:
The Power Query M code is easier to read on the Query Settings (right panel) inside Power Query than the Advanced Editor view below. See the attached Excel file for that. Click anywhere in the Customer/Maximum table then the Query menu will appear and the Edit button will be on the Ribbon.
let
Source = Odbc.Query("DSN=SOTAMAS90", "SELECT
TransactionDate, CustomerNo, TransactionType,
TransactionAmt
FROM
AR_TransactionPaymentHistory
ORDER BY
TransactionDate"),
#"Add Transaction Polartiy" = Table.AddColumn(Source, "PolarityAmt", each if [TransactionType] = "P" then -[TransactionAmt] else [TransactionAmt]),
#"Grouped Rows" = Table.Group(#"Add Transaction Polartiy", {"CustomerNo"}, {{"All", each _, type table [CustomerNo=nullable text, PolarityAmt=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ([All],"index",1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"TransactionDate", "CustomerNo", "TransactionType", "TransactionAmt", "PolarityAmt", "index"}, {"TransactionDate", "CustomerNo", "TransactionType", "TransactionAmt", "PolarityAmt", "index"}),
#"Add Running Total" = Table.AddColumn(#"Expanded Custom", "Running Total", each List.Sum(List.FirstN( Table.SelectRows( #"Expanded Custom" , (t1)=> t1[CustomerNo] = _[CustomerNo])[PolarityAmt] , _[index] ))),
#"Group by Customer" = Table.Group(#"Add Running Total", {"CustomerNo"}, {{"Maximum", each List.Max([Running Total]), type number}}),
#"Sort by maximum running total" = Table.Sort(#"Group by Customer",{{"Maximum", Order.Ascending}})
in
#"Sort by maximum running total"
------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------
Original Message:
Sent: 01-10-2024 08:49
From: David Overholt
Subject: Sorting By A Running Total - Alternatives?
I have run into this or similar issues many times. instead of using the Crystal running total you can instead use a formula and create your own running total. put the formula in in the details tab so it calculates on every record. create another formula to reset the shared variables to zero and put that formula in the customer Group Header. I have a dummy formula below where I used {AR_InvoiceHistoryHeader.TaxableSalesAmt} you would use the field or formula that you are using in the running total. the "highest balance" shared variable should give you the highest balance. Let me know if this doesn't make sense.
Shared numbervar RunningTotal;
Shared Numbervar HighestBalance;
RunningTotal:=RunningTotal+{AR_InvoiceHistoryHeader.TaxableSalesAmt};
if RunningTotal>HighestBalance then HighestBalance:=RunningTotal;
RunningTotal
------------------------------
David Overholt
DWD Technology Group
------------------------------
Original Message:
Sent: 01-10-2024 08:31
From: Susan Pawlowic
Subject: Sorting By A Running Total - Alternatives?
Thanks all! The main issue is the need for the running total from the beginning of time, which is the only way to get an accurate balance. The only way to do this is with a running total, which refuses to be summed, calculated, or used in a formula. It doesn't sound like it can be done with Crystal, which would have allowed me to add a hyperlink to the report from Customer Inquiry. I believe it can be done with Sage Intelligence, but would be able to add just a hyperlink to Report Manager or Viewer rather than one directly to the report.
------------------------------
Susan Pawlowic
Sage 100 Solutions Architect
RKL eSolutions, LLC
spawlowic@rklesolutions.com
(717) 735-9109 Ext. 4352
Original Message:
Sent: 01-10-2024 08:18
From: Wayne Schulz
Subject: Sorting By A Running Total - Alternatives?
ChatGPT is pretty good for these types of puzzles.
When I asked it recommended a subreport to find the highest balance and then sort this on the main report.
------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT