Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to create a calulated member based on a value parameter

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

  • Thursday, June 14, 2012 4:31 PM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Thursday, June 14, 2012 5:48 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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