Solved

# SSRS - Running Value

Posted on 2014-10-07
195 Views
I have a report that I'm trying to calculate some running value, but can't seem to get it to work correctly.

Example:

So the above example is how my report is.  The column E "Qty Short" is my running value.  I'm trying to take (On hand) - (Qty Req) = Qty Short.

This is what I had done, but it's not working correctly.

=RunningValue(Fields!Qty_Req.Value, Sum, "table1_group1")

I'm not even sure if runningvalue is the right function to use.  Basically, I just want to take my "On Hand" and subtract the "Qty Req" to determine how many qty I'm short of.
0
Question by:holemania

LVL 18

Accepted Solution

x-men earned 500 total points
ID: 40367904
configure your dataset with the following query:

select [Job],[Item ID] ,[Qty Req], [On Hand]
,[On Hand]-(select sum(T2.[Qty Req]) from ee as T2 where T2.[Item ID] =T1.[Item ID] and T2.Job<=T1.Job) as [Running Qty Req]
from ee as T1

my test was:

create database lab
go
use lab
go
create table EE (Job int,[Item ID] char(5),[Qty Req] int, [On Hand] int)
go
insert into EE VALUES (92992,'AB993',4,20)
insert into EE VALUES (92993,'AB993',3,20)
insert into EE VALUES (92994,'AB993',6,20)
insert into EE VALUES (92995,'AB993',8,20)
insert into EE VALUES (92996,'AB993',6,20)
go

select [Job],[Item ID] ,[Qty Req], [On Hand]
,[On Hand]-(select sum(T2.[Qty Req]) from ee as T2 where T2.[Item ID] =T1.[Item ID] and T2.Job<=T1.Job) as [Running Qty Req]
from ee as T1

Result:
Job               Item ID      Qty Req      On Hand      Running Qty Req
92992      AB993           4                     20                        16
92993      AB993           3                     20                       13
92994      AB993           6                     20                         7
92995      AB993           8                     20                        -1
92996      AB993           6               20                        -7
0

LVL 28

Expert Comment

ID: 40368736
This is how I get my running total.

drag a textbox to your design view and shrink as small as possible and call it hidden_total

Then click expressions (fx) and enter the following:

``````=Count(Fields!NameOfField.value, "datasetName")
``````

Finally, add a pagefooter to the designer view and enter the following:

``````=ReportItems!hidden_total.value
``````

and you should get a running total.
0

Author Closing Comment

ID: 40371479
Thank you.  That worked.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…