Inelegant Complex Pseudo Crosstab Report

[KMKelly]

I have a report that looks like this:

Name Period Mon 1, ... Mon 12 Last12_Sum Prev12_sum
-----------------------------------------------------------
Ins Co 1 30 12% ... 15% 14% 12%
Ins Co 1 60 25% ... 23% 24% 22%
Ins Co 1 90 57% ... 55% 56% 54%
Ins Co 1 120 88% ... 87% 88% 86%

Ins Co 2 30 15% ... 18% 17% 15%
...

For each insurance company what % of invoices are paid within 30 days, 60 days, 90 days, 120 days for 12 invoice creation months with a last 12 month and previous 12 month total.

The table the data comes from is set up like this(this non-normalized summary level info was set up previously for other reasons, we use non-realtime data updated weekly):

Invoice # Pays_30 Pays_60 Pays_120... Inv_cre_pd, Div

At the time of running the report the user can select the end month of the report and what division to run. (We are a University hospital, so Peds Cardiology, Hem/Onc etc..)

I have it set up in a terrible way, using iif statements to make essentially every single field for a given insurance company. Example: iif(inv_cr_pd=200505 and pays_30>0,1,0)/iif(inv_cre_pd=200505,1,0); basically sum of those invoices with payment in 30 days for those invoices from May 2005/all invoices from May 2005. Same thing for each month and both totals for 30 days, then same thing for 60, 90, and 120. 56 calculated fields in total.

Now, I need to add another element making sure that anything included within a category has had time to age out, ie no 15 day old invoices included in the 30 day category since many may not be paid at 15 but could be by 30 days and it depresses the success rate for that month. I have the function to do the age out which is very simple, but it is one more element I am trying to squeeze into a massively unweildy monstrosity already. I would love a more elegant solution, butI am having trouble finding one and I am tired of pounding my head.

Anyone have any ideas?

 

[markphsd]

Without getting into the details.

I would think that if your feeling is that you believe you set it up in a terrible way, you should first consider that. If that's the "Anyone have any ideas?" part then i would suggest you look at how you store the information.

Since i haven't really done something like this, this might not be the best solution, but it seems like it would work.

If you have a table of orders, and get a total payment for that order in an orderpayments table, I would first total up all the payments outstanding within range of dates, maybe a variable with the dateadd function so you can loop through 1 to 4 of adding up 30 days to 120. Create a crosstab query that you can build for each of the dates that filters the info. I'm don't think you can build that will show all of the dates at the same time.

I would create a temp table with the fields,
InsuranceCompany
Period
ThirtyDays
SixtyDays
NinetyDays
OneHundredTwentyDays

Since you are going be getting the totals of each period paid, and probably the totals of the unpaid amount, you can create those fields to. It would be nice to see those on the report i'm sure.

When you run those querys you can update the table with the information for the periods.

For the first query you could filter between 15 and 30 get that last element you were talking about.


Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.