ApplyBI

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

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.