Remove Items from Grand Total Calculation

[AntonioPerera]

Hi ,

I am trying to get the grand totals of a report but
the grand total is incorrect because some items are not being supressed.

The report format is as follows :

Group Header 1 : Vendor
Group Header 2 : PO Number
Group Header 3 : Item Type

Item Type : Amount : Received : Total fot Item type 1 : total for item type 2

The selection criteria is as follows :

1.Item types are only 1 and 2 (out of 10)
2.Sum of received should not be greater than 0
(Sorted by group header 1)

My problem is that the items which I want to supress on
the report is not being shown on the report but they form part of the grand total calculation for Item types 1 nd 2

How can I completely remove these from the grand total calculation ?

 

[dgillz]

The problem here is that you suppressed the records. Suppressed records, as you have discovered, are still in the report, they just do not display, but they do evaluate in any sub or grand totaling you do.

The solution is to exclude these records from the report entirely with a record selection formula. Take you suppression formula and copy it to a record selection formula and this problem is solved.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[lbass]

If you have used the record selection criteria to eliminate as many of the undesired records as possible and you still need to remove more because of duplicate values, for instance, then insert a running total and choose "use a formula" in the evaluation section in order to remove unwanted records. In the formula, use the opposite of the criteria you are using for suppression.

-LB

 

[AntonioPerera]

Hi Lbass

I cannot copy the group selection formulas to the record selection area as it gives a message that the records have to be read later.

Please advise how I can do it.

 

[dgillz]

Antonio,

Please post both your record and group selection formulas.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Madawc]

I think what you need is a running total that adds for the entire report, but uses a formula to decide whether to add. It sounds like you have been using summary totals, which are quick and convenient but which work on the data rather than the things you want to print.

Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

Variables are user-defined fields. One useful variant are shared variables to pass data from a subreport back to the main report. You can also use variables to show page totals. For normal counting I find running totals or summary totals much easier.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Please note that if you are using group selection (which is not the same as suppression), you need to use running totals. If you are not also using suppression, you can insert a running total, select the field and summary, evaluate for each record, reset never. If you want to show subtotals for each item group in the report footer, then create separate running totals for each, and in the evaluation section, use a formula like:

{table.itemtype} = 1 //or 2

-LB

 

[AntonioPerera]

Hi dillgz,

My record and group selection formulas are as follows :

Record :

{POPORH1.RCPDATE} <= 20051231.00 and
{POPORH1.DATE} <= 20051231.00 and
{POPORH1.VDCODE} in "20100001" to "20100011" and
{POPORL.ITEMNO} in ["EMP-300", "TRN-300"]


Group :

Sum ({POPORL.OQRECEIVED}, {POPORH1.VDCODE}) <> 0.00

 

[lbass]

You must use running totals, as noted earlier. To get grand totals for each item type, insert a running total where you do a summary of whatever field you want to summarize, evaluate using a formula->x+2 button:

{POPORL.ITEMNO} = "EMP-300" //"TRN-300" in a second running total

Reset never. Place the running totals in the report footer.

-LB