Resolving an attribute member from a member property

What will be the fastest way to resolve an atttribute member from one of its member properties, e.g. the key value from the source database assuming that the attribute key uses the dimension surrogate key for its KeyColumn?

You can use the member properties Key0, Key1, ... KeyN. Each of theses represents one of the columns in the attributes key columns.

E.g.

select [Time By Day].[Years].Members dimension properties key0, key1 on 0
from budget

|||

This is not what I was asking. Consider the following key attribute and member properties

Product (attribute key)

- ProductOriginal Key (member property and/or attribute)

The question was how can I resolve the Product attribute by knowing the ProductOriginalKey member property?

|||

Since there is many-to-one relationship between related attributes, there are actually multiple products with the same ProductOrdinal. You can easily get the set of all Product attribute members which have certain ProductOrdinal value, by using

Exists(Product.Product.Product.MEMBERS, Product.ProductOrdinal.&[key_of_ordinal])

HTH,

Mosha (http://www.mosha.com/msolap)

|||Thank you. Unfortunately, Exists doesn't seem to work as an allowed set security filter with parent-child dimensions as I reported on connect with CTP2 SP2. Neither does crossjoining works (*). A bug perhaps? Meanwhile, any idea what works to crossjoin two sets to create an allowed set?|||Sorry - I didn't see you mentioning before that it was Parent-Child. Can you please give more concrete description of the structure of dimension, which attributes you are securing etc. Example from Adventure Works would be most appreciated.|||

The link in my previous post demonstrates how this can be reproduced with AW. Assuming the AdventureWorks sample cube:

1. Create a new role Reviewer
2. Create an allowed set on the Employee dimension (cube level) as follows:
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997])
3. Browse the cube under Reviewer by Employee. Notice that all employees are returned. In comparison, the following query returns only two employees and their supervisors (as it should).
select [Measures].[Reseller Sales Amount] on 0,
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997]) on 1
from [Adventure Works]

4. Trying [Employee].[Employees].Members * [Employee].[Hire Year].&[1997] throws an exception, as well as
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

|||

Well, since you are defining dimension security - there is no need to ever write Exists as expression of any allowed set. Dimension security will do Exists itself. So all you need to do is to define security on Hire Year attribute instead of trying to do it on Employees attribute. Simply specify [Employee].[Hire Year].&[1997] as expression for the Allowed Set on Hire Year attribute - and everything will work as you need.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

This works! Thank you so much for your help. I'd appreciate it if you could answer one more question for me. Let's say that when the user slices by the Sales Territory Region attribute hierarchy of the Sales Territory dimension, I want the user to see only the regions serviced by the allowed employees only. Is the following allowed set expression the most efficient way to do so?

Exists([Sales Territory].[Sales Territory Region].[Sales Territory Region].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

I understand that I cannot use [Employee].[Hire Year].Members since security polices are not applied yet so essentially I have no other choice but to carry the Employee filter to the other dimensions. I am just concerned that with large dimensions this may incur significant performance penalty.

|||Yes, I beleive this is the best way to do it.

Resolving an attribute member from a member property

What will be the fastest way to resolve an atttribute member from one of its member properties, e.g. the key value from the source database assuming that the attribute key uses the dimension surrogate key for its KeyColumn?

You can use the member properties Key0, Key1, ... KeyN. Each of theses represents one of the columns in the attributes key columns.

E.g.

select [Time By Day].[Years].Members dimension properties key0, key1 on 0
from budget

|||

This is not what I was asking. Consider the following key attribute and member properties

Product (attribute key)

- ProductOriginal Key (member property and/or attribute)

The question was how can I resolve the Product attribute by knowing the ProductOriginalKey member property?

|||

Since there is many-to-one relationship between related attributes, there are actually multiple products with the same ProductOrdinal. You can easily get the set of all Product attribute members which have certain ProductOrdinal value, by using

Exists(Product.Product.Product.MEMBERS, Product.ProductOrdinal.&[key_of_ordinal])

HTH,

Mosha (http://www.mosha.com/msolap)

|||Thank you. Unfortunately, Exists doesn't seem to work as an allowed set security filter with parent-child dimensions as I reported on connect with CTP2 SP2. Neither does crossjoining works (*). A bug perhaps? Meanwhile, any idea what works to crossjoin two sets to create an allowed set?|||Sorry - I didn't see you mentioning before that it was Parent-Child. Can you please give more concrete description of the structure of dimension, which attributes you are securing etc. Example from Adventure Works would be most appreciated.|||

The link in my previous post demonstrates how this can be reproduced with AW. Assuming the AdventureWorks sample cube:

1. Create a new role Reviewer
2. Create an allowed set on the Employee dimension (cube level) as follows:
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997])
3. Browse the cube under Reviewer by Employee. Notice that all employees are returned. In comparison, the following query returns only two employees and their supervisors (as it should).
select [Measures].[Reseller Sales Amount] on 0,
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997]) on 1
from [Adventure Works]

4. Trying [Employee].[Employees].Members * [Employee].[Hire Year].&[1997] throws an exception, as well as
Exists([Employee].[Employees].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

|||

Well, since you are defining dimension security - there is no need to ever write Exists as expression of any allowed set. Dimension security will do Exists itself. So all you need to do is to define security on Hire Year attribute instead of trying to do it on Employees attribute. Simply specify [Employee].[Hire Year].&[1997] as expression for the Allowed Set on Hire Year attribute - and everything will work as you need.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

This works! Thank you so much for your help. I'd appreciate it if you could answer one more question for me. Let's say that when the user slices by the Sales Territory Region attribute hierarchy of the Sales Territory dimension, I want the user to see only the regions serviced by the allowed employees only. Is the following allowed set expression the most efficient way to do so?

Exists([Sales Territory].[Sales Territory Region].[Sales Territory Region].Members, [Employee].[Hire Year].&[1997], 'Reseller Sales')

I understand that I cannot use [Employee].[Hire Year].Members since security polices are not applied yet so essentially I have no other choice but to carry the Employee filter to the other dimensions. I am just concerned that with large dimensions this may incur significant performance penalty.

|||Yes, I beleive this is the best way to do it.

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

Resetting Key Column Numeration from SQL Tables

I used a lot of fictitious data in order to test the code of my web site. But I noticed that when I deleted all fictitious data from the tables, the primary key columns keep creating automatically numbers that are above the last one before all data was erased instead of starting again from number 1. How do I reset this automatic numeration in order to restart from 1 again ?

You have to use the DBCC Checkpoint command

Refer this: http://www.sqlteam.com/item.asp?ItemID=8003

|||Check out BOL for DBCC CHECKIDENT command.|||

Another way to do this is to use Truncate Table Command rather than delete.

This will reset identity.

resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?
Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?
|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary k
ey.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE
instead of DELETE, the
identity will be reset for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary
key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?

resetting identiy seed

I have designated a instId column in a table as an identity column/primary key.
Having recently added loads of data and truncating the table/deleting the
table, how could I reset the identiy seed such that instId=1 for the next
record I insert?Check out DBCC CHECKIDENT. Also, if you empty the table using TRUNCATE TABLE instead of DELETE, the
identity will be reset for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Check out DBCC CHECKIDENT in SQL BOL.
ALI
Patrick wrote:
> I have designated a instId column in a table as an identity column/primary key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||Patrick
If you issue TRUNCATE Table SQL Server will reset an Identity property
otherwise take look at DBCC CHECKIDENT command in the BOL
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?|||DBCC CHECKIDENT
Checks the current identity value for the specified table and, if needed,
corrects the identity value.
Syntax
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
See books online for some good examples.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:D455778A-C1CD-4A88-8A33-B2F2EFFAEE7F@.microsoft.com...
>I have designated a instId column in a table as an identity column/primary
>key.
> Having recently added loads of data and truncating the table/deleting the
> table, how could I reset the identiy seed such that instId=1 for the next
> record I insert?sql

resetting Identity Seed on change of primary key

I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset itself to the value of 1 when the Primary key changes. For example if I add the following 3 records to the table: 1st record - Primary key is 1, foreign key is 1; 2nd record - Primary key is 1, foreign key is 2; third record - Primary key is 2, foreign key is 3, but I want the foreign key to be reset to 1.

Quote:

Originally Posted by Rick Kay

I have a table that has a Primary key and a foreign key. The primary key is NOT an Identity field, however, the foreign key is. I would like to know if there is a way to have the foreign key reset itself to the value of 1 when the Primary key changes. For example if I add the following 3 records to the table: 1st record - Primary key is 1, foreign key is 1; 2nd record - Primary key is 1, foreign key is 2; third record - Primary key is 2, foreign key is 3, but I want the foreign key to be reset to 1.

You should read the topic
DBCC CHECKIDENT
in books on-line help. If I understand correctly what you are trying, it won't work.

You will have to write code to generate your own FK values.

Tom.|||

Quote:

Originally Posted by folderol

You should read the topic
DBCC CHECKIDENT
in books on-line help. If I understand correctly what you are trying, it won't work.

You will have to write code to generate your own FK values.

Tom.

Tom, that's exactly what I thought, but I wanted to be sure someone else agreed with me. Thanks for your response.|||This will reseed the identity no for a column in a table.

declare @.intCounter int
set @.intCounter = 0
update (YOUR_TABLE)
SET @.intCounter = (YOUR_COLUMN) = @.intCounter + 1

Reseting the unique id if a table.

I'm building a web site. there is a database.

I've set a primary key and unique of a field.also I set it to auto numbering.

Everytime I insert a record , that field will increase 1 (type bigint , start from 1).

After lots time of inserting record , the id going to be larger number.. I wondering how can I reset that to zero?

Run query Truncate Table YuorTableName

Reseting automatic increments on primary keys

Hello
I've a SQL database that has several tables and relathionships between them.
Most of my tables have
the primary key set to automatic increments using identity on table properti
es.
It all did well in testing fases...
My problem is that when going to production how can I reset those identities
values on several primary keys to have my keys strat on 1 instead of the la
st number used in testing...
my procedure, that didn't worked, was first erasing all tables, second conve
rt all primary keys with identity set to no identity, third save table, fort
h put primary key back to identity increments, fifth save table again
The first record inserted, instead of having primary key with 1 brought prim
ary key with 80 or so...
how can I reset those values?!?!
best regards
Jorge RibeiroHi Jorge,
You can reset the current value of the identity column with:
DBCC CHECKIDENT('<table name>', RESEED, 1)
However, you shouldn't attach any meaning to the value of the identity
column for a number of reasons, so whether it starts at 1 or 80 shouldn't
make any difference.
Jacco Schalkwijk
SQL Server MVP
"Jorge Ribeiro" <jorge.ribeiro@.irsocial.mj.pt> wrote in message
news:99FB747F-DAED-4264-886C-FF5D28FDF426@.microsoft.com...

quote:

---

> Hello
> I've a SQL database that has several tables and relathionships between

---

them. Most of my tables have

quote:

---

> the primary key set to automatic increments using identity on table

---

properties.

quote:

---

> It all did well in testing fases...
> My problem is that when going to production how can I reset those

---

identities values on several primary keys to have my keys strat on 1 instead
of the last number used in testing...

quote:

---

> my procedure, that didn't worked, was first erasing all tables, second

---

convert all primary keys with identity set to no identity, third save table,
forth put primary key back to identity increments, fifth save table again

quote:

---

> The first record inserted, instead of having primary key with 1 brought

---

primary key with 80 or so...

quote:

---

> how can I reset those values?!?!
> best regards
> Jorge Ribeiro

---

|||Jacco is correct. You can use the CHECKIDENT function to reseed the
identity. However this will not change any of the current values. To do
that you will have to move the data to a different table, reseed the
identity and then import the data back in.
Rand
This posting is provided "as is" with no warranties and confers no rights.

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.

Reset pimary key to 1

Hi Forum, I have a .mdf that I have used to test SQL data app. I want to reuse the .mdf; delete all added data and reset Customer_ID primary colomn back to 1. All good info appreciated thanks Paul

Hello there,

Try execute the statement TRUNCATE TABLE <table name>.

cheers,

Eric

Reset Id field

I have a few SQL tables that use an auto incrementing integer key field, ie it has 'is identify' set to yes

The tables have been used for testing while the application was developed.

I plan to delete all data from these tables when the application goes live. Is there a way to start SQL counting from 1 again without deleting and re-creating thr tables?

Check out Books on line for DBCC CHECKIDENT.

|||

Excellent -thanks for that - searched using those terms and found what I needed.

Regards

Clive

Required Role for SQL 2005 Security operations

We need a user with db_owner permissions plus permission for these
operations:
Create db master key
Create certificate
Create symmetric key
And decrypt / encrypt by key
should it be securityadmin?
YanivHi ,Yaniv
BOL says , if you want the user to perfom CREATE CERTIFICATE for example
,so
'Requires CREATE CERTIFICATE permission on the database.'
Read the BOL for each statement that you need and make a decision. You may
want to consider create a ROLE which will contain all those requierd
permissions
<yaniv.harpaz@.gmail.com> wrote in message
news:1153658166.225912.167740@.p79g2000cwp.googlegroups.com...
> We need a user with db_owner permissions plus permission for these
> operations:
> Create db master key
> Create certificate
> Create symmetric key
> And decrypt / encrypt by key
> should it be securityadmin?
> Yaniv
>|||Thank You Uri,
Yaniv
Uri Dimant wrote:
> Hi ,Yaniv
> BOL says , if you want the user to perfom CREATE CERTIFICATE for example
> ,so
> 'Requires CREATE CERTIFICATE permission on the database.'
> Read the BOL for each statement that you need and make a decision. You may
> want to consider create a ROLE which will contain all those requierd
> permissions
>
>
>
>
>
> <yaniv.harpaz@.gmail.com> wrote in message
> news:1153658166.225912.167740@.p79g2000cwp.googlegroups.com...
> > We need a user with db_owner permissions plus permission for these
> > operations:
> > Create db master key
> > Create certificate
> > Create symmetric key
> > And decrypt / encrypt by key
> >
> > should it be securityadmin?
> >
> > Yaniv
> >

requesting query optimization help

so here are the tables, with a little info about each one:
create table areas (
areaid uniqueidentifier primary key not null, -- PK, clustered
areanumber int identity(1, 1) not null, -- indexed
areaname varchar(100) not null,
regionnumber int not null) -- FK to regions table PK
the areas table has about 16000 rows, and three unindexed columns not
listed here.
create table regions (
regionnumber int identity(1, 1) primary key not null, -- PK, clustered
regionname varchar(100) not null,
parentregionnumber int not null) -- FK to regions table PK
the regions table has about 800 rows, and about 12 columns not listed
here
create table regionrelationships (
regionnumber int primary key not null,
relatedregionnumber int primary key not null) -- PK is clustered
the regionrelationships table has about 1700 rows, and no unlisted
columns
and here are the various versions of the stored procedure query i'm
trying to optimize with the execution plan costs listed with each one.
the original stored procedure body looked liked this:
SELECT DISTINCT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
WHERE a.regionnumber = 56
ORDER BY a.areaname
cost: .205
then business rules caused a change in the stored procedure body, and
the cost went way up:
SELECT DISTINCT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
WHERE a.regionnumber = 56 OR a.regionnumber IN (
SELECT relatedregionnumber
FROM regionrelationships
WHERE regionnumber = 56)
ORDER BY a.areaname
cost: .518
i've tweaked a bunch of stuff, and this is the best i've been able to
do so far:
SELECT DISTINCT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
LEFT JOIN regionrelationships AS r
ON r.relatedregionnumber = a.regionnumber
WHERE a.regionnumber = 56 OR r.regionnumber = 56
ORDER BY areaname
cost: .417
most of the remaining cost is in a hash right outer join (35%, mostly
CPU) and a cluster scan on areas (44%, mostly I/O)
i've tried a lot of things, including:
* using the areanumber as the primary key of the areas table instead of
the areaid, to make the cluster index smaller, to try to make cluster
scans faster (no effect at all)
* creating clustered indexes on columns other than the primary keys,
such as the regionrelationships regionnumber column, to try to make
hash scans faster (no effect at all)
so i'm just wondering, i'm new to this aspect of optimization. is there
anything here that stands out as something i can do to improve the
performance of this stored procedure? hopefully i've provided enough
information.
thanks in advance for any help
jasonOn 15 Mar 2006 13:45:49 -0800, jason wrote:
(snip)
>then business rules caused a change in the stored procedure body, and
>the cost went way up:
>SELECT DISTINCT a.areanumber, a.areaname
>FROM areas AS a WITH (NOLOCK)
>WHERE a.regionnumber = 56 OR a.regionnumber IN (
> SELECT relatedregionnumber
> FROM regionrelationships
> WHERE regionnumber = 56)
>ORDER BY a.areaname
>cost: .518
>
>i've tweaked a bunch of stuff, and this is the best i've been able to
>do so far:
(snip)
Hi Jason,
Try this version instead:
SELECT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
WHERE a.regionnumber = 56
UNION ALL
SELECT a.areanumber, a.areaname
FROM areas AS a
INNER JOIN regionrelationships AS r
ON r.relatedregionnumber = a.regionnumber
WHERE r.regionnumber = 56
AND r.relatedregionnumber <> 56
ORDER BY areaname
Indexing suggestions:
- In regionrelationships, make sure that regionnumber is the first of
the two columns in the primary key.
- In areas, have a clustered index with regionnumber as first column. Or
(maybe even better) a nonclustered index on (regionnumber, areaname,
areanumber).
Hugo Kornelis, SQL Server MVP|||thank you very much sir!

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
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>