Nested Subtotal problem 2

[glenntb]

Hi all. Our office just upgraded to Office 2003. One of our daily reports uses nested subtotals (Data / Subtotals...) to present the MTD sales activity sorted by sales person + calendar date:

Date Value Name
11/1 1 Fred
11/1 1 Fred
11/1 Total: 2
11/2 1 Fred
11/2 Total: 1
3 Fred Total

When the report is produced on a machine without Office SP1 installed, the subtotals work perfectly. If the report is produced on a machine with Office SP1 installed, the subtotals will not work on any grouping with only one line in it. For instance the above example would end up looking like this:

Date Value Name
11/1 1 Fred
11/1 1 Fred
11/1 Total: 2
11/2 1 Fred
3 Fred Total
11/2 Total: 1 [red]<-- This line s/b above the "Fred Total" line [/red]

Any idea why this is? Our solution at this point is to only produce the report on non-SP1 machines - but I'd prefer a better solution. Thanks in advance!

Glenn

 

[KenWright]

Sounds like the following documented bug and there is a hotfix for it:-

http://support.microsoft.com/default.aspx?id=833855

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

Try the hotfix first and if no joy there then maybe the following note I found from a newsgroup post will help - make sure you back up the registry first though if you go this route:-

> > I too am having troubles with nested levels. I have tried it
again and again. It used to work so easily(!). I did the same
process, without really having to think about it, last quarter and it
worked fine and easily. I just did an update of Windows XP, and that
may be the problem. I am sorting on 4 columns and sub-totalling those
columns. It appears the first 3 or 4 names sort one way and the rest
sorts another way -- but even that way is not consistent. Very
frustrating, but glad to hear it may not just be me! Using Excel 2003
with Office Professional for Windows XP.
> >
> >
> > "M Wheeler Ithaca NY" wrote:
> >


I got in contact with Microsoft and there is a fix for this that
requires an update to the registry to tell Excel 2003 to use the Excel
97 subtotal method since the new code subtotals incorrectly. I updated
my registry and the subtotaling now works. The registry entry that
needs to be added is:



Add the following registry key to -
HKEY_CURRENT_USER\software\microsoft\office\11\excel\options

Dword = Excel97Subtotals

Value = 1

Be sure to backup the registry before updating.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[glenntb]

Thanks Ken! The hotfix didn't seem to do it, but the registry edit did! Well worth a [purple]star[/purple]! Thanks!

Glenn

 

[KenWright]

You're welcome - Glad it worked for you

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[ksmtech]

We recently upgraded to Office 2003 and have noticed this same issue... Thanks for the fix! The registry edit works on my standalone PC.

The problem, though:
Our users work in a terminal services environment with Citrix MetaFrame. We use thin clients and publish their entire desktop. Neither the hotfix from MS nor the registry edit seems to apply to their profiles.
Has anyone gotten this to work in a terminal services environment?

Thanks,
Jon

 

[GlennUK]

A contact of mine had this problem recently Ken, so thanks for that, and have another star.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

Cheers Glenn

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------