[SOLVED]Not getting the Subtotal and Total correctly in the report

A couple of things:

Firstly, you named your MS SQL server connection 'SQLORA'? If you did, at least for the sake of us understanding what you're trying to achieve, change that to something sane.
Secondly, don't sum percentages. You can sum the values that a percentage gets calculated from and then RECOMPUTE (for which FOCUS has a statement) the percentage from those values, but you never ever sum percentages directly. Not even when you think you know what you're doing, because at some point you will be proven wrong.
It appears that your data 'columns' (%Congest, Congest, PolledUnits, etc) are in fact data values in different rows that you happen to want to print intermixed in a specific order.
Since FOCUS does not know about that, you can't expect it to handle those (sub-)totals correctly - it doesn't have the information to do so.
So, either handle the subtotals and totals in your SQL statement (through SQL window functions), or handle the pivoting on the FOCUS or on the Excel end.

To separate those column values into separate columns, you can DEFINE a bunch of virtual fields like:

DEFINE FILE SQLOUT
    PCT_CONGEST_xxx/D12.2% MISSING ON SOME = IF DATA EQ '%Congest' THEN xxx ELSE MISSING;
    etc.
END
TABLE FILE SQLOUT
SUM
    PCT_CONGEST_xxx

BY DIVISION
BY REGION
BY STATE
-* BY DATA ; Already handled through DEFINEs and SUM
WHERE ...

Extra benefit of doing so: You can give those columns their proper data types back, so that dates are dates and percentages are doubles formatted as percentages that can be recomputed properly. Your current values are probably alphanumeric values that can't be summed without converting them.

WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :

Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010

Ignored post by Wep5622 posted

Guru

posted

Hi Wep,

Sorry for the delay in response.
Thanks a lot for the quick response and the suggestion/solution and inputs

Iam using SQLORA and it was a typo(I dint change SQLMSS to SQLORA while posting this).

Iam not summing the percentages in the subtotal at a division level and in the total. For the percentages calculation, I need to use these :
% Congest w/o AB = SUM(Congest w/o AB) / SUM(Polledunits)

% Congest = SUM(Congest) / SUM(Polledunits)

Actually when I pass only few selected states, Iam not getting the total and the subtotal but when I pass all the states, I get the subtotal and the total.

DEFINE FILE SQLOUT
PCT_CONGEST_xxx/D12.2% MISSING ON SOME = IF DATA EQ '%Congest' THEN DATA ELSE MISSING;
END
TABLE FILE SQLOUT
SUM
PCT_CONGEST_xxx
BY DIVISION
BY REGION
BY STATE
-* BY DATA ; Already handled through DEFINEs and SUM
WHERE STATE IN (&STATE_SEL)
END
-RUN
-EXIT

When Iam trying to run the above code, Iam getting this error message :

0 ERROR AT OR NEAR LINE 191 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC266) IF .. THEN .. ELSE .. SYNTAX ERROR
0 ERROR AT OR NEAR LINE 196 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: PCT_CONGEST
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

May I know what is wrong in the above code?

Thanks a lot in advance!

Regards,
IP

Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML

Posts: 270 | Registered: October 30, 2014

Ignored post by info4pal posted

Wep5622

Virtuoso

posted

Well, you're now summing a text value into a D12.2% value (which is not what I suggested). I'm sure that won't end well.

The error message is plain wrong though. That is worth opening a case for, since there is in fact nothing wrong with the IF .. THEN .. ELSE .. syntax. Getting that through support is a major task, but if nobody reports it it's not going to get fixed.

But to get back on topic, you're saying you're not summing percentages? Then where are those subtotals coming from?
Those look exactly like you are doing just that and if you don't want that you'll have to COMPUTE those percentages at the FOCUS side of the query so you can use RECOMPUTE for the sub- and grand-totals.

If that doesn't mean anything to you, get yourself educated into the basics of FOCUS - that's not the purpose of these fora, we're busy people too.

WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :

Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010

Ignored post by Wep5622 posted

Guru

posted

Hi Wep,

Thanks a lot for your time and the suggestions.
I shall open a case with the Information Builders for the If then else and will try doing the Compute and RECOMPUTE.

Thanks a lot again for all your help.

Regards,
IP

Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML

Posts: 270 | Registered: October 30, 2014

Ignored post by info4pal posted

Francis Mariani

Expert

posted

I don't think we are seeing the code that actually gets run.

If this is part of the actual code

PCT_CONGEST_xxx/D12.2% MISSING ON SOME = IF DATA EQ '%Congest' THEN DATA ELSE MISSING;

then this is not a column that RECOMPUTE or SUMMARIZE can recompute for subtotals. Since DATA obviously already contains a percentage amount, how, filtering out some STATEs, do you expect to get proper subtotals?

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, 2005

Ignored post by Francis Mariani posted

Guru

posted

Hi Francis,

This line is not part of the code and per Wep's suggestion,I had to do something similar to do this in the COMPUTE and later tried RECOMPUTE too but nothing is working as expected:
PCT_CONGEST_xxx/A255 MISSING ON SOME = IF DATA EQ '%Congest' THEN DATA ELSE MISSING;

The code which I had posted earlier is the original code and yes Francis, you are correct - DATA column already has the percentage amount's and on the basis of the state selection, I need to show the subtotal and the total only for the selected states in the report and that is my requirement.

Hope Iam clear now.

Please let me know any suggestions/solutions to proceed further and is it feasible in webfocus to show the selected state's subtotal and total in the report.

This is the most recent code which I have tried:

Below is the code being used in my report:
-SET &ECHO='ALL';
-DEFAULT &STATE_SEL='AL,AZ,CO,ID';
-TYPE &STATE_SEL

-* Convert ' OR ' to ', ' in the parameter list
-SET &STATE_SEL = STRREP (&STATE_SEL.LENGTH, &STATE_SEL, 4, ' OR ', 2, ', ', &STATE_SEL.LENGTH, 'A&STATE_SEL.LENGTH');
-TYPE &STATE_SEL

ENGINE SQLORA SET DEFAULT_CONNECTION SQLORA
SQL SQLORA PREPARE SQLOUT FOR
SELECT * FROM SCHEMA1.TABLE_NAME1;
END

TABLE FILE SQLOUT
SUM
CNT_IF_3_65 AS '15-JUL-16'
CNT_IF_3_80 AS '29-JUL-16'
CNT_IF_YTD_90 AS 'YTD_AVG'
COMPUTE PCT_CONGEST/A255 MISSING ON NEEDS SOME = IF DATA EQ '% Congest' THEN DATA ELSE MISSING;
COMPUTE PCT_CONGEST_WO/A255 MISSING ON NEEDS SOME = IF DATA EQ '% Congest W/o AB' THEN DATA ELSE MISSING;
COMPUTE CONGEST1/A255 MISSING ON NEEDS SOME = IF DATA EQ 'Congest' THEN DATA ELSE MISSING;
COMPUTE CONGEST_WO/A255 MISSING ON NEEDS SOME = IF DATA EQ 'Congest w/o AB' THEN DATA ELSE MISSING;
COMPUTE POLLED/A255 MISSING ON NEEDS SOME = IF DATA EQ 'Polledunits' THEN DATA ELSE MISSING;
BY DIVISION 
BY REGION 
BY STATE
BY DATA AS 'Data'
WHERE STATE IN (&STATE_SEL)
ON DIVISION RECOMPUTE PCT_CONGEST PCT_CONGEST_WO CONGEST1 CONGEST_WO POLLED CNT_IF_3_65 CNT_IF_3_80 CNT_IF_YTD_90 
ON TABLE PCHOLD FORMAT HTML
END

Thanks a lot in advance for all your help!

Regards,
IP

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

Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML

Posts: 270 | Registered: October 30, 2014

Ignored post by info4pal posted

Wep5622

Virtuoso

posted

You're entirely missing the point.

The idea is to turn your groups of rows with a field/value per row into a single row with each value in its own field.

To that end, you need to compute the percentage values etc, not the column labels. Those labels you only need to inspect what the value in the current row means (which is what they did in my example COMPUTE), so that you can add the value in the row to the correct field.
Those COMPUTEd numeric values you can use with RECOMPUTE to get correct percentages and the like.

You will end up with your values and subtotals and whatnot in separate columns instead of in groups of rows, but this would be a first step to get there.

Alternatively, you could create a file per row type and calculate totals on those and store them. After that you can then merge those stored files and re-order them on some generated row-number and row type.

WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :

Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010

Ignored post by Wep5622 posted

Francis Mariani

Expert

posted

There are many holes in your post.

1) It appears "divided by 2" is not valid for the EAST division
2) The data sample you provided has several typos: "%Congest W/o AB" for EAST, "%Congest w/o AB" for WEST; 0 instead of O...

Needless to say, it's very difficult to solve a business problem without being provided reproducible code. There are two ways to provide reproducible code: one is to write a report that reads one of the IBI sample table; another is to write code that includes the creation of data.

The example below is of the second kind - the first part of the program creates a master and data that mimics your report data. The second part of the program prepares the data for the report - creating total and detail rows. The third parts creates the report. This is my suggestion: since your requirement is to divide the total percentages by 2, which is not a computation on the details rows therefore RECOMPUTE or SUMMARIZE won't help - create the subtotal and total rows as data, providing a sequence number to ensure they show up in the proper location in the report.

My code below gets you closer to your expected result. BUT: For EAST, it appears you cannot divide by 2, and for Total, I don't know what rule sets up the percentages. Now, someone that has as much patience as I did may be able to help you further.

Program:

-SET &ECHO='ALL';

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
SET CENT-ZERO=ON
-RUN

-* set up demo data
FILEDEF SQLOUT DISK sqlout.txt
-RUN

-WRITE SQLOUT EAST      E   AL%CONGEST               14.36        3.84        4.54
-WRITE SQLOUT EAST      E   AL%CONGEST W/O AB         4.69        0.37        0.59
-WRITE SQLOUT EAST      E   ALCONGEST               193.00       52.00       61.00
-WRITE SQLOUT EAST      E   ALCONGEST W/O AB         63.00        5.00        8.00
-WRITE SQLOUT EAST      E   ALPOLLEDUNITS          1344.00     1353.00     1336.00
-WRITE SQLOUT WEST      MTW AZ%CONGEST                2.45        0.91        0.99
-WRITE SQLOUT WEST      MTW AZ%CONGEST W/O AB         1.91        0.57        0.62
-WRITE SQLOUT WEST      MTW AZCONGEST               227.00       84.00       91.00
-WRITE SQLOUT WEST      MTW AZCONGEST W/O AB        177.00       53.00       57.00
-WRITE SQLOUT WEST      MTW AZPOLLEDUNITS          9250.00     9247.00     9163.00
-WRITE SQLOUT WEST      MTW CO%CONGEST                3.23        1.32        1.45
-WRITE SQLOUT WEST      MTW CO%CONGEST W/O AB         2.17        0.55        0.64
-WRITE SQLOUT WEST      MTW COCONGEST               394.00      164.00      161.00
-WRITE SQLOUT WEST      MTW COCONGEST W/O AB        264.00       68.00       71.00
-WRITE SQLOUT WEST      MTW COPOLLEDUNITS         12193.00    12451.00    11117.00
-WRITE SQLOUT WEST      W   ID%CONGEST                6.88        2.49        3.20
-WRITE SQLOUT WEST      W   ID%CONGEST W/O AB         4.45        0.82        1.24
-WRITE SQLOUT WEST      W   IDCONGEST               153.00       55.00       70.00
-WRITE SQLOUT WEST      W   IDCONGEST W/O AB         99.00       18.00       27.00
-WRITE SQLOUT WEST      W   IDPOLLEDUNITS          2223.00     2207.00     2178.00

FILEDEF MASTER DISK sqlout.mas
-RUN

-WRITE MASTER FILENAME=SQLOUT, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=SQLOUT, $
-WRITE MASTER FIELDNAME=DIVISION     , FORMAT=A10  , ACTUAL=A10, $
-WRITE MASTER FIELDNAME=REGION       , FORMAT=A04  , ACTUAL=A04, $
-WRITE MASTER FIELDNAME=STATE        , FORMAT=A02  , ACTUAL=A02, $
-WRITE MASTER FIELDNAME=DATA         , FORMAT=A16  , ACTUAL=A16, $
-WRITE MASTER FIELDNAME=CNT_IF_3_65  , FORMAT=P8.2C, ACTUAL=A12, $
-WRITE MASTER FIELDNAME=CNT_IF_3_80  , FORMAT=P8.2C, ACTUAL=A12, $
-WRITE MASTER FIELDNAME=CNT_IF_YTD_90, FORMAT=P8.2C, ACTUAL=A12, $
-RUN

-* create division total rows
TABLE FILE SQLOUT
SUM
COMPUTE ROW_TYPE/A1  = '2';
COMPUTE DIVISION_LABEL/A25 = 'TOTAL DIVISION ' | DIVISION;
DIVISION
COMPUTE HREGION/A04   = ''; AS 'REGION'
COMPUTE HSTATE/A02    = ''; AS 'STATE'
COMPUTE HDATA/A16     = IF DATA EQ '%CONGEST' THEN '%CONGEST TOTAL' ELSE DATA; AS 'DATA'

COMPUTE HCNT_IF_3_65/P8.2C   = IF DATA CONTAINS '%' THEN CNT_IF_3_65 / 2   ELSE CNT_IF_3_65;   AS 'CNT_IF_3_65'
COMPUTE HCNT_IF_3_80/P8.2C   = IF DATA CONTAINS '%' THEN CNT_IF_3_80 / 2   ELSE CNT_IF_3_80;   AS 'CNT_IF_3_80'
COMPUTE HCNT_IF_YTD_90/P8.2C = IF DATA CONTAINS '%' THEN CNT_IF_YTD_90 / 2 ELSE CNT_IF_YTD_90; AS 'CNT_IF_YTD_90'

BY DIVISION     NOPRINT
BY DATA         NOPRINT

ON TABLE HOLD AS HDIVTOTAL
END
-RUN

-* create report total row
TABLE FILE HDIVTOTAL
SUM
COMPUTE ROW_TYPE/A1  = '3';
COMPUTE DIVISION_LABEL/A25 = 'TOTAL';
COMPUTE DIVISION/A10  = 'ZZZZZZZZZZ';
COMPUTE HREGION/A04   = ''; AS 'REGION'
COMPUTE HSTATE/A02    = ''; AS 'STATE'
COMPUTE HDATA/A16     = IF DATA EQ '%CONGEST' THEN '%CONGEST TOTAL' ELSE DATA; AS 'DATA'

COMPUTE HCNT_IF_3_65/P8.2C   = IF DATA CONTAINS '%' THEN CNT_IF_3_65 / 2   ELSE CNT_IF_3_65;   AS 'CNT_IF_3_65'
COMPUTE HCNT_IF_3_80/P8.2C   = IF DATA CONTAINS '%' THEN CNT_IF_3_80 / 2   ELSE CNT_IF_3_80;   AS 'CNT_IF_3_80'
COMPUTE HCNT_IF_YTD_90/P8.2C = IF DATA CONTAINS '%' THEN CNT_IF_YTD_90 / 2 ELSE CNT_IF_YTD_90; AS 'CNT_IF_YTD_90'

BY DATA         NOPRINT

ON TABLE HOLD AS HREPTOTAL
END
-RUN

-* create detail data rows
TABLE FILE SQLOUT
PRINT
COMPUTE ROW_TYPE/A1  = '1';
COMPUTE DIVISION_LABEL/A25 = DIVISION;

CNT_IF_3_65
CNT_IF_3_80
CNT_IF_YTD_90

BY DIVISION
BY REGION
BY STATE
BY DATA

ON TABLE HOLD AS HDETAIL
END
-RUN

-* Create report
TABLE FILE HDETAIL
PRINT
CNT_IF_3_65
CNT_IF_3_80
CNT_IF_YTD_90

BY DIVISION     NOPRINT
BY ROW_TYPE     NOPRINT
BY DIVISION_LABEL
BY REGION
BY STATE
BY DATA

MORE
FILE HDIVTOTAL

MORE
FILE HREPTOTAL
END
-RUN

Report:

 
 DIVISION_LABEL             REGION  STATE  DATA              CNT_IF_3_65  CNT_IF_3_80  CNT_IF_YTD_90
 --------------             ------  -----  ----              -----------  -----------  -------------
 EAST                       E       AL     %CONGEST                14.36         3.84           4.54
                                           %CONGEST W/O AB          4.69         0.37           0.59
                                           CONGEST                193.00        52.00          61.00
                                           CONGEST W/O AB          63.00         5.00           8.00
                                           POLLEDUNITS          1,344.00     1,353.00       1,336.00
 TOTAL DIVISION EAST                       %CONGEST TOTAL           7.18         1.92           2.27
                                           %CONGEST W/O AB          2.35         0.19           0.30
                                           CONGEST                193.00        52.00          61.00
                                           CONGEST W/O AB          63.00         5.00           8.00
                                           POLLEDUNITS          1,344.00     1,353.00       1,336.00
 WEST                       MTW     AZ     %CONGEST                 2.45         0.91           0.99
                                           %CONGEST W/O AB          1.91         0.57           0.62
                                           CONGEST                227.00        84.00          91.00
                                           CONGEST W/O AB         177.00        53.00          57.00
                                           POLLEDUNITS          9,250.00     9,247.00       9,163.00
                                    CO     %CONGEST                 3.23         1.32           1.45
                                           %CONGEST W/O AB          2.17         0.55           0.64
                                           CONGEST                394.00       164.00         161.00
                                           CONGEST W/O AB         264.00        68.00          71.00
                                           POLLEDUNITS         12,193.00    12,451.00      11,117.00
                            W       ID     %CONGEST                 6.88         2.49           3.20
                                           %CONGEST W/O AB          4.45         0.82           1.24
                                           CONGEST                153.00        55.00          70.00
                                           CONGEST W/O AB          99.00        18.00          27.00
                                           POLLEDUNITS          2,223.00     2,207.00       2,178.00
 TOTAL DIVISION WEST                       %CONGEST TOTAL           6.28         2.36           2.82
                                           %CONGEST W/O AB          4.26         0.97           1.25
                                           CONGEST                774.00       303.00         322.00
                                           CONGEST W/O AB         540.00       139.00         155.00
                                           POLLEDUNITS         23,666.00    23,905.00      22,458.00
 TOTAL                                     %CONGEST TOTAL           6.73         2.14           2.55
                                           %CONGEST W/O AB          3.31         0.58           0.78
                                           CONGEST                967.00       355.00         383.00
                                           CONGEST W/O AB         603.00       144.00         163.00
                                           POLLEDUNITS         25,010.00    25,258.00      23,794.00

This message has been edited. Last edited by: Francis Mariani,

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, 2005

Ignored post by Francis Mariani posted

Guru

posted

Hi Francis,

Thanks a ton for all your help on this.I appreciate your patience and time to help me on time.

I just have a quick question.
Since the data which I get is dynamic everytime and whatever values you have hardcoded is static in the below lines of code,would you please let me know how can I make it dynamic instead of hardcoding the values since the data values keeps changing everytime.

I tried replacing the values such as 14.36 with their respective name say &CNT_IF_3_65 and also with &CNT_IF_3_65.EVAL but Iam getting the value as 0 when I run this.

-* set up demo data
FILEDEF SQLOUT DISK sqlout.txt
-RUN

-WRITE SQLOUT EAST      E   AL%CONGEST               14.36        3.84        4.54
-WRITE SQLOUT EAST      E   AL%CONGEST W/O AB         4.69        0.37        0.59
-WRITE SQLOUT EAST      E   ALCONGEST               193.00       52.00       61.00
-WRITE SQLOUT EAST      E   ALCONGEST W/O AB         63.00        5.00        8.00
-WRITE SQLOUT EAST      E   ALPOLLEDUNITS          1344.00     1353.00     1336.00
-WRITE SQLOUT WEST      MTW AZ%CONGEST                2.45        0.91        0.99
-WRITE SQLOUT WEST      MTW AZ%CONGEST W/O AB         1.91        0.57        0.62
-WRITE SQLOUT WEST      MTW AZCONGEST               227.00       84.00       91.00
-WRITE SQLOUT WEST      MTW AZCONGEST W/O AB        177.00       53.00       57.00
-WRITE SQLOUT WEST      MTW AZPOLLEDUNITS          9250.00     9247.00     9163.00
-WRITE SQLOUT WEST      MTW CO%CONGEST                3.23        1.32        1.45
-WRITE SQLOUT WEST      MTW CO%CONGEST W/O AB         2.17        0.55        0.64
-WRITE SQLOUT WEST      MTW COCONGEST               394.00      164.00      161.00
-WRITE SQLOUT WEST      MTW COCONGEST W/O AB        264.00       68.00       71.00
-WRITE SQLOUT WEST      MTW COPOLLEDUNITS         12193.00    12451.00    11117.00
-WRITE SQLOUT WEST      W   ID%CONGEST                6.88        2.49        3.20
-WRITE SQLOUT WEST      W   ID%CONGEST W/O AB         4.45        0.82        1.24
-WRITE SQLOUT WEST      W   IDCONGEST               153.00       55.00       70.00
-WRITE SQLOUT WEST      W   IDCONGEST W/O AB         99.00       18.00       27.00
-WRITE SQLOUT WEST      W   IDPOLLEDUNITS          2223.00     2207.00     2178.00

Thanks a lot in advance!

Regards,
IP

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

Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML

Posts: 270 | Registered: October 30, 2014

Ignored post by info4pal posted

Francis Mariani

Expert

posted

Oh boy! You need a lot of training.

Did you read "the first part of the program creates a master and data that mimics your report data"? This means that I'm trying to help you by creating some data similar to yours - it does not mean that you should copy it in your program.

Over and out.

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, 2005

Ignored post by Francis Mariani posted

Guru

posted

Hi Francis,

Sorry I missed that point.
I got it now.Thanks a lot for all your help!
It worked

Regards,
IP

Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML

Posts: 270 | Registered: October 30, 2014

Ignored post by info4pal posted

Danny-SRL

Virtuoso

posted

Pal,

Did you try the MacGuyver approach?

  
TABLE FILE SQLOUT
SUM
CNT_IF_3_65 AS '15-JUL-16'
CNT_IF_3_80 AS '29-JUL-16'
CNT_IF_YTD_90 AS 'YTD_AVG'
BY DIVISION 
BY REGION 
BY STATE
BY DATA AS 'Data'
WHERE STATE IN (&STATE_SEL)
ON TABLE SET ASNAMES OFF
ON TABLE HOLD AS PAL
END  
-RUN
JOIN BLANK WITH DATA IN PAL TO BLANK IN FSEQ AS M_
DEFINE FILE PAL
BLANK/A1 WITH DATA = ' ';
XDIVISION/format of division = IF COUNTER EQ 3 THEN 'Total' ELSE DIVISION;
XREGION/format of region = IF COUNTER EQ 2 OR 3 THEN ' ' ELSE REGION;
XSTATE/format of state = IF COUNTRY EQ 2 OR 3 THEN ' ' ELSE STATE;
DIVSRT/I1=IF COUNTER EQ 3 THEN 2 ELSE 1;
REGSRT/I1=IF COUNTER EQ 1 THEN 1 ELSE 2;
END
TABLE FILE PAL
WHERE COUNTER LE 3;
SUM
CNT_IF_3_65 AS '15-JUL-16'
CNT_IF_3_80 AS '29-JUL-16'
CNT_IF_YTD_90 AS 'YTD_AVG'
BY DIVSRT NOPRINT
BY XDIVISION AS DIVISION
BY REGSRT NOPRINT
BY XREGION AS REGION
BY XSTATE AS STATE
ON XDIVISION SUBTOTAL
ON TABLE COLUMN-TOTAL
END

The MacGuyver file and master:

  
Master:
FILE=FSEQ, SUFFIX=FIX, DATASET=FOCALPOINT/FSEQ.TXT, $
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

Data (notice the first character is a blank!)
 FSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVERFSEQFILEFORMCGUYVER

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, 2006

Ignored post by Danny-SRL posted

Guru

posted