We have a requirement to read m rows with n columns each from a table and show distinct values of a column as different columns for those m rows in the report. For example if we assume the CAR table with Country and Car columns, each different value for Car should go in a new column in the report with a mapping flag against the country indicating that country makes that car.

Assuming the below data from CAR example --

  
COUNTRY	         CAR
-------           ----
ENGLAND	         JAGUAR
ENGLAND	         JENSEN
ENGLAND	         TRIUMPH
JAPAN	         TRIUMPH
JAPAN	         DATSUN
JAPAN	         TOYOTA
ITALY	         ALFA ROMEO
ITALY	         MASERATI
ITALY	         BMW
W GERMANY	         AUDI
W GERMANY	         BMW
FRANCE	         PEUGEOT
FRANCE	         MASERATI
FRANCE	         TRIUMPH

The report should look like --

  
COUNTRY	JAGUAR	JENSEN	TRIUMPH	DATSUN	TOYOTA	ALFA ROMEO   MASERATI  AUDI  BMW   PEUGEOT
-------  ------    ------   -------  ------   ------   ----------   --------  ----  ---   -------
ENGLAND	X	X	X							
JAPAN			X	X	X					
ITALY						X	    X		  X	
W GERMANY								     X	  X	
FRANCE			X				    X			X

Is it doable from WF? Lots of thanks in advance for your help/suggestion.

DEFINE FILE CAR
THESPOT/A1='X';
END

TABLE FILE CAR
SUM THESPOT
BY COUNTRY
ACROSS CAR
END

Check the manual regarding the ACROSS command to see what else you can do with it such as controling the order of columns other than low to high alphabetical/numerical.

Thank you Mickey. I will check this option...

helpme, you can do this with defines but Mickey's way sooooo much cleaner and more efficient. Also you may want to check out COLUMNS option in the reporting manual.

Ira
wf 538 aix 533

Thanks Ira.

I have one more question... can we give the across-total columns a dynamic name... i want to name the across-total column which sums up categories with the category group name. Is it possible?

try this.
DEFINE FILE CAR
THESPOT/A1='X';
COUNTRY_CAR/A30 = COUNTRY|' '|CAR;
END

TABLE FILE CAR
SUM THESPOT
BY COUNTRY
ACROSS CAR NOPRINT
ACROSS COUNTRY_CAR AS ''
END

Thank you Spence.

But I am looking for a different thing...

  
DEFINE FILE EMPLOYEE
THESPOT/A1='X';
DEPARTMENT_N/A30 = DEPARTMENT;
END

TABLE FILE EMPLOYEE
SUM THESPOT
BY CURR_JOBCODE
ACROSS DEPARTMENT AS ''
ACROSS LAST_NAME AS '' ACROSS-TOTAL AS ''
ON TABLE PCHOLD FORMAT EXL2K
END

That gives me the report ...

  
	MIS							PRODUCTION						
	BLACKWOOD	CROSS	GREENSPAN	JONES	MCCOY	SMITH	TOTAL	BANNING	IRVING	MCKNIGHT	ROMANS	SMITH	STEVENS	TOTAL
CURR_JOBCODE														
A01												X		
A07			X										X	
A15									X					
A17		X						X						
B02					X					X				
B03				X										
B04	X										X			
B14						X								

I want the TOTAL column (across-total for MIS) to be as MIS and that of PRODUCTION to be PRODUCTION rather than just TOTAL.

I played around with this a bit and didn't get too far. It would take hold files and Dialogue Manager and lots more code than you have and I'm not sure that it is worth it. You'd have to know how many across values you would need for each department and how many departments. I think it would be ugly.

this might do what you want and i hope there is a better way than this.
SET ASNAMES = ON
DEFINE FILE EMPLOYEE
THESPOT/I2=1;
DEPARTMENT_N/A16 = 'Total' |' '|DEPARTMENT;
LAST_NAME /A16 = LAST_NAME;
END

TABLE FILE EMPLOYEE
SUM THESPOT
BY DEPARTMENT
BY DEPARTMENT_N AS 'LAST_NAME'
BY CURR_JOBCODE
ON TABLE HOLD AS HOLD1
END

TABLE FILE EMPLOYEE
SUM THESPOT
BY DEPARTMENT
BY LAST_NAME
BY CURR_JOBCODE
ON TABLE HOLD AS HOLD2
END

MATCH FILE HOLD1
SUM THESPOT
BY DEPARTMENT
BY LAST_NAME
BY CURR_JOBCODE
RUN
FILE HOLD2
SUM THESPOT
BY DEPARTMENT
BY LAST_NAME
BY CURR_JOBCODE
AFTER MATCH HOLD OLD-OR-NEW
END

TABLE FILE HOLD
SUM THESPOT
ACROSS DEPARTMENT AS ''
ACROSS LAST_NAME AS ''
BY CURR_JOBCODE
END

I've got it! Spence, you were very close but you didn't have any values for the PRODUCTION department. They were all zero. I changed yours to use Universal Concatenation and I swapped the job code and last name sorts.

SET ASNAMES = ON
DEFINE FILE EMPLOYEE
THESPOT/I2=1;
DEPARTMENT_N/A16 = 'Total' |' '|DEPARTMENT;
LAST_NAME /A16 = LAST_NAME;
END

TABLE FILE EMPLOYEE
SUM THESPOT
BY DEPARTMENT
BY CURR_JOBCODE
BY DEPARTMENT_N AS 'LAST_NAME'
ON TABLE HOLD AS HOLD1
END
TABLE FILE EMPLOYEE
SUM THESPOT
BY DEPARTMENT
BY CURR_JOBCODE
BY LAST_NAME
ON TABLE HOLD AS HOLD2
END
TABLE FILE HOLD1
SUM THESPOT
ACROSS DEPARTMENT AS ''
ACROSS LAST_NAME AS ''
BY CURR_JOBCODE
MORE
FILE HOLD2
END
  

Ginny - you found the better way.

Thanks, but I couldn't have done it without the start that you made. Still seems like a lot of work.

Thanks for all your posts. This really helps me...

BTW, can we show the across-total column before the other columns?

this will work in the example
ACROSS HIGHEST LAST_NAME AS ''
it only works b/c no employee has a last name from U - Z

Thank you Spence but we cannot assume that there will not be any last name starting with U-Z. And the other solution proposed to show the across-total (the category group value) column along with other categories also work with the same assumption... is there a way we can do it without depending on the data?

If you add a phony sort key that is 1 for the total lines and 2 for the details, that should work.

SET ASNAMES = ON
DEFINE FILE EMPLOYEE
THESPOT/I2=1;
DEPARTMENT_N/A16 = 'Total' |' '|DEPARTMENT;
LAST_NAME /A16 = LAST_NAME;
SORTKEY/I1=1;
END

TABLE FILE EMPLOYEE
SUM THESPOT
BY SORTKEY
BY DEPARTMENT
BY CURR_JOBCODE
BY DEPARTMENT_N AS 'LAST_NAME'
ON TABLE HOLD AS HOLD1
END
DEFINE FILE EMPLOYEE ADD
SORTKEY/I1=2;
END
TABLE FILE EMPLOYEE
SUM THESPOT
BY SORTKEY
BY DEPARTMENT
BY CURR_JOBCODE
BY LAST_NAME
ON TABLE HOLD AS HOLD2
END
TABLE FILE HOLD1
SUM THESPOT
ACROSS DEPARTMENT AS ''
ACROSS SORTKEY NOPRINT
ACROSS LAST_NAME AS ''
BY CURR_JOBCODE
MORE
FILE HOLD2
END
  

Did you consider a multiverb report?

TABLE FILE CAR
SUM
SALES
BY COUNTRY
SUM SALES
BY COUNTRY
ACROSS MODEL
END

(this is not your final report, but should just give you an idea about the multiverb)

Frank,

I looked at using a multi-verb request early on in this post but it wasn't doing what the poster requested. With Spence's help, I think what we came up with is the way to go and provides the flexibility that the poster wants with being able to name his across fields and vary their position.

Thank you Ginny. It really works...

That was an excellent solution Ginny. Many thanks to you. It really worked just as I wanted it to.. but i wonder if i can print on new tab sheet after every 10th column. As I work with real data which may turn upto 100 or more columns, it is required that we show only few columns on each excel tabsheet... after every 10 columns, the curr_jobcode and another set of last_name should be printed on next tab sheet. Is it doable?

Look up BYTOC in Advanced Search. Or search for it on the forum. There are lots of examples on how to do that, even in some recent posts.

However, since you are doing an ACROSS, that might not help. Seems to me that a virtical sort as opposed to horizontal might be more reasonable. You might need to reconsider your report format.

You could do also do something like this with an Excel template, where you have already built in the display/print /macro functionality that you desire and then WF populates it with data. I have never done anything with templates, but I sat through a session at last year's Summit that explained their purpose and functionality. Someone with more experience using templates would have to provide additional info. Maybe the session is still available somewhere on IBI's website.

And, Helpme, this could probably be done with Dialogue Manager, alternate mastering techniques with occurs, i.e. fancy WebFOCUS techniques. However, based on your questions, you may not be at the skill level necessary to do this.

Unfortunately, most all of us work at other companies. I personally don't have the time to go into extensive research on this. Tried it last week with the techniques I mentioned and didn't make enough progress in a short amount of time to make it worthwhile for me to continue. May be someone else can.

Sorry.

Thanks for your posts Ginny and Darin. I have worked with excel templates but reserved that as my last option for this page-break on across sorting... i expected a more direct approach from WF to achieve this. BYTOC works only on by sorting fields.. isn't it? i expected something like ACROSSTOC to do the same on across sorting fields...

There is no such thing.

The problem with ACROSS is that depending on the value of the sort field, there are varying numbers of values. That makes it difficult to stack the columns with a BY without knowing how many there are. I'm sure it can be done. I just don't have the time to do it for you.

Sorry.

No problem Ginny. I wil try to figure out a way...