Working with SSRS – Sql Server Reporting Services

So I started my new job doing .NET Consulting work using the 3.5 framework last week unfortunately the start of my job has mostly been limited to updating stored procedures in sql server (thank god I’m done with you oracle) and the reports in sql server reporting services (SSRS) that use them. SSRS sure seems to have alot of quirks and some unusual functionality.

Through out my upcoming days I’ll be posting any minor tips or ins and out that I stumble upon to have them tied together in 1 post both for my reference and hopefully the benefit of others.

Tip #1 -  Inserting text or a Line Break into a text box on the report with data fields

=Fields!LastName.Value & “, ” & Fields!FirstName.Value & vbCrLf & Fields!MiddleName.Value

Tip #2 – Convert a DateTime field to only display the Date portion

=CDate(Fields!BirthDate.Value).ToShortDateString()

Tip #3 – Working with bit flags and IIf statement proper comparison, note case after the equality

iif(Parameters!IsBitFlagSetForSomething.Value = True, “Bit Flag is set”, “Bit Flag is not set”)

Tip #4 Alternating Row background colors, set the background property to:

=iif(RowNumber(Nothing) Mod 2, “LightGrey”,”white”)

More to come hopefully

BloggingContext.ApplicationInstance.CompleteRequest();

An error occured during local report processing

My first biggest headache with sql server reporting services was dealing with this report set that had toggle buttons that could collapse and unfold portions of the report along with a navigation tree built into the report. Both of these features I wasn’t even aware existed until just recently, I’ve found out since then that the toggled reports are known as “drill down reports” and the navigation adeptly named “document navigation”

I was attempting to update a report using these features however anytime I would click a toggle button or try to navigate a link or use the page change buttons I would be presented with this most horrific and meaningless error message ever

An error occurred during local report processing. An internal error occurred on the report server. see the error log for more details.

Great, so an error occured go look at the logs, eventually I figure out that the logs it is referring to are stored in drive:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles for Sql Server 2005. In the log files I found 1 error message relating to an invalid in line sql data set for a subreport which I fix figuring that would be it. I build the reports again goto preview and am presented with the exact same error again.

After a great long time searching trying to find cause I eventually found an article that pointed me to this Microsoft article (I’m not sure why I didn’t find this in Google originally usually they’re at the top of the list).
http://support.microsoft.com/kb/912705

FIX: Error message when you try to drill through a report in SQL Server 2005 Reporting Services: “An internal error occurred on the report server. See the error log for more details”

Which the solution was simple, update sql server 2005 to service pack 2 and all would be all, and so it was said and so it was done and so it was well.

BloggingContext.ApplicationInstance.CompleteRequest();