Link to home
Start Free TrialLog in
Avatar of gosi75
gosi75Flag for Iceland

asked on

Reporting Server generated parametrized query fails with Unknown member

I'm having this kind of problem with my query:

http://connect.microsoft.com/SQLServer/feedback/details/127234/reporting-server-generated-parametrized-query-fails-with-unknown-member#

I haven't been able to find any solution for this on the web, any hints much appreciated.

I need to be able to select in my filter a combination of all normal values and the Unknownmember as the cube I'm working on has many values registered on the unknownmember.

Any workaround possible?


Here is my main query
WITH 
MEMBER [Measures].[YTD Sales Invoice Transaction Value] AS SUM(YTD([Date].[Year - Month - Date].CurrentMember), [Measures].[Sales Invoice Transaction Value])


SELECT NON EMPTY
{
[Measures].[Sales Invoice Transaction Value],
[Measures].[YTD Sales Invoice Transaction Value]
} on columns,
non empty
{
crossjoin(
( Descendants( [Logistics Trade Agreement Expanded].[Category - Trade Agreement].children )),
[Customer].[Customer].children 
)
}
on rows
FROM
( SELECT ( STRTOSET(@DateYearMonthDate) ) ON COLUMNS FROM
( SELECT ( STRTOSET(@CustomerDepartment) ) ON COLUMNS 

FROM [Cube]))

WHERE 
(
IIF( STRTOSET(@DateYearMonthDate).Count = 1, STRTOSET(@DateYearMonthDate), [Date].[Year - Month - Date].currentmember ), 
IIF( STRTOSET(@CustomerDepartment).Count = 1, STRTOSET(@CustomerDepartment), [Customer].[Department].currentmember )
)

Open in new window


Here is the parameter query for CustomerDepartment
WITH 
MEMBER [Measures].[ParameterCaption] AS [Customer].[Department].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Customer].[Department].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [Customer].[Department].CURRENTMEMBER.LEVEL.ORDINAL 

SELECT {
	[Measures].[ParameterCaption], 
	[Measures].[ParameterValue], 
	[Measures].[ParameterLevel]} ON COLUMNS , 
[Customer].[Department].children ON ROWS FROM 
[Cube]

Open in new window

Avatar of TempDBA
TempDBA
Flag of India image

Did you try the workarounds in the same link that you posted?
ASKER CERTIFIED SOLUTION
Avatar of gosi75
gosi75
Flag of Iceland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gosi75

ASKER

I found the answer myself.