Solved

SSRS - Running Value

Posted on 2014-10-07
3
198 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 29

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

728 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