Complement Set in Crystal Reports

[ebstarasia]

I have a list of reports/data that all contain one or more "event codes." What I am trying to do is compare the event codes in each data to the list of all possible "event codes" and have a list of what the data is lacking besides their reference number.

For example:

if the control or key set of event codes were {1,2,3,4,5}

i want to compare the following reports and have them return what code they are missing

Data A: {1,2,5} therefore next it would say {3,4}
Data B: {1,3} --> {2,4,5}

In a nutshell how would I write a relative complement of A (control set) & B (data)??

 

[Madawc]

If you had totals for each of the possible event codes, checking each of the possible fields, you might then display them at the end of the report. Put them in order and also suppress zero, if your version of Crystal allows it.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

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

 

[ebstarasia]

I'm not trying to have a total missing number at the end of the report, I am more looking at having the event codes that are missing displayed after the file/data ref number.

 

[Madawc]

You could adapt the method to show this.

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

 

[MCuthill]

ebstarasia,

Depending on the structure of your report, you should be able to use the following. I am assuming there is a group for "Event", with the codes being Detail level records.

Create a formula field for each event code, like:
{@Code1}

IF {Table.EventCode}="1" THEN 1 ELSE 0

{@Code2}

IF {Table.EventCode}="2" THEN 1 ELSE 0

etc etc.

Then sum these on a your event and create the following formula field to display the codes missing:
{@Code1Missing}

IF Sum({@Code1},{table.event}) = 0 THEN "1" ELSE ""

{@Code2Missing}

IF Sum({@Code2},{table.event}) = 0 THEN "2" ELSE ""

You should be able to then concatenate these formulas to get a list of the codes missing at the event-level.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[ebstarasia]

MCuthill,

Would you make two separate formula fields for {@code1} and {@codemissing} or have them in one formula?

 

[MCuthill]

ebstarasia,

They need to be separate, or you can use one formula to conditionally increment variables to house the values... the above takes a couple more fields, but will be more easily understood by (starting out) report developers. Keeping it simple!

If you want to take the other approach, you need the following:
{@VariableRESET_All} -- Place this in the group header

WhilePrintingRecords;
NumberVar Code1Count:=0;
NumberVar Code2Count:=0;
NumberVar Code3Count:=0;
NumberVar Code4Count:=0;
NumberVar Code5Count:=0;

{@VariableACCUM} -- placed at the details level

WhilePrintingRecords;
NumberVar Code1Count;
NumberVar Code2Count;
NumberVar Code3Count;
NumberVar Code4Count;
NumberVar Code5Count;

IF {Table.EventCode} ="1" THEN CodeCount1:=CodeCount1+1 ELSE
IF {Table.EventCode} ="2" THEN CodeCount2:=CodeCount2+1 ELSE
IF {Table.EventCode} ="3" THEN CodeCount3:=CodeCount3+1 ELSE
IF {Table.EventCode} ="4" THEN CodeCount4:=CodeCount4+1 ELSE
IF {Table.EventCode} ="5" THEN CodeCount5:=CodeCount5+1

You would then still need to handle the display of the missing elements in the group footer. Using this approach also means the display MUST be in the group footer (after the variables are tallied). With the previous approach, you could place the results in either the Group Header or Group Footer.

Example Display Formula:
{@VariableDISPLAY_Code1} -- in group footer

WhilePrintingRecords;
NumberVar Code1Count;

IF Code1Count = 0 THEN "1," ELSE ""

Please Note: I added the comma to the result. Do this for all but result 5 (for either solution). It will simplify the concatenation.

{@Concatenation}

{@VariableDISPLAY_Code1} & {@VariableDISPLAY_Code2} & {@VariableDISPLAY_Code3} & {@VariableDISPLAY_Code4} & {@VariableDISPLAY_Code5}

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Do you have a database field that returns a string like:

1,4,5

?

Or do you have a formula that summarizes the codes present in a report and that returns this string?

I can't tell what your data actually looks like in the detail section of the report.

-LB

 

[ebstarasia]

this is what the report looks like essentially.

 

[ebstarasia]

disregard last reply by me

 

[ebstarasia]

What the report does, is that there are groups of HBLNO therefore one group will have be a HBLNO called SBAC2. When you drill down it will show multiple files with the same HBLNO but diff location detail and event code.

I need formula that will go find out the missing event code in a group and print the event code(s)

 

[lbass]

I think Mike's method should work, but here's another approach. I assumed that the event code was a number--so adjust as necessary:

//{@reset} for the group header:
whileprintingrecords;
stringvar x := "";
stringvar z := "";

//{@accum} for the detail section:
whileprintingrecords;
stringvar x;
if not(totext({table.eventcode},0,"") in x) then
x := x + totext({table.eventcode},0,"")+", ";

//{@displaymissingcodes} for the group footer:
whileprintingrecords;
stringvar x;
numbervar array y := [1,2,3,4,5];//add the potential codes here
numbervar i;
numbervar j := ubound;
stringvar z;
for i := 1 to j do(
if not(totext(y,0,"") in x) and
not(totext(y,0,"") in z) then
z := z + totext(y,0,"")+", "
);
if len(z)>2 then
left(z,len(z)-2)

-LB

 

[ebstarasia]

LB,

for {@accum} im gettin an error of "too many arguments have been given to this funtion." in the first totext({table.eventcode},0,""). CR highlights 0,

 

[ebstarasia]

i modified the {@accum} formula once i realized what i was looking at exactly.

but the event codes are strings: ["AE","VD","VA","UV","OA","I","CR","CT","AL","RL","AR","AV"]

so i am trying to modify {@displaymissingcodes}
this is what i have:

whileprintingrecords;
stringvar x;
stringvar array y := ["AE","VD","VA","UV","OA","I","CR","CT","AL","RL","AR","AV"];
numbervar i;
numbervar j := ubound;
stringvar z;
for i := 1 to j do(
if not(y in x) then
z:= z + y + ", ");

if len(z)>2 then
left(z,len(z)-2);

 

[lbass]

//{@reset} for the group header:
whileprintingrecords;
stringvar x := "";
stringvar z := "";

//{@accum} for the detail section:
whileprintingrecords;
stringvar x;
if not({table.eventcode} in x) then
x := x + {table.eventcode}+", ";

//{@displaymissingcodes} for the group footer:
whileprintingrecords;
stringvar x;
stringvar array y := ["AE","VD","VA","UV","OA","I","CR","CT","AL","RL","AR","AV"];//add the potential codes here
numbervar i;
numbervar j := ubound;
stringvar z;
for i := 1 to j do(
if not(y in x) and
not(yin z) then
z := z + y+", "
);
if len(z)>2 then
left(z,len(z)-2)

-LB

 

[ebstarasia]

THANKS LBASS!!! works perfect. problem solved.

 

[ebstarasia]

after reviewing the report, i'm trying to clean it up. Is there away for me to only display the data (house bills) that have missing codes and skip the ones that are not missing anything?

 

[lbass]

You could add the stringvar array to a formula in the report header:
whileprintingrecords;
stringvar array y := ["AE","VD","VA","UV","OA","I","CR","CT","AL","RL","AR","AV"];
numbervar k := ubound;

Then suppress the sections you wish with:

whileprintingrecords;
numbervar k;
distinctcount({table.eventcode},{table.housebills}) = k

-LB

 

[ebstarasia]

does the

whileprintingrecords;
numbervar k;
distinctcount({table.eventcode},{table.housebills}) = k

go in the same formula {@displaymissingcodes} or a separate formula of its own?

 

[MCuthill]

Then suppress the sections you wish with:
whileprintingrecords;
numbervar k;
distinctcount({table.eventcode},{table.housebills}) = k

I would think this goes in the suppression formula field (the "x+2" next to suppress in the section expert).

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."