Cfrgtkky

I need to sort the columns based on the date parameter entered to be the prior year month first and increase up to the date entered, for the period of 12 months. For example, parameter value entered '03/31/2018', it would first display 'Apr 2017', then 'May 2017', ...so on..., last column 'Mar 2018'`. Thank you!

First of all, next time be sure to post what you tried so far and why it is not working. This way people will see that you put some effort into it and they will be happy to help you.

Second go to your Tablix Properties > Sorting > Add and use the sort expression:

=CDate(Fields!YourDate.Value)

Then go to your Tablix Properties > Filters > Add and add the following two filters:

'Expression1

=DateSerial(Year(DateAdd("yyyy", -1, CDate(Parameters!YourParameter.Value))), Month(CDate(Parameters!YourParameter.Value)), "1").AddMonths(1)



'Format1

=Date/Time



'Operator1

>



'Value1

=CDate(Fields!YourDate.Value)

And the second filter:

'Expression2

=DateSerial(Year(DateAdd("yyyy", -1, CDate(Parameters!YourParameter.Value))), Month(CDate(CDate(Parameters!YourParameter.Value))), "1").AddMonths(13)



'Format2

=Date/Time



'Operator2

<



'Value2

=CDate(Fields!YourDate.Value)

I have modified my dataset to include one more field "IndexColumn", which sorts in a specified order, starting with "A" for a prior year month (April 2017):

=SWITCH(Fields!Indexcolumn.Value = “A”, Format(DateAdd("m", -11, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),

Fields!Indexcolumn.Value = “B”, Format(DateAdd("m", -10, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),

Fields!Indexcolumn.Value = “C”, Format(DateAdd("m", -9, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),

Fields!Indexcolumn.Value = “D”, Format(DateAdd("m", -8, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),

Fields!Indexcolumn.Value = “E”, Format(DateAdd("m", -7, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),

Fields!Indexcolumn.Value = “F”, Format(DateAdd("m", -6, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),

Fields!Indexcolumn.Value = “G”, Format(DateAdd("m", -5, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),

Fields!Indexcolumn.Value = “H”, Format(DateAdd("m", -4, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),

Fields!Indexcolumn.Value = “I”, Format(DateAdd("m", -3, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),

Fields!Indexcolumn.Value = “J”, Format(DateAdd("m", -2, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),

Fields!Indexcolumn.Value = “L”, Format(DateAdd("m", -1, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),

Fields!Indexcolumn.Value = “M”, Format(DateAdd("m", 1, CDate(Parameters!AsOfDate.Value)),"MMM yyyy")

Cannot forget the error, which might pop up, which needs to be addressed: "The Value expression for the text box ‘Textbox9’ refers directly to the field ‘Indexcolumn’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.."