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