Problem in getting SUM of Running Total Field

Hi buddy...

i m creating a report as below

--------------------------
Report Head ==>>> | Field1 |
--------------------------
Field2 Field3 Field4 Field5 Field6 Field7
--------------------------

Detail Section Here.....

--------------------------
sum(Field7)
--------------------------

Field7 is my Running Formula Field. i.e. Field7= (Field1 + Field5 - Field6)
nd thats working well.

Working ::

i m generating a report that nd gets the sum of Field7 as far as there is some record in details.

but when there is no record in the details section Sum of Field7 shows me nothing,
where as i am required to display the value of Field1 in it if there is nothing in the details section.

He who give solution will be considered a well qualified person.

with thanks nd admiration in advance

Silly StarCreate another formula with this :

If IsNull(Field7) then
Field1
else
Field7|||Hey KristyW

You Are Real Genius

Thats Worked Perfectly.

Thank u So much Sir but there r more Problems regarding Crystal Report
May i ask them

Silly Star|||u r a real "Silly Star"|||Hi ...

i m creating a report as below

--------------------------
GroupHeader ==>>> | Field1 |
--------------------------
Field2 Field3 Field4 Field5 Field6 Field7
--------------------------

Detail Section Here.....

--------------------------
GroupFooter==>> sum(Field7)
--------------------------

Field7 is the Running Formula Field. i.e. Field7= (Field1 + Field5 - Field6)
and i reset this formula on GroupChange
nd thats working well.

but when a group is changed it does not work properly (Meaning the desired result is not displayed)

He who give solution will be considered a well qualified person.

with thanks nd admiration in advance

Silly Star|||hai silly star

Edit the running field as

In the summary part give field 7 as the field to summarize and typeof summary is sum

In the evaluate column check the 'for each record' and

In the reset column check the 'on chage of group'

try this and it will work for you i believe

bye

viji

Problem in Calculating Total

Hi Everybody,

I need to generate report for all the days in a current month. I have to display the total in the folg. way.

Day1 Day2 etc.
Group Name sum of amount sum of amount
from day1 to end from day2 to end
of month of month

upto end of month.

I had a problem in setting the columns. And is there any way to calculate the group wise sum with out creating groups.

If you have any idea, pls. help me

Thanks,
harmonycitraHi,
I dont think it is possible to have groupwise total without creating group. You can group,find sum and suppress group header.

Madhivanan|||Thanks,

But I'd done it by Cross tab. The only difference is,

instead of starting from 1st to 31st, I reversed the order from end of month to beg. of month. So I'm calculating running total easily.

Any way, thank you!

Problem for Calling A Stored Procedure, Please help.

I am writing a Stored Procedure for other server (using C++ to receive the output values) as below, where @.Total ， @.balance， @.A are output values
create proc [MaxTime]

@.number varchar(30),

@.numbera varchar(30),

@.numberb varchar(30)

as

begin

declare @.balancefloat

declare @.table varchar(20)

declare @.freetotal varchar(20)

declare @.SQL nvarchar(4000)

declare @.A float

declare @.Total float

select @.balance = balance, @.table = table, @.freetotal = freetotal

from info where number = @.number
SELECT @.SQL = 'select @.A = A FROM' + @.table

+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)

+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +

' ORDER BY LEN(code) DESC'

exec sp_executesql @.sql,N'@.Afloat output',@.Aoutput

set @.Total = @.balance / @.A + @.freetotal

end

return

but the server got nothing, then i wrote another Stored Procedure below:

create proc [MaxTime]

(@.number varchar(30),

@.numbera varchar(30),

@.numberb varchar(30),

@.Total float output,

@.balance float output,

@.A float output)

--here is the only modified i made

as

begin

declare @.table varchar(20)

declare @.freetotal varchar(20)

declare @.SQL nvarchar(4000)

select @.balance = balance, @.table = table, @.freetotal = freetotal

from info where number = @.number
SELECT @.SQL = \'select @.A = A FROM\' + @.table

+ \' WHERE LEFT(code, 1) = \' + LEFT(@.incomingcode, 1)

+ \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @.incomingcode+ \') = 1\' +

\' ORDER BY LEN(code) DESC\'

exec sp_executesql

--@.sql,N\'@.Afloat output\',@.Aoutput --im not sure about this line

set @.Total = @.balance / @.A + @.freetotal

end

return

system error message：

Msg 170, Level 15, State 1, Procedure MaxTime, Line 11

Line 11: Incorrect syntax near \'@.Total \'.

Msg 137, Level 15, State 1, Procedure MaxTime,, Line 21

Must declare the variable \'@.Total \'.

Please help, appreciated

Hi,xxd

If you wanna get data from database without using dataset.
maybe you can try function.

By the below case, the SQL substring can't add the local varity into the sentance.
It was because the "exec sp_executesql " will be the other Transcation.

|||Hi, HutTsai:

thanks for ur reply, as u mentioned about dataset, did you mean in SQL? or on the other server side?

and for the 'exec sp_executesql', it was only for excuting the dynamic@.sql to get those values that i need to calculate in set @.Total = @.balance / @.A + @.freetotal.

Thanks
|||

Hello xxd

as the requirement,I think you need to clac the value @.total return for Client that call sp [MAXTIME]

this is the sample code I wrote, try it. modi by your sample code.
if you need the detail for this. Let me know. :)

why "cursor"?
my target was get the return value From dynamic-SQLstring,
using the cursor delcare in global.
then fetch its content for out return value.

it's a better method.

Reference: Stored Procedure,Cursor;

Cheers,
Hunt

/* Sample Code by Hunt Begin*/

create proc [MaxTime]
(@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
@.Total float output)
--here is the only modified i made

as
begin
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.SQL nvarchar(4000)
select @.balance = balance, @.table = table, @.freetotal = freetotal
from info where number = @.number

set @.sql =
' Declare tmpcur cursor for '
' select @.A = A FROM' + @.table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'

exec (@.sql)
open tmpcur;
Fetch Next From tmpcur into @.A;
close tmpcur;
Deallocate tmpcur;

select @.total = @.balance / @.A + @.freetotal

/*Sample Code by Hunt End*/

|||Thanks Hunt:

1st, there are something wrong for this part below:
' Declare tmpcur cursor for '
' select @.a = a FROM'
please advise me that how to modify it, coz this is my 1st time to see write cursor this way :)
and for my case, i don't really need to use cursor, coz the ' select @.A = A FROM' + @.table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'
will only back me one set of data. anyway.

based on your code, i modified mine as below:
alter proc [MaxTime]

(@.number varchar(30),

@.numbera varchar(30),

@.numberb varchar(30),

@.Total float output,

@.balance float output)

as

begin

declare @.table varchar(20)

declare @.freetotal varchar(20)

declare @.SQL nvarchar(4000)

select @.balance = balance, @.table = table, @.freetotal = freetotal

from info where number = @.number
SELECT @.SQL = \'select @.A = A FROM\' + @.table

+ \' WHERE LEFT(code, 1) = \' + LEFT(@.incomingcode, 1)

+ \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @.incomingcode+ \') = 1\' +

\' ORDER BY LEN(code) DESC\'

exec sp_executesql

--don't know where did i get those \ from

set @.Total = @.balance / @.A + @.freetotal

end

return @.Total
return @.balance

however, got error message like:
Msg 201, Level 16, State 4, Procedure MaxTime, Line 0
Procedure 'MaxTime' expects parameter '@.Total', which was not supplied.

but one step closed i think

Cheers.
|||

ok,the important checkpoint on sys.procedure -> "exec sp_executesql"
check with my sample.
you'll get what you want.
Hint: as you set output varity with value,you shouldn't return any value. it's useless.
Reference with Books Online "sp_executesql"
Cheers,
Hunt
alter proc [sp_test1]
(@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
@.Total float output,
@.balance float output)
as
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.A float
declare @.SQL nvarchar(4000);
declare @.SQLparm nvarchar(500);

set @.table ='car'
set @.balance = 3.0
set @.freetotal = 2.0
--the section below will be the most important.
set @.SQLparm = N'@.A float output'
select @.sql = ' select @.A = 2.0 FROM ' + @.table
exec sp_executesql @.sql,@.SQLparm ,@.A output
set @.Total = @.balance / @.A + @.freetotal
go

declare @.tot float
declare @.free float
exec [sp_test1] '1','2','3',@.tot output,@.free output
print ''
print @.tot
print @.free
go

|||thank you for writing all of those code.

however, why i need those below?
declare @.tot float
declare @.free float
exec [sp_test1] '1','2','3',@.tot output,@.free output

and i think it should be like exec [sp_test1] '1','2','3', @.Total output, @.balance output

otherwise, in fact, there are two clients are calling this procedure 1 of them was alright for getting the outputs, the other one doesn't, it is an application, in this application all i can do is to identify three input parameters,
which are @.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
and there are no more space for @.Total output and @.balance output.

so that when this application pass the exec command to sql server, it would be like exec [sp_test1] '1','2','3' rather than exec [sp_test1] '1','2','3', @.Total output, @.balance output

hope i explained clearly.

Cheers
|||

this thread got a little problem,i can't post any word on it.

try put default value behind the varity.
@.total float =0 out,@.balance float =0 out

in this sample,you can call sp with no output param.
check it.

Cheer.

|||hi thanks,
i'll try it tomorrow, let you know then|||did you mean that
exec [sp_test1] '1','2','3', @.Total float = 0 output, @.balance float = 0 output

?
or exec [sp_test1] '1','2','3','0','0'

it works fine when i am using exec [sp_test1] '1','2','3','0','0' for outputing values for the server (C++) or i do not even need to use the output value, it also works. However i still can not set the @.total float =0 out,@.balance float =0 out for the application case, and that application tells me that it did not get anything.

so is there any better way to solve it out?

many thx
|||

You have to put the default value when you create stored procedure.
as below

Create Proc [MAXTime] (@.numbera varchar(20),@.numberb varchar(20),@.numberc varchar(20),
@.total float =0 out,@.balance float =0 out);

after you alter the sp,you can call the proc by
exec [MAXTime] '1','2','3'
or
exec [MAXTime] '1','2','3',@.total out,@.balance out

try it.

|||hi HuntTsai:

thanks for that, by now i do not thin k the output will solve my problem, after all i realised that there are 3 types of output function of sproc:

1select @.something
2@.something output
3return@.something and return(0)

all of above are the same(of course not) or for some special using?
thanks

|||

I think I don't know what's your original requirement(or question).
maybe it could be describe more detail. On basiclly, the output Question seems like be sloved.

anyway,when we use stored procedure, it was defined for regular process.
and the return types that you said in last post were the normal method.
(I add the 4th as fire_trigger).

1. select @.something
2. @.something output
3. return@.something and return(0)
4. sometimes we also set it up as another type of trigger.

Best Regrads. :)

|||Hi HuntTsai:

Thanks a lot.