I have a report which is to display rankings across fiscal years. The table stores the rank currently, for each vendor, for each fiscal year. My problem deals with multiple vendors having the same rank. Using the code below, I get a report which looks good to start, but only includes one of the vendors which have equal rank.

DEFINE FILE VNDSRANK                                            
PARA_VENNM/A40 = PARAG(31, VENDOR_NM1, '~', 20, PARA_VENNM);    
VENNM1/A20 = GETTOK(PARA_VENNM, 31, 1, '~', 20, VENNM1);        
VENNM2/A20 = GETTOK(PARA_VENNM, 31, 2, '~', 20, VENNM2);        
VENSP/A1 = ' ';                                                 
END                                                             
-**                                                             
TABLE FILE VNDSRANK                                             
SUM VENNM1 AS '' OVER VENNM2 AS '' OVER VENSP AS ''             
ACROSS FY_END AS ''                                             
BY RANKING AS 'Ranking'                                    

			2011					2012
Ranking     
        211 	POLK COMMUNITY		PEGASUS INSURANCE
		COLLEGE				COMPANY INC
  

If I add in a BY statement on the vendor name, I will get those vendors which have the same rank, however, the alignment no longer has the 2012 vendors lining up with the 2011 vendors.

ACROSS FY_END AS ''        
BY RANKING AS 'Ranking'    
BY VENDOR_NM1 NOPRINT      

			2011					2012
Ranking     
        209	.					DELTA TECHNOLOGIES,
		.					INC.
		.	
		OKALOOSA WALTON		.
		COMMUNITY			.
							.
        210	COMPBENEFITS DENTAL	.
		& VISION CO			.
							.
							WELLCARE OF FLORIDA
		.					INC
		.				
		.
        211 	.					HEALTH OPTIONS INC
		.					
		.					PEGASUS INSURANCE
		.					COMPANY INC
		.		
		POLK COMMUNITY		.
		COLLEGE				.
							.
		.					WELLCARE HEALTH 
		.					PLANS
		.					
  

When I add in a BY on fiscal year, it gets closer.

ACROSS FY_END AS ''      
BY RANKING AS 'Ranking'  
BY FY_END NOPRINT        
BY VENDOR_NM1 NOPRINT    
  

			2011					2012
Ranking     
        209	DELTA TECHNOLOGIES,		.
		INC.					.
							.	
							OKALOOSA WALTON
							COMMUNITY
							.
        210	COMPBENEFITS DENTAL	.
		& VISION CO			.
							.
							WELLCARE OF FLORIDA
		.					INC
		.				
		.
        211 	POLK COMMUNITY		.
		COLLEGE 				.
							.
		.					HEALTH OPTIONS INC
		.					
		.
		.					PEGASUS INSURANCE
		.					COMPANY INC
		.		
		.					WELLCARE HEALTH 
		.					PLANS
		.					
  

What I need is for it to look like the following, but I don’t know how to get there, any suggestions? Those vendors listed under 2012 for Ranking 211 all have a ranking of 211 as they are tied.

			2011					2012
Ranking     
        209	DELTA TECHNOLOGIES,		OKALOOSA WALTON
		INC.					COMMUNITY				
							
        210	COMPBENEFITS DENTAL	WELLCARE OF FLORIDA
		& VISION CO			INC
						.
        211 	POLK COMMUNITY		HEALTH OPTIONS INC
		COLLEGE 				
							
		.					PEGASUS INSURANCE
		.					COMPANY INC
		.
		.					WELLCARE HEALTH
		.					PLANS
  

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

Look into the ACROSSPRT setting, though it works only for PRINT statements - perhaps you can change the SUM to a PRINT and use this setting.

The ACROSSPRT parameter reduces the number of report lines within each in a request that uses the PRINT command and an ACROSS phrase.

The syntax is:

SET ACROSSPRT = {NORMAL|COMPRESS}

where:

NORMAL
Does not compress report lines.

COMPRESS
Compresses report lines by promoting data values up within a sort group.

BGronli, try to use this new setting:
SET ACROSSPRT = COMPRESS
According to the documentation:
Compresses report lines by promoting data values up to replace missing values within a sort group.
Compression applies only to ACROSS fields, including ACROSS … COLUMNS. It has no effect on BY fields.
The only data values that are subject to compression are true missing values. If the value of the stored data is either 0 or blank and the metadata indicates that MISSING is ON, that value is not subject to compression.
Regards

As Francis recommended change SUM to PRINT and use SET ACROSSPRT=COMPRESS

SET ACROSSPRT=COMPRESS
-RUN
DEFINE FILE VNDSRANK
PARA_VENNM/A40 = PARAG(31, VENDOR_NM1, '~', 20, PARA_VENNM);
VENNM1/A20 = GETTOK(PARA_VENNM, 31, 1, '~', 20, VENNM1);
VENNM2/A20 = GETTOK(PARA_VENNM, 31, 2, '~', 20, VENNM2);
VENSP/A1 = ' ';
END
 
TABLE FILE VNDSRANK
PRINT
VENNM1 AS '' OVER
VENNM2 AS '' OVER
VENSP AS ''
ACROSS FY_END AS ''
BY RANKING AS 'Ranking'
END

-Ram

This message has been edited. Last edited by: Ram Prasad E,

I have made the changes you all recommended and using PRINT does allow all of the tied vendors to be displayed, however, it did not reduce the number of lines in the report. It still shows the first entry under 2011 with dots on 2012, followed by dots under 2011 and the first entry for 2012.

Any other suggestions?

Thank you.

You may need to use a HOLD file to define which row (ROWNUM) each vendor name should occupy. Here is an example using the CAR file.

TABLE FILE CAR
 SUM
 COMPUTE ROWNUM/I3 = IF (COUNTRY NE LAST COUNTRY) OR (SEATS NE LAST SEATS) THEN 1 ELSE (LAST ROWNUM + 1);
 BY COUNTRY
 BY SEATS
 BY MODEL
 ON TABLE HOLD AS HOLDCAR
END
-*
TABLE FILE HOLDCAR
 SUM MODEL
 BY COUNTRY
 BY ROWNUM NOPRINT
 ACROSS SEATS AS ''
END

SET ACROSSPRT=COMPRESS
-RUN
DEFINE FILE CAR
PARA_VENNM/A40 = PARAG(31, MODEL, '~', 20, PARA_VENNM);
VENNM1/A20 = GETTOK(PARA_VENNM, 31, 1, ' ', 20, VENNM1);
VENNM2/A20 = GETTOK(PARA_VENNM, 31, 2, ' ', 20, VENNM2);
VENSP/A1 = ' ';
RANK_COL/I1=IF COUNTRY EQ 'ENGLAND' THEN 1 ELSE 2;
END

TABLE FILE CAR
PRINT
VENNM1 AS '' OVER
VENNM2 AS '' OVER
VENSP AS ''
BY RANK_COL AS ''
ACROSS CAR AS ''
WHERE COUNTRY IN ('ITALY','ENGLAND');
END

If you still have issue, then it could be because of your data. You may need to share your source data that you are using.

Here is a sample of the test data. This report is to display up to five fiscal years, beginning with current - 4.

FY_END	VENDOR_NM1	           MINORITY	DISB_TOTAL	RANKING
2011	ALLIANCE FOR AGING, INC.       	A	28,067,724.45	205
2012	RSD AMERICA INC.               	A	51,450.00	         205
2011	PEMBROKE PINES FIRE RESCUE     	A	27,953,821.86	206
2012	LAWMEN'S AND SHOOTERS' SUPPLY, 	A	51,416.80	         206
2011	ST. LUCIE COUNTY BOARD OF      	A	27,530,635.51	207
2012	VERTICAL INTEGRATION, INC.     	M	50,659.33	         207
2011	CITRUSCOUNTY BOARDOFCOUNTYCOMMI	A	27,239,840.30	208
2012	SUNPASS PREPAID TOLL PROGRAM   	A	49,595.05	         208
2011	OKALOOSA WALTON COMMUNITY      	A	26,962,605.12	209
2012	DELTA TECHNOLOGIES, INC.       	A	49,188.73	         209
2011	COMPBENEFITS DENTAL & VISION CO	A	26,864,774.81	210
2012	WELLCARE OF FLORIDA INC        	A	47,886.97	         210
2011	POLK COMMUNITY COLLEGE         	A	26,747,439.67	211
2012	HEALTH OPTIONS INC             	A	47,862.36	         211
2012	WELLCARE HEALTH PLANS          	A	47,862.36	         211
2012	PEGASUS INSURANCE COMPANY INC  	A	47,862.36	         211
2011	STATE COLLEGE OF FLORIDA, MANAT	V	26,691,680.34	212
2012	OKALOOSA COUNTY WATER AND SEWER	A	47,808.04	         212
2011	NORTHGATEARINSO, INC.          	A	26,645,616.01	213
2012	CLERK OF THE COURT, BAY COUNTY 	A	47,790.15	         213
  

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

Hi,

I think this should work for you:

  
FILEDEF BGRONLI DISK focalpoint/bgronli.txt
TABLE FILE BGRONLI 
BY RANKING
BY FY_END
RANKED BY VENDOR
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
SUM VENDOR
ACROSS  FY_END
BY     RANKING
BY RANK NOPRINT
END

This message has been edited. Last edited by: Danny-SRL,

Thanks Dan, that did get me closer. Everything looked great up through the first instance of multiple vendors with same rank, because the LIST number matched under each FY. In this instance, that occured at Rank 211. The LIST number for 211 FY 2011 is 211 and under 2012 they are 211, 212, 213. The next entry under 2011 has LIST 212, while under 2012, LIST is 214, because they are different they get put to a different line within the Rank.

I was able to fix this by with the following:

DEFINE FILE VNDSRANK                        
RANKING1/I6 = RANKING;                      
RANKING2/A6 = EDIT(RANKING1);               
PLACE/A6 = RANKING2;                        
END                                         
-**                                         
TABLE FILE VNDSRANK                         
LIST VENDOR_NM1                             
      PLACE                                 
BY FY_END                                   
BY RANKING                                  
&WHERE1                                     
WHERE MINORITY NE 'E' OR 'G' OR 'Z';        
WHERE RANKING NE ' ' AND RANKING NE '0';    
ON TABLE SET MISSING ON                     
ON TABLE SET ACROSSPRT COMPRESS             
ON TABLE HOLD AS VNDRNK                     
END                                         
-**                                                                    
DEFINE FILE VNDRNK                                                     
LIST1/I6 = LIST;                                                       
LIST2/A6 = EDIT(LIST1);                                                
PLACE/A12 = IF RANKING EQ LAST RANKING THEN PLACE||LIST2 ELSE PLACE;   
PARA_VENNM/A40 = PARAG(31, VENDOR_NM1, '~', 20, PARA_VENNM);           
VENNM1/A20 = GETTOK(PARA_VENNM, 31, 1, '~', 20, VENNM1);               
VENNM2/A20 = GETTOK(PARA_VENNM, 31, 2, '~', 20, VENNM2);               
VENSP/A1 = ' ';                                                        
END                                                                    
-**                                                                    
TABLE FILE VNDRNK                                                      
SUM VENNM1 AS '' OVER VENNM2 AS '' OVER VENSP AS ''                    
ACROSS FY_END AS ''                                                    
BY RANKING AS 'Ranking'                                                
BY PLACE NOPRINT                                                         

This keeps the PLACE the same for the first instance of a multiple, then concatenates the LIST to create a unique PLACE for the remainder of the multiples and produces the report as requested.

I didn't understand what was the problem, but if it now works for you, fine.

quote:

ON TABLE SET MISSING ON

What does 'ON TABLE SET MISSING ON' do? I am familiar with HOLDMISS - but not this one.
Thanks for any help understanding it.