Escrow Officers Report - Actual Fee Per Order

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
MJA-99
Posts: 33
Joined: Fri Apr 08, 2011 2:41 pm

Escrow Officers Report - Actual Fee Per Order

Post by MJA-99 »

I have been asked to create an Escrow Officer's Report (Custom). The report will only have one order per detail line with different groupings. For an individual order, what is the easiest way to get the Actual Fee, IBLAmount, Estimated Fee and Account Balance?

I have found some of the fields above with exception of Actual Fee. Please let me know where to find all of the above fields in SQL Server Database so I can check against what I am using for IBLAmount, Est. Fee, and Account Balance. Is there a view, stored procedure, or function that already consolidates these values per Order?

Thanks,

Michael
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Escrow Officers Report - Actual Fee Per Order

Post by Lisa Ennis »

The dbo.TransactionHudLineAndAmountFull view has the information that you need. Most of our customers count their revenue when fees to a revenue order contact are disbursed. A revenue order contact is an order contact that the "Include on revenue reports" flag is checked (dbo.OrderContact.IncludeOnRevenueReports=1). Selecting from this view where IncludeOnRevenueReports=1 will give you all the detail lines for all the charges paid to the revenue order contact. Depending on how you are writing your stored procedure, you can determine how you want to further write your report. Some customers want a column for each bill code, while others find having a report grouped by a bill code acceptable. You may want to further limit the records returned to be just one specific bill code.

An example of a report that does the grouping by bill code is the standard "Fees by Bill Code" report that can be found in the Default tree, Fees/Revenue folder.

To get some of the other data that you are looking for is a little more complicated. To get estimated fees, you would have to use the dbo.TransactionHudLineAndAmount table, and link to the dbo.Transaction table and only get records where the transaction is pending. To get the IBA Balance, and the Ledger Balance, the functions dbo.LedgersBalanceWithPendingAndHeldWithIBABalanceByTACByTranDate and dbo.LedgersBalanceWithPendingAndHeldWithIBABalanceByTACByTrustDate can be used.
Lisa Ennis
Senior Report Developer
SoftPro
MJA-99
Posts: 33
Joined: Fri Apr 08, 2011 2:41 pm

Re: Escrow Officers Report - Actual Fee Per Order

Post by MJA-99 »

Thank you for your help. Information from your last post was very useful. The problem I am finding is getting everything to rollup to Order Number so there is only one record per Order for ESC or 1102 related data. Is there any shortcut you would recommend in addition to your last message that would eliminate the extra records?

If above is not clear enough, I can provide one of the Orders which has multiple rows or different values in IBABalance, etc. I can also provide TSQL. Some of the values are correct according to client and have only one Order per record, yet other Orders contain multiple records usually with IBA Balance and Actual Fee being the cause for duplicate Orders in results. They are only wanting 1102 or ESC records returned as I mentioned above (I can explain this although I am sure you know what I mean by ESC and 1102).

Again, your last post was extremely helpful in confirming a lot of things based on my work so far in trying to understand your SQL Server database. I have only worked with it for roughly 8 or 9 days and have been using existing reports, Views, SP’s, UDF, etc to figure out data.
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Escrow Officers Report - Actual Fee Per Order

Post by Lisa Ennis »

There are a couple of different ways to get only one record per order. You could do the following:

Code: Select all

SELECT 
	O.ID AS OrderID,
	O.ReceivedDate,
	---All other fields that you want that return only one record per order
	ESC.DateEscrowFeesFirstPaid,
	ISNULL(ESC.EscrowFees,0) AS EscrowFees
FROM 
	dbo.[Order] AS O
	LEFT OUTER JOIN (SELECT OrderID, MIN(TrustAccountingDate) AS DateEscrowFeesFirstPaid, ISNULL(SUM(Amount),0) AS EscrowFees
			FROM dbo.TransactionHudLineAndAmountFull 
			WHERE BillCode='ESC' AND IncludeOnRevenueReports=1
			GROUP BY OrderID) AS ESC ON O.ID=ESC.OrderID
Or, if you need a column for each bill code, you can still use a subquery, something like this:

Code: Select all

SELECT 
	O.ID AS OrderID,
	O.ReceivedDate,
	---All other fields that you want that return only one record per order
	BC.DateFeesFirstPaid,
	ISNULL(BC.EscrowFeesTotal,0) AS EscrowFeesTotal,
	ISNULL(BC.TitlePremiumTotal,0) AS TitlePremiumTotal,
	--Continue with a column for each bill code
	ISNULL(BC.OtherFeesTotal,0) AS OtherFeesTotal
FROM 
	dbo.[Order] AS O
	LEFT OUTER JOIN (SELECT OrderID, MIN(TrustAccountingDate) AS DateFeesFirstPaid, 
			ISNULL(SUM(CASE WHEN BillCode='ESC' THEN Amount ELSE 0 END),0) AS EscrowFeesTotal,
			ISNULL(SUM(CASE WHEN BillCode='TP' THEN Amount ELSE 0 END),0) AS  TitlePremiumTotal,
			ISNULL(SUM(CASE WHEN NOT(BillCode IN ('TP', 'ESC')) OR BillCode='OTH' THEN Amount ELSE 0 END),0) AS OtherFeesTotal
		            FROM dbo.TransactionHudLineAndAmountFull 
		            WHERE IncludeOnRevenueReports=1
		            GROUP BY OrderID) AS BC ON O.ID=BC.OrderID
Lisa Ennis
Senior Report Developer
SoftPro
MJA-99
Posts: 33
Joined: Fri Apr 08, 2011 2:41 pm

Re: Escrow Officers Report - Actual Fee Per Order

Post by MJA-99 »

Thank you Lisa. This was very helpful in addressing the multiple Orders issue and seems to be working. I used the first example you suggested in prior post.
Post Reply