Various Exceptions in Single Report 1

[bmacdo]

Using Crystal 11 off MS-SQL Server…

I am tasked with modifying an existing report that lists in a visit-based date range exceptions in a billing process, e.g. checks for missing approvals, etc. I am to note additionally other missing data, e.g. no personal residence or no primary diagnosis listed for clients. The report currently runs off a stored procedure and contains keys for client visit, client id, etc. that I can link to address and diagnosis tables. The report contains several dynamic sort prompts, but always pulls by one prompted service provider key.

If I were to report on the new missing data fields alone, I would create a formula and group on residence type or diagnosis rank to list the rows that have no personal residence or primary diagnosis. However, I’m confused about how to do both simultaneously, and given that the report is already selecting on other missing data. Rows selected because they lack an approval flag won’t be the same ones that lack a personal residence type…must I strip out the current selection criteria and do sub-reports for each type of exception?

Any advice will be appreciated.

Brad M.

 

[lbass]

You could set up a formula that checks for sets of missing data, e.g., so that anyone missing address, approval, AND diagnosis, was in one group, and those missing address and approval in another, etc. You would have to start the if/then formula checking for the most missing field data first.

However, if you want a discrete group for each type of missing field, then you would need to use subreports, since a specific record can ONLY appear in one group.

How will the report be used? You could set up a report sorted by client with multiple columns that are set up as checks for the presence of data in the fields, showing an "X" if present, etc.

-LB

 

[bmacdo]

The report is to assist service providers assuring their services are billed and credited. And, no, the missing data would ideally be printed all on the same row for client, not in batches by the missing field.

WRT "a formula that checks for sets of missing data, e.g., so that anyone missing address, approval, AND diagnosis, was in one group"...there will be rows for the same client/service coming up in which, e.g., the residence type will be something other than primary residence and diagnosis other than primary, for which those desired values do exist for the same client/service in another row.

You mean a formula that in part for a particular field checks all rows appearing for a given client & for that field and stays flagged false if it doesn't find it and then alerts in a GF?

 

[lbass]

Say you want to know if a primary diagnosis is present. You could create a formula like this:

if isnull({table.diagnosisstatus}) or
{table.diagnosisstatus} <> "Primary" then
"" else
"X"

...where "X" means the information is available. You would insert a group on client and then place conditional formulas like this in the detail section and then right click on each and insert maximums on them at the group level. Drag the groupname into group footer and suppress the details and group header.

-LB

 

[bmacdo]

You are the bomb. Gracias.