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     [SOLVED]Not getting the Subtotal and Total correctly in the report

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Not getting the Subtotal and Total correctly in the report
 Login/Join
 
Guru
posted
 Hi,

I have the below code wherein I need to get the subtotal and the total but Iam not getting the desired result 
as expected.I have a state parameter as a filter condition(State as a listbox in the parameter page) and based on the 
state values being selected in the listbox,the corresponding state's report should get displayed along with the subtotal 
at the division level and also the total at the end.
 
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 SQLMSS 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'
BY DIVISION 
BY REGION 
BY STATE
BY DATA AS 'Data'
WHERE STATE IN (&STATE_SEL)
ON DIVISION SUBTOTAL CNT_IF_3_65 CNT_IF_3_80 CNT_IF_YTD_90 
ON TABLE PCHOLD FORMAT EXL07
END  


-*report output which Iam getting once I run the report:


Division 	Region 	State 	Data 			15-JUL-16 	29-JUL-16 	YTD AVG 2016
EAST 		E 	AL 	%Congest 		14.36% 		3.84% 		4.54%
				%Congest W/o AB 	4.69% 		0.37% 		0.59%
				Congest 		193 		52 		61
				Congest w/o AB 		63 		5 		8
				Polledunits 		1344 		1353 		1336

*TOTAL DIVISION EAST		1,344			1,353		1,364		1,336

	
WEST 		MTW 	AZ 	%Congest 		2.45% 		0.91% 		0.99%
				%Congest w/o AB 	1.91% 		0.57% 		0.62%
				Congest 		227 		84 		91
				Congest w/0 AB 		177 		53 		57
				Polledunits 		9250 		9247 		9163


			CO 	%Congest 		3.23% 		1.32% 		1.45%
				%Congest w/o AB 	2.17% 		0.55% 		0.64%
				Congest 		394 		164 		161
				Congest w/0 AB 		264 		68 		71
				Polledunits 		12193 		12451 		11117

		w 	ID 	%Congest 		6.88% 		2.49% 		3.20%
				%Congest w/o AB 	4.45% 		0.82% 		1.24%
				Congest 		153 		55 		70
				Congest w/0 AB 		99 		18 		27
				Polledunits 		2223 		2207 		2178

*TOTAL DIVISION WEST					2,223		2,207		2,178
TOTAL							2,223		2,207		2,178



This is the o/p which I need (Expected result):

Division 	Region 	State 	Data 			15-JUL-16 	29-JUL-16 	YTD AVG 2016
EAST 		E 	AL 	%Congest 		14.36% 		3.84% 		4.54%
				%Congest W/o AB 	4.69% 		0.37% 		0.59%
				Congest 		193 		52 		61
				Congest w/o AB 		63 		5 		8
				Polledunits 		1344 		1353 		1336

Total Division East 		%Congest Total 		14.36% 		3.84% 		4.54%
				%Congest 		4.69% 		0.37% 		0.59%
				Congest 		193 		52 		61
				Congest w/o AB 		63 		5 		8
				Polledunits 		1344 		1353 		1336

	
WEST 		MTW 	AZ 	%Congest 		2.45% 		0.91% 		0.99%
				%Congest w/o AB 	1.91% 		0.57% 		0.62%
				Congest 		227 		84 		91
				Congest w/0 AB 		177 		53 		57
				Polledunits 		9250 		9247 		9163


			CO 	%Congest 		3.23% 		1.32% 		1.45%
				%Congest w/o AB 	2.17% 		0.55% 		0.64%
				Congest 		394 		164 		161
				Congest w/0 AB 		264 		68 		71
				Polledunits 		12193 		12451 		11117

		w 	ID 	%Congest 		6.88% 		2.49% 		3.20%
				%Congest w/o AB 	4.45% 		0.82% 		1.24%
				Congest 		153 		55 		70
				Congest w/0 AB 		99 		18 		27
				Polledunits 		2223 		2207 		2178

Total Division West 		%Congest Total 		6.28% 		2.36% 		2.82%
				%Congest 		4.26% 		0.97% 		1.25%
				Congest 		774 		303 		322
				Congest w/o AB 		540 		139 		155
				Polledunits 		23666 		23905 		22458


Total 				%Congest Total 		10.32% 		3.10% 		3.68%
				%Congest 		4.48% 		0.67% 		0.92%
				Congest 		967 		355 		383
				Congest w/o AB 		603 		144 		163
				Polledunits 		25010 		25258 		23794 



For the %'s( like %Congest Total,%Congest under the total),the values should be the sum of the percentages(for each division) divided by 2 and for the remaining ones(like
Congest,Congest w/o AB,Polledunits),it is the sum of the values under that division.

I tried subtotal,summarize and Iam not getting the expected result.


Could anyone please help me on this.Any solution/s are greatly appreciated!

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, 2014Report This Post
Virtuoso
posted Hide Post
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, 2010Report This Post
Guru
posted Hide Post
Hi Wep,

Sorry for the delay in response.
Thanks a lot for the quick response and the suggestion/solution and inputs Smiler
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, 2014Report This Post
Virtuoso
posted Hide Post
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, 2010Report This Post
Guru
posted Hide Post
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, 2014Report This Post
Expert
posted Hide Post
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, 2005Report This Post
Guru
posted Hide Post
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, 2014Report This Post
Virtuoso
posted Hide Post
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, 2010Report This Post
Expert
posted Hide Post
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, 2005Report This Post
Guru
posted Hide Post
Hi Francis,

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

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, 2014Report This Post
Expert
posted Hide Post
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, 2005Report This Post
Guru
posted Hide Post
Hi Francis,

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

Regards,
IP


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report This Post
Virtuoso
posted Hide Post
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, 2006Report This Post
Guru
posted Hide Post
Hi,

Thanks a lot Francis and Danny for your solutions.
I really appreciate your time and knowledge on this.
It worked!! Smiler

Regards,
IP


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report 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     [SOLVED]Not getting the Subtotal and Total correctly in the report

Copyright © 1996-2020 Information Builders