0

I;m looking to create a formula in SSRS 2008 for Bal_Toward_Thresh in the example output data I've provided. The "bal_toward_thresh" will allow me to sum up to a threshold ("Bal_Toward_Thresh") which is defined as a variable in my query(in the attached example it's $500). At which point I need the report to start re-summing a running total on the next row, with the next transaction amount. I'm not sure how to structure it in SSRS to have the "Bal_Toward_Thresh" formula, reference the prior cell's formula to determine whether it needs to include the balance from the prior cell, or restart from the "Daily_Tran_Total" on the current line. I've tried something along the lines of this in SSRS however I know this to be poor logic and wrong.

=IIF((ReportItems!(preceding Bal_toward_thresh).value > Fields!Threshold.Value), (Fields!Daily_Tran_Total), (ReportItems!TextBoxAbove.value + Fields!Daily_Tran_Total)) b

The problem being, I'm not familiar enough with SSRS to know whether it's possible, or how to reference the prior cell's data.

Tran_Date   Daily_Tran_Tot  Run_Total   Bal_Toward_Thresh   Count   Threshold
7/3/2015    35.1       35.1       35.1            0        500
7/7/2015    363.67     398.77       398.77       0        500
7/10/2015    215.91     614.68          614.62          1        500
7/14/2015    200       814.68       200       0        500
7/15/2015    200       1014.68       400       0        500
7/16/2015   1959.73     2974.41         2359.73       1        500
7/17/2015   1532.18     4506.59       1532.18       1        500
7/22/2015   81.63       4588.22       81.63       0        500
7/23/2015   860.42      5448.64         942.05          1        500
7/28/2015   200         5648.64         200             0        500
7/29/2015         1400        7048.64         1600            1        500
                 Fields that I need to calculate

Mock Up of Data I'm trying to Obtain

Eric Hauenstein
  • 2,557
  • 6
  • 31
  • 41
Calverzp
  • 1
  • 2
  • It would help if you included the sample data as text that could be copy-pasted, not as an image. It would also help if you clearly indicated what is the original source data and what is your desired result. It would also help if you tagged the question with the version of SQL Server that you use. SQL Server 2012+ supports window functions that may be useful in this case. – Vladimir Baranov Nov 10 '15 at 05:22
  • I just updated the tags, and added an example of the results I'm seeking within the post. Hopefully this will help to clarify, as it's a bit tricky explaining it in and of itself. Please let me know if you have any other questions. Thank you. – Calverzp Nov 10 '15 at 16:46

0 Answers0