Data set connecting to Multi Data Set

[ericfmyers]

I have a multi table report and all was fine in the details until I added in one last table. The main table is set like so:

Container
{Container.ContainerNumber}
{Container.CustomerName}
{Container.ProductID}
{Container.QTY}

The details are grouped by {Container.CustomerName} then by {Container.ContainerNumber}. The details list the {Container.ProductID} and {Container.QTY}. The {Container.QTY} is then summed up for each group.

On the report it looks like this:

CustomerName ABC123
Container Number 12345
Product ID QTY
_test1_ 1
_test2_ 2
Sub total 3
Container Number 12346
Product ID QTY
_test3_ 1
_test4_ 2
Sub total 3

Total 6

This all works fine until I add in the next table:

User
{User.ContainerNumber}
{User.ProductID}
{User.UserName}
{User.Date}

They are linked by ContainerNumber. But the User Table has multiple instances for each {User.ProductID} so it messes up the QTY like so:

CustomerName ABC123
Container Number 12345
Product ID QTY User
_test1_ 1 Bob
_test1_ 1 Ted
_test2_ 2
Sub total 4

Container Number 12346
Product ID QTY User
_test3_ 1
_test4_ 2 Mary
_test4_ 2 Ted
Sub total 5

Total 9

I don't really need the User's name I just want to know if someone is on it or not. So I could use a True/False statement for that. I know I could add another group but then it still messes up my totals at the end. Is there a way to keep the details to a number that matches the original table? Is there a link option that would accomplish this?

Thanks.

 

[lbass]

You could insert a group on product ID and add the quantity field to the group header and then insert a maximum on the User field at the Product ID level. If there is at least one user, the summary will be populated. But, this would only work if there can be only one quantity value per Product ID.

Then instead of inserting a summary on the quantity, you would insert a running total that used sum of quantity, evaluate on change of group: Product ID, reset on change of group: container#. For the customer name total, add another running total like the previous one, but with a reset on change of customer name. Each running total must be in the group footer of the corresponding reset group field.

-LB

 

[ericfmyers]

I tried the running total first but my numbers where still off. Found out that sometimes there are duplicate ProductIDs for the containers that throw the totals off. I'm going back to the Database manager and see if we can add a Y/N switch to the container table that states if it was worked on.

Thanks,
Eric

 

[lbass]

I think you set up the running total improperly. There cannot be duplicate ProductIDs within the container group if you have a group#3 on Product ID. The running total would only evaluate on change of this group. Assumes Group #1 on Customer, Group #2 on Container, Group #3 on Product ID.

You need a separate running total for each group level. Place the running totals in group footer sections.

-LB