First of many problems

[rjshirts]

I work for a medical billing company, and I'm working on a report for the company president. He wants to have a report that shows collections, payments, adjustments and charges against an account, and then percentages of different combinations of the above.
At the moment I've got a report with a subreport inside. The reason for doing this is I have the main report locked to values greater than "0", and the subreport allowing negative numbers.
What we are trying to get is a cross-tab that says something like this:
Net Collections XXXXXXX (ammount)
Charges -XXXXXXX (ammount)
Sum of Net Collections-Charges
The problem with the charges is we only want to pass certain fields to the charges field. Eg. Professional Courtesy, Write Offs, Charity, etc...
My formula at the moment in the subreport reads:
(if {COL.BC} in ["225", "235", "236", "237", "241", "280"]
then
(if {COL.BC}= "235" then "Bad Debt"
else
if {COL.BC}="237" then "Bad Debt"
else
if {COL.BC}="241" then "Professional Courtesy"
else
if {COL.BC}="225" then "Charity"
else
if {COL.BC}="236" then "Small Balance Write-off"
else
if {COL.BC}="280" then "Timely Filing")
else
"All other adjustments")

Is there a way to use distinctcounthere?
I need a way to suppress all other data and show only the fields in the array above.

Any Help on how to get this to work, without a sub-report would be best, would be greatly appreciated.
I'm using v.7 at them moment, but we are planning on an upgrade to v.9 or v.10 in the next two weeks, so info for those would be nice if it's more appropriate.

Thanks in advance.

Jonathan Shirts

 

[kchaudhry]

My approach would be to change the records selection formula:

I am not sure how V.7 works but for CR8.5 you can go to Report>Record selection and entert the following there:

{COL.BC} in ["225", "235", "236", "237", "241", "280"]

This way the only records returned meet this condition. After this you can create a formula like this:

@BCCount

if {COL.BC}= "235" then 1 else 0

Now you can insert a summary on this formula field and use the distinct count. You will have to create similar formulas for all other conditions.

Hope it helps!

Kchaudhry

 

[lbass]

I think you should provide more information. Are these fields coming from one table or more than one? Is there only one amount field and another field that defines whether the amount is a collection or a charge, or are there separate fields for collection amount and charge amount? Are the charge amounts entered in the database without a sign to indicate they are negative?

-LB

 

[rjshirts]

>>I think you should provide more information. Are these fields coming from one table or more than one?
Multiple tables - Database, amount, Year and Month, Transaction type, billing code (adjustment, etc...)

Are the charge amounts entered in the database without a sign to indicate they are negative? The ammounts are processed like this: -25.00

Thanks

Jonathan

 

[lbass]

One of the reasons I asked is because of your limitation on the {Col.BC} field--what is this field, and is it used for collections as well as charges?

I'll make some guesses in the absence of info. You can create separate collection and charges columns by using formulas like:

//{@collection}:

if {table.transtype} = "Collection" then {table.amount}

//{@charges}:

if {table.transtype} = "Charge" then {table.amount}

If you want to limit the charges (or collections) to certain values, as specified in your previous post, use a formula like:

if {table.transtype} = "Charge" and
{COL.BC} in ["225", "235", "236", "237", "241", "280"] then {table.amount}

Assuming you have a group on {table.account}, you can then insert summaries (sums) on the above to display at the group level. You can use an additional formula to get the net amount {@net}:

sum({@collection},{table.account})+ sum({@charges},{table.account})

If you are trying to translate codes to description fields and then come up with associated amounts, you could use a formula like:

if {COL.BC}="237" then "Bad Debt" else
if {COL.BC}="241" then "Professional Courtesy" else
if {COL.BC}="225" then "Charity" else
if {COL.BC}="236" then "Small Balance Write-off" else
if {COL.BC}="280" then "Timely Filing" else
"All other adjustments"

You could group on this and add insert a summary on{table.amount} to get summaries for each group instance (or use it as a crosstab row with{table.acct} as the column and {table.amount} as the summary field).

Wandering aimlessly here, but still willing to help...

-LB