using switch in tablix properties Filters tab

Question

I have the following code in my tablix properties in the filters pane,

=Switch( Parameters!Year.Value = Fields!Calendar_Year.Value, "Include", Parameters!Year.Value-1 = Fields!Calendar_Year.Value, "Include", IIF(Parameters!Year.Value = Fields!Calendar_Year.Value, Fields!Calendar_Year.Value, Fields!Calendar_Year.Value - 2), "Include" )

This returns me 3 years of data i.e. 2016, 2015 and 2014. My report is separated by months and as you see I pass a param to the report for the year. See below image:

So all 3 years show for which i have the data but where I dont have data for 2016 it shows the 2015 and 2014 (which is what I want to see)

I want to know how I can get rid of the 2014 column when there is data for 2016.

Like:(ignore the ordering in the image below)

EDIT:

SELECT NON EMPTY { [Measures].[A], [Measures].[B - COUNT], [Measures].[C], [Measures].[D], [Measures].[E], [Measures].[F], [Measures].[G - COUNT],[Measures].[EMAIL], [Measures].[MAIL], [Measures].[RENT] } ON COLUMNS
, NON EMPTY { ([Order Date].[Calendar Year].[Calendar Year].ALLMEMBERS * [Order Date].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS * [Order Date].[Month Name].[Month Name].ALLMEMBERS * [Dim Brand].[Brand Desc].[Brand Desc].ALLMEMBERS * [Dim Market].[Market Desc].[Market Desc].ALLMEMBERS ) } 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( 
SELECT ( {[Order Date].[Calendar Year].&[2014], [Order Date].[Calendar Year].&[2015], [Order Date].[Calendar Year].&[2016] } ) ON COLUMNS 
FROM ( SELECT ( { [Dim Market].[Market Desc].&[UK], [Dim Market].[Market Desc].&[USA] } ) ON COLUMNS 
FROM ( SELECT ( { [Order Date].[Month Number Of Year].[All] } ) ON COLUMNS 
FROM [Cube]))) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-06-03T14:16:33

I think you can achieve the desired result by using a BOTTOM N operator.

Go to the Year column group and right click it, select Group Properties....

Go to Filter tab and set the following settings. If your sorting property is ascending A-Z use a TOP N operator and value 2, otherwise descending (Z-A sorting) use BOTTOM N operator

It will filter only the two last years.

Let me know if this helps.

using switch in tablix properties Filters tab

1 Answers