cancel
Showing results for 
Search instead for 
Did you mean: 

Subtract a value from a Summary Field

Former Member
0 Kudos

Hello All,

I have built a Crystal report that has an embedded summary for a Sum of a specific field on the report. Now, I need to subtract 0.5 from this embedded summary field.

Is there any way this can be done on my report?

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor

Hi Nimish,

If this sum is in the report footer (grand total) then create a formula with this code:

Sum({field to sum}) - 0.5

Drag and drop this formula on the report footer.

It this needs to be on the group footer, then the formula would be:

Sum({field to sum}, {group field}) - 0.5

Replace {group field} with the field used to group the report.

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

This field is on the "Report header b" of my report within a crosstab. The report only shows Report Header a and Report Header b and the rest are either suppressed or hidden on the report.

When I edit the Summary for this calculation field on the report it shows the following:

Choose the field to summarize: Command.Hours

Calculate this summary: Sum

I tried both your steps above to create a new formula but the first formula gives me an answer of 58019.07 instead of 8.0 (please note that the value of Command.Hours = 8.5 and I need to take off 0.5 out of this calculation)

I used the second formula as Sum({Command.Hours}, {Command.Employee_Name}) - 0.5 but this gave me an answer of 975.59 instead of 8.0

Any thoughts on what i may be doing wrong?

abhilash_kumar
Active Contributor
0 Kudos

The solution I proposed above will not work with a Crosstab!

Here are two workarounds:

1) Right-click the summary field's cell > format field > common tab > click the formula button beside 'Display string' and use this code:

Totext((currentfieldvalue - 0.5))

2) Right-click one of the summary cells > Embedded Summary > Insert Embedded Summary.

Right-click the text 'Edit this formula' > Summarized field labels > Summarize horizontally. This should place the two fields side-by-side.

Right-click the same text again > Embedded Summary > Edit Calculation formula > use this code:

Gridvalueat(CurrentRowIndex, CurrentColumnIndex, 0) - 0.5

You should now have two summary cells - the first one that displays the original value and the second with the lower value.

Right-click one of the cells of the original summary field > format field > common tab > check 'Suppress'.

Highlight the same cell > Reduce its width.

The first workaround converts the field to string whereas the second workaround keeps the format intact.

-Abhilash

Former Member
0 Kudos

Thank you, The first option worked well for me.

Former Member
0 Kudos

Hi Abhilash,

I'm using your first method to solve this problem by creating a formula totext((currentfieldvalue - 0.5))

However, I have some data on the report that displays -0.5 when the value is 0.

How can I avoid this situation and display only 0 by not subtracting 0.5 from it.

I tried the following code:

If (currentfieldvalue - 0.5) < 0 Then 0 Else Totext((currentfieldvalue - 0.5))

but this gives me an error that states a number is required here.

abhilash_kumar
Active Contributor
0 Kudos

Modify the code to:

If (currentfieldvalue - 0.5) < 0 Then '0' Else Totext((currentfieldvalue - 0.5))


-Abhilash

Former Member
0 Kudos

Thanks!

Answers (0)