Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Excel Pivot Table
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Excel Pivot Table
 Login/Join
 
Virtuoso
posted
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

 
Posts: 1936 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
<JG>
posted
Unless you deliver the basic data and then use a macro to generate the pivot; I do not think so.
 
Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 18 | Registered: September 01, 2008Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 95 | Registered: July 31, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
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.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5617 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Excel Pivot Table

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.