Resetting the count on a Primary Key at a certain record? Impossible?

Hello,
Several consecutive records have been deleted out of an MSDE database
and the program that uses the database doesn't like it. I'm curious
if it's possible to... say... reset the primary key count so that the
records all shift to fill the "dead space" left by the deleted
records.
I have the feeling that this may be humorous to some of you, but any
input (including "you can't do this. that's the point of a primary
key") is appreciated.
Thanks!
Matt
hi Matt,
Matt Brown - identify wrote:
> Hello,
> Several consecutive records have been deleted out of an MSDE database
> and the program that uses the database doesn't like it. I'm curious
> if it's possible to... say... reset the primary key count so that the
> records all shift to fill the "dead space" left by the deleted
> records.
> I have the feeling that this may be humorous to some of you, but any
> input (including "you can't do this. that's the point of a primary
> key") is appreciated.
>
you can "reset" the identity value using a DBCC CHECKIDENT with the RESEED
option specified..
http://msdn2.microsoft.com/en-us/library/ms176057.aspx
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||On Mon, 02 Jul 2007 10:51:23 -0700, Matt Brown - identify wrote:

>Hello,
>Several consecutive records have been deleted out of an MSDE database
>and the program that uses the database doesn't like it. I'm curious
>if it's possible to... say... reset the primary key count so that the
>records all shift to fill the "dead space" left by the deleted
>records.
>I have the feeling that this may be humorous to some of you, but any
>input (including "you can't do this. that's the point of a primary
>key") is appreciated.
Hi Matt,
You can't do this. That's the point of a primary key.
Seriously - a primary key is intended to identify an entity. Think of
what would happen if you would change your name. Or get a new SSN.
Note that I am not contradicting Andrea. DBCC CHECKIDENT WITH RESEED
will affect new IDENTITY values, not existing values. I think that you
were asking about the latter.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||Matt Brown - identify wrote:
> Hello,
> Several consecutive records have been deleted out of an MSDE database
> and the program that uses the database doesn't like it. I'm curious
> if it's possible to... say... reset the primary key count so that the
> records all shift to fill the "dead space" left by the deleted
> records.
> I have the feeling that this may be humorous to some of you, but any
> input (including "you can't do this. that's the point of a primary
> key") is appreciated.
>
ooopppsss...
Hugo is right, completely right... I did not read with enougth attention and
only saw the "identity" resetting requirement... I'm sorry..
I'm just curious why "..program that uses the database doesn't like it.." :D
Agreeing completely with Hugo about the "keys" immutability, you can perhaps
workaround that..
you could create a "temporary" table where you insert all the current valid
rows of your table, truncate the original table in order to empty it and to
reset the idientity table's value, and finally refill it with the "orphaned"
rows..
so you can write something like
INSERT INTO tempTable SELECT * FROM dbo.YourTable WHERE... ORDER BY...;
TRUNCATE TABLE dbo.YourTable;
INSERT INTO dbo.YourTable (all_columns_but_the_identity_col)
SELECT all_columns_but_the_identity_col
FROM tempTable
WHERE... ORDER BY...;
DROP TABLE tempTable;
but, again, this is a "poor strategy"... you'll end up with gaps anyway, now
or then.. and this "workaround" does not work if you have declarative
referential integrity set on that table, if it's referenced by other rows in
other tables...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||On Jul 3, 6:34 am, "Andrea Montanari" <andrea.sql...@.virgilio.it>
wrote:
> Matt Brown - identify wrote:
>
>
> ooopppsss...
> Hugo is right, completely right... I did not read with enougth attention and
> only saw the "identity" resetting requirement... I'm sorry..
> I'm just curious why "..program that uses the database doesn't like it.." :D
> Agreeing completely with Hugo about the "keys" immutability, you can perhaps
> workaround that..
> you could create a "temporary" table where you insert all the current valid
> rows of your table, truncate the original table in order to empty it and to
> reset the idientity table's value, and finally refill it with the "orphaned"
> rows..
> so you can write something like
> INSERT INTO tempTable SELECT * FROM dbo.YourTable WHERE... ORDER BY...;
> TRUNCATE TABLE dbo.YourTable;
> INSERT INTO dbo.YourTable (all_columns_but_the_identity_col)
> SELECT all_columns_but_the_identity_col
> FROM tempTable
> WHERE... ORDER BY...;
> DROP TABLE tempTable;
> but, again, this is a "poor strategy"... you'll end up with gaps anyway, now
> or then.. and this "workaround" does not work if you have declarative
> referential integrity set on that table, if it's referenced by other rows in
> other tables...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
I haven't checked this is a while. That's actually perfect. I can
remove some recordsets, then reset the identity key count with this
here DBCC CHECKIDENT WITH RESEED, then everything should progress as
normal.
Righto!
Thanks,
Matt

reset table identity in TSQL

I have a table set up with a primary key identity column. Most of the time
this works fine, but there are occasions where I need to update a record and
set the primary key to a number that is not in the identity sequence. For
instance the identity value is 160001 and I need to manually update a
record's primary key to 5000.
So far the only way I know how to do this is to go into Enterprise manager
and select identity to NO on the identity field to save the table, run my
update queries, go back into Enterprise manager and set identity back to YES
and resave. I am sure there must be a way I can put this process into a
script. Is there are way to do this in TSQL without having to use Enterprise
Manager?
Example:
Identity primary key customer_id 160001 needs to be updated to 5000 and
afterwards restore identity back to 160001
thanks,
FrankLookup DBCC CHECKIDENT in SQL Server Books Online.
--
- Anith
( Please reply to newsgroups only )|||look up SET IDENTITY_INSERT in BOL
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Frank" <frankenberryz@.yahoo.com> wrote in message
news:eY0Bb.6$1i.21421@.news.uswest.net...
> I have a table set up with a primary key identity column. Most of the time
> this works fine, but there are occasions where I need to update a record
and
> set the primary key to a number that is not in the identity sequence. For
> instance the identity value is 160001 and I need to manually update a
> record's primary key to 5000.
> So far the only way I know how to do this is to go into Enterprise manager
> and select identity to NO on the identity field to save the table, run my
> update queries, go back into Enterprise manager and set identity back to
YES
> and resave. I am sure there must be a way I can put this process into a
> script. Is there are way to do this in TSQL without having to use
Enterprise
> Manager?
> Example:
> Identity primary key customer_id 160001 needs to be updated to 5000 and
> afterwards restore identity back to 160001
> thanks,
> Frank
>|||SET IDENTITY_INSERT tableName ON
GO
UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
GO
"Frank" <frankenberryz@.yahoo.com> wrote in message
news:eY0Bb.6$1i.21421@.news.uswest.net...
> I have a table set up with a primary key identity column. Most of the time
> this works fine, but there are occasions where I need to update a record
and
> set the primary key to a number that is not in the identity sequence. For
> instance the identity value is 160001 and I need to manually update a
> record's primary key to 5000.
> So far the only way I know how to do this is to go into Enterprise manager
> and select identity to NO on the identity field to save the table, run my
> update queries, go back into Enterprise manager and set identity back to
YES
> and resave. I am sure there must be a way I can put this process into a
> script. Is there are way to do this in TSQL without having to use
Enterprise
> Manager?
> Example:
> Identity primary key customer_id 160001 needs to be updated to 5000 and
> afterwards restore identity back to 160001
> thanks,
> Frank
>|||> I have a table set up with a primary key identity column. Most of the time
> this works fine, but there are occasions where I need to update a record
and
> set the primary key to a number that is not in the identity sequence. For
> instance the identity value is 160001 and I need to manually update a
> record's primary key to 5000.
Why? Should you really be using a surrogate if people care what the actual
value is?
In any case, see the following topics in Books Online:
SET IDENTITY_INSERT
DBCC CHECKIDENT
> So far the only way I know how to do this is to go into Enterprise manager
> and select identity to NO on the identity field to save the table, run my
> update queries, go back into Enterprise manager and set identity back to
YES
> and resave.
Please stop using Enterprise Manager to manipulate data. You can do
irrepairable damage, or a whole lot of unnecessary locking, e.g. when the
tool goes behind the scenes and makes a complete copy of a 4 billion row
table without warning you first (many of EM's shortcuts are accomplished
exactly this way).
> Identity primary key customer_id 160001 needs to be updated to 5000 and
> afterwards restore identity back to 160001
Here's what my approach would be (though I still fail to see the purpose of
assigning some known, meaningful value to a surrogate).
CREATE TABLE blat
(
blatID INT IDENTITY(160000, 1),
cust VARCHAR(32)
)
GO
SET NOCOUNT ON
INSERT blat(cust) VALUES('lobster')
INSERT blat(cust) VALUES('shrimp')
GO
DBCC CHECKIDENT('blat', RESEED, 4999)
GO
INSERT blat SELECT cust FROM blat WHERE blatID = 160001
GO
DELETE blat WHERE blatID = 160001
GO
DBCC CHECKIDENT('blat', 'RESEED', 160000)
GO
INSERT blat(cust) VALUES('squid')
GO
SELECT * FROM blat
GO
DROP TABLE blat
GO
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
Please try this
declare @.i int
declare @.c1 nvarchar(100)
set @.i = 5000
set @.c1 = 'DBCC CHECKIDENT (Tablename, RESEED,'+
ltrim(rtrim(convert(char,@.i)))+')'
exec sp_executesql @.c1
After updating
declare @.i int
declare @.c1 nvarchar(100)
set @.i = 160001
set @.c1 = 'DBCC CHECKIDENT (Tablename, RESEED,'+
ltrim(rtrim(convert(char,@.i)))+')'
exec sp_executesql @.c1
Thanks
Hari
MCDBA
"Frank" <frankenberryz@.yahoo.com> wrote in message
news:eY0Bb.6$1i.21421@.news.uswest.net...
> I have a table set up with a primary key identity column. Most of the time
> this works fine, but there are occasions where I need to update a record
and
> set the primary key to a number that is not in the identity sequence. For
> instance the identity value is 160001 and I need to manually update a
> record's primary key to 5000.
> So far the only way I know how to do this is to go into Enterprise manager
> and select identity to NO on the identity field to save the table, run my
> update queries, go back into Enterprise manager and set identity back to
YES
> and resave. I am sure there must be a way I can put this process into a
> script. Is there are way to do this in TSQL without having to use
Enterprise
> Manager?
> Example:
> Identity primary key customer_id 160001 needs to be updated to 5000 and
> afterwards restore identity back to 160001
> thanks,
> Frank
>|||> SET IDENTITY_INSERT tableName ON
> GO
> UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
> GO
This was my initial angle, but regardless of IDENTITY_INSERT setting, I get:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'customer_id'.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Can I put this into a transaction?
begin transaction
set identity_insert tblName ON
UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
commit transaction
Would that work?
thanks,
Frank
"Raymond D'Anjou (raydan)" <raydan@.canatrade.nospamcom> wrote in message
news:ub8z#LavDHA.560@.TK2MSFTNGP11.phx.gbl...
> SET IDENTITY_INSERT tableName ON
> GO
> UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
> GO
> "Frank" <frankenberryz@.yahoo.com> wrote in message
> news:eY0Bb.6$1i.21421@.news.uswest.net...
> > I have a table set up with a primary key identity column. Most of the
time
> > this works fine, but there are occasions where I need to update a record
> and
> > set the primary key to a number that is not in the identity sequence.
For
> > instance the identity value is 160001 and I need to manually update a
> > record's primary key to 5000.
> >
> > So far the only way I know how to do this is to go into Enterprise
manager
> > and select identity to NO on the identity field to save the table, run
my
> > update queries, go back into Enterprise manager and set identity back to
> YES
> > and resave. I am sure there must be a way I can put this process into a
> > script. Is there are way to do this in TSQL without having to use
> Enterprise
> > Manager?
> >
> > Example:
> > Identity primary key customer_id 160001 needs to be updated to 5000 and
> > afterwards restore identity back to 160001
> >
> > thanks,
> > Frank
> >
> >
>|||I was thinking that maybe IDENTITY_INSERT doesn't work for updates after I
posted the message.
BOL only mentions that it works for updates.
Sorry, should have put "Untested" before my solution.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:eaiPsNavDHA.1872@.TK2MSFTNGP09.phx.gbl...
> > SET IDENTITY_INSERT tableName ON
> > GO
> >
> > UPDATE tableName SET customer_id = 5000 WHERE customer_id = 160001
> > GO
> This was my initial angle, but regardless of IDENTITY_INSERT setting, I
get:
> Server: Msg 8102, Level 16, State 1, Line 1
> Cannot update identity column 'customer_id'.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>|||I get the error:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'customer_id'
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#v45XNavDHA.560@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Please try this
> declare @.i int
> declare @.c1 nvarchar(100)
> set @.i = 5000
> set @.c1 = 'DBCC CHECKIDENT (Tablename, RESEED,'+
> ltrim(rtrim(convert(char,@.i)))+')'
> exec sp_executesql @.c1
> After updating
> declare @.i int
> declare @.c1 nvarchar(100)
> set @.i = 160001
> set @.c1 = 'DBCC CHECKIDENT (Tablename, RESEED,'+
> ltrim(rtrim(convert(char,@.i)))+')'
> exec sp_executesql @.c1
>
>
> Thanks
> Hari
> MCDBA
>
> "Frank" <frankenberryz@.yahoo.com> wrote in message
> news:eY0Bb.6$1i.21421@.news.uswest.net...
> > I have a table set up with a primary key identity column. Most of the
time
> > this works fine, but there are occasions where I need to update a record
> and
> > set the primary key to a number that is not in the identity sequence.
For
> > instance the identity value is 160001 and I need to manually update a
> > record's primary key to 5000.
> >
> > So far the only way I know how to do this is to go into Enterprise
manager
> > and select identity to NO on the identity field to save the table, run
my
> > update queries, go back into Enterprise manager and set identity back to
> YES
> > and resave. I am sure there must be a way I can put this process into a
> > script. Is there are way to do this in TSQL without having to use
> Enterprise
> > Manager?
> >
> > Example:
> > Identity primary key customer_id 160001 needs to be updated to 5000 and
> > afterwards restore identity back to 160001
> >
> > thanks,
> > Frank
> >
> >
>|||Step 1: Make an INSERT script for the row with PK value 5000.
Step 2: Delete the existing row with PK value 160001
Step 3: Set IDENTITY_INSERT for the table ON
Step 4: Run the INSERT script for PK 5000
Step 5: Set IDENTITY_INSERT for the table OFF
Step 6: Run DBCC CHECKIDENT(<tablename>, RESEED, 16000)
Hope this helps.

Resequence column

Any ideas how to resequence the numbers in a column after inserting or
updating a record. The column is not the Unique ID and is an integer column
it is used to allow users to select the order in which things are displayed
for them the column is displayOrder and the select statement would Order By
that column. I am trying to do this in a stored procedue but I am not
having any luckPlease read: www.aspfaq.com/5006
Can you provide some more information including DDLs, sample data & expected
results, so that others can better understand your requirements?
Anith|||Assuming it's just a sequential resequencing you want and there
are no foreign key issues:
create table test (id int, name varchar(50))
insert into test select 5, 'test 1'
insert into test select 6, 'test 2'
insert into test select 9, 'test 3'
select * from test
declare @.id int
set @.id = 0
update test
set @.id = id = @.id + 1
"Ian Galloway via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:9aea371c6f2e4868b71c18edec3f9a87@.SQ
webservertalk.com...
> Any ideas how to resequence the numbers in a column after inserting or
> updating a record. The column is not the Unique ID and is an integer
column
> it is used to allow users to select the order in which things are
displayed
> for them the column is displayOrder and the select statement would Order
By
> that column. I am trying to do this in a stored procedue but I am not
> having any luck|||Here is an example of what I am trying to do
tbl_Browsers
ID browserName browserVersion displayOrder
1 Intern Explorer 6.5 1
2 Intern Explorer 6.0 2
3 Intern Explorer 5.0 3
4 Intern Explorer 4.0 4
5 Firefox 1.1 5
5 Firefox 1.2 6
the displayOrder columns holds the users preference of which order he would
like to display these records, as he inserts new records he may want the
newer record to be diplayed third in the list so I created this procedure:
CREATE PROCEDURE sp_sptInsertTicketStatusType
(@.strBrowserName VarChar(20), @.strBrowserVersion VarChar(225),
@.intDisplayOrder Int)
AS
update tbl_Browser set displayOrder = (displayOrder + 1)
where displayOrder >= @.intDisplayOrder
insert tbl_Browsers (browserName, browserVersion, displayOrder) values
(@.strName, @.strDescription, @.intDisplayOrder)
GO
Thhis would increment all the displayOrder Values by one where the
submitted display order is equal to or greater than the newly submitted
records displayOrder.
The problem is when editing the records to update the displayOrder things
can get out of sequence that way. I need a way to do the following
Edit a record - say ID 1 (IE 6.5) and make its displayOrder 3 - I need a
way to decrement the displayOrder of the records with displayOrder of 1
and 2 (or lesser values) by one and increment the others to fit
displayOrder 3 into the sequence so ultimately the table would look like
this - of course this would be dynamic as the display order can be any
value
ID browserName browserVersion displayOrder
2 Intern Explorer 6.0 1
3 Intern Explorer 5.0 2
1 Intern Explorer 6.5 3
4 Intern Explorer 4.0 4
5 Firefox 1.1 5
5 Firefox 1.2 6|||Not sure how you are accomodating multi-row inserts & updates with your
existing logic. Ignoring that, based on your narrative, you could do:
CREATE TABLE Browsers(
Browser VARCHAR( 20 ) NOT NULL,
Version DECIMAL( 2, 1 ) NOT NULL,
DisplayOrder INT NOT NULL
PRIMARY KEY ( Browser, Version ) ) ;
-- for inserts
BEGIN TRAN
UPDATE Browsers SET displayOrder = displayOrder + 1
WHERE displayOrder >= @.DisplayOrder;
INSERT Browsers SELECT @.Browser, @.version, @.DisplayOrder ;
COMMIT
-- for updates
BEGIN TRAN
UPDATE Browsers
SET displayOrder = displayOrder - 1
WHERE displayOrder <= @.DisplayOrder
...
UPDATE Browsers
SET displayOrder = @.DisplayOrder
WHERE Browser = 'Intern Explorer'
AND Version = 6.5
COMMIT TRAN
Anith|||I hope this can clarify a bit more what my desired results are, using the
table tbl_sptTicketStatus which has a primary key (ID) I want a user to be
able to select the display order of the record. For inserting new records I
use the following.
This is the isert procedure
________________________________________
_____________
-- If user selects default dispayOrder (0) then add it as the last item to
display
IF @.intDisplayOrder = 0
BEGIN
Set @.intDisplayOrder = (select count(displayOrder) from
tbl_sptTicketStatus)+1
BEGIN TRAN
INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
(@.strName, @.strDescription, @.intDisplayOrder)
COMMIT TRAN
END
ELSE
-- If a diplayOrder is entered into the user form then insert it into the -
- proper place and increment all records equal to or greater than it
IF @.intDisplayOrder >= 1
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder + 1)
where displayOrder >= @.intDisplayOrder
INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
(@.strName, @.strDescription, @.intDisplayOrder)
COMMIT TRAN
END
__________________________
For the EDIT procedure I need to make sure that if the user changes the
diplayOrder that the other records resequence accoringly, here is what I
have now for the procedure, but it does not get the desired affect
__________________________
IF @.intDisplayOrder = (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
ELSE
IF @.intDisplayOrder < (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder - 1)
WHERE displayOrder <= @.intDisplayOrder
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
ELSE
IF @.intDisplayOrder > (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus
SET displayOrder = (displayOrder + 1)
WHERE displayOrder > @.intDisplayOrder
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
GO
_______________________
so using just the ID and displayOrder column here is what should happen
original data
ID diplayOrder
10 1
20 2
30 3
40 4
now the user may want to make ID 40 the first option in a list which should
make the table look like this
ID diplayOrder
40 1
10 2
20 3
30 4
Then the user may choose to make the displayOrder of ID 10 to become third
which would make the table look like this
ID diplayOrder
40 1
20 2
10 3
30 4
so when I pull the data I will use Order By displyOrder.
This and other tables using this method may have any number or rows.
Thanks for any assistance.
Message posted via http://www.webservertalk.com|||I hope this can clarify a bit more what my desired results are, using the
table tbl_sptTicketStatus which has a primary key (ID) I want a user to be
able to select the display order of the record. For inserting new records I
use the following.
This is the isert procedure
________________________________________
_____________
-- If user selects default dispayOrder (0) then add it as the last item to
display
IF @.intDisplayOrder = 0
BEGIN
Set @.intDisplayOrder = (select count(displayOrder) from
tbl_sptTicketStatus)+1
BEGIN TRAN
INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
(@.strName, @.strDescription, @.intDisplayOrder)
COMMIT TRAN
END
ELSE
-- If a diplayOrder is entered into the user form then insert it into the -
- proper place and increment all records equal to or greater than it
IF @.intDisplayOrder >= 1
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder + 1)
where displayOrder >= @.intDisplayOrder
INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
values
(@.strName, @.strDescription, @.intDisplayOrder)
COMMIT TRAN
END
__________________________
For the EDIT procedure I need to make sure that if the user changes the
diplayOrder that the other records resequence accoringly, here is what I
have now for the procedure, but it does not get the desired affect
__________________________
IF @.intDisplayOrder = (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
ELSE
IF @.intDisplayOrder < (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder - 1)
WHERE displayOrder <= @.intDisplayOrder
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
ELSE
IF @.intDisplayOrder > (select displayOrder from tbl_sptTicketStatus where
[ID] = @.intID)
BEGIN
BEGIN TRAN
UPDATE tbl_sptTicketStatus
SET displayOrder = (displayOrder + 1)
WHERE displayOrder > @.intDisplayOrder
UPDATE tbl_sptTicketStatus SET
displayOrder = @.intDisplayOrder,
[Name] = @.strName,
[description] = @.strDescription
WHERE [ID] = @.intID
COMMIT TRAN
END
GO
_______________________
so using just the ID and displayOrder column here is what should happen
original data
ID diplayOrder
10 1
20 2
30 3
40 4
now the user may want to make ID 40 the first option in a list which should
make the table look like this
ID diplayOrder
40 1
10 2
20 3
30 4
Then the user may choose to make the displayOrder of ID 10 to become third
which would make the table look like this
ID diplayOrder
40 1
20 2
10 3
30 4
so when I pull the data I will use Order By displyOrder.
This and other tables using this method may have any number or rows.
Thanks for any assistance.
Message posted via http://www.webservertalk.com|||Here's a revision of the procedure sans any transactions. Add them where
you like.
Try it out and let me know how it works out.
create PROCEDURE sp_sptInsertTicketStatusType
(@.strBrowserName VarChar(20), @.strBrowserVersion VarChar(225),
@.intDisplayOrder Int)
AS
declare @.olddisplayorder tinyint
select @.olddisplayorder = displayorder
from tbl_browser
where browserName = @.strBrowserName
and browserversion = @.strBrowserVersion
delete
from tbl_browser
where browserName = @.strBrowserName
and browserversion = @.strBrowserVersion
if @.intDisplayOrder > @.olddisplayorder
begin
update tbl_Browser
set displayOrder = (displayOrder - 1)
where displayorder between @.olddisplayorder and @.intDisplayOrder
end
if @.intDisplayOrder < @.olddisplayorder
begin
update tbl_Browser
set displayOrder = (displayOrder + 1)
where displayorder between @.intDisplayOrder and @.olddisplayorder
end
if (@.intDisplayOrder = 0)
select @.intDisplayOrder = max(displayorder) + 1
from tbl_Browser
insert tbl_Browser (browserName, browserVersion, displayOrder) values
(@.strBrowserName, @.strBrowserVersion, @.intDisplayOrder)
GO
"Ian Galloway via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:aaf79ff55d9d4406bc34ebdab258dc6f@.SQ
webservertalk.com...
> I hope this can clarify a bit more what my desired results are, using the
> table tbl_sptTicketStatus which has a primary key (ID) I want a user to be
> able to select the display order of the record. For inserting new records
I
> use the following.
> This is the isert procedure
> ________________________________________
_____________
> -- If user selects default dispayOrder (0) then add it as the last item to
> display
> IF @.intDisplayOrder = 0
> BEGIN
> Set @.intDisplayOrder = (select count(displayOrder) from
> tbl_sptTicketStatus)+1
> BEGIN TRAN
> INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
> values
> (@.strName, @.strDescription, @.intDisplayOrder)
> COMMIT TRAN
> END
> ELSE
> -- If a diplayOrder is entered into the user form then insert it into
he -
> - proper place and increment all records equal to or greater than it
> IF @.intDisplayOrder >= 1
> BEGIN
> BEGIN TRAN
> UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder + 1)
> where displayOrder >= @.intDisplayOrder
> INSERT tbl_sptTicketStatus ([name], [description], displayOrder)
> values
> (@.strName, @.strDescription, @.intDisplayOrder)
> COMMIT TRAN
> END
> __________________________
> For the EDIT procedure I need to make sure that if the user changes the
> diplayOrder that the other records resequence accoringly, here is what I
> have now for the procedure, but it does not get the desired affect
> __________________________
> IF @.intDisplayOrder = (select displayOrder from tbl_sptTicketStatus where
> [ID] = @.intID)
> BEGIN
> BEGIN TRAN
> UPDATE tbl_sptTicketStatus SET
> displayOrder = @.intDisplayOrder,
> [Name] = @.strName,
> [description] = @.strDescription
> WHERE [ID] = @.intID
> COMMIT TRAN
> END
> ELSE
> IF @.intDisplayOrder < (select displayOrder from tbl_sptTicketStatus where
> [ID] = @.intID)
> BEGIN
> BEGIN TRAN
> UPDATE tbl_sptTicketStatus SET displayOrder = (displayOrder - 1)
> WHERE displayOrder <= @.intDisplayOrder
> UPDATE tbl_sptTicketStatus SET
> displayOrder = @.intDisplayOrder,
> [Name] = @.strName,
> [description] = @.strDescription
> WHERE [ID] = @.intID
> COMMIT TRAN
> END
> ELSE
> IF @.intDisplayOrder > (select displayOrder from tbl_sptTicketStatus where
> [ID] = @.intID)
> BEGIN
> BEGIN TRAN
> UPDATE tbl_sptTicketStatus
> SET displayOrder = (displayOrder + 1)
> WHERE displayOrder > @.intDisplayOrder
>
> UPDATE tbl_sptTicketStatus SET
> displayOrder = @.intDisplayOrder,
> [Name] = @.strName,
> [description] = @.strDescription
> WHERE [ID] = @.intID
> COMMIT TRAN
> END
> GO
> _______________________
> so using just the ID and displayOrder column here is what should happen
> original data
> ID diplayOrder
> 10 1
> 20 2
> 30 3
> 40 4
> now the user may want to make ID 40 the first option in a list which
should
> make the table look like this
> ID diplayOrder
> 40 1
> 10 2
> 20 3
> 30 4
> Then the user may choose to make the displayOrder of ID 10 to become third
> which would make the table look like this
> ID diplayOrder
> 40 1
> 20 2
> 10 3
> 30 4
> so when I pull the data I will use Order By displyOrder.
> This and other tables using this method may have any number or rows.
> Thanks for any assistance.
> --
> Message posted via http://www.webservertalk.com|||Armando Prato, that worked great and as desired. Thank you for your
assistance.
Message posted via http://www.webservertalk.com|||After testing I realized that deleting and recreating the record was
changing the ID field of the record which is the Identity seed, where I am
using this as a foriegn key in another table this would have broken
relationships. So I modified the proc a bit to insert the existing ID of
the record as opposed to creating a new ID. In order to acheive this the
SET IDENTITY_INSERT flag had to be set to on.
I also wanted to add error checking so that if an error occurs I do not
have a half complete transaction deleting a record and not updating it
propoerly. Here is the finished code I hope now that it is set up properly,
if any one sees errors or drawbacks feel free to let me know.
CREATE PROCEDURE sptBrowserTypeEdit
(@.intID Int, @.strBrowserName VarChar(20), @.strBrowserVersion VarChar(225),
@.intDisplayOrder Int)
AS
SET IDENTITY_INSERT tbl_sptBrowsers ON
declare @.olddisplayorder tinyint
select @.olddisplayorder = displayorder
from tbl_sptBrowsers
where [ID] = @.intID
BEGIN TRAN
delete from tbl_sptBrowsers where [ID] = @.intID
COMMIT TRAN
IF @.@.ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
if @.intDisplayOrder > @.olddisplayorder
BEGIN
BEGIN TRAN
UPDATE tbl_sptBrowsers SET displayOrder = (displayOrder - 1) WHERE
displayOrder between @.olddisplayorder and @.intDisplayOrder
COMMIT TRAN
IF @.@.ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
END
if @.intDisplayOrder < @.olddisplayorder
BEGIN
BEGIN TRAN
UPDATE tbl_sptBrowsers SET displayOrder = (displayOrder + 1) WHERE
displayOrder between @.intDisplayOrder and @.olddisplayorder
COMMIT TRAN
IF @.@.ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
END
IF (@.intDisplayOrder = 0)
SELECT @.intDisplayOrder = MAX(displayOrder) + 1 FROM tbl_sptBrowsers
BEGIN TRAN
INSERT tbl_Browser ([ID], browserName, browserVersion, displayOrder)
VALUES (@.intID, @.strBrowserName, @.strBrowserVersion, @.intDisplayOrder)
COMMIT TRAN
IF @.@.ERROR!=0
BEGIN
ROLLBACK TRAN
RETURN
END
GO
Message posted via http://www.webservertalk.comsql

Repost : Help on matrix and or table or ??

Hi Gurus
Sorry to insist but the need of an answer begin more urgent
we have a dataset like this
Key1 Primary Key of record
Key2 1st Foreign Key
Key3 2nd Foreign Key
Duration in seconds
Product Pn (field for pivot)
Qty Qn (value link to Product)
what we need
P1--Pn
Key3 Sum(Duration) Count(Key1) Sum(Q1)--Sum(Qn)
Key3 Key2 Sum(Duration) Count(Key1) Sum(Q1)--Sum(Qn)
Key3 Key2 Key1 Duration Q1--Qn
Gran Total Sum(Duration) Count(Key1) Sum(Q1)--Sum(Qn)
I seach among the messages of this group and i did not found an answer to
our problem.
It seems for us, that matrix answer to the need of building multiple
columns depending on the different content of one field ie product , and
give the possibility to associate a value link to this field ie Qty in our
example, the informations on rows seems to be only grouping fields, but
not value of field linked to this grouping field ie duration and count of
the primary key in our example
With table i can associate fields to grouping fields but we need to know all
the fields we want to display , if we have more then 20 products we have
to previous one field for each value possible of product : 20 and try to
hide the bank fields
We thought about to to reverse the table by an Stored procedure , but we did
not find a way to build or add dynamic columns to the table
What we need is a mix of table and matrix depending togather on the same
grouping fields .
Hope this is more clear, and thanks to inform us if exist a solution or not
with RS to our problem
Philippe
Espace-NTICYou can nest a table in a matrix (and vice versa). But I don't quite
understand your question. Maybe if you provided some instance data
(examples) it would be easier. Also, if your newsreader allows you to attach
an example instead of posting inline, it might be easier to read.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
news:ek3Uk3UXEHA.3676@.TK2MSFTNGP09.phx.gbl...
> Hi Gurus
> Sorry to insist but the need of an answer begin more urgent
> we have a dataset like this
> Key1 Primary Key of record
> Key2 1st Foreign Key
> Key3 2nd Foreign Key
> Duration in seconds
> Product Pn (field for pivot)
> Qty Qn (value link to Product)
> what we need
> P1--Pn
> Key3 Sum(Duration) Count(Key1)
> Sum(Q1)--Sum(Qn)
> Key3 Key2 Sum(Duration) Count(Key1) Sum(Q1)--Sum(Qn)
> Key3 Key2 Key1 Duration
> Q1--Qn
> Gran Total Sum(Duration) Count(Key1)
> Sum(Q1)--Sum(Qn)
>
> I seach among the messages of this group and i did not found an answer to
> our problem.
> It seems for us, that matrix answer to the need of building multiple
> columns depending on the different content of one field ie product ,
> and
> give the possibility to associate a value link to this field ie Qty in
> our
> example, the informations on rows seems to be only grouping fields, but
> not value of field linked to this grouping field ie duration and count of
> the primary key in our example
> With table i can associate fields to grouping fields but we need to know
> all
> the fields we want to display , if we have more then 20 products we
> have
> to previous one field for each value possible of product : 20 and try to
> hide the bank fields
> We thought about to to reverse the table by an Stored procedure , but we
> did
> not find a way to build or add dynamic columns to the table
> What we need is a mix of table and matrix depending togather on the same
> grouping fields .
> Hope this is more clear, and thanks to inform us if exist a solution or
> not
> with RS to our problem
> Philippe
> Espace-NTIC
>
>
>|||OK, I understand now. We are looking at supporting this natively in a future
release but I don't think you can do this in V1 without some tricks. I'll
forward it on to our trick specialist and get him to post a suggestion...
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
news:OGYAsqkXEHA.556@.tk2msftngp13.phx.gbl...
> Hi Brian
> for an example assume we start from the "Company Sales" report from RS
> samples
> This report has two grouping lines based on ProductCategory field and
> ProductSubcategory field
> This report has dynamic columns based on the different values of two
> nested
> fields OrderYear OrderQuarter
> At those dynamic column is associated a field : sum(SalesValue)
> what we need is ,:
> ADD at the left side of those dynamic columns , TWO STATIC columns one
> giving for example
> the content of SUM(SalesOrderDetail.UnitPrice *
> SalesOrderDetail.OrderQty
> * SalesOrderDetail.UnitPriceDiscount) as DISCOUNT and the other one the
> number of orders : COUNT(DISTINCT SalesOrderHeader.SalesOrderID)
> The content will follow the same grouping as for dynamic columns
> we join an XLS file in zip mode,showing the result we expect
> We have a lot of those reports built actually under Excel , that we want
> to
> move under RS
> Thanks for your help
> Philippe
> Espace-NTIC
>
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> a écrit dans le
> message de news: OWOFEBaXEHA.1152@.TK2MSFTNGP09.phx.gbl...
>> You can nest a table in a matrix (and vice versa). But I don't quite
>> understand your question. Maybe if you provided some instance data
>> (examples) it would be easier. Also, if your newsreader allows you to
> attach
>> an example instead of posting inline, it might be easier to read.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
>> news:ek3Uk3UXEHA.3676@.TK2MSFTNGP09.phx.gbl...
>> > Hi Gurus
>> >
>> > Sorry to insist but the need of an answer begin more urgent
>> >
>> > we have a dataset like this
>> > Key1 Primary Key of record
>> > Key2 1st Foreign Key
>> > Key3 2nd Foreign Key
>> > Duration in seconds
>> > Product Pn (field for pivot)
>> > Qty Qn (value link to Product)
>> >
>> > what we need
>> >
>> > P1--Pn
>> > Key3 Sum(Duration) Count(Key1)
>> > Sum(Q1)--Sum(Qn)
>> > Key3 Key2 Sum(Duration) Count(Key1) Sum(Q1)--Sum(Qn)
>> > Key3 Key2 Key1 Duration
>> > Q1--Qn
>> > Gran Total Sum(Duration) Count(Key1)
>> > Sum(Q1)--Sum(Qn)
>> >
>> >
>> > I seach among the messages of this group and i did not found an answer
> to
>> > our problem.
>> >
>> > It seems for us, that matrix answer to the need of building multiple
>> > columns depending on the different content of one field ie product ,
>> > and
>> > give the possibility to associate a value link to this field ie Qty in
>> > our
>> > example, the informations on rows seems to be only grouping fields,
> but
>> > not value of field linked to this grouping field ie duration and count
> of
>> > the primary key in our example
>> >
>> > With table i can associate fields to grouping fields but we need to
>> > know
>> > all
>> > the fields we want to display , if we have more then 20 products we
>> > have
>> > to previous one field for each value possible of product : 20 and try
> to
>> > hide the bank fields
>> > We thought about to to reverse the table by an Stored procedure , but
>> > we
>> > did
>> > not find a way to build or add dynamic columns to the table
>> >
>> > What we need is a mix of table and matrix depending togather on the
> same
>> > grouping fields .
>> >
>> > Hope this is more clear, and thanks to inform us if exist a solution
>> > or
>> > not
>> > with RS to our problem
>> >
>> > Philippe
>> > Espace-NTIC
>> >
>> >
>> >
>> >
>> >
>>
>
>|||Sleazy Hack # 4123:
1. Put two matrixes side-by-side with the same row groupings.
2. The left matrix should have the static columns. The right matrix should
have the dynamic columns.
3. In the right matrix, remove the text from the row headers and shrink the
row headers to be as narrow as possible.
4. As a final optimization, hand-edit the RDL to set the width of the row
headers to 0 (this can't be done in the design tool). After doing this, it
will be hard to edit the matrix in the design tool, so make sure you do this
after you're 100% done with all of your other edits.
Note: This won't work if you have drilldown enabled on row groupings, since
there's no way to synch up the drilldowns between the two matrixes. If you
need drilldown on rows, you'll need to wait for a future version where we
will support this functionality natively.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ugkhjUmXEHA.3668@.TK2MSFTNGP09.phx.gbl...
> OK, I understand now. We are looking at supporting this natively in a
> future release but I don't think you can do this in V1 without some
> tricks. I'll forward it on to our trick specialist and get him to post a
> suggestion...
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
> news:OGYAsqkXEHA.556@.tk2msftngp13.phx.gbl...
>> Hi Brian
>> for an example assume we start from the "Company Sales" report from RS
>> samples
>> This report has two grouping lines based on ProductCategory field and
>> ProductSubcategory field
>> This report has dynamic columns based on the different values of two
>> nested
>> fields OrderYear OrderQuarter
>> At those dynamic column is associated a field : sum(SalesValue)
>> what we need is ,:
>> ADD at the left side of those dynamic columns , TWO STATIC columns one
>> giving for example
>> the content of SUM(SalesOrderDetail.UnitPrice *
>> SalesOrderDetail.OrderQty
>> * SalesOrderDetail.UnitPriceDiscount) as DISCOUNT and the other one the
>> number of orders : COUNT(DISTINCT SalesOrderHeader.SalesOrderID)
>> The content will follow the same grouping as for dynamic columns
>> we join an XLS file in zip mode,showing the result we expect
>> We have a lot of those reports built actually under Excel , that we want
>> to
>> move under RS
>> Thanks for your help
>> Philippe
>> Espace-NTIC
>>
>> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> a écrit dans le
>> message de news: OWOFEBaXEHA.1152@.TK2MSFTNGP09.phx.gbl...
>> You can nest a table in a matrix (and vice versa). But I don't quite
>> understand your question. Maybe if you provided some instance data
>> (examples) it would be easier. Also, if your newsreader allows you to
>> attach
>> an example instead of posting inline, it might be easier to read.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
>> news:ek3Uk3UXEHA.3676@.TK2MSFTNGP09.phx.gbl...
>> > Hi Gurus
>> >
>> > Sorry to insist but the need of an answer begin more urgent
>> >
>> > we have a dataset like this
>> > Key1 Primary Key of record
>> > Key2 1st Foreign Key
>> > Key3 2nd Foreign Key
>> > Duration in seconds
>> > Product Pn (field for pivot)
>> > Qty Qn (value link to Product)
>> >
>> > what we need
>> >
>> > P1--Pn
>> > Key3 Sum(Duration) Count(Key1)
>> > Sum(Q1)--Sum(Qn)
>> > Key3 Key2 Sum(Duration) Count(Key1)
>> > Sum(Q1)--Sum(Qn)
>> > Key3 Key2 Key1 Duration
>> > Q1--Qn
>> > Gran Total Sum(Duration) Count(Key1)
>> > Sum(Q1)--Sum(Qn)
>> >
>> >
>> > I seach among the messages of this group and i did not found an answer
>> to
>> > our problem.
>> >
>> > It seems for us, that matrix answer to the need of building multiple
>> > columns depending on the different content of one field ie product
>> > ,
>> > and
>> > give the possibility to associate a value link to this field ie Qty
>> > in
>> > our
>> > example, the informations on rows seems to be only grouping fields,
>> but
>> > not value of field linked to this grouping field ie duration and
>> > count
>> of
>> > the primary key in our example
>> >
>> > With table i can associate fields to grouping fields but we need to
>> > know
>> > all
>> > the fields we want to display , if we have more then 20 products we
>> > have
>> > to previous one field for each value possible of product : 20 and try
>> to
>> > hide the bank fields
>> > We thought about to to reverse the table by an Stored procedure , but
>> > we
>> > did
>> > not find a way to build or add dynamic columns to the table
>> >
>> > What we need is a mix of table and matrix depending togather on the
>> same
>> > grouping fields .
>> >
>> > Hope this is more clear, and thanks to inform us if exist a solution
>> > or
>> > not
>> > with RS to our problem
>> >
>> > Philippe
>> > Espace-NTIC
>> >
>> >
>> >
>> >
>> >
>>
>>
>>
>|||Hi Brian,
in fact we need to drill dow on rows.
I have some ideas in mind to turn around the problem like
linking one report based on first level grouping to another one based on
second level grouping and so on
Other way' with a table object is it possible to add dynamicaly new columns
coming from a dataset calilng a crosstab PS building the columns'
Anyway thanks a lot for your help and congratluation to RS Team for this
product giving us the ability to add new fonctionalities of reporting to our
web application
Philippe
Espace-NTIC
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> a écrit dans le
message de news: uHTPwtsXEHA.3640@.TK2MSFTNGP11.phx.gbl...
> Sleazy Hack # 4123:
> 1. Put two matrixes side-by-side with the same row groupings.
> 2. The left matrix should have the static columns. The right matrix
should
> have the dynamic columns.
> 3. In the right matrix, remove the text from the row headers and shrink
the
> row headers to be as narrow as possible.
> 4. As a final optimization, hand-edit the RDL to set the width of the row
> headers to 0 (this can't be done in the design tool). After doing this,
it
> will be hard to edit the matrix in the design tool, so make sure you do
this
> after you're 100% done with all of your other edits.
> Note: This won't work if you have drilldown enabled on row groupings,
since
> there's no way to synch up the drilldowns between the two matrixes. If
you
> need drilldown on rows, you'll need to wait for a future version where we
> will support this functionality natively.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ugkhjUmXEHA.3668@.TK2MSFTNGP09.phx.gbl...
> > OK, I understand now. We are looking at supporting this natively in a
> > future release but I don't think you can do this in V1 without some
> > tricks. I'll forward it on to our trick specialist and get him to post a
> > suggestion...
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> > "Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
> > news:OGYAsqkXEHA.556@.tk2msftngp13.phx.gbl...
> >> Hi Brian
> >>
> >> for an example assume we start from the "Company Sales" report from RS
> >> samples
> >>
> >> This report has two grouping lines based on ProductCategory field and
> >> ProductSubcategory field
> >>
> >> This report has dynamic columns based on the different values of two
> >> nested
> >> fields OrderYear OrderQuarter
> >> At those dynamic column is associated a field : sum(SalesValue)
> >>
> >> what we need is ,:
> >> ADD at the left side of those dynamic columns , TWO STATIC columns one
> >> giving for example
> >> the content of SUM(SalesOrderDetail.UnitPrice *
> >> SalesOrderDetail.OrderQty
> >> * SalesOrderDetail.UnitPriceDiscount) as DISCOUNT and the other one
the
> >> number of orders : COUNT(DISTINCT SalesOrderHeader.SalesOrderID)
> >> The content will follow the same grouping as for dynamic columns
> >>
> >> we join an XLS file in zip mode,showing the result we expect
> >>
> >> We have a lot of those reports built actually under Excel , that we
want
> >> to
> >> move under RS
> >>
> >> Thanks for your help
> >>
> >> Philippe
> >> Espace-NTIC
> >>
> >>
> >> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> a écrit dans le
> >> message de news: OWOFEBaXEHA.1152@.TK2MSFTNGP09.phx.gbl...
> >> You can nest a table in a matrix (and vice versa). But I don't quite
> >> understand your question. Maybe if you provided some instance data
> >> (examples) it would be easier. Also, if your newsreader allows you to
> >> attach
> >> an example instead of posting inline, it might be easier to read.
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Philippe" <ph.boussaroque@.Espace-NTIC.com> wrote in message
> >> news:ek3Uk3UXEHA.3676@.TK2MSFTNGP09.phx.gbl...
> >> > Hi Gurus
> >> >
> >> > Sorry to insist but the need of an answer begin more urgent
> >> >
> >> > we have a dataset like this
> >> > Key1 Primary Key of record
> >> > Key2 1st Foreign Key
> >> > Key3 2nd Foreign Key
> >> > Duration in seconds
> >> > Product Pn (field for pivot)
> >> > Qty Qn (value link to Product)
> >> >
> >> > what we need
> >> >
> >> > P1--Pn
> >> > Key3 Sum(Duration) Count(Key1)
> >> > Sum(Q1)--Sum(Qn)
> >> > Key3 Key2 Sum(Duration) Count(Key1)
> >> > Sum(Q1)--Sum(Qn)
> >> > Key3 Key2 Key1 Duration
> >> > Q1--Qn
> >> > Gran Total Sum(Duration) Count(Key1)
> >> > Sum(Q1)--Sum(Qn)
> >> >
> >> >
> >> > I seach among the messages of this group and i did not found an
answer
> >> to
> >> > our problem.
> >> >
> >> > It seems for us, that matrix answer to the need of building
multiple
> >> > columns depending on the different content of one field ie
product
> >> > ,
> >> > and
> >> > give the possibility to associate a value link to this field ie Qty
> >> > in
> >> > our
> >> > example, the informations on rows seems to be only grouping fields,
> >> but
> >> > not value of field linked to this grouping field ie duration and
> >> > count
> >> of
> >> > the primary key in our example
> >> >
> >> > With table i can associate fields to grouping fields but we need to
> >> > know
> >> > all
> >> > the fields we want to display , if we have more then 20 products
we
> >> > have
> >> > to previous one field for each value possible of product : 20 and
try
> >> to
> >> > hide the bank fields
> >> > We thought about to to reverse the table by an Stored procedure ,
but
> >> > we
> >> > did
> >> > not find a way to build or add dynamic columns to the table
> >> >
> >> > What we need is a mix of table and matrix depending togather on the
> >> same
> >> > grouping fields .
> >> >
> >> > Hope this is more clear, and thanks to inform us if exist a solution
> >> > or
> >> > not
> >> > with RS to our problem
> >> >
> >> > Philippe
> >> > Espace-NTIC
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>