How to calculate Revenue

[austin22]

I am using Crystal Report 8.5 and I want to calculate how much revenue collected in a particular year.
I am having a hard time thinking this through...

Say that I had 176,000 cases filed in year 2003, and 55,000 of the cases filed were closed in 2003 due to payment. I want to know the revenue of the 55,000 cases closed in 2003. Then let's say 14,000 of the 176,000 cases filed in 2003 were closed in 2004...I want to know the revenue associated with those 14,000 that were closed in 2004...and so on.

I am not sure how to get started...

I started by writing down my thoughts as such:

Total cases filed for each year
Total cases closed for each year (monetary only)

So, I will use a my {Case Physical Table} which have the two case file date and case closed date. My monetary field is in the {Case Transaction table}...Also, I have to exclude the non-monetary payments such as JC-Jail Credit, IC-Indigency Credit and CS-Community Service. This field in the [Case Transaction table}.

Any help getting this report started will be greatly appreciated.

 

[dgillz]

Pretty broad question. Have you worked with crystal before?

Have you linked your 2 tables in crystal yet? If not please do so, and place all of the fields you want to see on the report.

Then use a record selection formula to filter out JC-Jail Credit, IC-Indigency Credit and CS-Community Service.

Then create the various year formulas to capture data by year:

if year({ClosedDate}) = 2003 then {RevenueField} else 0

Place these fields on your report as well,and insert your totals as desired.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[austin22]

Yes, I've used Crystal before...
Yes, I have linked the two tables...

The two tables are: {Case Physical Table} and {Case Transaction} both are linked by the case number. The report is grouped by the case filed date and then grouped by the case closed date (these fields are found in the 'case physical table')

Here is one of the many problems that I am running into:

When I establish my total cases filed and closed for each year, then the problem starts when I exclude the non-monetary "JC","IC" and "CS". My total number of cases filed changes and I do not want it to change. The only total that should change is the total cases closed, due the non-monetary criteria. Please see below:

Year Case Filed     Total Cases Filed    Total Cases Closed

     2003                176,775              73,661
     2004                192,832              50,677
     2005                171,395              28,986

Basically, what I want to see is the total number cases filed for each year, then out of the total case filed how many of those were closed with money attached to it...excluding the non-monetary.

I tried placing this formula in the group selection: {case transaction} <> ["jc","cs","ic"]...but it changed the total cases filed.

 

[dgillz]

Put it in the record selection, not the group selection.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?