30 Mar 2009

Reporting Services 2008 Report automatic upgrade gotcha.

For the Scrum for Team System project I have been focusing on the reports; I came across an issue which was due to the way the Reporting Services 2008 "automatic upgrade" feature upgraded our reports.

The Scrum for Team System template contains over 20 reports all written for Reporting Services 2005. This was done because Team Foundation Server 2008 only worked on SQL Server 2005 and Reporting Services 2005. Then in August of 2008 Team Foundation Server 2008 Services Pack 1 was released which added support for the Team Foundation Server 2008 is to run on SQL Server 2008 platform.

With Reporting Services 2008 there is a new reporting definition language schema. However reporting services does have backward compatibility method as describe below:

The report server will validate report definition file against the included reference to the RDL namespace which specifies the version of the report definition schema that is used.

The report is automatically upgraded the first time it is viewed, but the stored report definition file remains unchanged.

So if you edit the report from the server it will still remain in old schema and not the new schema.

To get automatically upgraded report definition you will open the report in one of the authoring tools: Business Intelligence Development Studio (BIDS) or Microsoft Report Builder 2.0.

So I did some testing to see how the 2005 reports would look after the reporting services 2008 processing. I found that 2 reports didn’t render the same as did on reporting services 2005. The 2 reports had a common layout they were using a reporting services sleazy hack: The Green-Bar Matrix. The cell which controls the background colour was a lot more visible than the original as well as the text. So I rewrote the reports and were released them with version 2.2.

Then one of our template users reported that one of the other reports stopped working. The report started to give the following error:

The processing of Parent for the tablix ‘table1’ cannot be performed. The comparison failed. Please check the data type returned by the Parent.

I was able to trace the error to an option in one of the report define groups: Recursive parent, which can found under advanced; removing the option made the report worked again. I haven’t been able trace why the error happens it only seems when our data came back in a certain way which I am currently looking into.

Some practical SQL Spatial tips.

I have just finished a project were I made a lot of use of the SQL Spatial to do some processing and loading into the database. Here are some of lesson I learnt:

  • The first method that needs to call after instantiating a SQLGeographybuilder object is: SetSrid() then the BeginGeography(), BeginPoint().
  • Before using sending a SQLGeometry object to the SQL Server use the IsVaild() function to ensure that Geometry object is valid.

    I create a console application which loaded line, which was using OS coordinates system, data from a flat file. Some of the line data within the file wasn’t as correct as I was led to believe. The application create the SQLGeometry object .NET but the Sql Server then rasied the error when it received and try to save it into the table.

  • The STPointN() function is 1 base which is stated in the Books On-Line
  • If your query needs to select some of the derive data from the functions you can help performance by using persisted compute columns on the base table.

    I had to create a view which needed the latitude and longitude of the starting and end points of the geom line also including the distance. The view would take about 20 seconds to finish executing. Once I created some persisted compute columns on the base table the view would finish executing under 3 seconds

  • You can create more than one spatial index for a spatial column.
  • Trying to join tables using the spatial functions isn’t great for performance.

1 Mar 2009

Working With Reporting Services Multiple Value Parameters

A couple of colleagues of mine were working on a complicated reporting services project; they had one report which had a parameter which allowed a user to select multiple values of numbers, the problem they were facing was how pass the selected values into the query.

They were looking for a way within SQL Server to split a string by a delimiter. As they were using a stored procedure to access the data and multiple value parameters, regardless of parameter type, are passed in as a string of comma separated values.

One thing which they weren’t aware of was that with a multiple value parameter, the behaviour can differ between how the data is being retrieved i.e.: stored procedure or embed sql statement.
If the data set is using direct query to gather the data then the multi value parameter can be used in the query with a IN clause like so:

Where column1 in (@pMultiValueParam)

What happens is that before the query is sent to the SQL Server the query the report server substitutes the @pMulitValueParam with a comma separated list which makes it valid in clause like so:

Where column1 in (10, 3, 4, 56)

Also it has been blogged (http://www.socha.com/blogs/john/2009/03/tfs-report-issues-with-sql-server-2008.html) that in reporting services 2008 there is a new change in the behaviour. You could see the following message when you have no values to select:

Incorrect syntax near ')'.

When there are no values to pass, Reporting Services 2008 simply removes @pMultiValueParam, so you get something like "Where column1 in ()" preventing this query from running.

The solution is to add an expression to the pMultiValueParam report parameter. The expression is evaluated in order to determine what is passed to the query.

Like so:

=IIF(Parameters! pMultiValueParam.Count > 0, Parameters!pMultiValueParam.Value, "")