ProLaw Reporting on Cash Receipts by Professional and Fee Component

Wednesday, February 16th, 2011 | ProLaw Case Management | Scott Randall

ProLaw is unable to generate a cash receipts report for fees which is grouped by billing component.  Upon review of the StmnLedger and StmnDetail tables in the ProLaw database, turns out the components atom is not inserted for fees (i.e., StmnDetail.CompType = ‘F’):

ProLaw StmnDetail Table

ProLaw StmnDetail Table – Missing Components Atom for Fee Receipts

While this may seem like a relatively obscure issue, the consequences have a real dollar impact for law firms subject to multi-jurisdiction tax reporting requirements.  Specifically, many government jurisdictions tax cash receipts for work performed within their jurisdiction.  Law firms track where work was performed at a time entry level (i.e., ProLaw Transactions) using different components to determine work location.  Until now, these ProLaw firms would need to generate reports on an accrual basis (i.e., fees billed grouped by component) and not a cash basis (i.e., fees received grouped by component).  As a result, the firm paid taxes on dollars yet to be received (which dollars may never be received if the firm ends up writing off billed amounts).

By creating two analysis tables in our company’s Cosmos BI Pro database (modifications should never, whenever possible, be added to another vendor’s database) and working a little stored procedure magic, I was able to correctly add the Components atom used within the Transactions time entry table to cash receipts:

Cosmos BIProStmnDetailAnalysis Table - Components Atom Populated for Fee Receipts

Cosmos BIProStmnDetailAnalysis Table – Components Atom Available for Fee Receipts

With the Components atom now available to cash receipts, using the following TSQL statement will generate the necessary Cash Receipts by Fee Component information:

SELECT CompID, SUM(Billed), SUM(Paid)
FROM Cosmos.dbo.BIProStmnDetailAnalysis
JOIN ProLaw.dbo.Components ON Components.Components = BIProStmnDetailAnalysis.Components
WHERE PaidDate >= ’2011-01-01′ AND PaidDate <= ’2011-01-31′
GROUP BY CompID

 

When executing this TSQL statement using Microsoft SQL Server Management Studio, results similar to the following should be returned:

ProLaw Fee Cash Receipts by Component

ProLaw Fee Cash Receipts by Component

In order to make report generation more turn-key for a firm’s accounting department, the stored procedure which generates the analysis tables was automated to run on a recurring basis using SQL Server Agent.  In addition, two views into the data were created for previous month and month-to-date values.  Finally, a Scheduled Tasks script utilizing BCP was implemented to place the necessary report files in CSV format into a protected shared directory which could be accessed, allowing the reports to be retrieved in Excel.

Reporting for Fee Cash Receipts by Component issue resolved!

 

Tags: , , , , , ,

You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

Page 1 of 11

Leave a Reply