Focal Point
Need to show distinct row values in different columns
March 04, 2008, 02:22 PM
<Helpme>Need to show distinct row values in different columns
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.
March 04, 2008, 02:49 PM
mgrackinDEFINE FILE CAR
THESPOT/A1='X';
END
TABLE FILE CAR
SUM THESPOT
BY COUNTRY
ACROSS CAR
END
Thanks!
Mickey
| FOCUS/WebFOCUS 1990 - 2011 |
March 04, 2008, 02:51 PM
mgrackinCheck 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 |
March 04, 2008, 02:53 PM
<Helpme>Thank you Mickey. I will check this option...
March 05, 2008, 09:58 AM
irahelpme, 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
March 05, 2008, 12:41 PM
<Helpme>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?
March 05, 2008, 01:21 PM
Spencetry 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.
March 05, 2008, 01:43 PM
<Helpme>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.
March 05, 2008, 02:54 PM
GinnyJakesI 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
March 05, 2008, 03:32 PM
Spencethis 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.
March 05, 2008, 05:43 PM
GinnyJakesI'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
March 06, 2008, 10:01 AM
SpenceGinny - you found the better way.
WF 8 version 8.2.04. Windows.
In focus since 1990.
March 06, 2008, 11:02 AM
GinnyJakesThanks, 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
March 06, 2008, 03:46 PM
<Helpme>Thanks for all your posts. This really helps me...
March 06, 2008, 03:57 PM
<Helpme>BTW, can we show the across-total column before the other columns?
March 06, 2008, 04:07 PM
Spencethis 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.
March 10, 2008, 11:20 AM
<Helpme>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?
March 10, 2008, 12:58 PM
GinnyJakesIf 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
March 10, 2008, 02:52 PM
FrankDutchDid 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 |
March 10, 2008, 03:14 PM
GinnyJakesFrank,
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
March 11, 2008, 12:50 PM
<Helpme>Thank you Ginny. It really works...
March 12, 2008, 04:58 PM
<Helpme>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?
March 12, 2008, 05:14 PM
GinnyJakesLook 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
March 12, 2008, 05:22 PM
Darin LeeYou 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
March 12, 2008, 05:31 PM
GinnyJakesAnd, 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
March 13, 2008, 11:41 AM
<Helpme>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...
March 13, 2008, 11:52 AM
GinnyJakesThere 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
March 13, 2008, 12:21 PM
<Helpme>No problem Ginny. I wil try to figure out a way...