Referencing Parameters in SSRS Calculated Members
Issue: You author an SSRS report that connects to a cube. You want to create a calculated member that uses a report parameter. For example, the Selector calculated member in the following query returns 1 if a given employee has exceeded 1 million in sales for the dates specified by the user in the report parameter.
WITH MEMBER [Measures].[Selector] AS SUM (
StrToSet(@DateCalendar), iif([Measures].[Reseller Sales Amount]>1000000, 1, NULL)
)
select non empty {[Measures].[Reseller Sales Amount],[Measures].[Reseller Order Quantity], selector} on 0,
non empty (Filter([Employee].[Employee].[Employee].Members, [Measures].[Selector]>0) * [Date].[Calendar].[Calendar Year]) on 1
from (select StrToSet(@DateCalendar) on 0 from [Adventure Works])
However, when you run the query you get the following error if you target Analysis Services 2005:
The DateCalendar parameter cannot be resolved because it was referenced in an inner subexpression.
Obviously, the built-in SSAS data provider has an issue parsing parameters in a calculated member. This issue is apparently fixed in SQL Server 2008 where the above query executes just fine.
Workaround: As a workaround with SQL Server 2005, try defining a set that references the parameter and use the set in the calculated members, as follows:
WITH SET [SchoolYears] AS STRTOSET(@DateCalendar)
MEMBER [Measures].[Selector] AS SUM (
[SchoolYears], iif([Measures].[Reseller Sales Amount]>1000000, 1, NULL)
)
select non empty {[Measures].[Reseller Sales Amount],[Measures].[Reseller Order Quantity], selector} on 0,
non empty (Filter([Employee].[Employee].[Employee].Members, [Measures].[Selector]>0) * [Date].[Calendar].[Calendar Year]) on 1
from (select StrToSet(@DateCalendar) on 0 from [Adventure Works])
