0

In SSRS I have column group per days (user can change range). I need to evaluate for each day for the previous five days. I found PREVIOUS function which gives me only one previous value. Is there any option how to evaluate, for example, last 5 values (columns) in one group? Report is built upon SSAS cube. On cube I found TAIL function, but it doesn't work for me - I need "floating" evaluation for previous five days for each day.

curveball
  • 4,320
  • 15
  • 39
  • 49
Ondrej Pospisil
  • 1
  • 3

1 Answers1

0

You can add an expression in your textbox, something like this:

=Sum(IIF(CDate(DateAdd("d", -5, Today())) < CDate(Fields!YourDateField.Value), Fields!YourFloatingSum.Value, 0))

It adds just the values to the sum which are within the last five days from today on.

You can also change the Today() function to a Field to make it more dynamic. That depends how you need it in your report.

Strawberryshrub
  • 3,301
  • 2
  • 11
  • 20
  • Thank you for the response. What do you exactly mean with "Fields!YourFloatingSum.Value" ? – Ondrej Pospisil Oct 08 '18 at 07:05
  • This is just a placeholder for the **Field** you use because I dont know how your field is called and what you want to evaluate. So lets say you want to evaluate the sales amount for each of the five previous days and your sales amount field name is `Fields!SalesAmount.Value`, then use the above expression with this field name `=Sum(IIF(CDate(DateAdd("d", -5, Today())) < CDate(Fields!YourDateField.Value), Fields!SalesAmount.Value, 0))`. Now just the sales amount of the previous five days gets summed up – Strawberryshrub Oct 08 '18 at 09:09