Search code examples
expressioncalculated-fieldssrs-2016reporting-services-2016

SQL Server Reporting: How calculate value based on the previous calculated value int the same column?

I'm trying to calculate a row value based on the previous row value in the same column within a report expression. I can't precalculate this from database since starting point of calculation is dependent from input parameters and values in a table should be recalculated dynamically within report itself.

In Excel analogical data and formula look like as it is shown below (starting point is always 100):

   B      C             D            E
   Price  PreviousPrice CalcValue    Formula
1  NULL   NULL          100 
2  2.6    2.5           104          B2/C2*D1
3  2.55   2.6           102          B3/C3*D2
4  2.6    2.55          104          B4/C4*D3
5  2.625  2.6           105          B5/C5*D4
6  2.65   2.625         106          B6/C6*D5
7  2.675  2.65          107          B7/C7*D6

I tried to calculate expected values ("CalcValue" is the name of column where expression is set) like this:

=Fields!Price.Value/ PreviousPrice.Value * Previous(reportitems("CalcValue").Value))

but got an error "Aggregate functions can be used only on report items contained in page headers and footers"

Can you please advice whether expected result is achievable in my case and suggest a solution?

Thank you in advance!

Sadly I'm still facing with issue: calculated column does not consider previous calculated value. E.g., I added CalcVal field with 100 as default and tried to calculate using above approach, like: =previous(runningValue(Fields!CalcVal.Value, sum, "DataSet1") ) * Fields!Price.Value/Fields!PreviousPrice.Value.

But in this case it always multiples Fields!Price.Value/Fields!PreviousPrice.Value by 100.. For example CalcVal on Fly always show 200 =previous(runningValue(Fields!CalcVal.Value, sum, "DataSet1")) * 2

https://i.sstatic.net/mKlKQ.jpg

Solution

  • I tried with your sample data, here is how I achieved the results

    enter image description here

    enter image description here

    Formula to use, You might have to take care of null values

      =Fields!Price.Value/(Fields!PreviousPrice.Value*Previous(Fields!CalcValue.Value))

    Edit: Update to answer after Op's comment

    enter image description here

    enter image description here

    enter image description here

    CalcValue is caluated with below formula i.e on the fly

    =RunningValue(CountDistinct("Tablix6"),Count,"Tablix6"*100

    and then Final value as below

    =Fields!Price.Value/(Fields!PreviousPrice.Value*
Previous(RunningValue(CountDistinct("Tablix6"),Count,"Tablix6"))*100)