Errors in 'counts' and 'sums' in a 1-to-many report 1

[montypython1]

Greetings & Merry Christmas,

What is the best way to count and sum records in a report which is based on a 1-to-many relationship?

I have a report in which the relationship is working perfectly on screen and in the browse windows (ex: when I view the 2 related tables in 2 browse windows, and then click on the desired record in the parent table, the child table shows only the related records).

The problem is with the report (I think) ... When I display the data, I am using a Group Header to hold the data from the parent table (ex: a vehicle's year, make, model, etc). I am using the detail section of the report to hold the data from the child table (ex: the numerous options installed on that particular vehicle).

When I try to count the number of records in the parent table, it counts the number of records in the child table, even though I have defined the report variables to be reset after the group header changes.

The result: It shows 31 vehicles (total number of options) when it should show 4 vehicles.

Any suggestions?

Thanks,
David Higgins

 

[crewchiefpro6]

His report has multiple groupings. What we need to count is really the number of group records, not detail records. The report has 4 groupings right now.

He would like to count the number of distinct models in the inventory. Then under these distinct models are different Model Numbers that all point to slightly different versions of the car.

For example:
LINCOLN VEHICLES (GROUP 1)

Aviator Models: (GROUP 2)

MODEL CODE: UAB878 (GROUP 3)
Stock Number, Color, Model Code, List Price , OPTIONS

(GROUP 4)
P1234 , RED , UAB878 , $54,750.00

*** DETAIL LINES BELOW WHICH HAVE EACH OPTION***** A46 32 VALVE V8
Z34 5 SPEED AUTOMOTIC TRANSMISSION
67T NAVIGATION SYSTEM
32H BOSE SUPER CD WITH 12 SPEAKERS
71F LIGHT PEARL PAINT UPGRADE


P1344 , WHT , UAB878 , $54,002.00

*** DETAIL LINES BELOW WHICH HAVE EACH OPTION***** A46 32 VALVE V8
Z34 5 SPEED AUTOMOTIC TRANSMISSION
67T NAVIGATION SYSTEM
32H BOSE SUPER CD WITH 12 SPEAKERS


P1321 , GRN , UAB878 , $53,250.00

*** DETAIL LINES BELOW WHICH HAVE EACH OPTION***** A46 32 VALVE V8
Z34 5 SPEED AUTOMOTIC TRANSMISSION
32H BOSE SUPER CD WITH 12 SPEAKERS
71F LIGHT PEARL PAINT UPGRADE

HERE WE WANT A COUNT OF THE NUMBER OF UAB878 AVIATORS
WHICH SHOULD BE 3 VEHICLES. THE VARIABLES SEEM ONLY TO
COUNT THE DETAIL LINES.


MODEL CODE: UAB812 (GROUP 3)

Stock Number, Color, Model Code, List Price , OPTIONS (GROUP 4)

P1411 , RED , UAB812 , $56,750.00

*** DETAIL LINES BELOW WHICH HAVE EACH OPTION***** A46 32 VALVE V8
Z34 5 SPEED AUTOMOTIC TRANSMISSION
67T NAVIGATION SYSTEM
32H BOSE SUPER CD WITH 12 SPEAKERS
71F LIGHT PEARL PAINT UPGRADE
16Z POWER SUNROOF


P1444 , WHT , UAB812 , $55,102.00

*** DETAIL LINES BELOW WHICH HAVE EACH OPTION***** A46 32 VALVE V8
Z34 5 SPEED AUTOMOTIC TRANSMISSION
67T NAVIGATION SYSTEM
32H BOSE SUPER CD WITH 12 SPEAKERS
16Z POWER SUNROOF


HERE WE WANT A COUNT OF THE NUMBER OF UAB812 AVIATORS
WHICH SHOULD BE 2 VEHICLES.

I hope that formatted properly. I know this is complex but it should be able to be performed.


I have never had to count GROUP records, and all attempts to get this to come out properly have not progressed well.

Don Higgins (for my brother Dave Higgins)
Crew Chief Pro Software

http://www.crewchiefpro.com

Don Higgins

http://www.crewchiefpro.com

 

[TamarGranor]

The usual solution to this type of problem is to use a pair of report variables. Be sure to list this in this order:

GroupCount
Initial Value = 0
Value to Store = IIF(GroupVar = <current group val>, 0, 1)
Calculate-Sum

GroupVar
Initial Value = <some impossible value for the grouping expr>
Value to Store = <current group val>

Tamar

 

[crewchiefpro6]

Thanks Tamar,

>> Value to Store = IIF(GroupVar = <current group val>, 0, 1)
<<
I guess I don't understand the <current group val>. I placed the group instead and I get a operator operand mismatch.

It was nice to finally meet you in Chicago. Great book, it has helped my understanding of SQL, and the code we use to generate the report data is very cool and somewhat complex. We never could have done it without the help of the book.

Don Higgins

http://www.crewchiefpro.com

Don Higgins

http://www.crewchiefpro.com

 

[TamarGranor]

You're grouping on some expression. Use the same expression as what I called "current group val." In your example, it looks like it would be the model code.

Tamar

 

[crewchiefpro6]

Thank you very much Tamar.


Don Higgins

http://www.crewchiefpro.com

 

[montypython1]

Hi Tamar,

Don and I are attempting to implement your suggestion above, but are missing something. We have taken your example and added the appropriate field names and expressions to match our database:

ModelNumCount:
Initial Value = 0
Value to Store = IIF(ModelNumVar = ModelNum, 0, 1)
Calculate-Sum

ModelNumVar:
Initial Value = 111111111 <some impossible value for the grouping expr>
Value to Store = ModelNumVar <current group val>

Following is a simplified example of what the report should look like:
StockNum ModelNum Options
A1234 M87 CD PLAYER
- - - - - - - - LEATHER
- - - - - - - - SUNROOF
A1235 M87 CD PLAYER
- - - - - - - - SUNROOF
M87 TOTAL = 2

Our current report counts 5 records (# of Detail records), instead of 2 records (# of records in the ModelNum Group).

Our apologies that we are not getting this yet, but any clarification would be greatly appreciated.

Thanks,
Dave and Don Higgins

 

[montypython1]

--- continued from previous post ---

Almost forgot .... the report will have 40 - 50 different ModelNum's (we are NOT trying to count just 1 specific ModelNum).

Thanks again.

Dave & Don Higgins

 

[TamarGranor]

Looks like I guess wrong that it was modelnum that you want to count. What you're actually counting is StockNum? If so, use that in the report variables.

Tamar

 

[crewchiefpro6]

Tamar, we are counting the number of stocknum in the group of modelnums.

We cannot figure out the way to put the variable in properly and have it count the number of modelnum, and the number of stocknum. Each time we add the information into the variable it still counts the number of detail lines. I am quite sure we are missing something simple.

Dave needs this done quickly, if we cannot get it would you be interested in fixing it (we will of course pay you).

I can have him upload sample files on his web site that you can download for testing.

Let us know if you are interested. This cannot be as difficult as we are making it.

Don


Don Higgins

http://www.crewchiefpro.com

 

[TamarGranor]

What are the grouping variables in the report in the order they're defined?

Tamar

 

[crewchiefpro6]

GroupCnt and GroupVar, in that order.

GroupCnt:
Value to Store: IIF(GroupVar = modelnum, 0, 1)

Initial Value: 0

Reset Value Based on: Group:Modelnum




GroupVar
Initial Value = 999999999
Value to Store = modelnum



Don Higgins

http://www.crewchiefpro.com

 

[montypython1]

Hi Tamar,

Don and I tried using StockNum instead of ModelNum, but are still not receiving the correct count of the number of vehicles within each ModelNum category (it seems to be counting the number of records in the "Detail" section of the report, instead of the number of records in the "Group Header" section).

I suspect this is so simple that I will feel foolish once we figure out our problem. I emailed the files to you, in case you wanted to see the problem firsthand.

We realize that you will be busy until late Tuesday, but any assistance will be appreciated.

Thanks,
Dave and Don Higgins

 

[TamarGranor]

Just to provide some closure, the issue seems to have been the order of the variable declarations. The counter variable must be defined before the "last group" variable, so that it can properly catch the changes in the "last group" variable.

Tamar

 

[montypython1]

Hi Tamar,

It worked like a charm.
Thank you so much!

Dave Higgins