Using condition to set SSRS matrix column name

0
votes

I was trying something new, using matrix and only want to see the recent two years and i made 2 columns. The first column had this expression '=MAX(Fields!Year.Value)-1' and the second column has the expression '=MAX(Fields!Year.Value)'. I expected to only see 2 year columns but it shows all of them and on the last column it sums up all the orders. See the photos below:

In the design view my matrix looks like this:

enter image description here

The output in report is:

enter image description here

The problem is that year 2007 only exists in another year and month, but i dont know why it shows up here. see the image next to see the original data.

The original data is:

enter image description here

And I just want to see the recent 2 years and ignore the rest of the years, like follows:

enter image description here

Thanks in advance.

1
reporting-servicesssrs-2012

1 Answers

1
votes

You are adding each year order, instead you have to add only those that correspond to your column year. For previous last year you have to sum only its orders values.

Use these expressions to Sum only the two recent years orders:

Previous last year:

=Sum(
iif(Fields!Year.Value=Max(Fields!Year.Value)-1,Fields!Total_Ord.Value,0)
)

Last year:

=Sum(
IIF(Fields!Year.Value=MAX(Fields!Year.Value),Fields!Total_Ord.Value,0)
)

UPDATE: Don't group by year and just add two columns under the Month column group.

This is the matrix you should have:

enter image description here

It will preview:

enter image description here

Let me know if this can help you.