Running total in RS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Running total in RS

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-06-01 : 14:03:25

Hello everyone I am trying to create a running total for

[hours Required]
40

[hours earned]
2

[hours still due]
38

so the more they earn the less hours still due. I guess I need the [hours still due] to reflect the hours they are still lacking.

CREATE VIEW [dbo].[HoursEarned]
AS
SELECT     TOP (100) PERCENT SUM(dbo.Contact_tbl.[Earned hours]) AS [Hours earned], dbo.Contact_tbl.[Catagory for hours], dbo.Parent_Sc.[Parent First Name], 
                      dbo.Parent_Sc.[Parent Last Name], dbo.Parent_Sc.[Mo Hours], dbo.Parent_Sc.[Req hours] AS RequiredHours, dbo.Parent_Sc.[Referral Status], 
                      dbo.Contact_tbl.[Contact Date], dbo.Parent_Sc.[Date of Referral]
FROM         dbo.Parent_Sc INNER JOIN
                      dbo.Contact_tbl ON dbo.Parent_Sc.[Parent ID] = dbo.Contact_tbl.[Parent ID]
GROUP BY dbo.Contact_tbl.[Catagory for hours], dbo.Parent_Sc.[Parent First Name], dbo.Parent_Sc.[Parent Last Name], dbo.Parent_Sc.[Mo Hours], 
                      dbo.Parent_Sc.[Referral Status], dbo.Contact_tbl.[Contact Date], dbo.Parent_Sc.[Date of Referral], dbo.Parent_Sc.[Req hours]
HAVING      (SUM(dbo.Contact_tbl.[Earned hours]) IS NOT NULL) AND (dbo.Parent_Sc.[Referral Status] = N'Active') AND 
                      (dbo.Parent_Sc.[Date of Referral] IS NOT NULL)
ORDER BY dbo.Parent_Sc.[Parent Last Name]

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-01 : 14:17:59

can you show some sample data from your tables please? on what field value you want to take cumulative sum? is there a pk value?

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-06-01 : 23:07:01

How do you post an image I seem to be having some problems doing that

CONTACT TABLE

93 4/2/2008 12:00:00 AM campbelljenny Home Visit Early Head Start Home Visit NULL 4 Parenting Early Head Start Home Visit
94 4/3/2008 12:00:00 AM campbelljenny NULL Structured Parenting Activities NULL 2.5 Parenting Structured Parenting Activities
97 4/6/2008 12:00:00 AM campbelljenny NULL Structured Parenting Activities NULL 1.5 Parenting Structured Parenting Activities
98 4/7/2008 12:00:00 AM campbelljenny NULL Structured Parenting Activities NULL 2.5 Parenting Structured Parenting Activities
100 4/8/2008 12:00:00 AM campbelljenny NULL Structured Parenting Activities NULL 2.5 Parenting Structured Parenting Activities
101 4/9/2008 12:00:00 AM campbelljenny NULL Structured Parenting Activities NULL 2.5 Parenting Structured Parenting Activities
102 4/10/2008 12:00:00 AM campbelljenny NULL Structured Parenting Activities NULL 1.5 Parenting Structured Parenting Activities
103 4/11/2008 12:00:00 AM campbelljenny NULL Structured Parenting Activities NULL 2.5 Parenting Structured Parenting Activities

parent_Sc

Deena Allen 30 NULL Inactive NULL
Amber Alongi 40 NULL Active San Diego
Charlotte Alto 20 NULL Active Alpine
Norman Amador 20 NULL Inactive NULL
Linda Apodoca NULL NULL NULL NULL
Desiree Armendariz 20 NULL Active Alpine
Brett Barnwell 40 NULL Active Alpine
Lisa Belshe 20 NULL Inactive San Diego
Danielle Blackfox 20 NULL Inactive San Diego
Rhonda Bongiorno NULL NULL NULL NULL
Crystal Brown 20 NULL Inactive NULL

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-06-02 : 13:18:32

I think I got it figured out, I looked among my script and found a similar report I did. I think this is close to what I am looking for, I looked a the report that I did on reporting services and its pretty close to what I am looking for.


USE [IncidentReportsdb]
GO
/****** Object:  StoredProcedure [dbo].[TotalLosses_Type]    Script Date: 10/29/2008 14:17:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TotalLosses_Type](@StartDate datetime,
@EndDate datetime)
AS SELECT     COUNT ([Violation Type]) AS Total, [Violation Type], SUM([Loss]) AS [Total_Losses], [Inspector]
FROM         dbo.Revised_MainTable
WHERE      (Date BETWEEN @StartDate AND @EndDate)
GROUP BY [Violation Type], [Inspector]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 13:23:02

this wont give you cumulative sum or count. it just aggregates over group and gives you result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 13:40:23

upload image in some shared server and post link here

which quantity are you trying to aggregate cumulatively?

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-06-02 : 16:58:22

Sorry Viskah16 I explained that terribly

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-06-02 : 18:57:46

can anyone see a problem with this??

=Fields!HoursRequired.Value - RunningValue(Fields!HoursEarned.Value, Sum, "DataSet1")

I'm just trying to get a running total of [hours required]-[hours earned]

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-06-03 : 13:33:04

ok Problem solved thank you Visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 14:06:30

the expression looks fine. what issue are you facing?

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-06-03 : 15:31:06

Hi Visakh16, I finally figured out how to do the running total on the client end, now I am trying to get running total per group. for instance

[Parent Last Name] [Hours Still Due]

Dunn 70.0

Bridwell 50.00

Does that make sense??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources