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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Running Totals with report using ACROSS

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Running Totals with report using ACROSS
 Login/Join
 
Member
posted
Has anyone been able to successfully use a running total in a report similar to the following (this is the detail of the report which uses an ACROSS by Business Month) :
Jan Feb Mar Apr May Jun .... Dec
SortField1 SortField2 2005
2006
2007
2008

SortField2 2005
2006
2007
2008

Then, I want to be able to RECAP and print a SUBFOOT section on SortField1 that still has the business year line of detail... looking like:
SortField1 Totals 2005
2006
2007
2008

My problem is that I can not successfully keep a running total of just 2006 volumes and 2007 volumes, etc.

I'm currently using WF 7.62 with a SQL database.

Any help would be appreciated!
THanks!


WF 7.62, SQL 2000, Windows XP
 
Posts: 16 | Location: Philadelphia, PA | Registered: May 12, 2005Report This Post
Virtuoso
posted Hide Post
You need to place [ code ] tags around your sample to preserve the spacing.

I take it you want something like this:
  

                                 Jan  Feb  Mar  Apr May Jun .... Dec
 State       City        Year
 ----------  ----------
 aaaaaaaaaa  pppppppppp  2005      x    x    x    x   x   x        x
                         2006      x    x    x    x   x   x        x
                         2007      x    x    x    x   x   x        x
                         2008      x    x    x    x   x   x        x

             qqqqqqqqqq  2005      x    x    x    x   x   x        x
                         2006      x    x    x    x   x   x        x
                         2007      x    x    x    x   x   x        x
                         2008      x    x    x    x   x   x        x
             .                        .                        .
             .                        .                        .
             .                        .                        .

             zzzzzzzzzz  2005      x    x    x    x   x   x        x
                         2006      x    x    x    x   x   x        x
                         2007      x    x    x    x   x   x        x
                         2008      x    x    x    x   x   x        x

 aaaaaaaaaa  Totals      2005      x    x    x    x   x   x        x
                         2006      x    x    x    x   x   x        x
                         2007      x    x    x    x   x   x        x
                         2008      x    x    x    x   x   x        x



Look at FRL and its chart-of-account feature. That may provide a neat solution.

Otherwise, I suggest you dispense with ACROSS.
define the four years and the 48 monthly figures as separately named variables,

year/YY=...; month/MT=...;

Y1/A4 WITH X = '2005'; ...
Y4 . . . = '2008'

X101/D6 = if year eq 2005 and month eq 'JAN' then X else 0;
. . .
X412/D6 = if year eq 2008 and month eq 'DEC' then X else 0;

Build the City- and State-level display blocks as recaps. which in effect pick up a SUM NOPRINT value of each variable at the appropriate sort level.

This message has been edited. Last edited by: j.gross,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
Or you can create a dummy city "Totals"

run your normal report without totals and an other report that says something like

DEFINE FILE XXX
DUMMYCITY/A25='Totals';
END
TABLE FILE XXX
SUM SALES
BY STATE
BY DUMMYCITY
BY YEAR
ACROSS MONTH
END


If the overall report takes a lot of time, you can first put the data into an hold file, run the first report and the second from that HOLD file.
You also can combine the data first into one dataset with the MORE command and build your final report direct from the HOLD file as one report.
To get the DUMMYCITY records at the end you may need an extra sort field.

I hope this helps a bit.




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
Virtuoso
posted Hide Post
medougherty and Jack,

This is a perfect case for McGuyver.

  
-* File CarMcGuyver.fex
JOIN BLANK WITH BODYTYPE IN CAR TO BLANK IN FSEQ AS B_
-*
DEFINE FILE CAR
BLANK/A1 WITH BODYTYPE=' ';
XCOUNTRY/A10=IF COUNTER EQ 3 THEN 'Grand' ELSE COUNTRY;
XCAR/A16=IF COUNTER EQ 2 OR 3 THEN 'Total' ELSE CAR;
END
-*
TABLE FILE CAR
SUM SALES
BY COUNTER NOPRINT
BY XCOUNTRY AS COUNTRY 
BY XCAR AS CAR
BY SEATS
ACROSS MPG IN-GROUPS-OF 10 ACROSS-TOTAL
WHERE COUNTER LE 3
HEADING
"Sales by Country, Car and Bodytype"
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
ENDSTYLE
END


You can adapt the sorting, if necessary.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Member
posted Hide Post
Danny,
I tried your example and it comes up with no records... I have a CAR file, but what is FSEQ?

- Mary Beth


WF 7.62, SQL 2000, Windows XP
 
Posts: 16 | Location: Philadelphia, PA | Registered: May 12, 2005Report This Post
Virtuoso
posted Hide Post
Mary Beth,

FSEQ is the McGuyver file. I'll send it to you. In the mean time, look on the IBI site for McGuyver. If you don't find anything ther is a ppt that I can send you.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Gold member
posted Hide Post
quote:
Originally posted by medougherty:
Danny,
I tried your example and it comes up with no records... I have a CAR file, but what is FSEQ?

- Mary Beth


FSEQ is a master file for a file of 2 segments.

take a look at this link from ibi site: The MacGyver Technique by Noreen Redden


PROD: WebFOCUS 7.1.0 on Linux/Tomcat 5.5.12 (standalone)/Informix on AIX
TEST: WebFOCUS 7.1.3 on Linux/Tomcat 5.5.16 (standalone)/Informix on AIX
 
Posts: 53 | Location: Montreal,Quebec,Canada | Registered: February 13, 2006Report This Post
Virtuoso
posted Hide Post
Mary Beth,

FSEQ.mas
  
FILENAME=FSEQ,SUFFIX=FIX,
    DATASET=C:\IBI\APPS\FOCALPOINT\FSEQ.DAT
  SEGNAME=CHAR1,SEGTYPE=S0
   FIELDNAME=BLANK,BLANK,A1,A1,$
  SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
   FIELDNAME=CHAR,CHARS,A1,A1,$
   FIELDNAME=COUNTER,ORDER,I2,I4,$


Change the DATASET to point to where you store the FSEQ.dat file.

FSEQ.dat
  
 FILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVER

IMPORTANT!!! The first character of the file must be a space!!! As for the rest you can put what ever you want.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Member
posted Hide Post
Thanks for the article!

-MB Dougherty


WF 7.62, SQL 2000, Windows XP
 
Posts: 16 | Location: Philadelphia, PA | Registered: May 12, 2005Report This Post
Member
posted Hide Post
quote:

J.G.,

Thanks... you definitely have me going in the right direction with dropping the ACROSS and using variables. I'm actually using the ACROSS, holding the file, thereby making the ACROSS output into fields, and then using the DEFINE variables....see code below...

My one last issue is that I can't seem to get the variables to reset themselves when sale_chan_code changes. I've tried using the "WITH" in the DEFINE, but it doesn't seem to have any effect. Any suggestions?

DEFINE FILE SALEDATA
Y1M1_SC_GALS/D20.0 WITH sale_chan_code = IF business_year EQ 2005 THEN Y1M1_SC_GALS + sal1;
Y1M1_SC_SPOTC/D20.0 WITH sale_chan_code= IF business_year EQ 2005 THEN Y1M1_SC_SPOTC + spo1;
Y2M1_SC_GALS/D20.0 WITH sale_chan_code= IF business_year EQ 2006 THEN Y2M1_SC_GALS + sal2;
Y2M1_SC_SPOTC/D20.0 WITH sale_chan_code= IF business_year EQ 2006 THEN Y2M1_SC_SPOTC + spo2;
END
TABLE FILE SALEDATA
PRINT
spo1
sal1
MAR1
Y1M1_SC_GALS
Y1M1_SC_SPOTC
spo2
sal2
MAR2
Y2M1_SC_GALS
Y2M1_SC_SPOTC
BY sos_code
BY fuel_type_code
BY deal_type_code
BY HIGHEST sale_chan_code
BY pricing_category
BY business_year
ON sale_chan_code RECAP
Y1M1_SC_MARGIN/D6.4 = IF Y1M1_SC_GALS NE 0 THEN Y1M1_SC_SPOTC/Y1M1_SC_GALS;
Y2M1_SC_MARGIN/D6.4 = IF Y2M1_SC_GALS NE 0 THEN Y2M1_SC_SPOTC/Y2M1_SC_GALS;
ON sale_chan_code SUBFOOT
"--- YEAR 1 (2005) ---> <+0> "--- YEAR 2 (2006) ---> <+0> END

Thanks again for all your help!
Mary Beth


WF 7.62, SQL 2000, Windows XP
 
Posts: 16 | Location: Philadelphia, PA | Registered: May 12, 2005Report This Post
Virtuoso
posted Hide Post
Mary Beth,

You need an ELSE in your DEFINEs.

Did you try McGuyver? It saves you having to define all the columns.

If you do decide to define fields, I would suggest some dialog manager in order not to hard code the years - so that you do not have to change your procedure next year...


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
Mary Beth

The solution you have chosen is not the most easy one.

The McGuyver solution is the best one, but not the most easy way to understand.

I'll write down more detailed my solution.

DEFINE FILE XXX
SEQCODE/I1=1;
END
TABLE FILE XXX
SUM SALES
BY STATE
BY CITY
BY YEAR
BY MONTH
BY SEQCODE
ON TABLE HOLD AS HOLD1
END
-RUN
DEFINE FILE HOLD1
DUMMYCITY/A25='Totals'; 
-* the dummycity field should have the same format as the CITY field.
SEQCODE/I2=2;
END
SET ASNAMES = ON
-*
TABLE FILE HOLD1
SUM SALES
BY STATE
BY DUMMYCITY AS CITY
BY YEAR
BY MONTH
BY SEQCODE
ON TABLE HOLD AS HOLD2
END
-RUN
TABLE FILE HOLD1
PRINT *
ON TABLE HOLD AS HOLD3
MORE
FILE HOLD2
END
-RUN
TABLE FILE HOLD3
SUM
SALES
BY SEQCODE NOPRINT
BY STATE
BY CITY
BY YEAR
ACROSS MONTH
END


This should do what you want.

If you need an overall total you can ad a 3th HOLD file.




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
I haven't tried the McGuyver yet because my I need WF admin to create the master file for me (our development is locked down as far as masters/synonyms are concerned).

I will try it once I get the master created. Thanks!
MBD


WF 7.62, SQL 2000, Windows XP
 
Posts: 16 | Location: Philadelphia, PA | Registered: May 12, 2005Report This Post
Expert
posted Hide Post
You can create your own private master and file for FSEQ in your development directory. We allow our developers to do that. We only lock down the enterprise RDBMS masters.

I would like that you would be alright.


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, 2006Report This Post
Member
posted Hide Post
IF I have the following DEFINES, then the variables never reset themselves when sale_chan_code changes (as if the WITH sale_chan_code phrase isn't even there):
  
Y1M1_SC_GALS/D20.0 WITH sale_chan_code = IF business_year EQ 2005 THEN  Y1M1_SC_GALS + sal1 ELSE Y1M1_SC_GALS + 0;
Y1M1_SC_SPOTC/D20.0 WITH sale_chan_code= IF business_year EQ 2005 THEN Y1M1_SC_SPOTC + spo1 ELSE Y1M1_SC_SPOTC + 0;
Y2M1_SC_GALS/D20.0 WITH sale_chan_code= IF business_year EQ 2006 THEN Y2M1_SC_GALS + sal2 ELSE Y2M1_SC_GALS + 0;
Y2M1_SC_SPOTC/D20.0 WITH sale_chan_code= IF business_year EQ 2006 THEN Y2M1_SC_SPOTC + spo2 ELSE Y2M1_SC_SPOTC + 0;



IF I have these DEFINES then the recapped value is always zero:
  
Y1M1_SC_GALS/D20.0 WITH sale_chan_code = IF business_year EQ 2005 THEN  Y1M1_SC_GALS + sal1 ELSE 0; 
Y1M1_SC_SPOTC/D20.0 WITH sale_chan_code= IF business_year EQ 2005 THEN Y1M1_SC_SPOTC + spo1 ELSE 0; 
Y2M1_SC_GALS/D20.0 WITH sale_chan_code= IF business_year EQ 2006 THEN Y2M1_SC_GALS + sal2 ELSE 0;
Y2M1_SC_SPOTC/D20.0 WITH sale_chan_code= IF business_year EQ 2006 THEN Y2M1_SC_SPOTC + spo2 ELSE 0; 


What do I need in the ELSE that'll reset the variable's value to zero when sale_chan_code changes?

MBD


WF 7.62, SQL 2000, Windows XP
 
Posts: 16 | Location: Philadelphia, PA | Registered: May 12, 2005Report This Post
Expert
posted Hide Post
Have you tried the LAST syntax and is your data sorted, i.e.

FLD/D20= IF SALE_CHAN_CODE NE LAST SALE_CHAN_CODE AND ....;

You can do this in a define or compute. If done in a define, the data must be sorted by sale_chan_code for this to work. If done in a compute, you must be sorting on sale_chan_code for this to 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, 2006Report This Post
Member
posted Hide Post
Ginny,

Thank you!! Here's the defines that finally work properly:
DEFINE FILE SALEDATA
Y1M1_SC_GALS/D20.0 = IF (sale_chan_code EQ LAST sale_chan_code) AND (business_year EQ 2005) THEN Y1M1_SC_GALS + sal1 ELSE IF business_year EQ 2005 THEN sal1;
Y1M1_SC_SPOTC/D20.0 = IF (sale_chan_code EQ LAST sale_chan_code) AND (business_year EQ 2005) THEN Y1M1_SC_SPOTC + spo1 ELSE IF business_year EQ 2005 THEN spo1;
Y2M1_SC_GALS/D20.0 = IF (sale_chan_code EQ LAST sale_chan_code) AND (business_year EQ 2006) THEN Y2M1_SC_GALS + sal2 ELSE IF business_year EQ 2006 THEN sal2;
Y2M1_SC_SPOTC/D20.0 = IF (sale_chan_code EQ LAST sale_chan_code) AND (business_year EQ 2006) THEN Y2M1_SC_SPOTC + spo2 ELSE IF business_year EQ 2006 THEN spo2;
END

Thanks a million!
MBD


WF 7.62, SQL 2000, Windows XP
 
Posts: 16 | Location: Philadelphia, PA | Registered: May 12, 2005Report This Post
Virtuoso
posted Hide Post
I agree with Dan -- McGuyver is the way to go (why didn't I see that earlier? Sweating)

Here's a complete procedure, using specimen data from ggorder.

I separated gathering of specimen data (into HOLD) from generation of the report (against HOLD). The latter part should be readily re-usable.

  
-DEFAULTH &ECHO=OFF
-****************************************** GET SAMPLE DATA
JOIN CLEAR *
USE
 ibisamp/ggorder.foc AS ggorder
END
DEFINE FILE ggorder
 YEAR/YY=ORDER_DATE;
 MONTH/MT  =ORDER_DATE;
END
TABLE FILE ggorder
 SUM QUANTITY
 BY STORE_CODE
 BY PRODUCT_CODE
 BY YEAR
 BY MONTH
 IF STORE_CODE   CONTAINS '00'
 IF PRODUCT_CODE CONTAINS '00' OR '11' OR '44'
ON TABLE HOLD
END
-RUN
-********************************************************************************************************
-DEFAULTH &SORT1='STORE_CODE',    &NAME1='Store'
-DEFAULTH &SORT2='PRODUCT_CODE',  &NAME2='Prod.'
EX MAKESEQ 1,3 , ECHO=&ECHO
JOIN CLEAR *
JOIN BLANK WITH QUANTITY IN HOLD TO BLANK IN FSEQ AS A:
DEFINE FILE HOLD
 BLANK/A1 WITH QUANTITY = ' ';
 Z1/A1    = DECODE COUNTER(      1 1,        2 1,        3 3,         ELSE '?');
 Z2/A1    = DECODE COUNTER(      1 1,        2 2,        3 3,         ELSE '?');
 Sort1/A10=IF COUNTER EQ 1 THEN    &SORT1
      ELSE IF COUNTER EQ 2 THEN                &SORT1
      ELSE IF COUNTER EQ 3 THEN                            '*TOTAL'   ELSE '?';
 Sort2/A10=IF COUNTER EQ 1 THEN    &SORT2
      ELSE IF COUNTER EQ 2 THEN               '*TOTAL'
      ELSE IF COUNTER EQ 3 THEN                            ' '        ELSE '?';
END
TABLE FILE HOLD
 SUM QUANTITY
   BY Z1   NOPRINT    BY Sort1 AS '&NAME1' UNDER-LINE
   BY Z2   NOPRINT    BY Sort2 AS '&NAME2'
   BY YEAR AS Year
   ACROSS MONTH AS Mo.
 IF COUNTER FROM 1 TO 3
END
-********************************************************************************************************


The utility I use to establish the McGuyver file:
  
-* File MakeSeq.fex
-DEFAULTH &1=' '  , &2=' ', &ECHO=OFF
-DEFAULTH &FIRST=1, &LAST=100,
-DEFAULTH &FILE=FSEQ, &KEY=BLANK, &COUNTER=COUNTER
-SET &FIRST = IF @|&1 NE @ THEN &1 ELSE &FIRST ;
-SET &LAST  = IF @|&2 NE @ THEN &2 ELSE &LAST  ;

 EX -LINES 7
 EDAPUT MASTER, &FILE, CV, MEM
  FILE=&FILE, SUFFIX=FOC, $
    SEGNAME=FSEQ1, SEGTYPE=S1, $
      FIELD=&KEY, , A1, ACCEPT=' ', INDEX=I, $
    SEGNAME=FSEQ2, SEGTYPE=S1, PARENT=FSEQ1, $
      FIELD=&COUNTER, , I4, $
 END

 CREATE FILE &FILE

 MODIFY FILE &FILE
  FREEFORM &KEY &COUNTER
  MATCH &KEY &COUNTER
    ON NOMATCH INCLUDE
    ON MATCH REJECT
 DATA
-SET &echo=&ECHO;
-REPEAT SEQ.LOOP FOR &I FROM &FIRST TO &LAST ;
 ' ', &I, $
-SET &ECHO=OFF;
-SEQ.LOOP
-SET &ECHO=&echo;
 END


HTH.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
Mary Beth,

Whatever technique you choose, ALWAYS have an ELSE in an IF-THEN-ELSE logical computation. If you don't, you will never really know what Focus will do when the condition is not true.

Concerning your site's ferocious security when it comes to master files... you can create them on the fly!

  
FILEDEF MCGMAS DISK FSEQ.MAS
FILEDEF MCGDAT DISK FSEQ.DAT
-RUN
-WRITE MCGMAS FILENAME=FSEQ,SUFFIX=FIX
-WRITE MCGMAS  SEGNAME=CHAR1,SEGTYPE=S0
-WRITE MCGMAS   FIELDNAME=BLANK,BLANK,A1,A1,$
-WRITE MCGMAS  SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
-WRITE MCGMAS   FIELDNAME=CHAR,CHARS,A1,A1,$
-WRITE MCGMAS   FIELDNAME=COUNTER,ORDER,I2,I4,$
-RUN
-WRITE MCGDAT  FILEFORMCGUYVER
-RUN
FILEDEF FSEQ DISK FSEQ.DAT
-RUN

Add these lines to the beginning of the code (any of the examples using FSEQ) and it should work: the files are written to the temp directory and are erased automatically.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Member
posted Hide Post
Danny ( and everyone who replied !),

Thank you sooo much for the logic that creates the FSEQ file on the fly.... I finally understand how the McGuyver method can work for me.

I currently have it working and the end result is four Excel files, each with 20 worksheets consisting of four sets of the following: detail data, summary 1 data, summary 2 data, summary 3 data, and summary 4 data.

The code, using this method, is so much more compact than using DEFINES...
Thank you !
MBD


WF 7.62, SQL 2000, Windows XP
 
Posts: 16 | Location: Philadelphia, PA | Registered: May 12, 2005Report This Post
Virtuoso
posted Hide Post
MBD,

Excellent! and you're welcome.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Running Totals with report using ACROSS

Copyright © 1996-2020 Information Builders