Want Running / Grand Total excluding supressed values

[Former Member]

Dear All,

I have made a report in Crystal Report where there is 1 Group which contains database fields as Document No, Document Date, Customer Name and Sales Order Document Total and in the detail section I have database fields as Row Status of Sales Order Line Items as Open or Closed.

Now I have put a formula in the Group database fields that if any row status which is in the detail section has 'C' means closed then it should supress it. The formula is - if {Command.LineStatus} = 'C' THEN TRUE ELSE FALSE. This is supressing and I am having the report as per the desired requirement. Now I want to have a Total of the report which should show me the rand Total of Only what is shown in the report but the problem is that if I am putting the running total then in the grand total the Document Total which are supressed also are adding up. How to resolve it.

Means I only want a running or grand total of what data is been fetched and not the supressed values.

Please help and advise.

Regards,

Swamy

[Former Member · Accepted solution]

In running total evaluate select formula and add this

<{Command.LineStatus} <> 'C'>

Or use a formula

<If {Command.LineStatus} <> 'C' then 1 else 0>

Then do a a crystal sum summary on this formula.

Ian

[Former Member · Accepted solution]

Dear IanWaterman,

I tried both your options but its giving a wrong total. Kindly advise if you need any further clarifications

Regards,

Swamy

[Former Member · Accepted solution]

sorry tis formula

<>If {Command.LineStatus} <> 'C' then 1 else 0<>

Is for a count, if you want a total replace 1 with your valuefield.

Run the report without your suppression and place these formula or RT in details and see where it is breaking down.

Both options should work provided you are not duplicating data.

Ian

[Former Member · Accepted solution]

Dear

No there seems to be still the problem.

I once again try to explain my report. I am trying to make a Sales Order which are open. Open for me means for Sales Order whose no Delivery or A/R Invoice is made. Meams all lines items are open. To check this I made a Group and placed the document numer, Document Total in the Group Header and used the supress formula in this field as in format > supress as if {Command.LineStatus} = 'C' THEN TRUE ELSE FALSE.

The field Command.LineStatus is in the detail section. By the above formula I am able to bring only the sales order whose target document is not made for any single line or in other words the sales order is open.

The SQL query is :

SELECT T0.[DocNum], T0.[DocStatus], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal], T2.[LineNum], T2.[TargetType], T2.[TrgetEntry], T2.[LineStatus] FROM ORDR T0  INNER JOIN NNM1 T1 ON T0.Series = T1.Series INNER JOIN RDR1 T2 ON T0.DocEntry = T2.DocEntry WHERE T1.[SeriesName] = 'KSAPRJ' and  T0.[CANCELED] = 'N' AND    T0.[DocStatus] = 'O'

As per your advise I made a new formula field 'Total' and used If {Command.LineStatus} <> 'C' then {Command.DocTotal} else 0

and then placed it in the same Group Header beside Document Total and then run the report then its showing corectly the values but when I sum up the 'Total' field the Adding is wrong.

Can you advise is my report making process wrong or any advise.

Regards,

Swamy

[Former Member · Accepted solution]

Do you have some additional suppression?

If yes you will need to add that condition to your Total formula too.

Ian

[Former Member · Accepted solution]

you need to create 4 formulas

reset

whileprintingrecords;

numbervar x:=0;

calc

whileprintingrecords;

numbervar x:= x+formula;

display

whileprintingrecords;

numbevar x;

x

reset goes in group header

calc goes next to the value being calc

display goes in footer

if you use a formula to count the records based upon a condition it should calc correctly