Trust Accounts By Profile Detail

[MJA-99]

In the current Office Control Summary Report, it set to run by Trust Account. The client wants to run this for all of their Trust Accounts at once in a new report and then also include the same figures for each Branch(Profile-->Name). This report involves numerous SQL Server Objects, i.e., Views, Stored Procedure, UDF's and would like to know the easiest way to get the Receipts, Reciepts Cancelled, Disbursements, and Disbursements Cancelled, Current Balance, and Previous Balance (Month? - Need to clarify this with client). In looking through the TSQL, everything is done by TAC, and client wants it done by Profile or Branch. Some of their TAC's have multiple Branches attached to them. I am told by my Project Manager that LedgerID is one to one with OrderID and since OrderID is attached to LedgerID and OrderID has OwningProfileID, I am thinking that is the best way to get the data they want. It would be very helpful if you had any input, or have any caveats relative to my comments above. Thanks again for your help.

[Lisa Ennis]

You are correct in going back to the Order table to get the owning profile id (see my reply to your previous post on this topic). All of our standard reports are written by trust account, specifically for reconciliation purposes. To change those reports to be able to run those for multiple trust accounts and branches at one time is quite an undertaking. Keeping the reports run by trust account code, and grouping them by branch within the trust account is easier.

The only suggestion that I can make for running the reports for multiple trust accounts is to create a stored procedure where you get the trust accounts and branches that you want in one temp table, and then loops through and executes our existing stored procedures for the information that your are needing for each trust account, unioning that data into another temp table, and then return those results. To give you any further direction than that I would have to actually develop a stored procedure that does that.

I would caution you to be careful if you decide to go this route. The majority of the balancing of the trust accounts is done within the reports, so when we are asked to customize those reports, we recommend very thorough testing before putting those into a production environment to ensure their accuracy.

[MJA-99]

Prior to reading your post, I found a way to write SP that is basically in line with what you are saying above. The client says it is working correctly. Thanks Lisa.