Issues in SSRS Reports when SQL Server is updated from 2005 to 2008/2008 R2

I hate when up-gradation of software removes or changes existing features. They may provide new features that help us to achieve the same result as before but it’s still a re-work. Anyway, while up-gradation of SQL Server 2005 to 2008/2008 R2, I came across a similar situation.

I have faced following 2 issues due to this up-gradation:

  1. StrToMember function behavior changes
  2. Conditional coloring of data values stopped working (solved in 2008 R2)

StrToMember function Issue

There is a common parameter called ‘@Grouping’ in most of our reports which has a number of dimension hierarchies as options along with an option ‘All’.  We select the hierarchy to be included in a cross joins across rows.

Following MDX query was included in most of our reports that stopped working after up-gradation.

Note: - Here I have replaced input parameter ‘@Grouping’ with the selected option ‘[Product].[Category]‘.

WITH Set GroupingSet1 as 
GENERATE([Customer].[Customer Geography].[Country].Members,
CROSSJOIN([Customer].[Customer Geography].CurrentMember, 
	StrToMember("[Product].[Category]" + '.CurrentMember')))

Member [Measures].[Category] as Case "[Product].[Category]"
When "All"
Then "All"
Else StrToMember("[Product].[Category]" + '.CurrentMember').Member_Caption 
End

 

The above query doesn’t work as desired (as in sql 2005 it used to work) and gives the result as ‘All’ as shown in the figure below.

StrToMember MDX function Issue

StrToMember MDX function Issue

So I had to change the query as follows which worked like a charm:

WITH Set GroupingSet1 as
GENERATE([Customer].[Customer Geography].[Country].Members, 
CROSSJOIN([Customer].[Customer Geography].CurrentMember, 
              StrToSet("[Product].[Category]" + '.CurrentMember')))

Member [Measures].[Category] as
Case "[Product].[Category]"
When "All"
Then "All"
Else StrToValue("[Product].[Category]" + '.CurrentMember.Member_Caption')
End

 

Conditional coloring of data values stopped working (In SQL Server Reporting Services 2008)

We always needed to show negative values in red color, don’t you?  Well that stopped working after up-gradation from SSRS 2005 to 2008. But after we up-graded to 2008 R2, that problem was solved.

5 Responses to “Issues in SSRS Reports when SQL Server is updated from 2005 to 2008/2008 R2”

  1. Sasha says:

    Is the demo version available ?

  2. Test says:

    Great blog post thanks for posting

  3. Oleg says:

    Unique and great post. Useful for everyone who upgrades from 2005 to 2008 R2 on BI side of course.

  4. Claris says:

    Good article on SSRS upgradation. I’ll remember this. Thanks

  5. ilahi says:

    This article is really good and new.

Leave a Reply