Group total always adds 1 for the last group 1

[Microboot]

I've looked through forums and can't find a solution to the exact problem I am having so here goes.

We have a database which holds asset data for each PC we own. The fields I'm using are Business(We are split into 5 businesses), Site(We have over 20 sites) and Operating System of the PC's.

I have 3 Groups

Group 1 - Business
Group 2 - Site
Group 3 - Operating System

I have 1 Summary which summarises the number of each OS per site

So far it works.

I have 6 formulas for the various operating systems

BusinessWindows95
BusinessWindows98
BusinessWindowsME
BusinessWindowsNT
BusinessWindows2000
BusinessWindowsXP

And they all look like this(Except for the variable name which changes to reflect the OS I'm counting)

WhilePrintingRecords;
Global NumberVar BusinessWindows2000;
IF {Hdw_DOS.Host_Operating_System} = 'Microsoft Windows 2000' THEN BusinessWindows2000 := BusinessWindows2000 + 1 ELSE BusinessWindows2000;

Each variable also has a corresponding reset formula which looks like this

WhilePrintingRecords;
Global NumberVar BusinessWindows2000 := 0;

I have placed the reset formulas in the Business group header

I have placed the BusinessWindows2000 etc. formulas in the details section.

And I display the results in the Business Footer.

So what I have so far is a Summary which shows OS's by Site - Works Fine.

I then have the variables which shows the OS's by Business - I have pasted some live data below so you can see the problem.

Rotterdam
Microsoft Windows XP 2 <- My Summary
Windows 95 1
Windows 98 49
Windows NT 3
Sint Niklaas
Windows 98 8 My Formulas
Windows NT 1 !
V
Total number of Windows 95 PC's for the Business is 1.00
Total number of Windows NT PC's for the business is 5.00
Total number of Windows 98 PC's for the Business is 57.00
Total number of Windows 2000 PC's for the business is 0.00
Total number of Windows ME PC's for the business is 0.00
Total number of Windows XP PC's for the business is 2.00

As you can see Windows NT is actually 1 more than it should be. All the rest are correct. What's happening is that because the last OS Group is Windows NT it counts it twice. If I look further down my data it's not always Windows NT, it could be Windows 98 but it's always the last one listed in the group.

Everything else works i.e. variable resets it's just this one thing and it's driving me nuts.

Can anybody help. I apologise for the long post but I thought more detail rather than less would be better.

Thanks in advance
Michael

 

[Reebo99]

Create 6 formulas like :

IF {Hdw_DOS.Host_Operating_System} = 'Microsoft Windows 2000' then 1 else 0

IF {Hdw_DOS.Host_Operating_System} = 'Microsoft Windows NT' then 1 else 0

IF {Hdw_DOS.Host_Operating_System} = 'Microsoft Windows 98' then 1 else 0

etc.....

Then put these formulas in the detail, right click, insert summary, and sum the field, base it on different groups to see different totals.


Reebo
Scotland (Sunny with a Smile)

 

[Ngolem]

It sounds like you have separate formulas for each system and that isn't really necessary.

OH! I see why...since you are doing summary totals on them. I prefer my totals manually done, so I will show you how to do it that way....it isn't a major change to your structure.

Let us start with the reset formula (You don't need the word &quot;Global&quot; in declaring the variable since it is Global by default).

You require 2 types of totals if doing them manually. An BusinessTotal and a SiteTotal. I will show you how this is done in single formulas for each.

The only thing I would caution is that placing these in the group header you run the risk of resetting your totals in a repeated Group Header. This is prevented by making your formula like the following:

@ResetBusinessTotals (suppressed in Business Group 1 hdr)

WhilePrintingRecords;

if not inrepeatedGroupHeader then
(
NumberVar BusinessWindows2000 := 0;
NumberVar BusinessWindows95 := 0;
NumberVar BusinessWindowsNT := 0;
NumberVar BusinessWindows98 := 0;
NumberVar BusinessWindowsME := 0;
NumberVar BusinessWindowsXP := 0;
);

Similarly we make a reset formula for the SiteTotals that we place in the Site Group header (Group 2)

@ResetSiteTotals (suppressed in Site Group 2 header)

WhilePrintingRecords;

if not inrepeatedGroupHeader then
(
NumberVar SiteWindows2000 := 0;
NumberVar SiteWindows95 := 0;
NumberVar SiteWindowsNT := 0;
NumberVar SiteWindows98 := 0;
NumberVar SiteWindowsME := 0;
NumberVar SiteWindowsXP := 0;
);

Now in the details section we place a SINGLE calculation formula.

@CalcOS (Suppressed in Detail Section)

WhilePrintingRecords;

NumberVar BusinessWindows2000 ;
NumberVar BusinessWindows95 ;
NumberVar BusinessWindowsNT ;
NumberVar BusinessWindows98 ;
NumberVar BusinessWindowsME ;
NumberVar BusinessWindowsXP ;
NumberVar SiteWindows2000 ;
NumberVar SiteWindows95 ;
NumberVar SiteWindowsNT ;
NumberVar SiteWindows98 ;
NumberVar SiteWindowsME ;
NumberVar SiteWindowsXP ;

IF {Hdw_DOS.Host_Operating_System} = &quot;Microsoft Windows 2000&quot; THEN
(
BusinessWindows2000 := BusinessWindows2000 + 1;
SiteWindows2000 := SiteWindows2000 + 1;
)
ELSE IF {Hdw_DOS.Host_Operating_System} = &quot;Microsoft Windows 95&quot; THEN
(
BusinessWindows95 := BusinessWindows95 + 1;
SiteWindows95 := SiteWindows95 + 1;
)
.
.
...[ADD IN OTHER COMPARISONS]...
.
.
ELSE IF {Hdw_DOS.Host_Operating_System} = &quot;Microsoft Windows ME&quot; THEN
(
BusinessWindowsME := BusinessWindowsME + 1;
SiteWindowsME := SiteWindowsME + 1;
)

Now you can create your display formula for the Site and Business. Since there are less than 254 characters involved you can do this in a single formula but I would do it in two in order to get a razor sharp edge to the totals.

@DisplaySiteOSDescriptions (placed in Site footer)

WhilePrintingRecords;

&quot;Windows 95 &quot; + chr(13) + chr(10) +
&quot;Windows 98 &quot; + chr(13) + chr(10) +
&quot;Windows NT&quot; + chr(13) + chr(10) +
&quot;Windows 2000&quot; + chr(13) + chr(10) +
&quot;Windows ME&quot; + chr(13) + chr(10) +
&quot;Windows XP&quot; ;


@DisplaySiteOSCount (Placed to the right of @DisplaySiteOSDescriptions )

WhilePrintingRecords;

NumberVar SiteWindows2000 ;
NumberVar SiteWindows95 ;
NumberVar SiteWindowsNT ;
NumberVar SiteWindows98 ;
NumberVar SiteWindowsME ;
NumberVar SiteWindowsXP ;

totext(SiteWindows2000,0) + chr(13) + chr(10) +
totext(SiteWindows95,0) + chr(13) + chr(10) +
totext(SiteWindowsNT,0) + chr(13) + chr(10) +
totext(SiteWindows98,0) + chr(13) + chr(10) +
totext(SiteWindowsME,0) + chr(13) + chr(10) +
totext(SiteWindowsXP,0) ;


You would do a similar two formula types for your Business totals.

By my count this is only 7 formulas so this will be less than what you started with and you have total control over what is happening.

I don't think you will have any problems with the math using this approach.

Jim Broadbent

 

[Reebo99]

JB,

Microboot is only doing a conditional count, the easiest way is to do formulas like:

If condition then 1 else 0

then sum the field. You cna then sum by different groups, giving different levels of conditional counts.

I'm not saying your post won't work, I'm just saying it's quicker, easier and less hassle to support using my post.

Reebo
Scotland (Sunny with a Smile)

 

[Microboot]

Thanks to you both for your help. I have decided to go with Reebo99's solution for it's simplicity but ngolem's post actually fixes another one of my post so I win twice

Thank again for your speedy an insightful posts.

Michael

 

[Ngolem]

Reebo

there is nothing particularily complicated in my approach. It is a matter of style.

I just find that by doing summary reports in this manner I have total control over the process and I can easily format reports the way I wish.

Jim Broadbent

 

[Reebo99]

Jim,

didn't mean to sound so confrontational......it's been a long day....please accept my apologies...

Reebo
Scotland (Sunny with a Smile)

 

[Ngolem]

accepted

Jim Broadbent