Problems counting records

[Fadius]

I am using Crystal Reports XI.

I have a formula which is generating a type and the report is grouped on type. The problem I am running into is if the item has been counted in one type, it is not being counted in another type even though it is possible. Here is what is making up my types and grouping.

if {sspmgntactivityreportbymonth.type_rtk_of_credentialinggroupinfo}in["SSP CAQH Enrollment","SSP Government Enrollment",
"SSP Individual Enrollment","SSP IPA Enrollment"]
and {sspmgntactivityreportbymonth.credentialing_startdate_of_credentialing} in YearToDate
then "New Provider Work Items"

else

if {sspmgntactivityreportbymonth.type_rtk_of_credentialinggroupinfo}="SSP Change Form"
and {sspmgntactivityreportbymonth.credentialing_startdate_of_credentialing} in YearToDate then "Change Requests"

else

if {sspmgntactivityreportbymonth.type_rtk_of_credentialinggroupinfo}="SSP Provider Setup"
and {sspmgntactivityreportbymonth.credentialing_startdate_of_credentialing} in YearToDate then "New Providers"

else

if {sspmgntactivityreportbymonth.type_rtk_of_credentialinggroupinfo}in["SSP CF_Add Location","SSP CF_Address Change",
"SSP CF_Name Change","SSP CF_Other","SSP CF_TAX ID ADD","SSP CF_Tax ID Change","SSP Group CF_Add Location",
"SSP Group CF_Address Change","SSP Group CF_Other","SSP Group CF_Tax ID Change","SSP CF_Stop Enrollment",
"SSP CF_Termination"] and {sspmgntactivityreportbymonth.credentialing_startdate_of_credentialing} in YearToDate
then "Change Work Items"

else

if {sspmgntactivityreportbymonth.type_rtk_of_credentialinggroupinfo}in["SSP CF_Add Payor","SSP Group CF_Add Payor"]
and {sspmgntactivityreportbymonth.credentialing_startdate_of_credentialing} in YearToDate
then "Payor Add's"

else

if {sspmgntactivityreportbymonth.type_rtk_of_credentialinggroupinfo}="SSP Converted"
and {sspmgntactivityreportbymonth.credentialing_startdate_of_credentialing} in YearToDate
then "Converted"

else

if {sspmgntactivityreportbymonth.presentdate_asof_of_credentialingassignments} in YearToDate then "Payor approvals"
else "Ignore"



The problem iw mainly with the Payor Approvals. As an item gets approved, a date gets entered. It could be any of the type_rtk_of_credentialinggroupinfo's listed. How can I get these to count even if it has been counted in a prior type or grouping.


Thank you,

 

[Madawc]

You'd find it easier overall if you split the tests into Boolians, which could be displayed and would show True or False in a test detail line while you are developing. E.g.

{sspmgntactivityreportbymonth.type_rtk_of_credentialinggroupinfo} in 
["SSP CAQH Enrollment","SSP Government Enrollment", "SSP Individual Enrollment","SSP IPA Enrollment"]

{sspmgntactivityreportbymonth.credentialing_startdate_of_credentialing} in YearToDate

You can then reference them in other formula fields or counts, just mentioning the name, e.g.

If @NewProvider and @WithinYear then New Provider Work Items"

You can also test these fields in Running-Total counts, or have a field that is 0 or 1 depending on the value and have a summary total on that.

The use of Crystal's automated totals is outlined at FAQ767-6524.


Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Fadius]

HOw would I group things? Would I still keep a formula for type and group on that?

 

[Fadius]

I figured the grouping out, however, I am still running into trouble with the counts as the Payor Approvals uses a different date field than all the other types. Here is how I am grouping:

if {@NewProvider} and {@CVIWithinYear} then "New Providers"

else

if {@NewProviderWorkItem} and {@CVIWithinYear} then "New Provider Work Items"

else

if {@CHangeRequests} and {@CVIWithinYear} then "Change Requests"

else

if {@ChangeRequestsWorkItems} and {@CVIWithinYear} then "Change Work Items"

else

if {@PayorAdds} and {@CVIWithinYear} then "Payor Adds"

else

if {@Converted} and {@CVIWithinYear} then "Converted"

else

if {@AsOfWithinYear} then "Payor Approvals"

If I do this and have group totals by month, for January Payor Approvals I get 118 when I display everyting. If I only select Payor Approvals, I get 1410. So I am still running into the same problem with the payor approvals not being counted if they have been counted once already.

 

[IanWaterman]

You can only count a record once.

You will need to use a command to bring data together in a UNION

Select 'New Providers' Type, then list of fileds required for report that meet new providers criteria
from table
union all
select "New Provider Work Items" ....
UNION all
etc ....

Then you will get the counts you want

Ian

 

[lbass]

Ian is correct. Depending upon your desired results, you might be able to just use running totals for each type where you limit the count in the evaluation section to those that meet your criteria. Reset never. Place in the report footer and add text to identify the results per "group".

-LB

 

[Madawc]

If you have several separate tests, you can count the same record twice if it meets two of them, and so on.

You can also group using a formula field that sets different values depedning on the tests.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[IanWaterman]

As LB says you can use a Running Total and that will allow you to count a record twice for a report summary.

But if you are grouping a report and want counts in a group it will only be counted in the first group it meets the criteria for.

If you want the latter you will need to UNION data so that records appears more than once.

Ian