 |
|||||
|
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
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 TopicGo | Search | Notify | Tools |
| ACROSS question | Login/Join |
| Silver Member |
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 | ||
|
| Virtuoso |
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 | |||
|
| Expert |
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 CARThis message has been edited. Last edited by: susannah,
| |||||
|
| Silver Member |
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 | |||
|
| Silver Member |
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 | |||
|
| Member |
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 | |||
|
| Silver Member |
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 | |||
|
| Expert |
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 | |||
|
| Guru |
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. | |||
|
| Expert |
- 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 | |||
|
| Virtuoso |
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 | |||
|
| Silver Member |
With the fear of being the laughing stock of this community, here I go 
WebFocus 5.2.4 iWay ETL Manager 5.2.4 Windows 2000 | |||
|
| Silver Member |
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 | |||
|
| Silver Member |
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 | |||
|
| Virtuoso |
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.
| |||||||
|
| Silver Member |
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 | |||
|
| Virtuoso |
Tim the set command to get all the data in the report is SET BYDISPLAY = ON good luck
| |||||||
|
| Silver Member |
Woot! Thanks Frank WebFocus 5.2.4 iWay ETL Manager 5.2.4 Windows 2000 | |||
|
| Virtuoso |
it's in version 7.1.3 a clickable option
| |||||||
|
| Member |
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 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |