Optimización de consultas MDX en Reporting services
3 julio, 2012
Realizar pruebas para mejorar la experiencia de usuario
30 julio, 2012

Hi everyone,

I not going to make any philosophic reflexions, neither make a thought about the BI business problems and the user’s point of view. What I show you this time, it’s a terrible issue that unfortunately, some people who works with Reporting Services maybe has encountered with.

I posted a reference to what I found in the Internet in MSDN forums too. Just because it was freaking out our minds in a recently Project, I gave it more importance that it seems out there; however people affected are worried about and still needs a solution.

The problem is an exception executing some reports deployed in Sharepoint Reporting Services Site. Some people post their cases in the next links showing that the issues were from a heterogeneous origin, and not deploying to Sharepoint only:

http://www.bidn.com/blogs/DustinRyan/ssis/125/ssrs-2008-index-was-out-of-range-error

http://www.developmentnow.com/g/115_2005_6_0_0_552665/Index-was-out-of-range–depending-on-parameters-.htm

http://www.sql-server-performance.com/forum/threads/index-was-out-of-range-must-be-non-negative-and-less-than-the-size-of-the-collection-parameter-name-index.28619/

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/973e3546-3f52-4d1e-b370-6af79a4c3c53/

The message we received was:

An error occurred during client rendering.

Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index

After reading those posts briefly I wrote the next summary taking the problem’s proposed origins:

– Occurs when the size of report exceeds page size.

– When putting images, especially in the header and/or footer.

– Embedding report item inside other one.

– Document maps may cause this exception exporting to pdf or excel.

– Sometimes occurs when exporting to output formats other than html.

 Generally, headers and footer may cause this exception too.

And here is my case. We had 37 reports, each one contained from 2 to 5 data grids (i.e. Matrix control) with the same structure and behavior. They showed data grouped in rows by datetime and in columns one dimension the client choose to see as a information slice. For example a measure (whatever you like,-number of logins, sum of downloads maded, etc.-) by Account users in time:

So someone could said -something smells bad in your code, dude-. Then, After trying and trying again with multiple deployments on Sharepoint and various changes in report designer, we found that one of the cells had a weird behavior different from the others. That was the proof. An image can show this:

When I click the expand icon (-Why product engineers didn’t see that I can collapse rows with a ‘plus’ icon?-) Rows from matrix showed total result by year intersecting with Account type. That was only one row for 2007 year; normally it’s what every people expects! But that was not the case. It showed the three total rows (year, month and day) with the information from Total year cell. Something curious was a warning message deploying to local Sharepoint too:

Warning : ‘Calendar_Year’ text box object extends further of the bottom border of its container.

We reached this conclusion after remaking the report putting each report item increasingly as I was deploying step by step. The warning appears in the final steps when I had only one matrix in the report. So we decided to track this clue. I deleted rows, columns, and groups. Finally, I resolved the issue remaking the matrix from scratch. What it seemed more unusual was that it didn’t fail in every machine we deployed the report. Index out of bounds exception only on one of them, duplicity  of total rows in every one.

Conclusion: There’s some corruption code originated somehow by Visual Studio or deploying system that inserts the rdl in the Report Server database. We don`t need to give the responsibility to Sharepoint (which others usually do all along). The ball is in Reporting Services roof. So, this is our advice and our recommendation to those who face this issue. Dissecting the report, his elements or items, and remaking that who seems to fail when it’s deployed. Another approach will be reading rdl code, looking for incongruences and white spaces that don’t seem to be in the right place. For these to work, you must be an rdl code expert reader. That’s why I suggest to redo it again in ReportDesigner, if possible.

Sometimes in life, our programmer’s life, our programs get out scheduled task to getting in a unprogrammed world of wonderland. As it said in soccer’s world, computer science, that is it.

Compártelo: Share on FacebookTweet about this on TwitterShare on LinkedInPin on Pinterest

Comments are closed.

NEWSLETTER