Distinct count on multiple fields

[TEM3]

Using Crystal Reports 8.5 and Oracle 9i tables......

I need guidance on a game plan for a report on "assignment" productivity.

All activity for a completed assignment is in one table (LABREPT). The activities involved in completing an assignment are the analysis, the review and the final approval. All three contributing activities are tracked by a User ID and a date. For a given date period ({LABREPT.Date Completed}) I want to a distinct count on the number of people involved during that period. The same User ID can appear in any of the three fields ({LABREPT.Analyst}, {LABREPT.Peer Reviewer} and {LABREPT.Final Approval}). I only want to count the User ID once from any of those three fields during the time period. Simply put, I just want to establish how many people were working (doing any of the three activities) during that time period.

Any suggestions?

 

[PatriciaObreja]

If you have the records grouped by {LABREPT.Date Completed}, you can insert a summary DISTINCT COUNT for UserID.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[TEM3]

The problem is that the User ID is used to populate three fields: {LABREPT.Analyst}, {LABREPT.Reviewed By} and {LABREPT.Approved By}. I want to count the User ID once no matter which of the three fields it appears in.

 

[PatriciaObreja]

Then maybe you should create a formula to concatenate the values from the 3 fields, assuming that when 2 of the 3 have no value, then appears "", and then use DISTINCT COUNT for the values of this formula.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[lbass]

Do you have any duplicate data? If not, you could just write a formula like this:

if {table.userID} in [{LABREPT.Analyst},{LABREPT.Peer Reviewer},{LABREPT.Final Approval}] then 1

Place this in the detail section and right click on it and insert a SUM (not a count) on it at the desired level. If you have repeating data, the formula would be a little different. Let me know.

-LB

 

[TEM3]

Don't think that would work. Too many possible combinations.

 

[lbass]

What do you mean?

-LB

 

[TEM3]

Ibass, yes, sometimes the same person (User ID) can Review and Approve the same assignment, in which case the second two fields (Review and Approve) would have the same User ID.

 

[TEM3]

What do you mean?

-LB

I was replying to the concatenation suggestion......

 

[PatriciaObreja]

I see now, so in this case lbass sugestion with "in array" should work, isn't it?

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[TEM3]

What I would do in another "language" would be to create a temporatory table (array) of User ID's and populate it with the contents of the three fields from each appropriate record, but only if the User ID did not already exist. Then I would just count the populated members of the table/array.

Problem is, I don't know how to do that in Crystal.

 

[lbass]

Okay, try this in the detail section:

whileprintingrecords;
stringvar x;

if instr(x,{LABREPT.Analyst}) = 0 then
x := x + {LABREPT.Analyst} + ",";
if instr(x,{LABREPT.Peer Reviewer}) = 0 then
x := x + {LABREPT.Peer Reviewer}+",";
if instr(x,{LABREPT.Final Approval}) = 0 then
x := x + {LABREPT.Final Approval}+",";

Then use a formula like this for the distinctcount in the group footer or report footer:

whileprintingrecords;
stringvar x;
ubound(split(x,","))-1

If you are doing this at the group level, you would have to add a reset formula for x in the group header.

-LB

 

[TEM3]

Thanks. I'll give it a shot.....

 

[TEM3]

The following was suggested by one of our programmers (I have not had a chance to try it either):

Here's some Oracle SQL that seems to fit what you want to do:

select distinct "USER_ID" from
(
select a."Analyst Assigned" "USER_ID"
from labrept a
where a."Date Completed" between '01-JAN-08' and '31-JAN-08'
union
select r."Reviewed By" "USER_ID"
from labrept r
where r."Date Completed" between '01-JAN-08' and '31-JAN-08'
union
select p."Approved By" "USER_ID"
from labrept p
where p."Date Completed" between '01-JAN-08' and '31-JAN-08'
)

 

[lbass]

That looks like it would work, too.

-LB

 

[TEM3]

My programmer has gone home for the weekend and I don't know (much) SQL......

{LABREPT.Date Completed} is a datetime field. Do I need to modify the above formula?

 

[lbass]

You're placing this in a command where the date values appear like strings but represent literals, and this will work with date datatypes in this case.

-LB

 

[TEM3]

I did your formulas as follows:

whileprintingrecords;
stringvar x;

if instr(x,{LABREPT.Analyst Assigned}) = 0 then
x := x + {LABREPT.Analyst Assigned} + ",";
if instr(x,{LABREPT.Reviewed By}) = 0 then
x := x + {LABREPT.Reviewed By}+",";
if instr(x,{LABREPT.Approved By}) = 0 then
x := x + {LABREPT.Approved By}+",";

When the report runs I get a error that the stringvar (x) is over 254 characters. Makes sense since there is about 30 possible User ID's of up to 8 characters.

 

[lbass]

Time to upgrade. Try this:

whileprintingrecords;
stringvar x;
stringvar y;

if instr(x,{LABREPT.Analyst Assigned}) = 0 then
if len(x +{LABREPT.Analyst Assigned} +",") > 253 then
x := x else
x := x + x +{LABREPT.Analyst Assigned} +",";
if len(x + {LABREPT.Analyst Assigned}+",") > 253 then (
if instr(y,{LABREPT.Analyst Assigned}) = 0 then
if len(y + {LABREPT.Analyst Assigned}+",") > 253 then
y := y else
y := y + {LABREPT.Analyst Assigned}+","
);

if instr(x,{LABREPT.Reviewed By}) = 0 then
if len(x +{LABREPT.Reviewed By} +",") > 253 then
x := x else
x := x + x +{LABREPT.Reviewed By} +",";
if len(x + {LABREPT.Reviewed By}+",") > 253 then (
if instr(y,{LABREPT.Reviewed By}) = 0 then
if len(y + {LABREPT.Reviewed By}+",") > 253 then
y := y else
y := y + {LABREPT.Reviewed By}+","
);

if instr(x,{LABREPT.Approved By}) = 0 then
if len(x +{LABREPT.Approved By} +",") > 253 then
x := x else
x := x + x +{LABREPT.Approved By} +",";
if len(x + {LABREPT.Approved By}+",") > 253 then (
if instr(y,{LABREPT.Approved By}) = 0 then
if len(y + {LABREPT.Approved By}+",") > 253 then
y := y else
y := y + {LABREPT.Approved By}+","
);

Then change the display formula to:
whileprintingrecords;
stringvar x;
stringvar y;
ubound(split(x,","))-1 +
ubound(split(y,","))-1

This assumes that there will not be a total string of unique IDs greater than 506 in length (including commas), i.e., this formula should accommodate up to about 56 user IDs.

-LB

 

[TEM3]

I will try the new one, but, once I narrowed my groups (by laboratory and lab section) and initialized x to null between each group, it worked fine.

Now my next challenge is to determine the number of working days in each month as defined by the count of dates where something was done. So on any particular day of the month is there is a corresponding date in one of the following fields, that date will be considered a work day and added to the total:

{LABREPT.Date Completed}
{LABREPT.Review Date}
{LABREPT.Approve Date}

Any suggestion on doing this?? I am grouping on {LABREPT.Date Completed}.

The number my boss wants is assignments per analyst per work day.