crystal reports - formula field conditional statement on what records to sum

4

votes

crystal reports formula field Good day, I am making a sales report on Crystal Report - VS2010 c#

I have this view in MS SQL

vSales

OrderNo

OrderDate

Amount

PaymentType

Payment type can either be Cash or Check I performed select all because I need both in the same report

so how do I get the sum of Amount where PaymentType is equal to cash?

I used Sum for my total Amount, both check and cash, in the formula workshop

Sum ({vDailySales.Amount})

I can't modify my SELECT statement because I need all the record with cash & check payment

c#crystal-reports

4

votes

Create two formulas - CashAmount and CheckAmount, for example second one:

if {vDailySales.PaymentType}="cheque" 
then {vDailySales.Amount}
else 0

On report, use aggregates of said formulas - like Sum({@CashAmount}).

3

votes

There are two ways I would use; depending on what output I wanted.

The first way, and the simplest would be group on the payment type. Then create a sum in the footer of the group. You can use the "Running Total" tool or use the code below.

SUM({vDailySales.Amount}, {vDailySales.PaymentType})

This will show a result for each of the payment types, so in your case twice, once for cash and once for cheque.

I think through here you can use formulas in the "Evaluate" section of running total; where you would use a true/false statement for what you wanted to show. The statement for "cash" for example would be:

if {vDailySales.PaymentType}="Cash" then true else false

The other method, which would be simpler if you wanted a grand total would be to use a conditional sum.

Define the variables in the header:

SHARED numbervar sumcash;
SHARED numbervar sumcheque;
sumcheque:=0;
sumcash:=0;

Then for each line in the report; add a field to conditionally sum the amounts; using a formula like:

SHARED numbervar sumcash;
SHARED numbervar sumcheque;

if {vDailySales.PaymentType}="cheque" then
     sumcheque = sumcheque + {vDailySales.Amount};
else
     sumcash = sumcash + {vDailySales.Amount};

Then at the bottom of the report, make a formula to display each of the variables seperately.

2

votes

A third option is to use a running-total field. Because of how they are calculated, they need to be in a footer section.

To create one, follow these steps:

select the {vDailySales.Amount} field on the canvas, right click, then select Insert, Running Total...
Set the Running Total Name field as appropriate, perhaps 'Sum of Cash'.
Ensure that the Type of Summary is 'sum'.
Select the 'Use a formula' option, click the conditional-formula button, then enter the following as the formula's text: {vDailySales.PaymentType}="Cash"
Select 'Never' for the Reset option or 'On change of group' (selecting the appropriate group) if this RT field will tally an amount by group.
Place field in the Report Footer section.

Repeat the process for a second running-total field that will summarize cheques (change step 4 to reference "Cheque" instead of "Cash".

crystal reports - formula field conditional statement on what records to sum

3 Answers