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     [CASE OPENED] Pivot Table Mechanics and Formatting
Go
New
Search
Notify
Tools
Reply
  
[CASE OPENED] Pivot Table Mechanics and Formatting
 Login/Join
 
Member
posted
Hello,

I have the following code and its output as far as the data goes is what I would expect:

TABLE FILE HOLD1
PRINT USER_COUNT
BY VAR2
ACROSS VAR3
ON TABLE SET PAGE NOPAGE
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K PIVOT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
ENDSTYLE
END

What would I need to do to add totals to the columns and rows? My output looks like this right now:

Sum of User_Count
Var2 | Var3-Category1 | Var3 - Category2
1 2 4
2 5 0
3 3 3
...

All I'd like to do is add a total column to the right that would have values 6, 5, 6, etc. and a total row along the bottom that would have (if this were the end of my table) 10, 7 as values.

Thanks in advance...

This message has been edited. Last edited by: FP Mod Chuck,


intsoccersuperstar
WebFOCUS 8105m
Windows, Excel
 
Posts: 16 | Registered: October 17, 2018Reply With QuoteReport This Post
Expert
posted Hide Post
For row totals use
ON TABLE ROW-TOTAL

I'm not sure that pivot tables can have the COLUMN-TOTAL equivalent though.

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
Expert
posted Hide Post
Just spotted that you have ON TABLE NOTOTAL. Remove that and add ON TABLE COLUMN-TOTAL.

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
Member
posted Hide Post
Thanks Tony. I get the totals now, however in the Excel file that opens up only Columns A and B are an actual part of the pivot table. All the data looks right but columns C (Var 3- Category 2) and D (Grand Total) are just normal excel output. Any idea why this might be happening


intsoccersuperstar
WebFOCUS 8105m
Windows, Excel
 
Posts: 16 | Registered: October 17, 2018Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
TABLE FILE HOLD1
PRINT USER_COUNT
BY VAR2
ACROSS VAR3
ON TABLE SET PAGE NOPAGE
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K PIVOT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
ENDSTYLE
END

We posting sample code, get used to showing it using one of the sample databases installed (generally) with WebFOCUS, such as CAR, GGSALES, EMPLOYEE etc.

That way, you can help us understand what you are trying to do exactly rather than us have to second guess.

For instance, you do not show any calculations (or COMPUTEs) in your code but this is inferred in your subsequent question. So give us more detail if you would like us to be able to help.

The code using a sample database, that I am using, is as follows. Try expanding upon it to explain what you are trying to do.
TABLE FILE GGSALES
PRINT DOLLARS
BY REGION
ACROSS PCD
ON TABLE SET PAGE NOPAGE
ON TABLE ROW-TOTAL COLUMN-TOTAL
ON TABLE PCHOLD FORMAT EXL2K PIVOT
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
Member
posted Hide Post
Thanks Tony. What you have there using the sample code is exactly what I am trying to do - but for some reason my entire output is not in the pivot table. It would be as if the pivot table ended at column H (G100) in your output, and then columns I, J, K, and L are present but are just normal excel output outside of the pivot table.

I actually just tested my code by limiting the number of rows I am outputting (by using WHERE ROWNUM<1000 in SQL prior to ending the SQL statement and using WebFocus) and it works perfectly. I am thinking it must have something to do with the size of the output, because when I try to run the whole thing I get a message when Excel opens stating

"PivotTable report "PivotTable1" contained incomplete information. To restore the missing information, click OK, and then, with the PivotTable report selected, on the Analyze tab, in the Data group, click Refresh."

And then I get a "PivotTable load problems" message as well.

Again, when I limit my output using ROWNUM<1000 it works perfectly, I only get this message and run into the half-completed PivotTable if I let it run without limits.


intsoccersuperstar
WebFOCUS 8105m
Windows, Excel
 
Posts: 16 | Registered: October 17, 2018Reply With QuoteReport This Post
Expert
posted Hide Post
How many unique rows of data might your request result in?

e.g. the number of rows produced by something like this?
TABLE FILE HOLD1
PRINT USER_COUNT
BY VAR2
BY VAR3
ON TABLE HOLD
END

I am wondering if this is an Excel 2003 limitation.

Once you have the number then it might be worth following up with a case with Tech Support.

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
Member
posted Hide Post
Over 66,000 rows. Thanks, I will check with Tech Support.


intsoccersuperstar
WebFOCUS 8105m
Windows, Excel
 
Posts: 16 | Registered: October 17, 2018Reply With QuoteReport This Post
Platinum Member
posted Hide Post
66,000 rows is over Excel2k's row limit.


WebFOCUS 8.2.03 - Production
WebFOCUS 8.2.04 - Sand Box
Windows 2012 R2 Server
HTML, PDF, Excel
In FOCUS since 1980
 
Posts: 116 | Location: Seattle, WA | Registered: April 07, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CASE OPENED] Pivot Table Mechanics and Formatting

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