![]() |
||||||||||||
Go ![]() | New ![]() | Search ![]() | Notify ![]() | Tools ![]() | Reply ![]() | ![]() |
Guru |
Hi, My output which Iam getting in the report is like this : PAGE 1 Region Data CNT_IF_3_65 CNT_IF_2_65 CNT_IF_1_65 CNT_IF_0_65 CNT_IF_3_80 CNT_IF_2_80 CNT_IF_1_80 CNT_IF_0_80 CNT_IF_3_90 CNT_IF_2_90 CNT_IF_1_90 CNT_IF_0_90 CNT_IF_YTD_90 E % Congested w/o PE Total 3.04% 3.06% 2.99% 2.86% 1.07% 1.13% 1.02% 1.00% 0.52% 0.49% 0.46% 0.46% 0.49% MW % Congested w/o PE Total 4.90% 4.86% 4.48% 4.53% 2.27% 2.26% 1.96% 2.08% 1.37% 1.26% 1.12% 1.16% 1.28% MTW % Congested w/o PE Total 2.60% 2.72% 2.52% 2.27% 1.55% 1.63% 1.48% 1.32% 0.95% 0.99% 0.87% 0.74% 0.92% W % Congested w/o PE Total 4.10% 4.33% 4.11% 4.06% 2.30% 2.41% 2.11% 2.17% 1.32% 1.38% 1.20% 1.22% 1.30% TOTAL CTL % Congested w/o PE Grand Total3.64% 3.73% 3.51% 3.39% 1.81% 1.88% 1.66% 1.65% 1.05% 1.05% 0.92% 0.91% 1.01% But I need my output to be like : Region Data New_field value1 value2 CNT_IF_3_65 CNT_IF_2_65 CNT_IF_1_65 CNT_IF_0_65 CNT_IF_3_80 CNT_IF_2_80 CNT_IF_1_80 CNT_IF_0_80 CNT_IF_3_90 CNT_IF_2_90 CNT_IF_1_90 CNT_IF_0_90 CNT_IF_YTD_90 I have tried doing it with OVER, Across etc and nothing is giving me the expected result. Could anyone please help? Thanks a lot in advance for all your help! Regards, IPThis message has been edited. Last edited by: info4pal, Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML | ||
|
Virtuoso |
This could be one way -SET &LOOP = 0; -SET &NBFLD = 3; FILEDEF PIVOTED DISK BASEAPP/PIVOTED.ftm -REPEAT PIVOTCOL FOR &LOOP FROM 1 TO &NBFLD STEP 1 DEFINE FILE CAR IND /I3 = &LOOP.EVAL; PIVOTFLD /A40V = IF IND EQ 1 THEN COUNTRY ELSE IF IND EQ 2 THEN CAR ELSE MODEL; END TABLE FILE CAR PRINT PIVOTFLD COUNTRY NOPRINT BY IND ON TABLE HOLD AS PIVOTED FORMAT ALPHA END -RUN FILEDEF PIVOTED DISK BASEAPP/PIVOTED.ftm (APPEND -PIVOTCOL TABLE FILE PIVOTED PRINT * END -RUN WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007 | |||
|
Platinum Member |
Also possible use PUTDDREC - FILEDEF PUTDD1 DISK PUTDD1.TXT -RUN TABLE FILE CAR PRINT COMPUTE OUT1/I1 = PUTDDREC('PUTDD1',6, COUNTRY, 50, OUT1); COMPUTE OUT1/I1 = PUTDDREC('PUTDD1',6, CAR, 50, OUT1); COMPUTE OUT1/I1 = PUTDDREC('PUTDD1',6, MODEL, 50, OUT1); ON TABLE SAVE -* No name - no want output anyway! END -RUN CMD TYPE PUTDD1.TXT Create own synonym and is ready ![]() Kofi Client Server 8.1.05: Apache; Tomcat;Windows Server 2012 Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012 | |||
|
Expert |
Kofi, that's very interesting! I've never used nor knew about the PUTDDREC function. I can see its usefulness. Thanks for the education. 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 | |||
|
Platinum Member |
I thank you Francis, and all other Expert who give great information and help me learn by reading all post on forum. I also RTFM ![]() Kofi Client Server 8.1.05: Apache; Tomcat;Windows Server 2012 Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012 | |||
|
Virtuoso |
Pal, This seems to me an example for the use of an alternate master. If you HOLD your output you will create a file with the following fields: Region Data CNT_IF_3_65 CNT_IF_2_65 CNT_IF_1_65 CNT_IF_0_65 CNT_IF_3_80 CNT_IF_2_80 CNT_IF_1_80 CNT_IF_0_80 CNT_IF_3_90 CNT_IF_2_90 CNT_IF_1_90 CNT_IF_0_90 CNT_IF_YTD_90 Create a new MASTER with 2 segments. SEGMENT1 has fields Region Data SEGMENT2 has PARENT=SEGMENT1, OCCURS=VARIABLE and field NEW_FIELD Then you just have to PRINT NEW_FIELD BY REGION BY DATA Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF | |||
|
Expert |
One of the nice things about PUTDDREC is that you can supply the length of the record either as a literal or a function, so you have the possibility of removing trailing spaces from the file.
This was my first though, depending on the data source. Waz...
| |||||||||||||||||||||||||||
|
Guru |
Hi, Martin - Iam using your technique/code. Others - Thanks for the suggestions and Iam glad that Iam learning something new everyday ![]() This is how my code is now : -SET &ECHO='ALL'; -INCLUDE IBFS:/WFC/Repository/Myfolder/Ref_folder/oracle_conn.fex -RUN -SET &LOOP = 0; -SET &NBFLD = 13; FILEDEF PIVOTED DISK NET_CAP_RPT/PIVOTED.ftm -REPEAT PIVOTCOL FOR &LOOP FROM 1 TO &NBFLD STEP 1 SQL SQLORA PREPARE REPORT1 FOR SELECT * FROM CONN1.TABLE_REF; END TABLE FILE REPORT1 PRINT * BY DIVISION BY REGION BY STATE WHERE DIVISION NE '' OR '??' WHERE DATA EQ '% Congested w/o PE' OR '% Congested w/o PE Grand Total' ON TABLE HOLD AS HOLD_MAIN END -RUN TABLE FILE REPORT1 PRINT REPORT_TAB DIVISION REGION STATE DATA CNT_IF_3_65 CNT_IF_2_65 CNT_IF_1_65 CNT_IF_0_65 CNT_IF_3_80 CNT_IF_2_80 CNT_IF_1_80 CNT_IF_0_80 CNT_IF_3_90 CNT_IF_2_90 CNT_IF_1_90 CNT_IF_0_90 CNT_IF_YTD_90 WHERE REPORT_TAB EQ '' ON TABLE HOLD AS HOLD1 END -RUN DEFINE FILE HOLD1 IND /I3 = &LOOP.EVAL; PIVOTFLD /A40V = IF IND EQ 1 THEN CNT_IF_3_65 ELSE IF IND EQ 2 THEN CNT_IF_2_65 ELSE IF IND EQ 3 THEN CNT_IF_1_65 ELSE IF IND EQ 4 THEN CNT_IF_0_65 ELSE IF IND EQ 5 THEN CNT_IF_3_80 ELSE IF IND EQ 6 THEN CNT_IF_2_80 ELSE IF IND EQ 7 THEN CNT_IF_1_80 ELSE IF IND EQ 8 THEN CNT_IF_0_80 ELSE IF IND EQ 9 THEN CNT_IF_3_90 ELSE IF IND EQ 10 THEN CNT_IF_2_90 ELSE IF IND EQ 11 THEN CNT_IF_1_90 ELSE IF IND EQ 12 THEN CNT_IF_0_90 ELSE CNT_IF_YTD_90; END TABLE FILE HOLD1 PRINT PIVOTFLD DIVISION NOPRINT REGION NOPRINT STATE NOPRINT DATA NOPRINT BY IND ON TABLE HOLD AS PIVOTED FORMAT ALPHA END -RUN FILEDEF PIVOTED DISK NET_CAP_RPT/PIVOTED.ftm (APPEND -PIVOTCOL TABLE FILE PIVOTED PRINT IND PIVOTFLD DATA BY DIVISION BY REGION BY STATE ON TABLE HOLD AS HOLD_PIVOT END -RUN MATCH FILE HOLD_MAIN PRINT DATA CNT_IF_3_65 CNT_IF_2_65 CNT_IF_1_65 CNT_IF_0_65 CNT_IF_3_80 CNT_IF_2_80 CNT_IF_1_80 CNT_IF_0_80 CNT_IF_3_90 CNT_IF_2_90 CNT_IF_1_90 CNT_IF_0_90 CNT_IF_YTD_90 BY DIVISION BY REGION BY STATE RUN FILE HOLD_PIVOT PRINT IND PIVOTFLD BY DIVISION BY REGION BY STATE AFTER MATCH HOLD AS HOLD_OUTPUT OLD-OR-NEW END TABLE FILE HOLD_OUTPUT PRINT * END -RUN -EXIT Output of Hold_output which Iam getting is : DIVISION REGION STATE DATA CNT_IF_3_65 CNT_IF_2_65 CNT_IF_1_65 CNT_IF_0_65 CNT_IF_3_80 CNT_IF_2_80 CNT_IF_1_80 CNT_IF_0_80 CNT_IF_3_90 CNT_IF_2_90 CNT_IF_1_90 CNT_IF_0_90 CNT_IF_YTD_90 IND PIVOTFLD . . . . . . . . . . . . . . . . . 1 27-JAN-17 . . . . . . . . . . . . . . . . . 2 10-FEB-17 . . . . . . . . . . . . . . . . . 3 24-FEB-17 . . . . . . . . . . . . . . . . . 4 10-MAR-17 . . . . . . . . . . . . . . . . . 5 27-JAN-17 . . . . . . . . . . . . . . . . . 6 10-FEB-17 . . . . . . . . . . . . . . . . . 7 24-FEB-17 . . . . . . . . . . . . . . . . . 8 10-MAR-17 . . . . . . . . . . . . . . . . . 9 27-JAN-17 . . . . . . . . . . . . . . . . . 10 10-FEB-17 . . . . . . . . . . . . . . . . . 11 24-FEB-17 . . . . . . . . . . . . . . . . . 12 10-MAR-17 . . . . . . . . . . . . . . . . . 13 YTD AVG 2017 EAST E AL % Congested w/o PE 5.16% 5.67% 5.62% 5.28% 1.93% 2.33% 2.04% 2.10% 1.15% 1.09% 0.95% 1.08% 1.09% 0 EAST E AR % Congested w/o PE 7.05% 7.32% 8.17% 7.83% 2.84% 3.27% 3.49% 3.28% 1.55% 1.46% 1.79% 1.85% 1.68% 0 EAST E FL % Congested w/o PE 0.74% 0.93% 0.88% 0.92% 0.20% 0.20% 0.23% 0.18% 0.06% 0.09% 0.08% 0.08% 0.08% 0 EAST E GA % Congested w/o PE 1.75% 2.61% 2.65% 2.64% 0.88% 0.87% 0.88% 0.88% 0.88% 0.43% 0.44% 0.88% 0.70% 0 EAST E LA % Congested w/o PE 10.34% 10.04% 10.42% 10.72% 6.41% 7.04% 6.51% 6.33% 3.80% 3.52% 3.78% 3.62% 3.71% 0 EAST E MA % Congested w/o PE 0.00% 0.00% 100.00% 100.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0 EAST E MD % Congested w/o PE 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0 EAST E MS % Congested w/o PE 15.09% 14.42% 14.95% 15.38% 5.66% 6.73% 3.74% 3.85% 0.00% 0.00% 0.00% 0.00% 0.00% 0 EAST E NC % Congested w/o PE 2.27% 2.00% 1.82% 1.61% 0.33% 0.29% 0.27% 0.33% 0.06% 0.06% 0.06% 0.06% 0.06% 0 EAST E NJ % Congested w/o PE 1.26% 1.89% 0.42% 0.42% 0.42% 1.26% 0.00% 0.00% 0.21% 1.05% 0.00% 0.00% 0.34% 0 EAST E OK % Congested w/o PE 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0 EAST E PA % Congested w/o PE 1.39% 1.63% 0.81% 0.68% 0.35% 0.34% 0.07% 0.07% 0.35% 0.20% 0.07% 0.07% 0.18% 0 EAST E SC % Congested w/o PE 3.28% 3.30% 3.33% 2.84% 0.23% 0.47% 0.71% 0.47% 0.00% 0.00% 0.00% 0.00% 0.00% 0 EAST E TN % Congested w/o PE 2.28% 2.76% 2.91% 2.27% 0.33% 0.32% 0.32% 0.49% 0.16% 0.16% 0.16% 0.00% 0.13% 0 EAST E TX % Congested w/o PE 7.36% 6.87% 6.60% 6.32% 3.19% 2.97% 2.49% 2.41% 1.41% 1.11% 1.00% 1.11% 1.23% 0 EAST E VA % Congested w/o PE 4.07% 3.74% 4.10% 3.85% 1.31% 1.18% 1.24% 1.22% 0.66% 0.59% 0.52% 0.32% 0.55% 0 EAST MW IA % Congested w/o PE 7.62% 7.43% 6.94% 6.66% 4.17% 4.09% 3.46% 3.57% 2.77% 2.30% 1.92% 1.87% 2.32% 0 EAST MW IL % Congested w/o PE 0.42% 0.42% 0.00% 0.00% 0.42% 0.42% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.08% 0 EAST MW IN % Congested w/o PE 7.42% 6.77% 6.00% 6.10% 2.85% 2.64% 1.58% 1.88% 1.14% 1.15% 0.90% 1.11% 1.11% 0 EAST MW KS % Congested w/o PE 13.20% 13.97% 17.35% 17.96% 3.40% 2.79% 5.26% 5.19% 2.20% 1.60% 3.12% 3.52% 2.39% 0 EAST MW MI % Congested w/o PE 1.85% 2.81% 1.75% 2.41% 0.34% 0.53% 0.35% 0.69% 0.34% 0.35% 0.18% 0.17% 0.31% 0 EAST MW MN % Congested w/o PE 2.63% 2.70% 2.36% 2.24% 1.40% 1.43% 1.19% 1.27% 0.84% 0.84% 0.70% 0.73% 0.81% 0 EAST MW MO % Congested w/o PE 7.88% 7.53% 7.71% 8.06% 3.04% 2.87% 2.91% 3.27% 1.55% 1.35% 1.35% 1.49% 1.45% 0 EAST MW ND % Congested w/o PE 3.10% 3.83% 2.89% 2.40% 1.82% 1.64% 1.62% 1.20% 1.09% 1.09% 1.08% 1.05% 1.15% 0 EAST MW NE % Congested w/o PE 6.48% 6.47% 5.84% 4.93% 3.98% 4.14% 3.40% 2.97% 2.44% 2.38% 2.21% 1.87% 2.31% 0 EAST MW OH % Congested w/o PE 4.14% 3.90% 3.23% 3.44% 0.84% 0.90% 0.54% 0.77% 0.48% 0.60% 0.36% 0.47% 0.55% 0 EAST MW SD % Congested w/o PE 6.58% 6.43% 5.96% 4.86% 3.89% 3.89% 3.87% 3.12% 2.99% 3.14% 3.13% 2.43% 2.91% 0 EAST MW WI % Congested w/o PE 3.89% 3.71% 2.85% 3.52% 1.50% 1.60% 1.04% 1.30% 0.81% 0.70% 0.42% 0.61% 0.76% 0 TOTAL CTL TOTAL CTL TOTAL CTL % Congested w/o PE Grand Total 3.64% 3.73% 3.51% 3.23% 1.81% 1.88% 1.66% 1.57% 1.05% 1.05% 0.92% 0.85% 1.00% 0 WEST MTW AZ % Congested w/o PE 1.60% 2.03% 1.91% 1.40% 1.07% 1.23% 1.16% 0.83% 0.68% 0.72% 0.69% 0.51% 0.66% 0 WEST MTW CO % Congested w/o PE 2.80% 2.79% 2.58% 2.44% 1.63% 1.72% 1.53% 1.42% 0.99% 1.06% 0.88% 0.76% 0.96% 0 WEST MTW NM % Congested w/o PE 4.49% 4.32% 3.97% 3.41% 2.50% 2.34% 2.10% 1.73% 1.55% 1.41% 1.28% 0.96% 1.35% 0 WEST W ID % Congested w/o PE 5.10% 5.10% 4.45% 4.30% 2.98% 3.10% 2.29% 2.25% 1.63% 1.63% 1.35% 1.33% 1.52% 0 WEST W MT % Congested w/o PE 6.56% 6.57% 6.36% 5.31% 4.40% 4.12% 3.86% 3.37% 2.88% 2.89% 2.93% 2.57% 2.79% 0 WEST W NV % Congested w/o PE 0.99% 0.83% 0.77% 0.70% 0.72% 0.56% 0.42% 0.51% 0.12% 0.06% 0.00% 0.00% 0.05% 0 WEST W OR % Congested w/o PE 5.08% 5.48% 5.44% 3.45% 2.73% 3.05% 2.63% 1.73% 1.68% 1.64% 1.48% 0.93% 1.41% 0 WEST W UT % Congested w/o PE 3.26% 3.05% 2.90% 2.72% 1.92% 1.78% 1.48% 1.47% 1.07% 0.98% 0.79% 0.73% 0.95% 0 WEST W WA % Congested w/o PE 4.14% 4.90% 4.63% 4.33% 2.04% 2.43% 2.34% 2.20% 1.27% 1.58% 1.36% 1.31% 1.39% 0 WEST W WY % Congested w/o PE 8.77% 8.77% 7.89% 7.10% 5.15% 4.93% 4.27% 3.97% 2.41% 2.74% 2.08% 1.85% 2.32% 0 The output which I need is :(I have taken a single state from the above output) DIVISION REGION STATE DATA CNT_IF_3_65 CNT_IF_2_65 CNT_IF_1_65 CNT_IF_0_65 CNT_IF_3_80 CNT_IF_2_80 CNT_IF_1_80 CNT_IF_0_80 CNT_IF_3_90 CNT_IF_2_90 CNT_IF_1_90 CNT_IF_0_90 CNT_IF_YTD_90 EAST E AL % Congested w/o PE 5.16% 5.67% 5.62% 5.28% 1.93% 2.33% 2.04% 2.10% 1.15% 1.09% 0.95% 1.08% 1.09% Output should like as shown below(This is the final output needed): DIVISION REGION STATE PERIOD MeasType Meas EAST E AL 27-JAN-17 1 5.16 EAST E AL 27-JAN-17 2 5.67 EAST E AL 27-JAN-17 3 5.62 EAST E AL 10-FEB-17 1 5.28 EAST E AL 10-FEB-17 2 1.93 EAST E AL 10-FEB-17 3 2.33 EAST E AL 24-FEB-17 1 2.04 EAST E AL 24-FEB-17 2 2.10 EAST E AL 24-FEB-17 3 1.15 EAST E AL 10-MAR-17 1 1.09 EAST E AL 10-MAR-17 2 0.95 EAST E AL 10-MAR-17 3 1.08 EAST E AL YTD AVG 2017 1 1.09 ... Could you or anyone please let me know how to achieve this? Iam stuck here and not able to proceed further. Thanks a lot for all your help! Regards, IP Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML | |||
|
Virtuoso |
Hi IP, You loop for the entire thing when you should only loop once your data has been extracted from SQL. As I mentioned, you should: 1- Extract your data from SQL and HOLD it as you already did 2- From the HOLD file, extract the date record and save the dates in variables as you did in your second post in the other thread 3- Build the loop for the other records to pivot the 13 columns from the HOLD file. 4- create your report based on new hold file PIVOTED You also need to keep your key fields if you want your data correctly. Something such as -INCLUDE IBFS:/WFC/Repository/Myfolder/Ref_folder/oracle_conn.fex -RUN SQL SQLORA PREPARE REPORT1 FOR SELECT * FROM CONN1.TABLE_REF; END TABLE FILE REPORT1 PRINT CNT_IF_3_65 CNT_IF_2_65 CNT_IF_1_65 CNT_IF_0_65 CNT_IF_3_80 CNT_IF_2_80 CNT_IF_1_80 CNT_IF_0_80 CNT_IF_3_90 CNT_IF_2_90 CNT_IF_1_90 CNT_IF_0_90 CNT_IF_YTD_90 WHERE REPORT_TAB EQ '' ON TABLE HOLD AS MYFILE END -RUN -READFILE MYFILE -RUN -TYPE &CNT_IF_3_65 -TYPE &CNT_IF_2_65 -TYPE &CNT_IF_1_65 -TYPE &CNT_IF_0_65 -TYPE &CNT_IF_3_80 -TYPE &CNT_IF_2_80 -TYPE &CNT_IF_1_80 -TYPE &CNT_IF_0_80 -TYPE &CNT_IF_3_90 -TYPE &CNT_IF_2_90 -TYPE &CNT_IF_1_90 -TYPE &CNT_IF_0_90 -TYPE &CNT_IF_YTD_90 -SET &LOOP = 0; -SET &NBFLD = 13; FILEDEF PIVOTED DISK NET_CAP_RPT/PIVOTED.ftm -REPEAT PIVOTCOL FOR &LOOP FROM 1 TO &NBFLD STEP 1 DEFINE FILE REPORT1 IND /I3 = &LOOP.EVAL; MEASTYPE /I3 = IF IND LE 4 THEN 1 ELSE IF IND LE 8 THEN 2 ELSE IF IND LE 12 THEN 3 ELSE 13; PIVOTFLD /A40V = IF IND EQ 1 THEN CNT_IF_3_65 ELSE IF IND EQ 2 THEN CNT_IF_2_65 ELSE IF IND EQ 3 THEN CNT_IF_1_65 ELSE IF IND EQ 4 THEN CNT_IF_0_65 ELSE IF IND EQ 5 THEN CNT_IF_3_80 ELSE IF IND EQ 6 THEN CNT_IF_2_80 ELSE IF IND EQ 7 THEN CNT_IF_1_80 ELSE IF IND EQ 8 THEN CNT_IF_0_80 ELSE IF IND EQ 9 THEN CNT_IF_3_90 ELSE IF IND EQ 10 THEN CNT_IF_2_90 ELSE IF IND EQ 11 THEN CNT_IF_1_90 ELSE IF IND EQ 12 THEN CNT_IF_0_90 ELSE CNT_IF_YTD_90; DATEFLD /A10V = IF IND EQ 1 THEN '&CNT_IF_3_65.EVAL' ELSE IF IND EQ 2 THEN '&CNT_IF_2_65.EVAL' ELSE IF IND EQ 3 THEN '&CNT_IF_1_65.EVAL' ELSE IF IND EQ 4 THEN '&CNT_IF_0_65.EVAL' ELSE IF IND EQ 5 THEN '&CNT_IF_3_80.EVAL' ELSE IF IND EQ 6 THEN '&CNT_IF_2_80.EVAL' ELSE IF IND EQ 7 THEN '&CNT_IF_1_80.EVAL' ELSE IF IND EQ 8 THEN '&CNT_IF_0_80.EVAL' ELSE IF IND EQ 9 THEN '&CNT_IF_3_90.EVAL' ELSE IF IND EQ 10 THEN '&CNT_IF_2_90.EVAL' ELSE IF IND EQ 11 THEN '&CNT_IF_1_90.EVAL' ELSE IF IND EQ 12 THEN '&CNT_IF_0_90.EVAL' ELSE '&CNT_IF_YTD_90.EVAL'; END TABLE FILE REPORT1 PRINT PIVOTFLD BY DIVISION BY REGION BY STATE BY DATEFLD BY MEASTYPE WHERE REPORT_TAB NE '' ON TABLE HOLD AS PIVOTED FORMAT ALPHA END -RUN FILEDEF PIVOTED DISK NET_CAP_RPT/PIVOTED.ftm (APPEND -PIVOTCOLThis message has been edited. Last edited by: MartinY, WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007 | |||
|
Guru |
Hi Martin, Sure I will try that but one question I have... How can I build the loop for the other records to pivot the 13 columns from the HOLD file as the percentage values are not static and the data keeps changing. Please correct me if Iam wrong. Regards, IP Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML | |||
|
Virtuoso |
Why your data should change ? When you produce a report/graph, you display the data as is at that moment. If you want to see if the data has changed, you run the report again and then you could have a different result (compare both reports). WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007 | |||
|
Guru |
Hi, Okay..I got it ![]() But when you say to build the loop for the other records to pivot the 13 columns from the HOLD file ...which records do you refer to? are these the percentages or something else? Once again sorry for troubling you ![]() Please help... Regards, IP Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML | |||
|
Virtuoso |
From your other post you have displayed the following about your data: This is the output which I get : REPORT_TAB DIVISION REGION STATE DATA CNT_IF_3_65 CNT_IF_2_65 CNT_IF_1_65 CNT_IF_0_65 CNT_IF_3_80 CNT_IF_2_80 CNT_IF_1_80 CNT_IF_0_80 CNT_IF_3_90 CNT_IF_2_90 CNT_IF_1_90 CNT_IF_0_90 CNT_IF_YTD_90 . . . . Date 27-JAN-17 10-FEB-17 24-FEB-17 10-MAR-17 27-JAN-17 10-FEB-17 24-FEB-17 10-MAR-17 27-JAN-17 10-FEB-17 24-FEB-17 10-MAR-17 YTD AVG 2017 ACCESS EAST E AL % Congested w/o PE 5.16% 5.67% 5.62% 5.28% 1.93% 2.33% 2.04% 2.10% 1.15% 1.09% 0.95% 1.08% 1.09% What I understand from that is when REPORT_TAB EQ ' ' the record contain the dates otherwise they are data records with the percentage per DIVISION/REGION/STATE. So your first read (outside the loop) is for the date record : REPORT_TAB EQ '' Following reads (innner loop) are for percentage records : REPORT_TAB NE '' or REPORT_TAB EQ 'ACCESS' To be able to produce a report/graph you first need to understand and analyse your data. I may be wrong, but according to information that I have and your data understanding, the sample code that I provided may answer your need. Look at it closely. WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007 | |||
|
Guru |
Hi, Thanks a lot Martin ![]() Finally,to get the graph as posted in the image in the earlier post, can I do something like this ? GRAPH FILE PIVOTED SUM PIVOTFLD BY DATEFLD BY REGION END -RUN Please let me know. Regards, IP | |||
|
Virtuoso |
You should probably have to do something such as: GRAPH FILE PIVOTED SUM PIVOTFLD BY DATEFLD BY MEASTYPE NOPRINT BY REGION ... <other graph formatting/attributes> END -RUN But I suggest to use the GUI. It may help to format the way you want. WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007 | |||
|
Guru |
Hi Martin, Thanks a ton for all your help !! I will use the GUI and you are a rockstar ![]() Once again thanks a million!! Regards, IP Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML | |||
|
Powered by Social Strata |
![]() | Please Wait. Your request is being processed... |
|