0

I've never done this for a matrix and it's going a little over my head. Trying to make a sales report, and I can do the matrix just fine. The part I'm having trouble with and I haven't been able to find a direct answer for through googling is how to get a specific value out of the matrix.

Here is a picture of it: Report enter image description here

What I need is on the far right where it says total at the top. There I need to do another break out (dynamically would be preferred but not required) the first Column would be %change of 2013 -> 2014. Then of course then next would be 2014 to 2015 and so on.

Here is the design view if it helps: Design View enter image description here

So the sum total of the year compared to the year previous.

I was trying to use iif and max but I think i'm just confusing myself but the expression i have in there is:

=Sum(iif(Max(Fields!Year.Value)=Fields!Year.Value,Fields!glamt.Value,1))

Just from my testing I got no idea what values it's pulling for that.

aduguid
  • 3,099
  • 6
  • 18
  • 37
Dale Kingston
  • 1
  • 2

1 Answers1

0

Something I've used before is to create a variable of the SUM of previous period, in you case year. So =SUM(IIF(Fields!Year.Value = "2017",Fields!glamt.Value,0) added into the Variables section of the report.

Then in a new column within the year grouping, you add your comparison formula;
=(SUM(glamt)-Variables!sum2017.Value) / Variables!sum2017.Value * 100.00

This gives you the percentage upshift/downshift, but you get the idea if you want the actual figure of increase/decrease in sales etc.

One of the downsides of this approach is that the variables will need to be added manually and you may have a large number depending on the number of previous periods you want to compare but should suffice for say the last 5 years.

Micha
  • 81
  • 1
  • 8