Create an automatic SUM ROW

Hello.
In a previous program i used to create reports, I had the option to enter a "SUM ROW".

I saw that in RS2005 I can check the "add total" when using the report wizard and it will create for me a row with SUM for my column.

Is there a way to create a row like this automatically with out the wizard (a sum row for all my columns instead of creating a textbox with "sum" for each column)?

Thanks in advance,
Roy.

Hi,

nom this behaviour is influenced by the normal summing / grouping operations. A sum in a row has nothing to do with this. I don′t think that the standard behaviour of the wizrad cab be changed in Reporting Services.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Hi Jens, Thanks for the replay.

I don't want the "standard behaviour" of the wizard to change.
I'll like the "SUM" je is makeing in the entire row.
what i whanted to know is:
if i'm making my own report, not with the wizard, is there a simple way to create a sum row like the wizard with out creating a textbox for every column manually?

Thanks in advance,
Roy.
|||

After making the report with the wizard you can simply use the Expression =SUM(Fields!SomeValueinQuery1.Value + Fields!SomeValueinQuery2.Value + Fields!SomeValueinQuery3.Value +Fields!SomeValueinQuery4.Value) OR use the appropiate ReportItems!Textbox1.Value, but there is no function like in Excel which automatically tries to gues what you want to summarize (even this function in Excel sometime guesses wrong what you want to achieve :-) )

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

Create a counter

I have to create a counter (it is possible to increase or decrease).
It is not record ID, but a part of an automatic generated name.
In case of intensive concurrent use I have a deadlock.
The steps:
1. Launch transaction with isolation level REPEATABLE READ (or SERIALIZABLE
in respect of finish are equal)
2. open the recordset
3. get old number
4. set new number
5. update
6. close record set
7. commit
If I run my program on three computer on the same database a deadlock occur.
How can I isolate the sessions without exceptions?
How can I qued up my clients for its new counter?
I am glad of any idea.
Regards,
Imre Ament"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
>
Yep that's a recipe for a deadlock. Each session gets and holds a read lock
on the table, then each tries to escalate it to an update lock. You need to
get the update lock when you first open the recordset. Then your clients
will serialize properly.
To get an exclusive or update lock with a select statement look at the
UPDLOCK and XLOCK hints.
David|||Never do something like this from the client, always use a stored procedure
and minimize the locking. How about doing something like this instead:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
> I am glad of any idea.
> Regards,
> Imre Ament
>sql

Create a counter

I have to create a counter (it is possible to increase or decrease).
It is not record ID, but a part of an automatic generated name.
In case of intensive concurrent use I have a deadlock.
The steps:
1. Launch transaction with isolation level REPEATABLE READ (or SERIALIZABLE
in respect of finish are equal)
2. open the recordset
3. get old number
4. set new number
5. update
6. close record set
7. commit
If I run my program on three computer on the same database a deadlock occur.
How can I isolate the sessions without exceptions?
How can I qued up my clients for its new counter?
I am glad of any idea.
Regards,
Imre Ament
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
>
Yep that's a recipe for a deadlock. Each session gets and holds a read lock
on the table, then each tries to escalate it to an update lock. You need to
get the update lock when you first open the recordset. Then your clients
will serialize properly.
To get an exclusive or update lock with a select statement look at the
UPDLOCK and XLOCK hints.
David
|||Never do something like this from the client, always use a stored procedure
and minimize the locking. How about doing something like this instead:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
> I am glad of any idea.
> Regards,
> Imre Ament
>