none
How to create a calulated member based on a value parameter

    Question

  • Hello,

    I have a problem filtering a metric in SSRS by a specific value.

    Example i want to filter Sales AMT USD less than 10,000,000 with a parameter but i dont know how to do it with specific value in this case < 10,000,000

    i know how to filter Sales AMT USD by dimension

    Example

     SELECT NON EMPTY { [Measures].[Sales Amt USD] } ON COLUMNS, NON EMPTY { ([Order Date].[Calendar YQMD].[Date].ALLMEMBERS * [Customer].[Geography].[State Province].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@OrderDateCalendarYQMD, CONSTRAINED) ) ON COLUMNS FROM [MDWT 2008R2]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    but i want to filter Sales AMT USD by a < Specific Value  that will be my parameter

    I Appreciate your answers

    Regards,

    Santana Garza

    Thursday, June 14, 2012 4:31 PM

Answers

  • Create a calculated member that returns back something if the Sales Amount exceeds the selected amount and NULL otherwise. Then use the NonEmpty function passing that calculated member as the second parameter. You'll only get cells exceeding the parameter value.

    WITH MEMBER [Measures].[Sales Amt > Selected] AS 
    	IIF( [Measures].[Sales Amt] > @Value, 1, NULL )
    SELECT	{ 
    		[Measures].[Sales Amt USD] 
    	} ON COLUMNS, 
    	NonEmpty (
    		{ [Order Date].[Calendar YQMD].[Date].ALLMEMBERS }
    		* { [Customer].[Geography].[State Province].ALLMEMBERS }
    		, [Measures].[Sales Amt > Selected] 
    	) ON ROWS DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
    FROM	( 
    		SELECT	STRTOSET(@OrderDateCalendarYQMD, CONSTRAINED) ON COLUMNS 
    		FROM	[MDWT 2008R2]
    	) 
    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    HTH, Martin

    http://martinmason.wordpress.com

    Thursday, June 14, 2012 5:48 PM