Adding up data in Crystal Reports

[macktruck]

Dear Experts,

I have quite a problem here :-

I am trying to add up data that sits on tables as some kind of hierarchy. The data is as follows :-

itemcode qty treetype
1001 2 N
1002 5 N
1003 1 S
1004 6 I
1005 9 I
1006 3 N

I would to sum up the data in a report as follows :-

the rows with treetype N are grouped as they are, but for treetype I the rows quantities are summed up and added as the totals for tree type S. Please note that the qty 1 for the row with tree type S is not added up. It is there for display purposes only.

itemcode qty treetype
1001 2 N
1002 5 N
1003 15 S
1006 3 N

Please help.

Regards,

 

[macktruck]

Any other information required for this ?

 

[Bloke152]

I would make a formula field that does

If treetype = "I" then "S"
else treetype

and then group by that field. Then create a summary field for quantity.

What do you need to show for item code?

 

[macktruck]

Thanks Bloke152, but when i group by that formula I get the data split into two. The first part is Treetype and the second type is S.

The key thing is to get the S under the tree type and so add up the quantities of the child items underneath, to sum up and the qty to appear at the parent items.

 

[macktruck]

Just some more information, the data is actually like this :


itemcode qty treetype
1001 2 N
1002 5 N
1003 1 S
1004 6 I
1005 9 I
1006 3 N
1007 1 S
1008 5 I
1009 9 I
1010 3 N

The quantity will be summed up for the child totals to add up and appear at the parent items. The child items will not display but will be hidden. The parent items will display.

Like the following :-

itemcode qty treetype
1001 2 N
1002 5 N
1003 15 S
1006 3 N
1007 14 S
1010 3 N

Item codes and row details for child items are hidden. The quantities are summed up.

 

[lbass]

I've never used it, but there is an option when inserting summaries to use hierarchical summaries. You might want to explore that.

-LB

 

[macktruck]

The information actually exists as hierarchical data in a different table. I wander if that would actually help ?

 

[macktruck]

I don't know if this would help but each row has a unique identifier. Am thinking of a way such that where is a row type S followed by I then this would have the same unique number and for a row type N a unique number giving us the possibility of grouping this way. Problem is I don't really know how to do that.

 

[lbass]

Is there some field that tells you which "I" records go with which "S" (other than the sequential item code field)?

-LB

 

[macktruck]

As a rule in the software after each S will be an I. There can never been differences in that such as having an S then an N and later I . That cannot happen.

There is a diffrent table that has the S and I linked as father and child. It has format such as

childitemcode fatheritemcode Qty
1004 1003 1
1005 1003 1

 

[lbass]

Yes, you should be using that table as part of the solution. What about item codes that have no children? Do they appear in this table with null child codes or are they not present at all?

-LB

 

[macktruck]

They are not present at all in this table.

 

[lbass]

Link the fatherchild table with a left join FROM the main table to the fatherchild table on main.itemcode = fatherchild.childitemcode. Then create a formula {@grp}:

if isnull({fatherchild.childitemcode}) then
{main.itemcode} else
{fatherchild.fatheritemcode}

Insert a group on {@grp}. Then create a formula for quantity {@qty} and place it in the group section:

if isnull({@fatherchild.childitemcode}) then
{main.qty} else
sum({main.qty},{@grp})-1 //to remove the parent qty = 1

This assumes that each itemcode has only one row.

-LB

 

[macktruck]

Thanks lbass, what I have realised is that the script works where I have only one set of father child relationship on the main table, but when I get two or more then some fathers are left out and the quantity gets wrong.

What change could be done to allow it to read multiple father child relationships.

 

[lbass]

I don't know what you mean. What I suggested should have worked for multiple item numbers in the main table that are "fathers".

Can you show sample data that illustrates the issue?

-LB

 

[macktruck]

This is the data from maintable. i.e. select qty, itemcode,treetype from maintable.

ItemCode Quanti TreeTyp DocEntr LineNum
MFx7 1 S 979 0
R219 2240 I 979 1
R223 840 I 979 2
R226 840 I 979 3
R224 1400 I 979 4
R220 840 I 979 5
R221 840 I 979 6
R225 2240 I 979 7
MFx20 1 S 979 8
R219 875 I 979 9
R223 875 I 979 10
R226 875 I 979 11
R224 875 I 979 12
R219 875 I 979 13
R220 875 I 979 14
R221 875 I 979 15
R225 875 I 979 16


Notice the two fathers that is MFx7m ,MFx20.

When I add the group formula I get 124 records. Here is a sample.

ItemCode Quanti TreeTyp DocEntr LineNum grp
MFx7 1 S 979 0 MFx7
R219 2240 I 979 1 CM13
R219 2240 I 979 1 DU13
R219 2240 I 979 1 GR02
R219 2240 I 979 1 MFx7
R219 2240 I 979 1 MU04
R219 2240 I 979 1 R245
R219 2240 I 979 1 SE01
R219 2240 I 979 1 SE02
R223 840 I 979 2 CM13
R223 840 I 979 2 DU13
R223 840 I 979 2 GR02
R223 840 I 979 2 MFx7
R223 840 I 979 2 MU04
R223 840 I 979 2 R245
R223 840 I 979 2 SE01
R223 840 I 979 2 SE02
R226 840 I 979 3 CM13
R226 840 I 979 3 DU13
R226 840 I 979 3 GR02
R226 840 I 979 3 MFx7
R226 840 I 979 3 MU04
R226 840 I 979 3 R245
R226 840 I 979 3 SA01
R226 840 I 979 3 SE01
R226 840 I 979 3 SE02
R224 1400 I 979 4 DU13
R224 1400 I 979 4 GR02
R224 1400 I 979 4 MFx7
R224 1400 I 979 4 MU04
R224 1400 I 979 4 R245
R224 1400 I 979 4 SE01
R224 1400 I 979 4 SE02
R220 840 I 979 5 CM13
R220 840 I 979 5 DU13
R220 840 I 979 5 GR02
R220 840 I 979 5 MFx7
R220 840 I 979 5 MU04
R220 840 I 979 5 PR07
R220 840 I 979 5 R245
R220 840 I 979 5 SE01
R220 840 I 979 5 SE02
R221 840 I 979 6 CM13
R221 840 I 979 6 DU13
R221 840 I 979 6 GR02
R221 840 I 979 6 MFx7
R221 840 I 979 6 MU04

Here is the grp formula

if isnull({ITT1.Code}) then
{INV1.ItemCode} else
{ITT1.Father}

After I group I get the following :


ItemCode Quanti TreeTyp DocEntr LineNum grp

MFx20
MFx20 1 S 979 8 MFx20

MFx7
MFx7 1 S 979 0 MFx7
R219 2240 I 979 1 MFx7
R223 840 I 979 2 MFx7
R226 840 I 979 3 MFx7
R224 1400 I 979 4 MFx7
R220 840 I 979 5 MFx7
R221 840 I 979 6 MFx7
R225 2240 I 979 7 MFx7
R219 875 I 979 9 MFx7
R223 875 I 979 10 MFx7
R226 875 I 979 11 MFx7
R224 875 I 979 12 MFx7
R219 875 I 979 13 MFx7
R220 875 I 979 14 MFx7
R221 875 I 979 15 MFx7
R225 875 I 979 16 MFx7

Notice that for MFx20 all the child items are lost.

 

[lbass]

Well, the problem is that the child items can apparently have more than one father--so the question again is what determines which line item goes with which father. There must be another field that distinguishes this.

-LB

 

[macktruck]

There really isn't a field that determines the Father of the child. If we were to work only with the one table : INV1, would it be possible ?

 

[macktruck]

From what I see in this, I am trying to group up data with no direct relationship. I guess we would have to create it. In crystal this could be possible though I have never tried it. Have you ?