Handling Discrete Parameters 1

[kskid]

System configuration: Crystal XI, Sp2 with ODBC connection to Oracle 8i database.

The purpose of the report is to follow the progression of a case through the database based on which table(s) that contain the same case.

First, can I create a group on a parameter containing discrete values regardless of whether the value exists on a table or not?

If I can't, then how can I tell how many discrete values were are contained in a parameter? I want to be able to step through each value and use a subreport to see if it exists on a table. I believe I can handle the rest once I get past this.

Thanks,

-lw

-lw

 

[lbass]

You can't group on parameter values, since the values are hard values that are intended to be matched against database field values. To determine the number of selected parameter values for a multi-valued parameter, you can use:

ubound({?Parm})

There is probably a better way to do this, if I'm following you. It sounds like you want to select a set of values and then see if each one exists in each of multiple tables. Is that correct? It might help if you used concrete examples.

-LB

 

[kskid]

[ol]
Here is the process.

[li]All cases are initiated from an external interface and queued up on Table_A[/li]
[li]Periodically, a batch program is executed and creates a case record for new cases on Table_B, which makes the case available for use within our system.[/li]
[li]When Case desk initiates a case, the case number is validated against Table_B. If valid, then a case record is created on Table_C[/li]
[li]If for some reason, the case was cancelled, then the case can be deleted at which time a record is created on Table_D and removed from Table_C.[/li]
[\ol]

Periodically, staff personnel will go through the cases and note any cases missing from Table_B.

This is where my report comes in. The staff will enter the missing case numbers and the report will provide the status of each case entered based on which table the record is found.

-lw

 

[lbass]

Can an individual case be in only one table at a time? Would the report be testing more than one case at a time?

-LB

 

[kskid]

Each case number is a unique number and is the primary key
Each case number follows one of the following sequence.

A complete case will appear in A,B, and C.

or

A cancelled/deleted case will appear in A,B, and D. (The record is deleted from Table_C)

--------
Notification logic

If a case does not appear in A, then it is either an invalid case number or the case has not been transmitted from the external source.

If the case is in Table_A but not in Table_B, then the batch job has not executed.

If the case is in A and B but not (C and D), then case desk has not created the case.

If the case is in A,B,C, then the case is complete and entered

If the case is in A,B,D, then the case has been deleted.

 

[lbass]

I would set this up as a command like this:

Select 'Table A' "Table", "TableA"."CaseNo"
From "TableA"
Union
Select 'Table B' "Table", "TableB"."CaseNo"
From "TableB"
Union
Select 'Table C' "Table", "TableC"."CaseNo"
From "TableC"
Union
Select 'Table D' "Table", "TableD"."CaseNo"
From "TableD"

Then you could insert a crosstab that would show you which tables each case number was in, or you could insert a group on {command.CaseNo}, and then do a series of formulas like this:

//{@A}:
if {command.Table} = "TableA" then 1

//{@B}:
if {command.Table} = "TableB" then 1

//etc.

Then create a formula like this:

if sum({@A},{command.caseno}) <> 0 and
sum({@B},{command.caseno}) = 0 then "Not Executed" else

if sum({@A},{command.caseno}) <> 0 and
sum({@B},{command.caseno}) <> 0 and
sum({@C},{command.caseno}) = 0 and
sum({@D},{command.caseno}) = 0 then "Case Not Created" else

if sum({@A},{command.caseno}) <> 0 and
sum({@B},{command.caseno}) <> 0 and
sum({@C},{command.caseno}) <> 0 then "Complete and Entered" else

if sum({@A},{command.caseno}) <> 0 and
sum({@B},{command.caseno}) <> 0 and
sum({@D},{command.caseno}) <> 0 then "Case Deleted"

-LB

 

[kskid]

Thanks lb. I'll give it a try. I am in the preliminary investigation stage at the moment and have to confirm the requirements with the user.

-lw