Solved

SSRS - Running Value

Posted on 2014-10-07
3
191 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you.  That worked.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now