Simple Pivot Table Totals Question

[rusty cranbrook]

Microsoft, you've done a better than usual job of issuing software
with documentation that is lacking. Good show!

I have spent hours now trying to figure out how to create a totals
field that sums values with recent versions of OWC. I have discovered
exactly nothing after a full perusal of the docs and the samples
included with the OWC. The data is sourced from an Access 2002 table;
the OWC control is placed on an Access form.

Here is what a sample of the data looks like; two row fields and a
detail field:

Country City Units
Canada Ottowa 200
Vancouver 230
Total

USA New York 410
St. Louis 312
Total
Grand Total

The Total and Grand Total items appear automatically - the pivot table
inserts them. However I can't find any way to get value into them; the
value which would be obvious is the sum of Units. Supposedly I can
create a totals field that sums Units - but I can't get the expression
builder to work. If I use the Calculated Totals and Fields icon, and
pick Create Calculated Total (per the help file, all of this) I get a
total mess. First, a new field is inserted in the detail area, called
New Total, obscuring the main detail field data. I want it in the
Totals row items as subtotals and grand totals. The second problem is
that I cannot enter any expression that OWC regards as valid. I use
the Insert Reference To button to enter [Units].[Units]. That fails
with "Formula Error - cannot find dimension member
("[Units].[Units]"). Entering Sum([Units].[Units]) creates the same
error; so does every variant I've tried.

This should be extremely simple.

Congrats Microsoft, for once again outdoing yourself in obscuring what
are probably some very useful features.

 

[rusty cranbrook]

I've since discovered a few more items about totalling, none of which
result in anything useful.

If you forget about the toolbar icon for calculated fields... if you
right click on a field header you can usually find an option called
"AutoCalc". For most of the headers, it offers only to Count, but for
the detail field it has choices like sum and average. Fine. So you
pick Sum. It sums each row of one item. So a row like Chicago that has
a detail value of 100 has a Sum value of immediately below the detail
value of... 100! How incredibly useful!!! 100 summed is 100! I really
needed to know that! Down in the Grand Totals area, what do I find?
The Grand Total is listed. But if I click the +, the expand indicator,
I see... a complete listing below of all the detail values. I suppose
it could make sense that it does this but it's inconsistent with the
sums in the detail area proper, which show detail and sum by default.
How incredibly confusing.

None of this results in what I want, what anyone would want, as I
don't need each row's value reiterated by the Sum - I only want it on
the Grand Total and Sub Total levels.

Also, once you apply the AutoCalc there doesn't seem to be any way to
get rid of it.

Many times you don't see the Sum or the Count unless you click the +
sign in the header. This can be confusing. I'm recording this all in
detail in case someone else is able to extract value from whatever I
found.