Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
ACROSS question
 Login/Join
 
Silver Member
posted
Hey

I've got this ridiculous report that has YTD data, along with multiple columns like customer number, company, productcode, product description, ...

My problem is the following.
The report displays data in the following structure (The values under the YTD years are quantities):
YTD
1999 2000 2001 2002 ...
CustNr ProdCode CompCode Qty Qty Qty Qty ...
1 0002 C001 10 15 19 40


However, if I want to add another column, that is NOT a BY-field and NOT a quantity (e.g. Product Description).
I'll get it either in multiple lines, or as a new column under each YTD value.
YTD
1999 2000 2001 2002 ...
CustNr ProdCode CompCode Qty ProdDesc Qty ProdDesc Qty ProdDesc Qty ProdDesc ...
1 0002 C001 10 Prod2 15 Prod2 19 Prod2 40 Prod2 ...

There's got to be some kind of simple solution to this, I can just feel it.
But I'm totally at a loss here.

Any help is appreciated.


WebFocus 5.2.4
iWay ETL Manager 5.2.4
Windows 2000
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Virtuoso
posted Hide Post
If you are just wanting the description to show on a report line then it becomes a 'by' field whehter it really is or not. I'm facing a similar issue to report all sorts of information and try to collapse to one line.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Expert
posted Hide Post
SUM DESCRIPTION OVER QTY
BY ...
ACROSS YTD
if there is more than 1 value per row.
but as Leah says, make it a byfield if you only want one per row, before the YTD across matrix.
If you want this description at the end of the YTD values, then try this:
TABLE FILE CAR
SUM MODEL NOPRINT BY CAR
SUM SALES ACROSS COUNTRY AND COMPUTE MODELL/A40=C1;
BY CAR

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




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by Leah:
If you are just wanting the description to show on a report line then it becomes a 'by' field whehter it really is or not. I'm facing a similar issue to report all sorts of information and try to collapse to one line.


Well I've got the same issue.
It's not just the description I need to show.
There are some other fields (in other tables) I need to show as well, and if I select them as "by" fields, the quantities aren't summed up only by customer anymore.


WebFocus 5.2.4
iWay ETL Manager 5.2.4
Windows 2000
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Silver Member
posted Hide Post
I solved it.
It's dirty, but I solved it.

I first used a SUM of _all_ the fields I needed, including the YTD field and sorted it by the fields I was planning to sort in the ACROSS.
Then I use a HOLD file to store that information, and afterwards I use practically the same query, only with the ACROSS, and with all the fields I needed to collapse to one line as "BY" statements.
I encountered another error however.
Because my column list is pretty large, I got the "maximum amount of BY-fields exceeded"-error.
After commenting a few BY fields the report worked.
Now that I have the result I wanted, I tried scheduling it in ReportCaster and guess what?
It stopped working.

I'm getting the following:
Task error:WARNING. MATCH FIELDS HAVE SAME NAME BUT DIFFERENT FORMATS: MATNR

(MATNR is a DEFINEd column, consisting of the first 4 characters of the productcode)


WebFocus 5.2.4
iWay ETL Manager 5.2.4
Windows 2000
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Member
posted Hide Post
If you are using MATCH command in your query BY field lengths should be equal. Change the define field(MATNR) length to match with other BY field length.


Production WebFOCUS 7.6.2, Platform Win2003, Database Oracle 10g
 
Posts: 26 | Location: Michigan | Registered: July 13, 2005Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by Jeeva:
If you are using MATCH command in your query BY field lengths should be equal. Change the define field(MATNR) length to match with other BY field length.

They're both A4.
I can't see why it's not working in ReportCaster, but it does work in MRE.

GROUP/A4 = EDIT(MAT_MATNR,'9999$$$$$$$$$$$$$$$');

Afterwards I used BY GROUP AS 'MATNR', so I thought it might have had something to do with the ASNAMES not matching. But I just passed "GROUP" instead of "MATNR" and still the same problem but on "GROUP" field now of course.


WebFocus 5.2.4
iWay ETL Manager 5.2.4
Windows 2000
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Expert
posted Hide Post
The difference in behaviour between running the fex in MRE and ReportCaster may be due to some environment setting that gets executed for self-service and MRE fexes but not for Report Caster. (For instance: SET HOLDLIST = PRINTONLY).

Add
?FF hold-file-name1
?FF hold-file-name2

to see which fields are actually in the HOLD files. Perhaps one of the fields is in the hold file twice, once with the original format and once with the new format.

Just a guess...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
Could you post your code...?

I like Susannah's example from Oct 3... that was sounding like it might have met your needs.


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Expert
posted Hide Post
quote:
I can't see why it's not working in ReportCaster, but it does work in MRE.

- Doesn't this sound odd?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
quote:
quote:
I can't see why it's not working in ReportCaster, but it does work in MRE.

- Doesn't this sound odd?

Francis

Not to me, as we've been told repeatedly by IB, the code set isn't really the same for MRE and Caster.

You might in your match be sure the style is

BY MATNR AS MATNR

....

BY GROUP AS MATNR

.....

OR VICE VERSA.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by Piipster:
Could you post your code...?

I like Susannah's example from Oct 3... that was sounding like it might have met your needs.


With the fear of being the laughing stock of this community, here I go Smiler

quote:

-SET ALL=ON;
-SET ASNAMES=ON;
-SET HOLDLIST=PRINTONLY;

-*Products
DEFINE FILE MATERIAL
GROUP/A4 = EDIT(MAT_MATNR,'9999$$$$$$$$$$$$$$$');
END

TABLE FILE MATERIAL
SUM
MAT_DESC_E AS 'MAT_DESC_E'
MAT_GRP_DESC_E AS 'MAT_GRP_DESC_E'
MAT_DIV AS 'MAT_DIV'
MAT_DIV_DESC AS 'MAT_DIV_DESC'
MAT_BU AS 'MAT_BU'
MAT_BU_DESC AS 'MAT_BU_DESC'
MAT_FMLY AS 'MAT_FMLY'
MAT_FMLY_DESC AS 'MAT_FMLY_DESC'
MAT_PRCTR AS 'MAT_PRCTR'
MAT_PRCTR_DESC AS 'MAT_PRCTR_DESC'
MAT_LGPRO AS 'MAT_LGPRO'
BY GROUP AS GROUP
WHERE MAT_MATNR LIKE '____B'
AND MAT_MTART = 'FERT';
ON TABLE HOLD AS 'Products' FORMAT ALPHA
END

JOIN DELIVERYITEM.VBELN
IN DELIVERYITEM
TO DOCUMENTPARTNER.VBELN
IN DOCUMENTPARTNER
AS J001
END

DEFINE FILE DELIVERYITEM
GROUP/A4=EDIT(MATNR,'9999$$$$$$$$$$$$$$$');
YTD2/I8YYMD = DATECVT(WADAT_IST,'YYMD','I8YYMD');
YTD/A4 = EDIT(YTD2,'9999');
END

TABLE FILE DELIVERYITEM
PRINT
LFIMG_TO AS 'LFIMG_TO'
YTD AS 'YTD'
BY DOCUMENTPARTNER.KUNNR AS 'KUNNR'
BY GROUP AS GROUP
BY VKGRP AS 'VKGRP'
BY VKBUR AS 'VKBUR'
BY BUKRS
WHERE DOCUMENTPARTNER.PARVW EQ 'WE';
WHERE DELIVERYITEM.MATNR NE 'REBATE'
AND DELIVERYITEM.MATNR NE 'COMMISSION'
AND DELIVERYITEM.MATNR IS-NOT MISSING
AND DELIVERYITEM.WADAT_IST IS-NOT MISSING
AND DELIVERYITEM.WADAT_IST GE '19980101';
-* 1998, anders pakt em soms data van 1900 (bad data??)
ON TABLE HOLD AS 'YTD' FORMAT ALPHA
END
-*
?FF YTD
?FF Products
-*
MATCH FILE YTD
PRINT LFIMG_TO
YTD
BY KUNNR
BY GROUP
BY VKGRP
BY VKBUR
BY BUKRS

-*Products
RUN
FILE Products
PRINT
MAT_DESC_E
MAT_GRP_DESC_E
MAT_DIV
MAT_DIV_DESC
MAT_BU
MAT_BU_DESC
MAT_FMLY
MAT_FMLY_DESC
MAT_PRCTR
MAT_PRCTR_DESC
MAT_LGPRO
BY GROUP

-*SalesOrg
RUN
FILE SALESORG
PRINT
VKORG
VTWEG
SPART
-*BUKRS
-*VKGRP
GBEZE
-*VKBUR
OBEZE
BZIRK
DBEZE
BY KUNNR
BY VKGRP
BY VKBUR
BY BUKRS

-*Customer
RUN
FILE CUSTOMER
PRINT
NAME1
NAME2
LAND1
LANDB
REGIO
REGIB
PSTLZ
ORT01
STRAS
STCEG
BY KUNNR AS 'KUNNR'

AFTER MATCH HOLD AS 'YTDMATCH' OLD-AND-NEW
END

TABLE FILE YTDMATCH
SUM
-* Customer
NAME1
NAME2
LAND1
LANDB
REGIO
REGIB
PSTLZ
ORT01
STRAS
STCEG
-* SalesOrg
VKORG
VTWEG
SPART
GBEZE
OBEZE
BZIRK
DBEZE
-* Products
MAT_DESC_E
MAT_GRP_DESC_E
MAT_DIV
MAT_DIV_DESC
MAT_BU
MAT_BU_DESC
MAT_FMLY
MAT_FMLY_DESC
MAT_PRCTR
MAT_PRCTR_DESC
MAT_LGPRO
-*
LFIMG_TO
YTD
BY KUNNR
BY GROUP
BY VKGRP
BY VKBUR
BY BUKRS
ON TABLE HOLD AS 'YTDTAB' FORMAT ALPHA
END

TABLE FILE YTDTAB
SUM LFIMG_TO
BY KUNNR
BY GROUP
BY VKGRP
BY VKBUR
BY BUKRS
BY NAME1
BY NAME2
BY LAND1
BY LANDB
BY REGIO
BY REGIB
BY PSTLZ
BY ORT01
BY STRAS
BY STCEG
-* SalesOrg
BY VKORG
BY VTWEG
BY SPART
BY GBEZE
BY OBEZE
BY BZIRK
BY DBEZE
-* Products
BY MAT_DESC_E
BY MAT_GRP_DESC_E
BY MAT_DIV
BY MAT_DIV_DESC
BY MAT_BU
BY MAT_BU_DESC
BY MAT_FMLY
BY MAT_FMLY_DESC
BY MAT_PRCTR
-*BY MAT_PRCTR_DESC
-*BY MAT_LGPRO

ACROSS YTD
-*ON TABLE PCHOLD FORMAT EXCEL
END


WebFocus 5.2.4
iWay ETL Manager 5.2.4
Windows 2000
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Silver Member
posted Hide Post
By the way, first I had single quotes around GROUP. I tried removing them and running it again, but to no avail.


WebFocus 5.2.4
iWay ETL Manager 5.2.4
Windows 2000
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by Francis Mariani:
quote:
I can't see why it's not working in ReportCaster, but it does work in MRE.

- Doesn't this sound odd?


After picking this "report" up again, I got a new clue today when browsing the source code of the report.
I also get the error of "GROUP not having the same format in the two FILEs that need to be matched", in the source code when I run it in "MRE".
Here's the error again:

(FOC063) WARNING. MATCH FIELDS HAVE SAME NAME BUT DIFFERENT FORMATS: GROUP


WebFocus 5.2.4
iWay ETL Manager 5.2.4
Windows 2000
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Virtuoso
posted Hide Post
Tim

blijkbaar een lastig probleem....
is group niet een reserved word in WF?
als je de applicatie uit elkaar sloopt en eerst de eerste twee match componenten eens draait, dus tot en met "products"
Kijken of het dan wel werkt.


I would advice to escalate it to the technical support.




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, 2006Report This Post
Silver Member
posted Hide Post
quote:
Originally posted by FrankDutch:
Tim

blijkbaar een lastig probleem....
is group niet een reserved word in WF?
als je de applicatie uit elkaar sloopt en eerst de eerste twee match componenten eens draait, dus tot en met "products"
Kijken of het dan wel werkt.


I would advice to escalate it to the technical support.


Frank, thx for the reply.
Group is indeed a reserved word, but WebFOCUS doesn't seem to bother that I use it.
In the meanwhile I have found the solution to this issue, through (as you suggested) tearing the report into different pieces.
The problem lies within the MATCHING technique I used.

I had code this code:
 MATCH FILE YTD
PRINT LFIMG_TO
YTD
BY KUNNR
BY GROUP
BY VKGRP
BY VKBUR
BY BUKRS

-*Products
RUN
FILE Products
PRINT
MAT_DESC_E
MAT_GRP_DESC_E
MAT_DIV
MAT_DIV_DESC
MAT_BU
MAT_BU_DESC
MAT_FMLY
MAT_FMLY_DESC
MAT_PRCTR
MAT_PRCTR_DESC
MAT_LGPRO
BY GROUP


Notice how in the first FILE ("OLD"), the sort order goes "KUNNR, GROUP, VKGRP, VKBUR, BUKRS, while the second FILE ("NEW") sort order only has "GROUP".
I had to adapt my report strategey a bit, but now I have this:
MATCH FILE YTD
PRINT 
KUNNR
LFIMG_TO
YTD
-*BY KUNNR
BY GROUP
BY VKGRP
BY VKBUR
BY BUKRS

RUN
FILE Products
PRINT 
MAT_DESC_E
MAT_GRP_DESC_E
MAT_DIV
MAT_DIV_DESC
MAT_BU
MAT_BU_DESC
MAT_FMLY
MAT_FMLY_DESC
MAT_PRCTR
MAT_PRCTR_DESC
MAT_LGPRO
BY GROUP

AFTER MATCH HOLD AS 'YTDMATCH' OLD
END

TABLE FILE YTDMATCH
SUM
MAT_DESC_E
MAT_GRP_DESC_E
MAT_DIV
MAT_DIV_DESC
MAT_BU
MAT_BU_DESC
MAT_FMLY
MAT_FMLY_DESC
MAT_PRCTR
MAT_PRCTR_DESC
MAT_LGPRO
-* Sortfields
BY KUNNR
BY GROUP
BY VKGRP
BY VKBUR
BY BUKRS

PRINT
LFIMG_TO
-* Sortfields
BY KUNNR
BY GROUP
BY VKGRP
BY VKBUR
BY BUKRS
-*
ACROSS YTD
END


Notice how I commented out the BY KUNNR part.
This got the MATCH working.
I also started using "MULTI VERB" statements, to get the things I need on one line and I think I'm almost done with it.
But I forgot about a SET command that _always_ prints the value, even when it's the same value of the previous row.
Does anyone remember this one?


WebFocus 5.2.4
iWay ETL Manager 5.2.4
Windows 2000
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Virtuoso
posted Hide Post
Tim

the set command to get all the data in the report is
SET BYDISPLAY = ON

good luck




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, 2006Report This Post
Silver Member
posted Hide Post
Woot!
Thanks Frank


WebFocus 5.2.4
iWay ETL Manager 5.2.4
Windows 2000
 
Posts: 44 | Location: Belgium | Registered: January 10, 2005Report This Post
Virtuoso
posted Hide Post
it's in version 7.1.3 a clickable option




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, 2006Report This Post
Member
posted Hide Post
quote:
Originally posted by Francis Mariani:
quote:
I can't see why it's not working in ReportCaster, but it does work in MRE.

- Doesn't this sound odd?


I'm dealing with a very similar issue now, and yes, I find it awfully odd.


WebFOCUS Version 7.1.1, Build 118, Apache Tomcat/5.0.19 JAVA version 1.4.2_13
 
Posts: 10 | Registered: October 16, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders