June 16, 2010 at 12:09 am
Hi,
I have query like as shoen below, its working fine.
but when i am trying to pass a parameter and trying to use this query in report (ssrs) i am not able to get the proper results.
can any one help me to pass parameters to this mdx to work properly in the report.
I wish to pass parameter in the place of "[Period Dim].[Month Name].&[100022]"
WITH MEMBER [Measures].[PrevPeriodBalance] AS
'SUM((null:[Period Dim].[Month Name].&[100022]), [Measures].[Amount])'
SELECT NON EMPTY { [Measures].[Amount], [Measures].[PrevPeriodBalance] } ON COLUMNS,
NON EMPTY { (([Account Dimension].[Parent Account].[Level 02].ALLMEMBERS) ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS
FROM ( SELECT ( { [Period Dim].[Month Name].&[100022] } ) ON COLUMNS
FROM [DWH Slappey]) WHERE ( [Period Dim].[Month Name].&[100022] )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Thanks.
June 16, 2010 at 9:46 am
Is your date "[Period Dim].[Month Name].&[100022]" a hierarchy?
This may be a good starting point to troubleshoot your issues:
gsc_dba
June 16, 2010 at 10:01 am
WITH
MEMBER [Measures].[PrevPeriodBalance] AS
Sum
(
NULL : [Period Dim].[Month Name].&[100022]
,[Measures].[Amount]
)
SELECT
NON EMPTY
{
[Measures].[Amount]
,[Measures].[PrevPeriodBalance]
} ON COLUMNS
,NON EMPTY
{[Account Dimension].[Parent Account].[Level 02].ALLMEMBERS}
DIMENSION PROPERTIES
MEMBER_CAPTION
,MEMBER_UNIQUE_NAME
,PARENT_UNIQUE_NAME
,LEVEL_NUMBER
ON ROWS
FROM
(
SELECT
StrToSet
(@YourDateParameterGoesHere
,CONSTRAINED
) ON COLUMNS
FROM [DWH Slappey]
)
CELL PROPERTIES
VALUE
,BACK_COLOR
,FORE_COLOR
,FORMATTED_VALUE
,FORMAT_STRING
,FONT_NAME
,FONT_SIZE
,FONT_FLAGS;
You may need to wrap "[Period Dim].[Month Name].&[" & Parameters!YourDateParameterGoesHere.Value & "]" in SSRS for it to work
gsc_dba
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply