Custom Subtotals

[Former Member]

Hi there

I am new to Crystal Reports and am struggling on my first report, so any help would be much appreciated.

I have created a report with grouping levels. I have got my subtotals in for each group.

I would however like to subtotal a field based on a condition. I would like to split my total hours up into "working hrs" and "non-working hrs" and do this for each area - which I have already grouped by. the sql is

SELECT SUM(Hrs)

FROM PROJECT_LEDGER

WHERE (A01 = N'basic rate') OR

(A01 = N'Bank Hol')

I would like this sql statement though to apply to each group rather than all the records in the database.

I don't know whether I need a formula, or an sql statement etc.

Many thanks

Amanda

[Former Member · Accepted solution]

Amanda,

The other two comments look good to me. I just wanted to clarify that I woudln't do the work in the SQL statement. I would use the SQL statement only for selecting the records I wanted and not for formatting. Crystal provides tools for formatting groups and creating totals.

You can create groups under report/group expert. From there you should be able to use the running totals and/or formulas to get the subtotals that you need.

Good luck!

[Former Member]

Hi,

Just write a Running total formula for your Particular field lets say it is 'Amount'.

Field to summarize is 'Amount'

Type of summary is 'Sum'

Under Evaluate Section

Go to Use Formula---> write formula like this.

if (Field= 'Working hours')

-- Here you can write any condition

Under Reset Section

select on change of group and select the group you are going to do subtotal.

Click OK.

Thats all.

Thanks,

Vijay

[Former Member]

Hi Amanda,

Try Using formulas or Running Totals and place them in Group Footer

Cheers

Rahul