Focal Point
[CLOSED] Excel Pivot Table

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/225105293

March 12, 2010, 08:08 AM
Danny-SRL
[CLOSED] Excel Pivot Table
Hello,

When creating a PIVOT table, WF automatically presents the verb object as Row Labels. Is there a way to have them displayed automatically as Column Labels?

  
DEFINE FILE CAR
2_WE/D7=WEIGHT*2;
END
TABLE FILE CAR
PRINT WEIGHT 2_WE
BY CAR
ON TABLE PCHOLD FORMAT EXL2K PIVOT
END

This message has been edited. Last edited by: Danny-SRL,


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

March 12, 2010, 09:08 AM
<JG>
Unless you deliver the basic data and then use a macro to generate the pivot; I do not think so.
March 15, 2010, 04:50 AM
Devi C
Hi Danny,

Are you expecting the below ouput?

  

DEFINE FILE CAR
2_WE/D7=WEIGHT*2;
END
TABLE FILE CAR
PRINT WEIGHT 2_WE
ACROSS CAR
ON TABLE PCHOLD FORMAT EXL2K PIVOT
END




WebFOCUS 7.6.4,7.6.9
OS: Windows XP
HTML,EXCEL,PDF
March 17, 2010, 12:25 PM
EWoerle
Danny,

What I've learned recently is that its not WF that presents the verb object as Row Labels (my opinion at least), but the way a pivot table behaves in general. If you were to create a pivot table manually you would see the same thing happening in Excel. What I learned is that Excel only puts the verbs across the top when you drag the data tab into the across sort area of the Pivot Table.

Recently we've had more users asking for this sort of thing by us, where as I haven't been able to actually put a proof of concept together, but I think the solution would be to create a by field to that as just a count of your verbs and put your file into a sort of up and down setup, then take that sort field and make it an across. The only problem is that you can't make that across field a noprint so you might want to style it as white text to hide it.

ie. IF SORT EQ 1 THEN RETAIL_COST ELSE IF SORT EQ 2 THEN DEALER_COST

Something like that while using a MacGyver type of technique to create your sort column.

Eric


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
March 18, 2010, 03:36 AM
Tony A
How would you like to be able to control the Excel pivot table AND get it to create a pivot chart as well?

I have an Excel template that allows you to do just that and the fex to populate would look like this -

APP FILEDEF pivot_out DISK userinfo/pivot_&MRE_User.EVAL.xls
TABLE FILE GGSALES
SUM DOLLARS    AS 'Dollars'
    BUDDOLLARS AS 'Budget'
 BY REGION     AS 'Region'
 BY ST         AS 'State'
 BY CITY       AS 'City'
 BY STCD       AS 'StoreCode'
 BY CATEGORY   AS 'Category'
 BY PRODUCT    AS 'Product'
HEADING
"Data Dollars"
"Data Budget"
"Page State"
"Page City"
"Page StoreCode"
"Row Category"
"Column Region"
"End"
ON TABLE SET HTMLCSS ON
ON TABLE HOLD AS pivot_out FORMAT EXL2K TEMPLATE Pivot_Flex SHEETNUMBER 2
ON TABLE SET BYDISPLAY ON
ON TABLE SET STYLE *
  TYPE=REPORT, HEADALIGN=BODY, $
ENDSTYLE
END
-RUN
-HTMLFORM BEGIN
<html>
<head>
<title>Pivot Web Archives</title>
<script language=javascript>
function fetch_src() {
  this.location.href = "file://[your server name]/userinfo/Pivot_&MRE_User.EVAL.xls";
}
</script>
</head>
<body bgcolor="#fcfbff" OnLoad="fetch_src();">
</body>
</html>
-HTMLFORM END

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10