Need to Group by Formula - But it's evaluated later
We recently migrated to a newer management system. The previous System held a table (for clarity sake I'll name the fields the same between the two systems) called SalesContract.ARBalance this was a field that (often erroneously) held the Contract's Balance. How this was calculated and populated in the table, I don't know.
So the new system does not have a 'static' .ARBalance field, which is a good thing from an accounting standpoint (all calculations are done real time and from one filed) but not from a reporting standpoint.
My goal is to duplicate a Crystal Report used in the old system which grouped by amount owed e.g.
This was fine as the formula only needed to pull from that single field before grouping.
With the new system, I first have to summarize all of the transactions in order to get the above criteria e.g.
Obviously I can't group by the above formula as Crystal must first evaluate the groups before it can evaluate the summaries.
I need to display summaries for each of the above groups (.01-300, 300.01-600 etc.) How can I accomplish this?
So the new system does not have a 'static' .ARBalance field, which is a good thing from an accounting standpoint (all calculations are done real time and from one filed) but not from a reporting standpoint.
My goal is to duplicate a Crystal Report used in the old system which grouped by amount owed e.g.
if isnull({Contract.ARBalance})then "Credit Balance - $0" else if
{Contract.ARBalance}<=0 then "Credit Balance - $0" else if
{SalesContract.ARBalance} in .01 to 300 then "$0.01 to $300.00" else if
{SalesContract.ARBalance} in 300.01 to 600.00 then "$300.01 to $600.00" else if
{SalesContract.ARBalance} in 600.01 to 1200.00 then "$600.01 to $1200.00" else if
{SalesContract.ARBalance}>1200.01 then "$1200.01 and Above"This was fine as the formula only needed to pull from that single field before grouping.
With the new system, I first have to summarize all of the transactions in order to get the above criteria e.g.
if isnull(Sum ({MDTrans.Amount}, {SalesContract.ID}))then "Credit Balance - $0" else if
(Sum ({MDTrans.Amount}, {SalesContract.ID}))<=0 then "Credit Balance - $0" else if
(Sum ({MDTrans.Amount}, {SalesContract.ID})) in .01 to 300 then "$0.01 to $300.00" else if
(Sum ({MDTrans.Amount}, {SalesContract.ID})) in 300.01 to 600.00 then "$300.01 to $600.00" else if
(Sum ({MDTrans.Amount}, {SalesContract.ID})) in 600.01 to 1200.00 then "$600.01 to $1200.00" else if
(Sum ({MDTrans.Amount}, {SalesContract.ID})) >1200.01 then "$1200.01 and Above"Obviously I can't group by the above formula as Crystal must first evaluate the groups before it can evaluate the summaries.
I need to display summaries for each of the above groups (.01-300, 300.01-600 etc.) How can I accomplish this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
peter57r:
can you help me a bit with the syntax? I understand what you're saying about running that summary as a command. I used the following in Crystal:
Select sum(MDTrans.Amount) as SumAmt from MDTrans
Select MDTrans.ContractID as _ContractID from MDTrans
GROUP BY MDTrans.ContractID
(MDTran.ContractID is the same as the above SalesContract.ContractID just in the MDTrans table)
This does create a 'table' with the SumAmt 'field' but there's no _ContractID 'field' created for me to link to
can you help me a bit with the syntax? I understand what you're saying about running that summary as a command. I used the following in Crystal:
Select sum(MDTrans.Amount) as SumAmt from MDTrans
Select MDTrans.ContractID as _ContractID from MDTrans
GROUP BY MDTrans.ContractID
(MDTran.ContractID is the same as the above SalesContract.ContractID just in the MDTrans table)
This does create a 'table' with the SumAmt 'field' but there's no _ContractID 'field' created for me to link to
ASKER
Nvm, got it Peter Thanks!
Select MDTrans.ContractID, sum(MDTrans.Amount) as SumAmt from MDTrans
GROUP BY MDTrans.ContractID
Select MDTrans.ContractID, sum(MDTrans.Amount) as SumAmt from MDTrans
GROUP BY MDTrans.ContractID
ASKER
Just an Update for posterity, the above was painfully slow if the SQL command was performed in Crystal, so I went ahead and created a View in the database so I could report off that directly. Worked Great! thanks again Peter.
SELECT ContractID, SUM(Amount) AS SumAmt
FROM dbo.MDTrans
GROUP BY ContractID
SELECT ContractID, SUM(Amount) AS SumAmt
FROM dbo.MDTrans
GROUP BY ContractID
As for your question, I think Peter's right. You could create totals for the different levels using formulas and variables, but in order to group by those levels, you're probably going to need the totals to be calculated outside of CR.
James