Placing a counter on a group of uniqurely idenified fields 1

[shaleen7]

In my report I list each employee's name along with the applications they've filed. I need to place a counter on each uniquely identified AppNo for each employee.

My report currently looks like this:
Jim
APPNO STATE
IU001 NJ
IU001 NY
IU001 CA
IU002 NJ
IU002 PA
IU005 WA

John
APPNO STATE
IP001 CT
IP001 CA
IP001 NY
IP004 NJ
IP003 MA
IP003 MN



Mary
APPNO STATE
IP008 NJ
IP009 NY
IP006 NJ
IP006 NJ
IP007 FL





Desired results:

Jim
APPNO STATE COUNTER
IU001 NJ 1
IU001 NY
IU001 CA
IU002 NJ 2
IU002 PA
IU005 WA 3

John
APPNO STATE COUNTER
IP001 CT 1
IP001 CA
IP001 NY
IP004 NJ 2
IP003 MA 3
IP003 MN



Mary
APPNO STATE COUNTER
IP008 NJ 1
IP009 NY 2
IP006 NJ 3
IP006 NJ
IP007 FL 4

Any advice?

Thanks

 

[hilfy]

Link from the table with the application/state info to the table that relates the apps to the employee. Use a Left Outer join for this. Then link to the employee table.

Group on Employee and then on application. Add a Summary field - distinct count of application for each employee.

-D

 

[lbass]

You can add a running total to display the counter as it accumulates. Use the running total editor and select {table.appno}, distinctcount, evaluate for every record, reset on change of group (employeeID).

-LB

 

[shaleen7]

Thanks for replying but that works if I want to sum up the # of unique appno for each employee.

I trying to place a counter next each unique app no and then the counter restarts with each employee.
For example:
Jim
APPNO STATE COUNTER
IU001 NJ 1
IU002 NJ 2
IU005 WA 3

John
APPNO STATE COUNTER
IP001 CT 1
IP004 NJ 2
IP003 MA 3


Mary
APPNO STATE COUNTER
IP008 NJ 1
IP009 NY 2
IP006 NJ 3
IP007 FL 4

 

[shaleen7]

lbass That's it. Thanks so much.

Thanks Hilfy.

 

[shaleen7]

I have another related question. What if I wanted to place the counter on the latest date that the app was filed.

APPNO STATE COUNTER APPDATE
IU001 NJ 1 5/2/2004
IU001 NY 2/2/1999
IU001 CA 4/2/2000
IU002 NJ 2 6/2/2004
IU002 PA 5/2/1989
IU005 WA 3 3/7/2003

John
APPNO STATE COUNTER
IP001 CT 1 6/9/2003
IP001 CA 5/3/2001
IP001 NY 3/2/1999
IP004 NJ 2 4/25/1998
IP003 MA 3 5/20/2002
IP003 MN 4/20/1998

 

[lbass]

This will automatically occur if you sort by date descending.

-LB

 

[shaleen7]

I would go with your suggestion but I only want to count the earliest app date for the appno for each employee. I guess I want to filter out the max date and then count.

Right now I get this.
John
APPNO STATE COUNTER APPDATE
IU001 NJ 1 5/2/2004
IU001 NY 1 2/2/1999
IU001 CA 1 4/2/2000
IU002 NJ 2 6/2/2004
IU002 PA 2 5/2/1989
IU005 WA 3 3/7/2003

Desired results:
John
APPNO STATE COUNTER APPDATE
IU001 NJ 1 5/2/2004
IU002 NJ 2 6/2/2004
IU005 WA 3 3/7/2003

 

[lbass]

I guess you mean you only want to DISPLAY the most recent record. You would need to insert a group on {table.AppNo}. You can suppress the group header and footer if you wish. Then go to report->edit selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.AppNo})

This will return only the most recent record per AppNo.

-LB

 

[shaleen7]

My problems with the requirements of this report seem to be growing.
I have 2 more additional problems:
1. Placing a counter that stays in order of occurrence
2. Applying a milestone column that’s based on the counter. The milestone are 1st, 5th, 10th, 15th, 20th


I’m trying to place a counter on the MAX app date of each unique ticket no and then the counter restarts with each employee The counter must be placed in order of occurrence. So if the ticket has two issue dates 5/1/2003 & 6/1/1998

6/1/1998 would be first and 5/1/2003 would be second.






Ultimately these are desired results
Susan
APPNO STATE APPDATE Counter Milestone
IU001 NY 6/1/2002 4
IU001 NJ 8/5/2003 5 5

IU002 CA 7/5/2001 3
IU003 PA 7/5/1998 1 1
IU004 WA 7/5/1999 2

Any help would be greatly appreciated.

 

[lbass]

There is a conflict in what you are saying. First you say:

"I’m trying to place a counter on the MAX app date of each unique ticket no"

So in the following case:

APPNO STATE APPDATE Counter Milestone
IU001 NY 6/1/2002 4
IU001 NJ 8/5/2003 5 5

...it appears you would only want to display and count the 8/5/2003 record.

But then you say,

"So if the ticket has two issue dates 5/1/2003 & 6/1/1998,
6/1/1998 would be first and 5/1/2003 would be second."

...Which suggests you aren't interested in the maximum date after all, but instead, ALL dates. Can you please clarify?

-LB

 

[shaleen7]

I'm sorry. I want all values.

I trying to place a counter next to each unique app no and then the counter restarts with each employee. The counter must be in order of occurrence and I must place a milestone or the nth value.
For example:
Jim


Susan
APPNO STATE APPDATE Counter Milestone
IU001 NY 6/1/2002 4
IU001 NJ 8/5/2003 5 5

IU002 CA 7/5/2001 3
IU003 PA 7/5/1998 1 1
IU004 WA 7/5/1999 2


Thanks.

 

[lbass]

Please note that you are no longer counting "unique" app ids--note that two instances of IU001 are being counted.

Try the following formula {@datecount}:

if {table.date} = NthSmallest(1,{table.date},{table.employeeID}) then 1 else
if {table.date} = NthSmallest(2,{table.date},{table.employeeID}) then 2 else
if {table.date} = NthSmallest(3,{table.date},{table.employeeID}) then 3 else
if {table.date} = NthSmallest(4,{table.date},{table.employeeID}) then 4 else
if {table.date} = NthSmallest(5,{table.date},{table.employeeID}) then 5 else //repeat with increments of 1 each time
0

The formula clauses should repeat up to the maximum number of dates that can occur per individual. You can use this same formula for the milestone column by conditionally suppressing it:

not ({@datecount} in [1,5,10,15,20])

-LB

 

[shaleen7]

I think I've completely confused myself. I'm sorry, again.
I need to place a counter on the earliest application date within the app no. The counter should restart with each employee.

Kim

Appno. State Earliest App Date Counter
IU0001 NJ 1/23/2003 4
IU0002 NY 1/23/1999 3
IU0003 PA 1/23/1998 2
IU0004 NJ 1/23/1995 1




Pam

Appno. State Earliest App Date Counter
IU0005 CA 1/23/1989 1
IU0006 NY 1/23/1999 4
IU0003 PA 1/23/1997 3
IU0007 NJ 1/23/1995 2

 

[lbass]

Do you have the option of reordering the groups by {@earliestappdate}?

-LB

 

[shaleen7]

No.

 

[lbass]

You will have to create a subreport to get the ranking. Insert a subreport. In the subreport add {table.appdate} to the details section, group on {table.employeeID} and then group on {table.appno}. In the subreport, go to report->edit selection formula->GROUP and enter:

{table.appdate} = minimum({table.appdate},{table.appno})

Then (still in the subreport) go to report->topN/group sort and choose "minimum of {table.appdate}" as your topN field in the {table.appno} group tab, and also check "descending" order. This will order the groups from earliest to latest within the subreport. Then go to the field explorer->special fields and drag "Group Number" to the details section of the subreport. Remove all other fields from the details section and suppress all subreport sections except the details section. Link the subreport to the main report on {table.employeeID}. Place the subreport in the Group 2 (App No) Header.

The subreport will now display the ranks for all appnos, repeating these for each appno in the main report, so now you will need to suppress the irrevelant rankings by creating a shared variable. In the main report, create a formula:

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

Place this in the Group 2 (AppNo) header and suppress it.

In the subreport, go to format->section->details->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar appno;

appno <> {table.appno};

This will suppress all but the appno that corresponds to the group header in the main report.

This worked for me without placing the subreport in a lower section than the shared variable, although you might want to insert a second group 2 header section for the subreport to play it safe, and then in the main report format the group header 2_a section to &quot;Underlay following sections.&quot;

-LB