"Bassist695" wrote:

> Smit -
>
> In the SQL for your report, do you have an ISNULL function around your
> financial amounts? Could it be that you're adding NULLs?
>
> One more observation after looking at your spreadsheet. This "bug" is
> affecting the same companies (8-11) almost every time, and down in the
> "Special" section, it is obvious that this issue does not arise every time
> you have zero-amounts.
>
> You'll also notice that if you sum all purple values in the "Excel Sum()"
> column for each section of the report, the value = the repeated value in the
> "SSRS SUM() column. It appears that RS is rolling up those companies to the
> same level. I would recommend checking your SQL again, and do not think that
> this is a Reporting Services bug by any means.
>
>
>
>
> "Smit-Dog" wrote:
>
> > It appears that I have come across a very weird but bad bug in the SUM()
> > function in the Report Designer.
> >
> > I have a series of financial numbers, broken down into various expense
> > categories, in a rolling 12 month format. For each month, a value is
> > retrieved from the dataset and displayed. At the end of the 12 month series,
> > I'm using a SUM() function to display a grand total for all values in the 12
> > month series.
> >
> > Here is the formula used:
> >
> > =sum(Fields!Sales_M01.Value + Fields!Sales_M02.Value +
> > Fields!Sales_M03.Value + Fields!Sales_M04.Value + Fields!Sales_M05.Value +
> > Fields!Sales_M06.Value + Fields!Sales_M07.Value + Fields!Sales_M08.Value +
> > Fields!Sales_M09.Value + Fields!Sales_M10.Value + Fields!Sales_M11.Value +
> > Fields!Sales_M12.Value)
> >
> > The problem is that the SUM() incorrectly sums the series if a zero (0)
> > value is encountered for any particular month. It continues to incorrectly
> > sum the series until it hits a record where there are not any zeros. What it
> > appears to do is sum the 12 month series for any contiguous grouping of
> > records where there is a zero value for a month, and uses that aggregation
> > for the SUM() total for each of the records in the grouping.
> >
> > If a particular month does not have any number to report, the field in the
> > database contains a 0 value, so nulls are not a factor here.
> >
> > To clearly show how the SUM() function is screwing up, I've provided a link
> > to an Excel file that was created by exporting the report. I've changed the
> > company names for obvious reasons. Check it out here:
> >
> > http://home.comcast.net/~wcsmith/SSRS_Sum_Error.xls
> >
> > I have a total column that was calculated by SSRS in the Report Designer
> > with the SUM() function. Next to that I have a column using the Excel SUM()
> > function to compare the two methods, and find the errors.
> >
> > The rows shaded in purple is where the SSRS SUM() function has screwed up.
> > If you select all the values for all the months in the contiguous range, i.e.
> > the entire purple sub-section, you will see that this is the total that the
> > SSRS SUM() function has come up with - for every record in the contiguous
> > range where a $0 was encountered. Very, very weird, and very bad when
> > reporting on financial calculations.
> >
> > I don't think I'm doing anything wrong with the SUM() function. It appears
> > that when $0 are encountered, SSRS just keeps adding not only for the current
> > record, but all subsequent records until it finds one where a zero does not
> > exist in any of the monthly values. This is the only thing I can surmise from
> > looking at the results.
> >
> > I am open to suggestions to workaround this issue. Should I be summing the
> > text boxes instead of the underlying values from the dataset? Should I
> > include the summed totals in the stored procedure that supplies the dataset,
> > and reference these directly?
> >
> > Thanks!
> >
> > - Bill
> >