RunningValue ?

I am having problems with the following -

this represents the dataset Date Customer Voucher Number Document Amount Check Amount 7/15/2006 Company G 101 15.00 45.00 7/15/2006 Compnay G 101 25.00 45.00 7/15/2006 Compnay G 101 5.00 45.00 7/18/2006 Company A 102 35.00 35.00 7/25/2006 Company M 103 45.00 50.00 7/25/2006 Company M 103 15.00 50.00

This represents the final report this is what I am getting Date Customer Voucher Number Document Amount Check Amount Check Amount (Hidden) Group 1 Header = Voucher Number (Hidden) Detail 7/15/2006 Company G 101 15.00 45.00 45.00 (Hidden) Detail 7/15/2006 Company G 101 25.00 45.00 45.00 (Hidden) Detail 7/15/2006 Company G 101 5.00 45.00 45.00 Group 1Footer 7/16/2006 Company G 45.00 45.00 135.00 (Hidden) Group1Header = Voucher Number (Hidden) Detail 7/18/2006 Company A 102 35.00 35.00 35.00 Group 1 Footer 7/19/2006 Company A 35.00 35.00 35.00 (Hidden) Group 1 Header = Voucher Number (Hidden) Detail 7/25/2006 Company M 103 45.00 50.00 50.00 (Hidden) Detail 7/25/2006 Company M 103 15.00 50.00 50.00 Group 1 Footer 7/25/2006 Company M 103 60.00 50.00 100.00 Report Footer Grand Total: 140.00 130.00 270.00

(I am using a table)

I can get the correct total for the Group 1 Footer Check Amount by dividing the Total by a count of the Voucher Numbers, but I have not been able to add the Group 1 totals of the Check Amount for the Grand Total in the footer. I have made various attempts using RunningValue with the Group 1 Check Amount Total field, but always getting a error - mostly that the scope was not correct.

Any suggestions will be appreciated.

Ok, so I solved my own problem. Maybe this will help you. I summed the Group 1 Footer cells with the correct Check Amount totals (45, 35,50) in a textbox in the Report Footer =Sum(ReportItems!CHEKTOTL_1.value). Voila! I was trying to make this way too complicated!|||

I am having the exact same problem and have tried about four different approaches without success.

Where are my CRW running totals?!

I my case it is cash receipts and applications (whereas your case appears to be payables).

How did you get your group 1 footer to get just the check amount. I have tried summing a iif(...) where only the first row of a check shows the value - and this did not work (cannot sum a calculation!). You indicate that you divided the total by the number of rows - would you mind showing the expression.

How then did you get this expression in the group 1 footer to be summed - I have all but givin up on runningvalue due to scope issues - this also killed my attempt at VBA code.

Thanks

Philip Neufeld

|||

Here is my solution. I hope my explanation makes sense.

I did some aggregating in the the SQL statement - summing the DocAmount and grouping by Voucher Number, Voucher Date, Check Total and Vendor.

My report is for multiple companies, so in the design portion I used a list data region sorted by company. Then I added a table, grouped by Company.

I put the Date, Voucher Number, Vendor, Voucher Amount and Check Amount in the detail rows of the table and summed the Voucher Amount and Check Total in the Table Summary row.

On the General tab of the table properties, I checked Insert a page break after this table and Fit table on one page if possible. When the report is exported to Excel, each company appears on a separate worksheet and the summaries also appear. That was another problem I was having with another solution I had devised - the totals appeared on my screen in the Reporting Manager, but apparently the formulas that I used to get the totals weren't exporting into Excel.

Hope this helps!

sjm

sql

RunningValue ?

I am having problems with the following -

this represents the dataset

Date

Customer

Voucher Number

Document Amount

Check Amount

7/15/2006

Company G

101

15.00

45.00

7/15/2006

Compnay G

101

25.00

45.00

7/15/2006

Compnay G

101

5.00

45.00

7/18/2006

Company A

102

35.00

35.00

7/25/2006

Company M

103

45.00

50.00

7/25/2006

Company M

103

15.00

50.00

This represents the final report

this is what I am getting

Date

Customer

Voucher Number

Document Amount

Check Amount

Check Amount

(Hidden)

Group 1 Header = Voucher Number

(Hidden)

Detail

7/15/2006

Company G

101

15.00

45.00

45.00

(Hidden)

Detail

7/15/2006

Company G

101

25.00

45.00

45.00

(Hidden)

Detail

7/15/2006

Company G

101

5.00

45.00

45.00

Group 1Footer

7/16/2006

Company G

45.00

45.00

135.00

(Hidden)

Group1Header = Voucher Number

(Hidden)

Detail

7/18/2006

Company A

102

35.00

35.00

35.00

Group 1 Footer

7/19/2006

Company A

35.00

35.00

35.00

(Hidden)

Group 1 Header = Voucher Number

(Hidden)

Detail

7/25/2006

Company M

103

45.00

50.00

50.00

(Hidden)

Detail

7/25/2006

Company M

103

15.00

50.00

50.00

Group 1 Footer

7/25/2006

Company M

103

60.00

50.00

100.00

Report Footer

Grand Total:

140.00

130.00

270.00

(I am using a table)

I can get the correct total for the Group 1 Footer Check Amount by dividing the Total by a count of the Voucher Numbers, but I have not been able to add the Group 1 totals of the Check Amount for the Grand Total in the footer. I have made various attempts using RunningValue with the Group 1 Check Amount Total field, but always getting a error - mostly that the scope was not correct.

Any suggestions will be appreciated.

Ok, so I solved my own problem. Maybe this will help you. I summed the Group 1 Footer cells with the correct Check Amount totals (45, 35,50) in a textbox in the Report Footer =Sum(ReportItems!CHEKTOTL_1.value). Voila! I was trying to make this way too complicated!|||

I am having the exact same problem and have tried about four different approaches without success.

Where are my CRW running totals?!

I my case it is cash receipts and applications (whereas your case appears to be payables).

How did you get your group 1 footer to get just the check amount. I have tried summing a iif(...) where only the first row of a check shows the value - and this did not work (cannot sum a calculation!). You indicate that you divided the total by the number of rows - would you mind showing the expression.

How then did you get this expression in the group 1 footer to be summed - I have all but givin up on runningvalue due to scope issues - this also killed my attempt at VBA code.

Thanks

Philip Neufeld

|||

Here is my solution. I hope my explanation makes sense.

I did some aggregating in the the SQL statement - summing the DocAmount and grouping by Voucher Number, Voucher Date, Check Total and Vendor.

My report is for multiple companies, so in the design portion I used a list data region sorted by company. Then I added a table, grouped by Company.

I put the Date, Voucher Number, Vendor, Voucher Amount and Check Amount in the detail rows of the table and summed the Voucher Amount and Check Total in the Table Summary row.

On the General tab of the table properties, I checked Insert a page break after this table and Fit table on one page if possible. When the report is exported to Excel, each company appears on a separate worksheet and the summaries also appear. That was another problem I was having with another solution I had devised - the totals appeared on my screen in the Reporting Manager, but apparently the formulas that I used to get the totals weren't exporting into Excel.

Hope this helps!

sjm

Running Totals

If I have a table that contains the following values :
Id Amount
A1 100
B1 50
C1 75
Is it possible to write a query that would output the following :
A1 100 100
B1 50 150
C1 75 225
in other words, add the value of a column in the current row to the value
of a column in the previous row ?
Thanks
SteveOn Sat, 12 Jun 2004 11:06:33 +0100, Steve W wrote:
>If I have a table that contains the following values :
>Id Amount
>A1 100
>B1 50
>C1 75
>Is it possible to write a query that would output the following :
>A1 100 100
>B1 50 150
>C1 75 225
>in other words, add the value of a column in the current row to the value
>of a column in the previous row ?
>Thanks
>Steve
>
Hi Steve,
You'll have to define "previous" row yourself. A table in a relational
database has no fixed ordering. Assuming you want the running total based
on alphabetic ordering by the value if ID, use something like:
SELECT a.Id, a.Amount,
(SELECT SUM(b.Amount)
FROM MyTable AS b
WHERE b.Id <= a.Id) AS RunningTotal
FROM MyTable AS a
ORDER BY a.Id
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Steve,
You can try this:
use db_dbname
go
select top 10 identity(int,1,1) as num_line, a.value1 as v1, a.value2 as v2
into #temp
from table_name a
where <condition>
go
declare @.valor as float -- or decimal
set @.valor = 0
update #temp
set @.valor = @.valor + v1, v2 = @.valor
go -- the result here...
select * from #temp
go
drop table #temp
Hope this help
Rogério
Brazil
"Steve W" <lsl@.btconnect.com> escreveu na mensagem
news:%23HfChVGUEHA.2724@.TK2MSFTNGP11.phx.gbl...
> If I have a table that contains the following values :
> Id Amount
> A1 100
> B1 50
> C1 75
> Is it possible to write a query that would output the following :
> A1 100 100
> B1 50 150
> C1 75 225
> in other words, add the value of a column in the current row to the value
> of a column in the previous row ?
> Thanks
> Steve
>sql

Running Total: Summing on Distinct Amount

I am trying to do a sum on a goal amount that is repeated for each record. But what is the the forumla to only sum on the distinct goal amount.

Example:

Month Year Goal Other Value

March 2007 500 5568

March 2007 500 5568

March 2007 500 5569

April 2007 600 5568

April 2007 700 5569

Total (I am receive)

March-April 2007 1600 5568

March-April 2007 1200 5569

Total (I excpect)

March-April 2007 1100 5568

March-April 2007 1200 5569

I haven't found anything online to help.

Thanks,

Sam

You could define you data set using the following SQL.

SELECT Month, Year, [Other Value], SUM(Goal)

FROM (

SELECT DISTINCT Month, Year, [Other Value], Goal

FROM TableName

)

GROUP BY Month, Year, [Other Value]

|||

I actually have more data being returned than what is being displayed in my example. I am already doing a group by in data set. Thanks for the thought.

Sam

|||

I hope this example is better:

Example:

Month Year Goal SalesID ActualSales

March 2007 500 5568 550

March 2007 500 5568 475

March 2007 500 5569 605

April 2007 600 5568 700

April 2007 700 5569 710

Running Total cursor

I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.

You shouldn't need a cursor. Try this instead,
first create a view to give the totals up to
the current date.

CREATE VIEW AmountTotals
AS
SELECT a.Code_id,
a.Amount,
a.Date,
(SELECT SUM(b.Amount)
FROM Amount b
WHERE a.Code_id=b.Code_id
AND b.Date <= a.Date) AS RunningTotal
FROM Amount a
GO

Now you can update all of your ReachedDates
using this view

UPDATE ID
SET ReachedDate=(SELECT MIN(Date)
FROM AmountTotals
WHERE RunningTotal >= 100
AND AmountTotals.Code_id=ID.Code_ID)

|||

you have to create a for update, insert, delete trigger on

table amount which shall automatically mark the flag when the amount is reached. trigger fires automatically

|||Hey Mark. Works like a charm. Thank you so much.|||

Yeah, right. And freeze the server like magic! The best and faster solution its using cursors. I can say because I use "the magic" in some points of my app, and Im having performance problems with it.

Its really sad that microsoft dont release real solutions for simple problems like that.

Running Total cursor

I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.

You shouldn't need a cursor. Try this instead,
first create a view to give the totals up to
the current date.

CREATE VIEW AmountTotals
AS
SELECT a.Code_id,
a.Amount,
a.Date,
(SELECT SUM(b.Amount)
FROM Amount b
WHERE a.Code_id=b.Code_id
AND b.Date <= a.Date) AS RunningTotal
FROM Amount a
GO

Now you can update all of your ReachedDates
using this view

UPDATE ID
SET ReachedDate=(SELECT MIN(Date)
FROM AmountTotals
WHERE RunningTotal >= 100
AND AmountTotals.Code_id=ID.Code_ID)

|||

you have to create a for update, insert, delete trigger on

table amount which shall automatically mark the flag when the amount is reached. trigger fires automatically

|||Hey Mark. Works like a charm. Thank you so much.|||

Yeah, right. And freeze the server like magic! The best and faster solution its using cursors. I can say because I use "the magic" in some points of my app, and Im having performance problems with it.

Its really sad that microsoft dont release real solutions for simple problems like that.

Running Total

I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
What I need to do is write a cursor that when the Amount adds up to $100.00
it Updates the ReachedDate to the YearPeriod fields value of that record. Any
help would be greatly appreciated.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1Likely, you don't need a cursor. However, you do need to provide DDL +
INSERT statements of sample data + expected results, so that we can better
analyze the problem.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"anaylor01 via SQLMonster.com" <u11795@.uwe> wrote in message
news:5eddecafb3c79@.uwe...
I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
What I need to do is write a cursor that when the Amount adds up to $100.00
it Updates the ReachedDate to the YearPeriod fields value of that record.
Any
help would be greatly appreciated.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Sorry. I have two tables. ID table that has these fields
Code_id, YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
Tom Moreau wrote:
>Likely, you don't need a cursor. However, you do need to provide DDL +
>INSERT statements of sample data + expected results, so that we can better
>analyze the problem.
>I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
>What I need to do is write a cursor that when the Amount adds up to $100.00
>it Updates the ReachedDate to the YearPeriod fields value of that record.
>Any
>help would be greatly appreciated.
--
Message posted via http://www.sqlmonster.com|||Let's try this again:
http://www.aspfaq.com/etiquette.asp?id=5006
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"anaylor01 via SQLMonster.com" <u11795@.uwe> wrote in message
news:5ede547a908d8@.uwe...
Sorry. I have two tables. ID table that has these fields
Code_id, YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
Tom Moreau wrote:
>Likely, you don't need a cursor. However, you do need to provide DDL +
>INSERT statements of sample data + expected results, so that we can better
>analyze the problem.
>I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
>What I need to do is write a cursor that when the Amount adds up to
>$100.00
>it Updates the ReachedDate to the YearPeriod fields value of that record.
>Any
>help would be greatly appreciated.
--
Message posted via http://www.sqlmonster.com|||See the article:
http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp
"anaylor01 via SQLMonster.com" wrote:
> I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
> What I need to do is write a cursor that when the Amount adds up to $100.00
> it Updates the ReachedDate to the YearPeriod fields value of that record. Any
> help would be greatly appreciated.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1
>

Running Total

I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
What I need to do is write a cursor that when the Amount adds up to $100.00
it Updates the ReachedDate to the YearPeriod fields value of that record. An
y
help would be greatly appreciated.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1Likely, you don't need a cursor. However, you do need to provide DDL +
INSERT statements of sample data + expected results, so that we can better
analyze the problem.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"anaylor01 via droptable.com" <u11795@.uwe> wrote in message
news:5eddecafb3c79@.uwe...
I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
What I need to do is write a cursor that when the Amount adds up to $100.00
it Updates the ReachedDate to the YearPeriod fields value of that record.
Any
help would be greatly appreciated.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1|||Sorry. I have two tables. ID table that has these fields
Code_id, YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
Tom Moreau wrote:
>Likely, you don't need a cursor. However, you do need to provide DDL +
>INSERT statements of sample data + expected results, so that we can better
>analyze the problem.
>I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
>What I need to do is write a cursor that when the Amount adds up to $100.0
0
>it Updates the ReachedDate to the YearPeriod fields value of that record.
>Any
>help would be greatly appreciated.
Message posted via http://www.droptable.com|||Let's try this again:
http://www.aspfaq.com/etiquette.asp?id=5006
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"anaylor01 via droptable.com" <u11795@.uwe> wrote in message
news:5ede547a908d8@.uwe...
Sorry. I have two tables. ID table that has these fields
Code_id, YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.
Tom Moreau wrote:
>Likely, you don't need a cursor. However, you do need to provide DDL +
>INSERT statements of sample data + expected results, so that we can better
>analyze the problem.
>I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
>What I need to do is write a cursor that when the Amount adds up to
>$100.00
>it Updates the ReachedDate to the YearPeriod fields value of that record.
>Any
>help would be greatly appreciated.
Message posted via http://www.droptable.com|||See the article:
http://www.sql-server-performance.c...dly_problem.asp
"anaylor01 via droptable.com" wrote:

> I have a table that consist fields Code_id,YearPeriod,Amount, ReachedDate.
> What I need to do is write a cursor that when the Amount adds up to $100.
00
> it Updates the ReachedDate to the YearPeriod fields value of that record.
Any
> help would be greatly appreciated.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1
>