First instance of a number within multiple groups 1

[c0wt0wn1]

I have a report that is pulling the sizes that our Employees wear. This report is for mass orders. I would like to get only the first instance of an Employee ID. Here is what the report kinda looks like.

Group 1 Garment
Group 2 Sizes
Details Garments Linked to Employee ID

I have the details section suppressed and that is where the counter for Employee ID is. The problem I am having is that 1 employee can have multiple sizes. For ordering all I really care about is the frist size that the Employee ID shows up in.

This is what I am getting.

Group 1 Jacket-Specific Type
Group 2 Size S Count of Emp = 4
(Suppressed) Emp # 1 shows up here
Group 2 Size M Count of Emp = 7
(Suppressed) Emp # 1 also shows up here.

I want only the first time that Emp # 1 shows up. How can I set this up... Please help.


-c0wt0wn1-



-c0wt0wn1-
MikeGose

 

[synapsevampire]

Rather than showing what you don't want (which is meaningless), post example data and what you do want output.

Add to that the Crystal version, database and connectivity used, and people will have a better chance of assisting you.

I would guess that if you add in a group by employee within the size, and then in the Report->Edit Selection Formula-Group add something like:

{table.size} = minimum({table.size,{table.empid})

You'll get the minimum size for each employee at that group level, but it's hard to be certain from this amount of information.

If you have employees that have multiple sizes for the same garment type, then hopefully you have a date stamp to qualify that the max date is the one you want.

-k

 

[c0wt0wn1]

Sorry... This should help

Group 1 Jacket-'Specific Type'
Group 2 Size S Count of Emp = 3
(Suppressed) Emp # 1
(Suppressed) Emp # 2
(Suppressed) Emp # 3

Group 2 Size M Count of Emp = 4
(Suppressed) Emp # 1
(Suppressed) Emp # 4
(Suppressed) Emp # 5
(Suppressed) Emp # 6

I only want to count the first time that Emp # 1 shows up.
I have to group by size because it is a mass order for the specific type of jacket.

I want the report to produce this type of results
Group 1 Jacket-'Specific Type'
Group 2 Size S Count of Emp = 3
(Suppressed) Emp # 1
(Suppressed) Emp # 2
(Suppressed) Emp # 3

Group 2 Size M Count of Emp = 3
(Suppressed) Emp # 1 (Exists in Size S. Do not count)
(Suppressed) Emp # 4
(Suppressed) Emp # 5
(Suppressed) Emp # 6




-c0wt0wn1-
MikeGose

 

[lbass]

I, for one, am concerned that you are going to have badly dressed employees, if the groups are ordered from small to medium to large and you always choose the first instance (smallest) of the garments.

-LB

 

[c0wt0wn1]

I had the same concern...

However, this is an extremely small example.
I work for Disney and our Costume counts and orders are huge. within the thousands in different categories. So you can see that if I have multiplle counts of the same person then I would order to many garments. That could cost the company hundreds of thousands of dollars.



-c0wt0wn1-
MikeGose

 

[lbass]

I meant that humorously, but wouldn't it cost the company thousands of dollars if the clothes you ordered were so small that people couldn't wear them and then had to be reordered? In my experience, people tend to get larger over time, rather than smaller.

-LB

 

[c0wt0wn1]

OK... I saw the above from -K and I missed something the first time i read it. I have the group and I am finally getting gounts for everything but I am still getting counts of each employee ID under group 2

I am using CR 8.5 on a SQL 2000 backend.. Again here is what is happening and what I want to happen...

Group 1 Jacket-'Specific Type'
Group 2 Size S Count of Emp = 3
(Suppressed) Emp # 1
(Suppressed) Emp # 2
(Suppressed) Emp # 3

Group 2 Size M Count of Emp = 4
(Suppressed) Emp # 1
(Suppressed) Emp # 4
(Suppressed) Emp # 5
(Suppressed) Emp # 6

I only want to count the first time that Emp # 1 shows up.
I have to group by size because it is a mass order for the specific type of jacket.

I want the report to produce this type of results
Group 1 Jacket-'Specific Type'
Group 2 Size S Count of Emp = 3
(Suppressed) Emp # 1
(Suppressed) Emp # 2
(Suppressed) Emp # 3

Group 2 Size M Count of Emp = 3
(Suppressed) Emp # 1 (Exists in Size S. Do not count)
(Suppressed) Emp # 4
(Suppressed) Emp # 5
(Suppressed) Emp # 6


-c0wt0wn1-
MikeGose

 

[lbass]

I think you can handle this by using a subreport. In the subreport, group by garment and then by employeeID and create a formula {@sharedsize} to display in the detail section:

whileprintingrecords;
shared stringvar size := {table.size};

Still in the subreport, go to report->edit selection formula->GROUP and enter:

{table.size} = minimum({table.size},{table.employeeID})

You can change this to maximum if you want, since "L" will be the minimum if the options are "L","M", or "S". This will result in one detail record per employee. Suppress all sections of the subreport. Link the subreport on the garment field and the employeeID field, and place the subreport in the detail_a section of the main report.

In the main report, create three formulas:

//{@reset} for the group (garment) header:
whileprintingrecords;
numbervar cnt := 0;
shared stringvar size := "";

//{@cnt} for the detail_b section (Insert another detail section):
whileprintingrecords;
numbervar cnt;
shared stringvar size;

if size = {table.size} then cnt := cnt + 1 else cnt := cnt;

//{@displcnt} for the group (garment) footer:
whileprintingrecords;
numbervar cnt;

You can remove borders and resize the subreport to be as small as possible, and also format the group header in the main report (format->section->group header->) to "Underlay following sections." You can suppress the detail_b section, but the detail_a section cannot be hidden or suppressed, just made small, for this to work.

If you had CR 9, you could do this differently, by creating a cumulative string, and checking for the presence of the employeeID in the string, but with 8.5 you would run up against the 254-character limit.

-LB

 

[c0wt0wn1]

Thanks to all that tried to help me. I greatly appreciate it.

I solved my problem using an array that automatically added any emp id that was not already in the array then checked in each record in each group if the emp id was already in the array.

I will post the code tomorrow when I return to my desk.

Thanks again...

-c0wt0wn1-
MikeGose

 

[bek22]

I suppose my suggestion might be a bit late as you've already found a solution that works but I'll post it anyway in case it is helpful.

Instead of writing functions in Crystal Reports to filter out subsequent listings of a particular employee, you could let the database do that work for you by using the GROUP BY clause and a Min or Max function in the Crystal SQL Designer. Here's an example query I wrote using the Northwind sample database in Access:

SELECT CompanyName, Min(OrderID) AS MinOrdId
FROM (SELECT Customers.CompanyName, Orders.OrderID
FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID)
GROUP BY CompanyName;

This query returns only one result per customer even if they've placed more than one order. In your case you'd probably do something like:

SELECT EmployeeName, Min(SizeID) AS MinSizeId
FROM (SELECT Employees.EmployeeName, Sizes.SizeID
FROM Employees INNER JOIN Sizes ON Employees.EmployeeID=Sizes.EmployeeID)
GROUP BY EmployeeName;

Then all that's left to do within Crystal Reports is list and group what the query spits out.