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,

For row totals use

ON TABLE ROW-TOTAL

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

T

Just spotted that you have ON TABLE NOTOTAL. Remove that and add ON TABLE COLUMN-TOTAL.

T

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

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

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.

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

Over 66,000 rows. Thanks, I will check with Tech Support.

66,000 rows is over Excel2k's row limit.