Stored Procedure Name:  SimplePareto

The following article describes how to use the SimplePareto SP to create a custom closed cases analysis report.

These SP provides a variety of information and statistics for closed cases.  This allows for financial and operational analysis of business operations for the matter flow at a firm.  

Assumptions

This SP is written to only return matters that are Closed.  That is evaluated by checking to see if they have a date entered in the 'Matter Close Date' field. 

This SP will group and total hours based on the Timekeeper's category.  This is set in the user settings by choosing a 'Reporting Category' for each user.

Please read the following article before attempting to create a custom report using this stored procedure:

Using SQL Stored Procedures to Create Custom Reports

Report Variables

The sections below contain the columns names, filter variables and special features of this report needed to create custom reports using this report stored procedure.

Stored Procs:

cr_Billing_Filter_   = 'SimplePareto'
cr_Billing_Data_    = 'SimplePareto'

Available Column Field Names:

Top Percentage

Description: This is an integer value which defines the grouping.  Using the old adage that 80% of our business come from 20% of our clients, enter a percentage to represent that "80%".  Matters that represent that percentage of firm fees received during the date range for case closing will be grouped together, those that represent the other percentage (ie, 20% of fees received) will be grouped next.

Field Name 'contributingpercent'

Data type: 'Integer'

Responsible Attorney

Description: The attorney in the matter lookup field 'Responsible Attorney'

Field Name 'atty'

Data type: 'Text'

Matter Name

Description:  The name of the matter.

Field Name 'name'

Data type: 'Text'

Matter Number

Description: The matter number for the matter.

Field Name 'matternumber'

Data type: 'Text'

Fees Billed

Description: The total fees billed for that matter.

Field Name 'feesbilled'

Data type: 'Decimal'

Matter Open Date

Description: The matter open date, pulls from field 'Matter Open Date'

Field Name 'opendate'

Data type: 'Date'

Matter Close Date

Description: Date from matter field 'Matter Close Date'

Field Name 'closeddate'

Data type: 'Date'

Shelf Life 

Description: Time in months that a matter was open.  Does a calculate to subtract close date from open date to get an integer representing months cases was opened.  Cases open and closed the same month would return a value of 0.

Field Name 'shelflife'

Data type: 'Text'

Fees Collected

Description: Total of fees collected for all timekeepers for the matter.

Field Name 'feescollected'

Data type: 'Decimal'

Referral Source

Description: The contact that referred the matter, if populated.  Pull the name from the 'Referred By' field on the matter.

Field Name 'refsource'

Data type: 'Text'

Referral Source

Description: The contact that referred the matter, if populated.  Pull the name from the 'Referred By' field on the matter.

Field Name 'refsource'

Data type: 'Text'

Practice Area

Description: Value from 'Practice Area' field on matter.

Field Name 'casetype'

Data type: 'Text'

Referral Type

Description: Value from the 'Referral Type' field on the matter.

Field Name 'reftype'

Data type: 'Text'

Total Hours by Timekeeper Category

Description: This columns is special!  To create a customizable set of columns based on the timekeeper (user) value specified in 'Reporting Category for each user, a custom field name can be entered in the format below:

'totalhours_XXXXX'   where XXXXX is the category.  so 'totalhours_associate' would create a column to sum all the billable hours entered by timekeepers whose 'Reporting Category' is set to 'Associate' If not all categories are added as columns, the SP will auto add leftover hours to a special column named 'totalhours_none'  which may be added as its own column.

Field Name 'totalhours_[SEE ABOVE FOR EXPLANATION OF WHAT HAPPENS AFTER THE UNDERSCORE]'

Data type: 'Decimal'

Available Filters and Filter Settings:

Top Percent Filter

Used in column 'contributingpercent'

Filter Type = 'Number'

Sql Param Name =  'topPercent'

Filter Default = an integer > 50 to represent the top % of matters fees received.  Default is 80.

Filter Conditions = blank

Param in Data = checked

Close Date

Used in column 'closeddate'

Filter Type = 'Date'

Sql Param Name =  'minDate|maxDate'

Filter Default = see filter default section for different options.

Filter Conditions = blank

Param in Data = unchecked 

Sorting

This SP will return and sort its returned data table in the following order

contributingpercent

feescollected

Advanced Options

Special Grouping Options

See explanation on column for totalhours_ for special functionality to total by timekeeper category, above.