SUM still includes suppressed values

Question

I've got a report that includes some limited duplicate information, there are multiple dates possible, but I want the dollar values to display on only the first item.

IE:

Item 1 - Aug 3, 2010 - Cost $1000
Item 1 - Aug 4, 2010 - (Cost suppressed)
Item 2 - Aug 3, 2010 - $100
Item 3 - Aug 4, 2010 - $200

When this is summed, it should display as $1,300, but instead it displays as $2,300.

I have suppressed the cost section by using a formula:

(
if (Previous({MyReportData;1.MyItemId}) = {MyReportData;1.MyItemId}) then
    true
else
    false
)

This formula works fine.

I have tried to create a formula to perform a custom SUM which will exclude the duplicated items, but it keeps saying "This field cannot be summarized.

Any ideas?

Answer 1

You have two choices

1. Modify your SQL to only bring in the data you want. You obviously dont want the row for August 4th 2010. So do not bring it into the report. Then your Summary will work as expected.

2. Create a running total and evaluate on change of row using the opposite of the suppression formula.

Answer 2

Another option is to just use global variables to keep track of your sum using the inverse condition you are using to suppress. This should be better, performance-wise, than using Crystal's Running Totals or Summary fields.

Although, I would assume that using a SQL Expression to simply select the rows with the earliest dates by ItemID would be even quicker and would avoid Crystal having to chug through the data.