Export to Excel - Rare bug with formulas?

Hi,

I'm experiencing a problem in SSRS 2005 with exporting a report to Excel (from the web interface) where it messes up the cells that are referenced in some formulas. This issue seems to be quite rare as it has only happened a few times in several months, but it is a critical issue for me as the report is dealing with financial data and there is no room for mistake.

Basically the report contains a table of financial data, where one column in particular is derived from 3 other columns in the report at run-time. The derived column uses an expression such as:

Code Snippet

=ReportItems!textbox1.Value - ReportItems!textbox2.Value - ReportItems!textbox3.Value - ReportItems!textbox4.Value

This is just in a simple table with no grouping, where there is a header row, one data row, and a footer row.

99% of the time the report runs fine and is accurate. However on the rare occassion when the bug raises its head... Let's say the table in report has 15 columns and 100 rows for a given month. If the bug occurs, it seems to affect 2 rows. When viewing the exported file in Excel, the formula in the derived column in the first buggy row will (incorrectly) try to reference the cells in the Header Row - which is strange because the expression is only referencing other fields in the same row of data, nothing to do with headers or footers - and therefore prints "#VALUE" in the cell because the formula is trying to operate on non-numeric data! And then further down the report, the derived column in the next buggy row will be trying to reference the values in a row that is 4 rows above, instead of the values in it's own row, which again is just puzzling...

I can't confirm that this happens when viewing the report initially from the web interface because the error doesn't get picked up until it is exported to Excel so that the users can work with the data.

Also, just thinking about it, and looking at these buggy rows in the export that I have saved I have noticed;

- The first buggy row is the 4th row of data, and references the header cells 4 rows above it (instead of the cells on the same row)

- The second buggy row is the 5th last row of data, and also references the cells 4 rows above it (instead of the cells on the same row)

Unfortunately I do not have any saved reports where this has happened in the past, so I can't really compare them to see if the same kind of pattern occurs.

And even more confusing is that simply exporting the report again will produce a correct report... However this can only happen after the user realises there is an error and goes back to do the export again, so that might also have something to do with it.

Has anyone else experienced this before, or have any suggestions for a workaround? I guess I could use an additional view to do the work, but I don't want to complicate maintenance on the report(s) that rely on the same data. I'm just baffled as to why this is even happening in the first place.

*Note: I haven't upgraded to SQL 2005 SP2 just yet, but the list of changes don't list a fix for this particular problem...

Thanks in advance for any advice

Bump Anyone have any ideas? Thanks.|||

Hmm, I dont know but I have a suggestion of what to maybe look at if you have not done so already.

Your expression has ReportItems.TextBox1, ReportItems.TextBox2, etc

You might want to check that these items exist and are not duplicated or being created in a different fashion, etc

If creating the reports in report designer or similar then the names for textboxes will get automatically generated so maybe they are getting generated differently for different reports?

Try giving the textboxes that you want to use names that the report designer does not use, like Column1, Column2, etc

Or even better that actual column names that matches your data

See if that has an affect, maybe an automatically created item is taking up that name and so therefore the values are coming from the wrong text box.

Just an idea?

|||

You are referring to textboxes for its values in the same row. I'm sure those textboxes have some expressions, say Fields!Field1.Value. So, why dont you use this expression in place of your report item value? This way, your report will be foolproof.

Shyam

|||

I have had the same problem. The only solution that i have found is to perform the calculation in a function into the code of report.

For example:

Public Function TitleSub(ByVal strValue As String) As String

Dim strRet As String

If Len(strValue) = 0 Then

strRet = "Employee"

ElseIf strValue = "Owner" Then

strRet = "President"

Else

strRet = strValue

End If

Return strRet

End Function

In the "Expression" invoke the function:

=Code.TitleSub(Fields!Title.Value)