I am currently working on a report that pulls all the ID's for a certain ORG from 2006 to now. What I am trying to do is trying to pull ALL the dates where the ID has been on this certain ORG but all I'm getting is the latest date... How can I pull this? I would greatly appreciate your responses.

This message has been edited. Last edited by: <Kathryn Henning>,

Please post your code.

Are you sorting by Date?

No, I am sorting by ID
I have never posted code so I am not sure how is this going to work so forgive me if this is not the correct way
And by the way thank you for looking at it!

  
TABLE FILE DW_STAFFING
-SET &SECTION='WHERE';
-INCLUDE FIOB0210/mrv/staffing_august_2011.fex
PRINT DW_STAFFING.DW_STAFFING.STARTING
DW_STAFFING.DW_STAFFING.ENDING
BY DW_STAFFING.DW_STAFFING.STAFF_ID
BY DW_STAFFING.DW_STAFFING.LAST01
BY DW_STAFFING.DW_STAFFING.FIRST
BY DW_STAFFING.DW_STAFFING.MIDDLE
BY DW_STAFFING.DW_STAFFING.STAFF_GROUP
BY DW_STAFFING.DW_STAFFING.ASSIGNMENT_STATUS
WHERE DW_STAFFING.DW_STAFFING.ASSIGNMENT_ORG_CODE LIKE 'S%';
WHERE DW_STAFFING.DW_STAFFING.ASSIGNMENT_ORG_CODE NE 'S10' OR 'S11';
WHERE DW_STAFFING.DW_STAFFING.STARTING GE '01/01/2006';
ON TABLE PCHOLD FORMAT EXL2K

This message has been edited. Last edited by: karinC,

I don't want the date to be a sort date because I don't want that to be the first field. Please let me kno what I'm doing wrong?

This message has been edited. Last edited by: karinC,

Since you metioned the date as a sorting field I put it there as an invisible field but it's still not working...
First I did it as a BY then I did it as PRINT and none are working

 
PRINT DW_STAFFING.DW_STAFFING.STARTING NOPRINT
DW_STAFFING.DW_STAFFING.STAFF_ID
DW_STAFFING.DW_STAFFING.LAST01
DW_STAFFING.DW_STAFFING.FIRST
DW_STAFFING.DW_STAFFING.MIDDLE
DW_STAFFING.DW_STAFFING.ASSIGNMENT_STATUS
DW_STAFFING.DW_STAFFING.STAFF_GROUP
DW_STAFFING.DW_STAFFING.STARTING
DW_STAFFING.DW_STAFFING.ENDING
WHERE DW_STAFFING.DW_STAFFING.ASSIGNMENT_ORG_CODE LIKE 'S%';
WHERE DW_STAFFING.DW_STAFFING.ASSIGNMENT_ORG_CODE NE 'S10' OR 'S11';
WHERE DW_STAFFING.DW_STAFFING.STARTING GE '01/01/2006';
 

Sorry I don't have full picture so can't say what exactly you are trying to do but if I were you I would start with something like this:-

TABLE FILE DW_STAFFING
PRINT 
   DW_STAFFING.DW_STAFFING.STARTING
   DW_STAFFING.DW_STAFFING.ENDING
BY LOWEST DW_STAFFING.DW_STAFFING.STAFF_ID
BY DW_STAFFING.DW_STAFFING.LAST01
BY DW_STAFFING.DW_STAFFING.FIRST
BY DW_STAFFING.DW_STAFFING.MIDDLE
BY DW_STAFFING.DW_STAFFING.STAFF_GROUP
BY DW_STAFFING.DW_STAFFING.ASSIGNMENT_STATUS
WHERE 
     DW_STAFFING.DW_STAFFING.ASSIGNMENT_ORG_CODE LIKE 'S%';
WHERE 
     DW_STAFFING.DW_STAFFING.ASSIGNMENT_ORG_CODE NE 'S10' OR 'S11';
WHERE 
     DW_STAFFING.DW_STAFFING.STARTING GE '01/01/2006';
ON TABLE PCHOLD FORMAT EXL2K  
END

quote:

I don't want the date to be a sort date because I don't want that to be the first field.

Consider the use of SEQUENCE to place the display fields in the desired order.

I'm wondering if I show you an example of what I'm trying to do would help (for example I would like to see all the dates this ID was under the ASSIGNMENT_ORG_CODE that starts with an S%) does it make more sense? It can happen several times.

 

STAFF_ID	LAST01	FIRST	MIDDLE	ASSIGNMENT STATUS	STAFF_GROUP      ASSIGNMENT_ORG_CODE	         STARTING	   ENDING
A465	DOE	JOE	KEN	P	    215	                   S72	                  01/16/2006   06/06/2006
				P	    215	                   S92	                  08/13/2006   07/13/2007
				P	    215	                   S76	                  08/06/2006   08/31/2007
				P	    215	                   S61	                  09/30/2006   10/10/2010
P038	DOE	JANE	JANE 	P	    215	                   S93	                  01/17/2006   03/14/2007

quote:

Originally posted by Doug:

quote:

I don't want the date to be a sort date because I don't want that to be the first field.

Consider the use of SEQUENCE to place the display fields in the desired order.

I'm sorry I don't understand. Would you please elaborate a little more? Is this something that can work on InfoAssist?

quote:

Originally posted by Jump_faster:
Sorry I don't have full picture so can't say what exactly you are trying to do but if I were you I would start with something like this:-

TABLE FILE DW_STAFFING
PRINT 
   DW_STAFFING.DW_STAFFING.STARTING
   DW_STAFFING.DW_STAFFING.ENDING
BY LOWEST DW_STAFFING.DW_STAFFING.STAFF_ID
BY DW_STAFFING.DW_STAFFING.LAST01
BY DW_STAFFING.DW_STAFFING.FIRST
BY DW_STAFFING.DW_STAFFING.MIDDLE
BY DW_STAFFING.DW_STAFFING.STAFF_GROUP
BY DW_STAFFING.DW_STAFFING.ASSIGNMENT_STATUS
WHERE 
     DW_STAFFING.DW_STAFFING.ASSIGNMENT_ORG_CODE LIKE 'S%';
WHERE 
     DW_STAFFING.DW_STAFFING.ASSIGNMENT_ORG_CODE NE 'S10' OR 'S11';
WHERE 
     DW_STAFFING.DW_STAFFING.STARTING GE '01/01/2006';
ON TABLE PCHOLD FORMAT EXL2K  
END

I tried this again but I am still not having any luck

Can you please try this and tell me what is the lowest date that you get?

 
TABLE FILE DW_STAFFING
 BY LOWEST   DW_STAFFING.DW_STAFFING.STARTING
 ON TABLE PCHOLD FORMAT HTML
END

AND

  
TABLE FILE DW_STAFFING
 BY LOWEST    ASSIGNMENT_ORG_CODE
 ON TABLE PCHOLD FORMAT HTML
END

you can do the BY as NOPRINT and then place the Date where you want it on the report.

The reason it was not working is because the field I was grabbing was only set up to give us only the last dates.
There was a child table that had all the dates. I asked the person that was in charge of building our reporting objects. This table that contain this field was not included there.
I tried the PRINT the date fields first and the rest as BY and it worked like a charm! Thanks everyone for all of your help!

You're using a reporting object and info assist--is it possible that the reporting object itself places a limit on the number of dates for each result.

About "SEQUENCE", Consider this: code

TABLE FILE CAR
SUM DCOST
BY COUNTR BY CAR
ON TABLE SET PAGE-NUM OFF
-*ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=COUNTRY,SEQUENCE=3, $
TYPE=REPORT, COLUMN=CAR,    SEQUENCE=1, $
TYPE=REPORT, COLUMN=DCOST,  SEQUENCE=2, $
ENDSTYLE
END