Cutom Revenue Report

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
smadderla
Posts: 16
Joined: Fri Nov 02, 2012 5:31 pm

Cutom Revenue Report

Post by smadderla »

Hi,
I am working on creating a custom report, to get closed orders and its revenue
I need to pull the revenue irrespective of its Transaction Status (Pending or Posted)

Following is what I have for getting the revenue of Escrow orders, this is only giving me revenue which is posted (Transaction Status == Posted)

LEFT JOIN dbo.Ledger(NOLOCK) AS L ON dbo.[Order].ID = L.OrderID
LEFT JOIN dbo.[Transaction] (NOLOCK) AS T ON L.ID = T.LedgerID
LEFT JOIN dbo.Adjustment(NOLOCK) AS A ON T.LedgerID = A.LedgerID
JOIN dbo.Escrow (NOLOCK) AS E ON dbo.[Order].ID=E.OrderID
INNER JOIN dbo.zrefTransactionStatus AS zTT ON A.TransactionStatusID=zTT.ID
LEFT JOIN dbo.TransactionHUDLineAndAmount(NOLOCK) AS THLA ON A.TransactionID = THLA.TransactionID

Thanks,
Sangeetha.
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Cutom Revenue Report

Post by Lisa Ennis »

You do not need a join to the Adjustment table at all for the 'Pending' transactions. I would recommend using a UNION statement where you first get all the posted transactions and their adjustments, and then UNION a SELECT statement that gets the pending transactions. Below is code from custom reports I have completed for SoftPro customers to get both posted and pending revenue transactions. Hope this helps. Have a great day.

Joins for getting the posted revenue:
dbo.TransactionHUDLineAndAmount AS THLA
INNER JOIN dbo.Adjustment AS A ON A.TransactionID=THLA.TransactionID
INNER JOIN dbo.TransactionType AS TT ON A.TransactionTypeID=TT.ID AND TT.IsDebit=1
INNER JOIN dbo.zrefTransactionStatus AS zTT ON A.TransactionStatusID=zTT.ID
INNER JOIN dbo.zrefAdjustmentType AS zAT ON A.AdjustmentTypeID=zAT.ID
INNER JOIN dbo.[Transaction] AS T ON THLA.TransactionID=T.ID
INNER JOIN dbo.OrderContact AS OC ON T.OrderContactID=OC.ID AND OC.IsLinkedDisb=1
INNER JOIN dbo.Ledger AS L ON T.LedgerID=L.ID

Joins for pending transactions:
dbo.TransactionHUDLineAndAmount AS THLA
INNER JOIN dbo.[Transaction] AS T ON THLA.TransactionID=T.ID
INNER JOIN dbo.zrefTransactionStatus AS zTT ON T.TransactionStatusID=zTT.ID AND zTT.Description = 'Pending'
INNER JOIN dbo.TransactionType AS TT ON T.TransactionTypeID=TT.ID AND TT.IsDebit=1
INNER JOIN dbo.OrderContact AS OC ON T.OrderContactID=OC.ID AND OC.IsLinkedDisb=1
INNER JOIN dbo.Ledger AS L ON T.LedgerID=L.ID
Lisa Ennis
Senior Report Developer
SoftPro
smadderla
Posts: 16
Joined: Fri Nov 02, 2012 5:31 pm

Re: Cutom Revenue Report

Post by smadderla »

Thanks for your help Lisa.
Post Reply