Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Need to show distinct row values in different columns
Go
New
Search
Notify
Tools
Reply
  
Need to show distinct row values in different columns
 Login/Join
 
<Helpme>
posted
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.
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
DEFINE FILE CAR
THESPOT/A1='X';
END

TABLE FILE CAR
SUM THESPOT
BY COUNTRY
ACROSS CAR
END


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Reply With QuoteReport This Post
<Helpme>
posted
Thank you Mickey. I will check this option...
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


aix-533,websphere 5.1.1,apache-2.0,
wf 538(d), 537 (p),
==============
7.6.11 (t) aix 5312
websphere 6.1.19
apache 2.0
 
Posts: 195 | Registered: October 27, 2006Reply With QuoteReport This Post
<Helpme>
posted
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?
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport This Post
<Helpme>
posted
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.
 
Reply With QuoteReport This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
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
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Ginny - you found the better way.


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Thanks, but I couldn't have done it without the start that you made. Still seems like a lot of work.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
<Helpme>
posted
Thanks for all your posts. This really helps me...
 
Reply With QuoteReport This Post
<Helpme>
posted
BTW, can we show the across-total column before the other columns?
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport This Post
<Helpme>
posted
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?
 
Reply With QuoteReport This Post
Expert
posted Hide Post
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
  


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
<Helpme>
posted
Thank you Ginny. It really works...
 
Reply With QuoteReport This Post
<Helpme>
posted
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?
 
Reply With QuoteReport This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
<Helpme>
posted
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...
 
Reply With QuoteReport This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
<Helpme>
posted
No problem Ginny. I wil try to figure out a way...
 
Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Need to show distinct row values in different columns

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.