SSRS: Export to excel issue. Index was out of range. Must be non-negative and less than size of the collection.

Question

I have a report that runs a sub report.  This sub report can hide or show certain columns.  The issue that I am having is that when I run or preview or export to PDF then everything is OK.  When I export to Excel then I get the following error:

An error occurred during local report processing.
An error occurred during rendering of the report.
An error occurred during rendering of the report.
Index was out of range.  Must be non-negative and less than size of the collection.
Parameter name: index


I am running: Windows 2003 Server SP2, SQL Server 2005 SP2

I have checked the forums and tried all of the solutions with no resolution.  I made sure the sub report is smaller than the space reserved for it.  I made sure nothing in the RDLs had negative widths/heights.  I removed/added headers/footers.  My last ditch effort is to start a blank report and try to copy the objects over to it. (Updated: didn't work)

Are there any solutions to this issue?  Are there any hot fixes?  I read that there was a bug for this issue and it was resolved in SP1 but I already have that applied.

Thank you in advance for your time.

Answer 1

Have you tried converting all your fields' location, height/width to the PT system?

When a report is rendered in Excel it has to convert inches to PT sometimes the rounding of decimal places are kooky.

My blog has a step by step on what to check.  see if this help you.

---

Rich Samford

Answer 2

Hi,

I got the exact same error.  
Here's what I did to fix it.

The following piece formula was in one of the fields returning a percentage.
=(ReportItems!ACTUAL_GROSS_MARGIN_1.value/IIF(ReportItems!ACTUAL_REVENUE.Value=0,1,ReportItems!ACTUAL_REVENUE.Value))

The error was fixed when I removed the first and last bracket.   When it said "collection" I immediately thought of the ReportItems Collection.  For some reason bracketing these two report items together confused VS and it it considered the index out of range.  If you have a piece of code that does a calculation like this with leading and end brackets try removing the brackets.

Like so:
=ReportItems!ACTUAL_GROSS_MARGIN_1.value/IIF(ReportItems!ACTUAL_REVENUE.Value=0,1,ReportItems!ACTUAL_REVENUE.Value)

Anyways, that's what fixed it for me.

Thanks,

Answer 3

Hello everyone,
I got the same bug :

An error occurred during local report processing.
An error occurred during rendering of the report.
An error occurred during rendering of the report.
Index was out of range.  Must be non-negative and less than size of the collection.
Parameter name: index

And I fixed it the same way that Dan_V did. There was a text box with the following code for the value:

=(

"Season: " & Year(Today) & "-" & (Year(Today)+1))

And this code cause the error. Since I remove the brackets like this:

="Season: " & Year(Today) & "-" & (Year(Today)+1)

Everything works well! So watch out any useless brackets.

----- FRENCH VERSION / VERSION FRANCAISE ----
Bonjour à tous,
j'ai eu la même erreur que Mecasetta en essayant d'exporter un rapport en format excel.

An error occurred during local report processing.
An error occurred during rendering of the report.
An error occurred during rendering of the report.
Index was out of range.  Must be non-negative and less than size of the collection.
Parameter name: index

Finalement, le code suivant était en erreur
=(

"Season: " & Year(Today) & "-" & (Year(Today)+1))

J'ai donc enlevé les parentheses inutiles (voir ci-bas) et tout est réglé!

="Season: " & Year(Today) & "-" & (Year(Today)+1)

Alex!

Answer 4

I was getting the same error I found I had a tablix on top of a list and the list had keep together on one page if possible checked. I unchecked it and the error went away.

Answer 5

Thank you Dan_V,

Your suggestion works fine for me too, in SSRS 2008 R2! Cheers!

Answer 6

DAN_V's answer worked for me too

Answer 7

I also had a similar error. 

My report had been running for quite some time, I made a big change to it and then received the error.

I tried looking through the answers above. But then I just deleted my .data file and it work.