6 Replies Latest reply: Jul 12, 2010 5:10 PM by sharon towler RSS

Want Running / Grand Total excluding supressed values

Kawish Junaid Mazhari
Currently Being Moderated

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

  • Re: Want Running / Grand Total excluding supressed values
    Ian Waterman
    Currently Being Moderated

    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

    • Re: Want Running / Grand Total excluding supressed values
      Kawish Junaid Mazhari
      Currently Being Moderated

      Dear IanWaterman,

       

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

       

      Regards,

      Swamy

      • Re: Want Running / Grand Total excluding supressed values
        Ian Waterman
        Currently Being Moderated

        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

        • Re: Want Running / Grand Total excluding supressed values
          Kawish Junaid Mazhari
          Currently Being Moderated

          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

Actions