Exception Rpt: Show all of set with any error

[bobhagan]

I have an exception report with a group header that looks for missing client information (SSN,DOB etc) and then in details, a set of status records (Referred, Opened, Transferred, Closed) that must be in this order.

I want to suppress all clients with no errors, but show all the records for clients with ANY error. The difficulty is that an error can occur on the last status. I have to set an error = true variable, but vars are evaluated WhilePrintingRecords, too late for the set to "know" it should all be displayed (I think).

Is there any way to accomplish this?

Thanks
Bob Hagan

 

[lbass]

I think you could handle this with a group select. First, group on {table.clientID}. Then let's say that your error check formula {@error} looks something like this:

if isnull({table.SSN}) or
isnull({table.DOB}) or
isnull({tble.gender}) //etc.
then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@error}, {table.clientID}) > 0

This would display all clients with errors in any record.

-LB

 

[bobhagan]

Right,

but then I need to look at the status records and if they are out of order, show the header and ALL the status records. I don't want to show client info as the footer unless there's no other way.

Thanks
Bob Hagan

 

[lbass]

I'm not following you. All records will be returned for a client who has an error in one of them. Is the order of the records a separate issue? I don't understand your reference to the group footer. The formula I gave you is for group selection, not for a summary in the footer. Also, as an aside, I don't see any need for using variables here; instead use a formula like {@error} that I suggested above.

If you want to order the detail records by the statuses "Referred, Opened, Transferred, Closed", I would guess that sorting by a date field would work, but if you don't have that to work with, then create a formula {@sort}:

if {table.status} = "Referred" then 1 else
if {table.status} = "Opened" then 2 else
if {table.status} = "Transferred" then 3 else
if {table.status} = "Closed" then 4

Then use this as your sort field (report->sort records).

If this still doesn't address your problem, then it would be helpful if you provided more information about your report structure and some sample data.

-LB

 

[bobhagan]

Right, the order of the records is an additional issue. There is a client table with a related status table. I'm showing client demographic information in a client header and status records as the report detail.

The statuses mark events (and dates)in a compliance system. E.g. a case must be opened (or closed) within 60 days of referral. Unfortunately, the data entry system has no way to enforce (say, by what codes are in a dropdown or by validation) the business rules. So client B Jones could show more than one referral, could show as transferred without being opened, opened after being closed, rather than re-referred, etc.

The way I've been thinking, is there are multiple sections and the suppression fomulas are independent. If client demographics has missing info, status records are also shown. If demographics is ok, it is suppressed and doesn't "know" whether there is an error in the sequence of status records which are read later.

Can I make @error read through the status records and show client and status if > 0? If so, great.

Thanks

 

[lbass]

So if either there is missing info OR there are errors in status sequence you want all info to show for that group? I think you would have to create a subreport to handle the sequencing error detection so that you could have the values available to suppress all sections within a group. In the subreport, create a sort formula as in my last post which will order the records by type of status, and use this to sort subreport records. Let's assume you have a group on {table.clientID}. Then create a subreport formula {@seqerror}:

if not onfirstrecord and
{table.clientID}=previous({table.clientID}) and
{table.date} < previous({table.date}) then 1 else 0

Create a second formula for the detail section of the subreport {@sumerror}:

whileprintingrecords;
shared numbervar sumerror := sumerror + {@seqerror};

Suppress all sections of the subreport, and place it in the GH#1a section of the main report, linking the subreport to the main report by {table.clientID}. In design mode, minimize the height of the subreport.

Place your group header fields in GH#1b. Then go to the section expert (format->section)->GH#1b->suppress and enter:

whileprintingrecords;
shared numbervar sumerror;

not isnull({table.SSN}) and
not isnull({table.DOB}) and //etc.
sumerror = 0

Use the same formula for the detail section and the group footer section. You can't suppress GH#1a or the subreport won't execute, but if you eliminate the borders and make the subreport small, it will take up minimal space.

-LB

 

[bobhagan]

I hadn't thought about a subreport in ahead of the displayed header. That's a good idea.

Thanks much.
Bob Hagan