Solved

SSRS - Running Value

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

Example:
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
Comment
Question by:holemania
3 Comments
 
LVL 18

Accepted Solution

by:
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

by:sammySeltzer
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")

Open in new window


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

=ReportItems!hidden_total.value

Open in new window


and you should get a running total.
0
 

Author Closing Comment

by:holemania
ID: 40371479
Thank you.  That worked.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

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…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question