Solved

SSRS - Running Value

Posted on 2014-10-07
3
197 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 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

730 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