I have a table with no index, When i try to create one to Check data
integrity on 3 column that suppose they are unique collection
(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
I know i may ignore duplicate values but i want to find it to delete it..
So How could i write query to find duplicate rows
Select Court_ID,Case_No,Case_Date from Case Where...'This script had written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:%23FjhHd17FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a table with no index, When i try to create one to Check data
>integrity on 3 column that suppose they are unique collection
>(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
>|||Islamegy wrote:
> I have a table with no index, When i try to create one to Check data
> integrity on 3 column that suppose they are unique collection
> (Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete
> it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
select Court_ID,Case_No,Case_Date, count(*)
group by Court_ID,Case_No,Case_Date
having count(*) > 1
Kind regards
robert
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Hitting on indexes
I have a number of tables with filled with duplicate indexed columns (Example
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.com/20021209/techspace1.shtml
Thanks
GYK
"John" wrote:
> I have a number of tables with filled with duplicate indexed columns (Example
> 1 below). Is it better to have a column in one index per table rather then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.com/20021209/techspace1.shtml
Thanks
GYK
"John" wrote:
> I have a number of tables with filled with duplicate indexed columns (Example
> 1 below). Is it better to have a column in one index per table rather then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>
Hitting on indexes
I have a number of tables with filled with duplicate indexed columns (Exampl
e
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.co...echspace1.shtml
Thanks
GYK
"John" wrote:
> I have a number of tables with filled with duplicate indexed columns (Exam
ple
> 1 below). Is it better to have a column in one index per table rather the
n
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column orde
r
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>
e
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.co...echspace1.shtml
Thanks
GYK
"John" wrote:
> I have a number of tables with filled with duplicate indexed columns (Exam
ple
> 1 below). Is it better to have a column in one index per table rather the
n
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column orde
r
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>
Hitting on indexes
I have a number of tables with filled with duplicate indexed columns (Example
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2
It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.com...chspace1.shtml
Thanks
GYK
"John" wrote:
> I have a number of tables with filled with duplicate indexed columns (Example
> 1 below). Is it better to have a column in one index per table rather then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>
|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>
1 below). Is it better to have a column in one index per table rather then
in 3 or 4 different indexes on the same table?
Will SQL pick and choose from different indexes (regardless of column order
in the indexes) to find the indexes it needs?
Exmaple 1:
Indexed field (Userid being the primary key)
Userid, UserFirstName, UserPhoneNumber
UserId, UserAddress1, UserAddress2
UserFirstName, UserAddress2
It really depends on what SQL statements are issued against this table.
Too many indexes harms the update/insert statements, however in many cases
it will not reduce the SELECT query performance.
You may want to consolidate all the SQL (SELECT/UPDATE/INSERT) and find out
which statments are getting affected.
Check this out too:
http://www.expresscomputeronline.com...chspace1.shtml
Thanks
GYK
"John" wrote:
> I have a number of tables with filled with duplicate indexed columns (Example
> 1 below). Is it better to have a column in one index per table rather then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>
|||> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
Index column order is important for seeks and ordered scans. Whether or not
an index is useful depends on the query particulars. The optimizer
evaluates the various execution plans possibility and chooses the least
costly one.
If the high-order column of an index is used in a predicate, it is likely to
be more useful than indexes that contain that column in other positions
because SQL Server can use seek operations based on high-order columns.
Data cardinality (statistics) are also considered because the most efficient
method can vary depending on actual data.
Non-clustered indexes can also cover a query, thereby eliminating access to
data pages. This can be especially useful for queries run frequently,
select only a few columns and return many rows. However, note that all of
your indexes except the presumably clustered primary key index probably
won't be used for single-row queries based on UserId that return columns in
addition to the indexed ones.
It's usually best to start with single-column indexes unless you determine
that composite indexes are more useful. Remember that the clustered index
key are stored in all non-clustered indexes. Assuming your primary key is
clustered, all 3 of you non-clustered can cover the UserId column, even
though it is not explicitly included in the UserFirstName, UserAddress2
index.
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.discussions.microsoft.com> wrote in message
news:82CA1DF2-3DD6-495F-A6EC-DA3C58A8999A@.microsoft.com...
>I have a number of tables with filled with duplicate indexed columns
>(Example
> 1 below). Is it better to have a column in one index per table rather
> then
> in 3 or 4 different indexes on the same table?
> Will SQL pick and choose from different indexes (regardless of column
> order
> in the indexes) to find the indexes it needs?
> Exmaple 1:
> Indexed field (Userid being the primary key)
> Userid, UserFirstName, UserPhoneNumber
> UserId, UserAddress1, UserAddress2
> UserFirstName, UserAddress2
>
hit locations in full text
I'm working on an aplication that could benefit from having the hit
location inside of a column. For example
A Column has
"United Stated Education System : Educational Stuff"
The full text search might match Education and Edicational from the
word eudcation. But I can not see any clear way to determine what
word(s) were matches and how they were used in the ranking. Is there
any known way to do this?
Thanks,
madmike
No, you have to implement a version of Porter Stemming algorithm to do
something like this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<madmik3@.gmail.com> wrote in message
news:1159463316.911169.56710@.e3g2000cwe.googlegrou ps.com...
> I'm working on an aplication that could benefit from having the hit
> location inside of a column. For example
> A Column has
> "United Stated Education System : Educational Stuff"
>
> The full text search might match Education and Edicational from the
> word eudcation. But I can not see any clear way to determine what
> word(s) were matches and how they were used in the ranking. Is there
> any known way to do this?
> Thanks,
> madmike
>
location inside of a column. For example
A Column has
"United Stated Education System : Educational Stuff"
The full text search might match Education and Edicational from the
word eudcation. But I can not see any clear way to determine what
word(s) were matches and how they were used in the ranking. Is there
any known way to do this?
Thanks,
madmike
No, you have to implement a version of Porter Stemming algorithm to do
something like this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<madmik3@.gmail.com> wrote in message
news:1159463316.911169.56710@.e3g2000cwe.googlegrou ps.com...
> I'm working on an aplication that could benefit from having the hit
> location inside of a column. For example
> A Column has
> "United Stated Education System : Educational Stuff"
>
> The full text search might match Education and Edicational from the
> word eudcation. But I can not see any clear way to determine what
> word(s) were matches and how they were used in the ranking. Is there
> any known way to do this?
> Thanks,
> madmike
>
Wednesday, March 21, 2012
High Latch Waits.. what would they indicate ?
If there is high contention to write to a single page especially since it
has an identity column and everything wants to be written to the same page
in one go, would that lead to latch waits and if so, how can we circumvent ?
Right now, we have a flurry of application servers that want to write to
this one particular table and at times its very heavy contention..
Does SQL server lets multiple apps to write to the same page for the same
table at the same time ? I thought locks would prevent it from happening..no
?If that's the problem, the obvious question is, can you avoid or reduce
writing to the same page?
Linchi
"Hassan" wrote:
> If there is high contention to write to a single page especially since it
> has an identity column and everything wants to be written to the same page
> in one go, would that lead to latch waits and if so, how can we circumvent ?
> Right now, we have a flurry of application servers that want to write to
> this one particular table and at times its very heavy contention..
> Does SQL server lets multiple apps to write to the same page for the same
> table at the same time ? I thought locks would prevent it from happening..no
> ?
>
>
has an identity column and everything wants to be written to the same page
in one go, would that lead to latch waits and if so, how can we circumvent ?
Right now, we have a flurry of application servers that want to write to
this one particular table and at times its very heavy contention..
Does SQL server lets multiple apps to write to the same page for the same
table at the same time ? I thought locks would prevent it from happening..no
?If that's the problem, the obvious question is, can you avoid or reduce
writing to the same page?
Linchi
"Hassan" wrote:
> If there is high contention to write to a single page especially since it
> has an identity column and everything wants to be written to the same page
> in one go, would that lead to latch waits and if so, how can we circumvent ?
> Right now, we have a flurry of application servers that want to write to
> this one particular table and at times its very heavy contention..
> Does SQL server lets multiple apps to write to the same page for the same
> table at the same time ? I thought locks would prevent it from happening..no
> ?
>
>
Wednesday, March 7, 2012
Hiding/Showing columns based on the columns present in the dataset
I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.
Can I do that?
Any reply showing me the right way is appricited.
-Thanks,
Digs
In the Column properties, expand the Visibility section, and then click on Hidden, and choose Expression.
In the expression, use the same input parameters that you are using to build the column list in your SQL Statement to set the property.
An example expression might look like this:
=iif(Parameters!ShowColumn1.Value= True, false, true)
Where Parameters!ShowColumn1 is a report parameter that you have defined,
This should get you started...
BobP
|||Hi,
Thanks for the reply..Need bit change =IIf(Fields!Collection.IsMissing=True,True,False) In Visibility -- > Hidden
-Thanks,
Digs
Sunday, February 26, 2012
Hiding the Title Output ?
is there a way to suppress the output of the title of each column ?
i.e.
if that query would normally return
Name | Age | Rank
John 12 Lt
Paul 11 Cpt
George 10 Sgt
Ringo 6 Prt
Can i get it to return just:
John 12 Lt
Paul 11 Cpt
George 10 Sgt
Ringo 6 Prtwhere ru runing this script? in QA?|||yes you can
select name as ' ' ,age as ' ',rank as ' ' from table_name
Originally posted by ontheDB
is there a way to suppress the output of the title of each column ?
i.e.
if that query would normally return
Name | Age | Rank
John 12 Lt
Paul 11 Cpt
George 10 Sgt
Ringo 6 Prt
Can i get it to return just:
John 12 Lt
Paul 11 Cpt
George 10 Sgt
Ringo 6 Prt|||actually the proposed query will return this:
| | |
John 12 Lt
Paul 11 Cpt
George 10 Sgt
Ringo 6 Prt
this means that the heading will not be suppressed, but rather renamed to empty strings.
in order to suppress the headings uncheck Print Column Headers in Tools/Options/Results of your query analyzer.|||Originally posted by ms_sql_dba
actually the proposed query will return this:
| | |
John 12 Lt
Paul 11 Cpt
George 10 Sgt
Ringo 6 Prt
this means that the heading will not be suppressed, but rather renamed to empty strings.
in order to suppress the headings uncheck Print Column Headers in Tools/Options/Results of your query analyzer.
This question is related to the reply, is it possible to "uncheck Print Column Headers" with actually going to Tools>Options>Results? Is there is a script that would do that same? For example in Oracle you can say:
Set Headings off
Is this possible in Query Analyzer 2000?
i.e.
if that query would normally return
Name | Age | Rank
John 12 Lt
Paul 11 Cpt
George 10 Sgt
Ringo 6 Prt
Can i get it to return just:
John 12 Lt
Paul 11 Cpt
George 10 Sgt
Ringo 6 Prtwhere ru runing this script? in QA?|||yes you can
select name as ' ' ,age as ' ',rank as ' ' from table_name
Originally posted by ontheDB
is there a way to suppress the output of the title of each column ?
i.e.
if that query would normally return
Name | Age | Rank
John 12 Lt
Paul 11 Cpt
George 10 Sgt
Ringo 6 Prt
Can i get it to return just:
John 12 Lt
Paul 11 Cpt
George 10 Sgt
Ringo 6 Prt|||actually the proposed query will return this:
| | |
John 12 Lt
Paul 11 Cpt
George 10 Sgt
Ringo 6 Prt
this means that the heading will not be suppressed, but rather renamed to empty strings.
in order to suppress the headings uncheck Print Column Headers in Tools/Options/Results of your query analyzer.|||Originally posted by ms_sql_dba
actually the proposed query will return this:
| | |
John 12 Lt
Paul 11 Cpt
George 10 Sgt
Ringo 6 Prt
this means that the heading will not be suppressed, but rather renamed to empty strings.
in order to suppress the headings uncheck Print Column Headers in Tools/Options/Results of your query analyzer.
This question is related to the reply, is it possible to "uncheck Print Column Headers" with actually going to Tools>Options>Results? Is there is a script that would do that same? For example in Oracle you can say:
Set Headings off
Is this possible in Query Analyzer 2000?
Hiding Subtotal in Matrix
Hi,
I have one column group and 3 columns under it in a matrix. I added subtotal
to that column group and now all the 3 columns are summarized and shown. Now
I want to hide one column summary(The other 2 column summary should be
shown). How to do that?
TIA,
SamYou will need to control the Visibility of the textboxes that makeup the
column by using an expression similar to
=iif(InScope("MatrixColumnGroupName"), false, true).
The scope portion of the Inscope() can be the name of a DatasSet, Grouping,
or DataRegion.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:ergsc6maEHA.2792@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have one column group and 3 columns under it in a matrix. I added
subtotal
> to that column group and now all the 3 columns are summarized and shown.
Now
> I want to hide one column summary(The other 2 column summary should be
> shown). How to do that?
> TIA,
> Sam
>|||There's no good way to do this in the current version.
But for a sleazy hack workaround, take a look at my reply on the thread from
yesterday titled "Matrix SubTotal"
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:ergsc6maEHA.2792@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have one column group and 3 columns under it in a matrix. I added
subtotal
> to that column group and now all the 3 columns are summarized and shown.
Now
> I want to hide one column summary(The other 2 column summary should be
> shown). How to do that?
> TIA,
> Sam
>|||Hi Chris and Bruce,
Thanks - It works
Samuel
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:%23TEUIwpaEHA.2812@.tk2msftngp13.phx.gbl...
> You will need to control the Visibility of the textboxes that makeup the
> column by using an expression similar to
> =iif(InScope("MatrixColumnGroupName"), false, true).
> The scope portion of the Inscope() can be the name of a DatasSet,
Grouping,
> or DataRegion.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Samuel" <samuel@.photoninfotech.com> wrote in message
> news:ergsc6maEHA.2792@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have one column group and 3 columns under it in a matrix. I added
> subtotal
> > to that column group and now all the 3 columns are summarized and shown.
> Now
> > I want to hide one column summary(The other 2 column summary should be
> > shown). How to do that?
> >
> > TIA,
> >
> > Sam
> >
> >
>
I have one column group and 3 columns under it in a matrix. I added subtotal
to that column group and now all the 3 columns are summarized and shown. Now
I want to hide one column summary(The other 2 column summary should be
shown). How to do that?
TIA,
SamYou will need to control the Visibility of the textboxes that makeup the
column by using an expression similar to
=iif(InScope("MatrixColumnGroupName"), false, true).
The scope portion of the Inscope() can be the name of a DatasSet, Grouping,
or DataRegion.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:ergsc6maEHA.2792@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have one column group and 3 columns under it in a matrix. I added
subtotal
> to that column group and now all the 3 columns are summarized and shown.
Now
> I want to hide one column summary(The other 2 column summary should be
> shown). How to do that?
> TIA,
> Sam
>|||There's no good way to do this in the current version.
But for a sleazy hack workaround, take a look at my reply on the thread from
yesterday titled "Matrix SubTotal"
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:ergsc6maEHA.2792@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have one column group and 3 columns under it in a matrix. I added
subtotal
> to that column group and now all the 3 columns are summarized and shown.
Now
> I want to hide one column summary(The other 2 column summary should be
> shown). How to do that?
> TIA,
> Sam
>|||Hi Chris and Bruce,
Thanks - It works
Samuel
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:%23TEUIwpaEHA.2812@.tk2msftngp13.phx.gbl...
> You will need to control the Visibility of the textboxes that makeup the
> column by using an expression similar to
> =iif(InScope("MatrixColumnGroupName"), false, true).
> The scope portion of the Inscope() can be the name of a DatasSet,
Grouping,
> or DataRegion.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Samuel" <samuel@.photoninfotech.com> wrote in message
> news:ergsc6maEHA.2792@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have one column group and 3 columns under it in a matrix. I added
> subtotal
> > to that column group and now all the 3 columns are summarized and shown.
> Now
> > I want to hide one column summary(The other 2 column summary should be
> > shown). How to do that?
> >
> > TIA,
> >
> > Sam
> >
> >
>
Friday, February 24, 2012
Hiding Report Parameter UI - Still Using Parameters for Dynamic Column Sorting
Hello!
We are using a SortBy parameter and a Direction parameter for our report.
We are using dynamic sorting to allow the user to click on a column header
and sort that column. A click checks whether or not it is sorted asc or
desc and if it is, it changes the sorting to the direction it isn't
currently sorting. This functionality works perfect, but we do not want to
allow the users to sort using the parameter ui drop-down boxes. If we
removing the promt from the report designer it gives an error saying that
the parameter is read only and the report errors out. If we try to uncheck
the prompt box in report manager, it gives the same errors. We want to use
these parameters for sorting within the report, but we do not wish to have
the UI there as dropdowns. Is there a way to correct this? We appreciate
your help!UI seems to be confusing. You could try to use my script:
http://blogs.msdn.com/levs/archive/2004/07/20.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"jrennard" <jrennard3@.schneidercorp.com> wrote in message
news:unAnIkUhEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello!
> We are using a SortBy parameter and a Direction parameter for our report.
> We are using dynamic sorting to allow the user to click on a column header
> and sort that column. A click checks whether or not it is sorted asc or
> desc and if it is, it changes the sorting to the direction it isn't
> currently sorting. This functionality works perfect, but we do not want
> to
> allow the users to sort using the parameter ui drop-down boxes. If we
> removing the promt from the report designer it gives an error saying that
> the parameter is read only and the report errors out. If we try to
> uncheck
> the prompt box in report manager, it gives the same errors. We want to
> use
> these parameters for sorting within the report, but we do not wish to have
> the UI there as dropdowns. Is there a way to correct this? We appreciate
> your help!
>|||That worked great.
Exactly what I was looking for.
I do have another question. Do you have a script, or know of a script that
will remove duplicate values from a filter UI? We have a bunch of clients
and when we pull in query values for the filter to filter by client it
duplicates clients within the list because on the same query we have
multiple users that are associated with the same client. A script to remove
those duplicates would be very sweet. Let me know what you think. Thanks
again! Great script!
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:OBB0FcVhEHA.1276@.TK2MSFTNGP09.phx.gbl...
> UI seems to be confusing. You could try to use my script:
> http://blogs.msdn.com/levs/archive/2004/07/20.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "jrennard" <jrennard3@.schneidercorp.com> wrote in message
> news:unAnIkUhEHA.3664@.TK2MSFTNGP11.phx.gbl...
> > Hello!
> >
> > We are using a SortBy parameter and a Direction parameter for our
report.
> > We are using dynamic sorting to allow the user to click on a column
header
> > and sort that column. A click checks whether or not it is sorted asc or
> > desc and if it is, it changes the sorting to the direction it isn't
> > currently sorting. This functionality works perfect, but we do not want
> > to
> > allow the users to sort using the parameter ui drop-down boxes. If we
> > removing the promt from the report designer it gives an error saying
that
> > the parameter is read only and the report errors out. If we try to
> > uncheck
> > the prompt box in report manager, it gives the same errors. We want to
> > use
> > these parameters for sorting within the report, but we do not wish to
have
> > the UI there as dropdowns. Is there a way to correct this? We
appreciate
> > your help!
> >
> >
>
We are using a SortBy parameter and a Direction parameter for our report.
We are using dynamic sorting to allow the user to click on a column header
and sort that column. A click checks whether or not it is sorted asc or
desc and if it is, it changes the sorting to the direction it isn't
currently sorting. This functionality works perfect, but we do not want to
allow the users to sort using the parameter ui drop-down boxes. If we
removing the promt from the report designer it gives an error saying that
the parameter is read only and the report errors out. If we try to uncheck
the prompt box in report manager, it gives the same errors. We want to use
these parameters for sorting within the report, but we do not wish to have
the UI there as dropdowns. Is there a way to correct this? We appreciate
your help!UI seems to be confusing. You could try to use my script:
http://blogs.msdn.com/levs/archive/2004/07/20.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"jrennard" <jrennard3@.schneidercorp.com> wrote in message
news:unAnIkUhEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello!
> We are using a SortBy parameter and a Direction parameter for our report.
> We are using dynamic sorting to allow the user to click on a column header
> and sort that column. A click checks whether or not it is sorted asc or
> desc and if it is, it changes the sorting to the direction it isn't
> currently sorting. This functionality works perfect, but we do not want
> to
> allow the users to sort using the parameter ui drop-down boxes. If we
> removing the promt from the report designer it gives an error saying that
> the parameter is read only and the report errors out. If we try to
> uncheck
> the prompt box in report manager, it gives the same errors. We want to
> use
> these parameters for sorting within the report, but we do not wish to have
> the UI there as dropdowns. Is there a way to correct this? We appreciate
> your help!
>|||That worked great.
Exactly what I was looking for.
I do have another question. Do you have a script, or know of a script that
will remove duplicate values from a filter UI? We have a bunch of clients
and when we pull in query values for the filter to filter by client it
duplicates clients within the list because on the same query we have
multiple users that are associated with the same client. A script to remove
those duplicates would be very sweet. Let me know what you think. Thanks
again! Great script!
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:OBB0FcVhEHA.1276@.TK2MSFTNGP09.phx.gbl...
> UI seems to be confusing. You could try to use my script:
> http://blogs.msdn.com/levs/archive/2004/07/20.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "jrennard" <jrennard3@.schneidercorp.com> wrote in message
> news:unAnIkUhEHA.3664@.TK2MSFTNGP11.phx.gbl...
> > Hello!
> >
> > We are using a SortBy parameter and a Direction parameter for our
report.
> > We are using dynamic sorting to allow the user to click on a column
header
> > and sort that column. A click checks whether or not it is sorted asc or
> > desc and if it is, it changes the sorting to the direction it isn't
> > currently sorting. This functionality works perfect, but we do not want
> > to
> > allow the users to sort using the parameter ui drop-down boxes. If we
> > removing the promt from the report designer it gives an error saying
that
> > the parameter is read only and the report errors out. If we try to
> > uncheck
> > the prompt box in report manager, it gives the same errors. We want to
> > use
> > these parameters for sorting within the report, but we do not wish to
have
> > the UI there as dropdowns. Is there a way to correct this? We
appreciate
> > your help!
> >
> >
>
Hiding parent column groups in matrix control
I use matrix control in my report. Thre are 4 column groups in that control : Year, Half year, Quarter, and Month. I can expand the groups from Year to Months using drilldown. But I would like to see the only group at a time: Half year or Months or Quarter or Year. I tried to solve this task in following way:
- Created parameter named DateMode containing four labels :
Year,half year, quarter, month, and ennumerated them 1,2,3,4 . They appeared in combo box in report.
-Set visibility expression for each group like: Parameters!DateMode.Value<>1 ( 2,3,4 ).
- Uncheck "Visibility can be toggled by another report item"
So in my opinion matrix control should show only one group selected in combo box.
But it is working only for Year. If I select another date mode, there is not column titles shown and data always are shown for Year.
If anybody already solved similar task, please share.
Thanks in advance.
Dima,
the visibility is triggered via true or false - therefor you will have to build up an expression like:
=IIF(Parameters!DateMode.Value = 1,true,false)
for the year group and
=IIF(Parameters!DateMode.Value = 4,true,false) for the month group
you could also have a look here http://www.msbicentral.com/ for some more suggestions.
hth....
cheers,
Markus
Hiding Matrix column data but still have totals displaying.
Hi,
I'm working on a matrix report with salary information. One of my
requirements is to have the sub total display but not the detail
within the report. Is this possible? "Here is an example: This "-"
is showing hidden salary information.
December January
HOURS TOTAL HOURS TOTAL
Employee A 5 - 4 -
Employee B 5 - 4 -
TOTAL $500 $450On Dec 18, 1:45 pm, michael h <mikej...@.gmail.com> wrote:
> Hi,
> I'm working on a matrix report with salary information. One of my
> requirements is to have the sub total display but not the detail
> within the report. Is this possible? "Here is an example: This "-"
> is showing hidden salary information.
> December January
> HOURS TOTAL HOURS TOTAL
> Employee A 5 - 4 -
> Employee B 5 - 4 -
> TOTAL $500 $450
Use the InScope() function in your Details Expression. Basically, the
InScope("groupName") returns True when the Details section is
contained in the named group. If your use the name of the Row Group,
then you will be InScope = True when the report is rendering the rows,
and InScope = False when the report is rendering the totals.
Use an Expression like:
= IIF( InScope( "matrix1_Employees"), "-", Sum( Fields!
Salary.Value ) )
-- Scott
I'm working on a matrix report with salary information. One of my
requirements is to have the sub total display but not the detail
within the report. Is this possible? "Here is an example: This "-"
is showing hidden salary information.
December January
HOURS TOTAL HOURS TOTAL
Employee A 5 - 4 -
Employee B 5 - 4 -
TOTAL $500 $450On Dec 18, 1:45 pm, michael h <mikej...@.gmail.com> wrote:
> Hi,
> I'm working on a matrix report with salary information. One of my
> requirements is to have the sub total display but not the detail
> within the report. Is this possible? "Here is an example: This "-"
> is showing hidden salary information.
> December January
> HOURS TOTAL HOURS TOTAL
> Employee A 5 - 4 -
> Employee B 5 - 4 -
> TOTAL $500 $450
Use the InScope() function in your Details Expression. Basically, the
InScope("groupName") returns True when the Details section is
contained in the named group. If your use the name of the Row Group,
then you will be InScope = True when the report is rendering the rows,
and InScope = False when the report is rendering the totals.
Use an Expression like:
= IIF( InScope( "matrix1_Employees"), "-", Sum( Fields!
Salary.Value ) )
-- Scott
Sunday, February 19, 2012
Hiding Data
Hi,
I am intetrested to know if there is a way to hide the information in a
specific column in my table. SQLServer 200. Something like a password
protection were you only see *****. I have a DBA but want to hide salary
information from him that is stored in the database.
Any suggestions
Thanks
Elmo"elmo" <elmo@.delphisure.com> wrote in message
news:hvKdnVvTd7D5Dv_cRVn-tg@.is.co.za...
> Hi,
> I am intetrested to know if there is a way to hide the information in a
> specific column in my table. SQLServer 200. Something like a password
> protection were you only see *****. I have a DBA but want to hide salary
> information from him that is stored in the database.
> Any suggestions
> Thanks
> Elmo
This isn't possible - a member of the sysadmin role can access all data in
all databases. You can encrypt/decrypt the sensitive information in your
client application using whatever encryption API is available, then just
store the encrypted value in the database table:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
Simon|||I have exactly the same issue.
Our security person wants an audit on all 'select' on 4 tables
containing sensitive data.
Any idea how we can manage to do this?|||"Praim Sankar" <praimnath.sankar@.cogeco.com> wrote in message
news:329c4589.0410051245.41ad320b@.posting.google.c om...
>I have exactly the same issue.
> Our security person wants an audit on all 'select' on 4 tables
> containing sensitive data.
> Any idea how we can manage to do this?
One way would be to run a trace, filtered on object name and SELECT. Or
there are commercial products for auditing purposes, such as this one (which
I've never used):
http://www.lumigent.com/products/entegra_sql.html
However, it would be extremely difficult to prevent a determined
administrator from viewing the data - he can disable auditing briefly,
restore a backup to another server without auditing etc. So any technical
solution needs a 'human' part too, ie. good staff, and a clear policy on
data access.
Simon
hiding columns in a matrix
I am building a matrix report that has fiscal quarters as the column group. I
have multiple data columns within the group. I am trying to allow the report
user to choose which data is displayed by using a parameter. I am able to
hide the columns of data that are not selected but the fiscal quarter labels
remain. Is there a way to limit the column group headers to only the visible
columns?You can hide the controls by setting the Visible option for that control. You
may have to use an expression so that the control is visible when it
satisfies the condition. See how 'iif' can be used in report programming MSDN
documentation.
HTH
Rajesh
MCSD.NET
http://meenrajan.blogspot.com
"KenBo" wrote:
> I am building a matrix report that has fiscal quarters as the column group. I
> have multiple data columns within the group. I am trying to allow the report
> user to choose which data is displayed by using a parameter. I am able to
> hide the columns of data that are not selected but the fiscal quarter labels
> remain. Is there a way to limit the column group headers to only the visible
> columns?
have multiple data columns within the group. I am trying to allow the report
user to choose which data is displayed by using a parameter. I am able to
hide the columns of data that are not selected but the fiscal quarter labels
remain. Is there a way to limit the column group headers to only the visible
columns?You can hide the controls by setting the Visible option for that control. You
may have to use an expression so that the control is visible when it
satisfies the condition. See how 'iif' can be used in report programming MSDN
documentation.
HTH
Rajesh
MCSD.NET
http://meenrajan.blogspot.com
"KenBo" wrote:
> I am building a matrix report that has fiscal quarters as the column group. I
> have multiple data columns within the group. I am trying to allow the report
> user to choose which data is displayed by using a parameter. I am able to
> hide the columns of data that are not selected but the fiscal quarter labels
> remain. Is there a way to limit the column group headers to only the visible
> columns?
Hiding column heading
I have a report that includes a group that I have hidden. the group becomes
visible using a toggle on a textbox in the group header. I would like to
place a column heading in the header row for the group detail. I want this
header to only appear when the detail is visible. I can use the visibility
property of a text box but if I do that then that affect any color or lines
on the group header row
SO
My question is : Is there a way to use the IIF function to evaluate whether
a particular textbox is visible?I don't think you can access the properties on the fly... At least I haven't
seen an exmple of how to do it ...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2424391D-05DD-46F7-A424-B1D965219647@.microsoft.com...
>I have a report that includes a group that I have hidden. the group becomes
> visible using a toggle on a textbox in the group header. I would like to
> place a column heading in the header row for the group detail. I want this
> header to only appear when the detail is visible. I can use the
> visibility
> property of a text box but if I do that then that affect any color or
> lines
> on the group header row
> SO
> My question is : Is there a way to use the IIF function to evaluate
> whether
> a particular textbox is visible?
visible using a toggle on a textbox in the group header. I would like to
place a column heading in the header row for the group detail. I want this
header to only appear when the detail is visible. I can use the visibility
property of a text box but if I do that then that affect any color or lines
on the group header row
SO
My question is : Is there a way to use the IIF function to evaluate whether
a particular textbox is visible?I don't think you can access the properties on the fly... At least I haven't
seen an exmple of how to do it ...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2424391D-05DD-46F7-A424-B1D965219647@.microsoft.com...
>I have a report that includes a group that I have hidden. the group becomes
> visible using a toggle on a textbox in the group header. I would like to
> place a column heading in the header row for the group detail. I want this
> header to only appear when the detail is visible. I can use the
> visibility
> property of a text box but if I do that then that affect any color or
> lines
> on the group header row
> SO
> My question is : Is there a way to use the IIF function to evaluate
> whether
> a particular textbox is visible?
Hiding a column in matrix Report
Hi ,
I have a matrix report and it works fine.
The above is the report generated.
I need to hide the column with null date... Is it possible?
Hi Lavanya,
You can use theFilter options for the matrix to remove such columns -http://msdn2.microsoft.com/en-us/library/ms187062.aspx
or of the dataset -http://msdn2.microsoft.com/en-us/library/ms183549.aspx
|||
Thanks for replying.That didnt solve my problem.
I will explain the requirement I have.
I am developing a compliance report. For a particular day I am developing the compliance report.
My Query to generate that goes like this.
Insert Into Temp_ParamTable SELECT dt.usr_name, SUBSTRING(ds.arrival, 1, 10) AS ADate, COUNT(SUBSTRING(ds.arrival, 1, 10)) AS arrival FROM dbo.disp_tech dt LEFT OUTER JOIN dbo.disp_time ds ON dt.usr_name = ds.technician WHERE (dt.role = 'dispenser technician' OR dt.role = 'Hybrid') AND (SUBSTRING(ds.arrival, 1, 10) = ''07/30/2007' ) GROUP BY dt.usr_name, SUBSTRING(ds.arrival, 1, 10)
Anderson , David 07/30/2007 1
Bangerd , Michael 07/30/2007 1
Berry , Jerry 07/30/2007 1
Breakall , Jeff 07/30/2007 1
Conklin , Terry 07/30/2007 2
DeLeon , Carlos 07/30/2007 3
Dorn , Craig 07/30/2007 1
I have many technicians but the above query returns only people who are compliant on that day. But my client wants to see all the technicians and if the technician has not worked on that day they want a zero across their name.
So what I did was I joined the above table with the table that has all the technician name.
Insert into Temp_Compliance SELECT TOP 100 PERCENT dt.usr_name, isnull( t.arrival, 0) AS arrival , isnull(t.ADate, 0) AS ADate FROM dbo.disp_tech dt LEFT OUTER JOIN dbo.Temp_ParamTable t ON dt.usr_name = t.usr_name WHERE (dt.role = 'Dispenser Technician') OR (dt.role = 'Hybrid') ORDER BY dt.usr_name
Adams , William 0 0
Alqueseva , JC 0 0
Anderson , David 1 07/30/2007
Asseng , Steve 0 0
Bangerd , Michael 1 07/30/2007
Barnaskas , Joe 0 0
Barry , Timothy 0 0
Berry , Jerry 1 07/30/2007
Bowen , Nels 0 0
Breakall , Jeff 1 07/30/2007
Brockel , Robert 0 0
Campbell , Bryan 0 0
Castro , Carlos 0 0
Childs , Michael 0 0
Clawson , Donald 0 0
Conklin , Terry 2 07/30/2007
Contractor , Contractor 0 0
Cooke , Jackie 0 0
DeLeon , Carlos 3 07/30/2007
Dorn , Craig 1 07/30/2007
So now I got all the technician names and for the techs who have not worked on 7/30/2007 I got a zero across
Now I develop my report using a matrix.
I drag and drop the usr_name in the row. Then I drag and drop the ADate in the column and for the data I create an expression like this =IIF(Fields!Count_Arrival.Value is nothing,"0",Fields!Count_Arrival.Value)
Now my report looks like this with a 0 column . How can I eliminate that. Is there any other way i ll have to follow?
Hiding
Hi,
I don't know if this is possible.
I have a matrix with one row group and one column group. Within the column group are three static columns.The rowgroup has subtotals enabled. What I want is that one of the three static columns is hidden in the report but shown in the subtotal. Can this be done?
Thanks,
QIf you're just trying to hide it (leaving a blank space/without rearranging
the layout), you can do that easily using the InScope property.
Set the Hidden property of the textbox to: =InScope("myrowgroupname")
(Typically, the row group name would be something like matrix1_fieldname
unless you renamed it)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Qbee" <Qbee@.discussions.microsoft.com> wrote in message
news:450CA2CE-0554-4D11-A295-72877EEC95AE@.microsoft.com...
> Hi,
> I don't know if this is possible.
> I have a matrix with one row group and one column group. Within the column
group are three static columns.The rowgroup has subtotals enabled. What I
want is that one of the three static columns is hidden in the report but
shown in the subtotal. Can this be done?
> Thanks,
> Q|||Hi Chris,
your solution works great but the textbox which is hidden leaves a blank
column in the report which doesn't look nice.
I am currently converting a lot of Business Objects reports and I notice
that there are table structures which are difficult to convert in reporting
services.
Q
"Chris Hays [MSFT]" wrote:
> If you're just trying to hide it (leaving a blank space/without rearranging
> the layout), you can do that easily using the InScope property.
> Set the Hidden property of the textbox to: =InScope("myrowgroupname")
> (Typically, the row group name would be something like matrix1_fieldname
> unless you renamed it)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Qbee" <Qbee@.discussions.microsoft.com> wrote in message
> news:450CA2CE-0554-4D11-A295-72877EEC95AE@.microsoft.com...
> > Hi,
> >
> > I don't know if this is possible.
> >
> > I have a matrix with one row group and one column group. Within the column
> group are three static columns.The rowgroup has subtotals enabled. What I
> want is that one of the three static columns is hidden in the report but
> shown in the subtotal. Can this be done?
> >
> > Thanks,
> >
> > Q
>
>|||Generally, tables and matrixes are straightforward. It's when you're trying
to hybridize the two (like you're doing) that we run into trouble.
It's a common enough thing to do that it's high on our future features
wishlist (but pretty expensive to do it right, so probably won't make it in
to SQL 2005).
In the mean time, you can simulate some of it (but not all of it, I'm
afraid) via controlling visibility. In your case, you might be able to do
something wherein you have several overlapping textboxes in a rectangle,
some of which are hidden while in the subtotal and the others of which are
hidden when in the details. Tricky, but possible. I think brucejoh posted
an example of this on the newsgroup a while back... You might want to search
for that.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Qbee" <Qbee@.discussions.microsoft.com> wrote in message
news:B97F4CDF-F084-4C91-8429-D893DC6158EE@.microsoft.com...
> Hi Chris,
> your solution works great but the textbox which is hidden leaves a blank
> column in the report which doesn't look nice.
> I am currently converting a lot of Business Objects reports and I notice
> that there are table structures which are difficult to convert in
reporting
> services.
> Q
> "Chris Hays [MSFT]" wrote:
> > If you're just trying to hide it (leaving a blank space/without
rearranging
> > the layout), you can do that easily using the InScope property.
> >
> > Set the Hidden property of the textbox to: =InScope("myrowgroupname")
> >
> > (Typically, the row group name would be something like matrix1_fieldname
> > unless you renamed it)
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach of
> > children under 3.
> > "Qbee" <Qbee@.discussions.microsoft.com> wrote in message
> > news:450CA2CE-0554-4D11-A295-72877EEC95AE@.microsoft.com...
> > > Hi,
> > >
> > > I don't know if this is possible.
> > >
> > > I have a matrix with one row group and one column group. Within the
column
> > group are three static columns.The rowgroup has subtotals enabled. What
I
> > want is that one of the three static columns is hidden in the report but
> > shown in the subtotal. Can this be done?
> > >
> > > Thanks,
> > >
> > > Q
> >
> >
> >
I don't know if this is possible.
I have a matrix with one row group and one column group. Within the column group are three static columns.The rowgroup has subtotals enabled. What I want is that one of the three static columns is hidden in the report but shown in the subtotal. Can this be done?
Thanks,
QIf you're just trying to hide it (leaving a blank space/without rearranging
the layout), you can do that easily using the InScope property.
Set the Hidden property of the textbox to: =InScope("myrowgroupname")
(Typically, the row group name would be something like matrix1_fieldname
unless you renamed it)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Qbee" <Qbee@.discussions.microsoft.com> wrote in message
news:450CA2CE-0554-4D11-A295-72877EEC95AE@.microsoft.com...
> Hi,
> I don't know if this is possible.
> I have a matrix with one row group and one column group. Within the column
group are three static columns.The rowgroup has subtotals enabled. What I
want is that one of the three static columns is hidden in the report but
shown in the subtotal. Can this be done?
> Thanks,
> Q|||Hi Chris,
your solution works great but the textbox which is hidden leaves a blank
column in the report which doesn't look nice.
I am currently converting a lot of Business Objects reports and I notice
that there are table structures which are difficult to convert in reporting
services.
Q
"Chris Hays [MSFT]" wrote:
> If you're just trying to hide it (leaving a blank space/without rearranging
> the layout), you can do that easily using the InScope property.
> Set the Hidden property of the textbox to: =InScope("myrowgroupname")
> (Typically, the row group name would be something like matrix1_fieldname
> unless you renamed it)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Qbee" <Qbee@.discussions.microsoft.com> wrote in message
> news:450CA2CE-0554-4D11-A295-72877EEC95AE@.microsoft.com...
> > Hi,
> >
> > I don't know if this is possible.
> >
> > I have a matrix with one row group and one column group. Within the column
> group are three static columns.The rowgroup has subtotals enabled. What I
> want is that one of the three static columns is hidden in the report but
> shown in the subtotal. Can this be done?
> >
> > Thanks,
> >
> > Q
>
>|||Generally, tables and matrixes are straightforward. It's when you're trying
to hybridize the two (like you're doing) that we run into trouble.
It's a common enough thing to do that it's high on our future features
wishlist (but pretty expensive to do it right, so probably won't make it in
to SQL 2005).
In the mean time, you can simulate some of it (but not all of it, I'm
afraid) via controlling visibility. In your case, you might be able to do
something wherein you have several overlapping textboxes in a rectangle,
some of which are hidden while in the subtotal and the others of which are
hidden when in the details. Tricky, but possible. I think brucejoh posted
an example of this on the newsgroup a while back... You might want to search
for that.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Qbee" <Qbee@.discussions.microsoft.com> wrote in message
news:B97F4CDF-F084-4C91-8429-D893DC6158EE@.microsoft.com...
> Hi Chris,
> your solution works great but the textbox which is hidden leaves a blank
> column in the report which doesn't look nice.
> I am currently converting a lot of Business Objects reports and I notice
> that there are table structures which are difficult to convert in
reporting
> services.
> Q
> "Chris Hays [MSFT]" wrote:
> > If you're just trying to hide it (leaving a blank space/without
rearranging
> > the layout), you can do that easily using the InScope property.
> >
> > Set the Hidden property of the textbox to: =InScope("myrowgroupname")
> >
> > (Typically, the row group name would be something like matrix1_fieldname
> > unless you renamed it)
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach of
> > children under 3.
> > "Qbee" <Qbee@.discussions.microsoft.com> wrote in message
> > news:450CA2CE-0554-4D11-A295-72877EEC95AE@.microsoft.com...
> > > Hi,
> > >
> > > I don't know if this is possible.
> > >
> > > I have a matrix with one row group and one column group. Within the
column
> > group are three static columns.The rowgroup has subtotals enabled. What
I
> > want is that one of the three static columns is hidden in the report but
> > shown in the subtotal. Can this be done?
> > >
> > > Thanks,
> > >
> > > Q
> >
> >
> >
Hide value of Subtotal header?
Hello,
I have 3 row groupings and one column grouping. The visibility of the
2nd two row groupings is toggled by the first. For each value in the
second grouping there is always only 1 in the third (like a first name
and a last name). Therefore, I need two subtotals, one for the first
row grouping, and one for the next two. I put the one for the 2nd and
3rd on the 2nd row grouping. When the first row group is hiding the
next two, the subtotal number appear correctly, but the word "Total" is
displayed with the numbers.
Example
The dataset returns
City, FirstName, LastName, ColumnName, Value
There is a rowgroup for each of the first three and a columngroup on
ColumnName.
The group visibility for FirstName and LastName is toggled by the city.
Column
NY Bob Smith 10
Jane Doe 20
Total 30
Here the total is for the 2nd rowgroup
When I toggle the City, it looks like this
Column
NY Total 30
But I don't want "Total" to be there. Is this Possible?
Thanks,
AbeIs there a way to know if your group visibility is set to hide the
header for the total row? Can you use InScope()? I've tried that a
few different ways and I haven't been able to come up with a solution.
Thanks,
Abe
I have 3 row groupings and one column grouping. The visibility of the
2nd two row groupings is toggled by the first. For each value in the
second grouping there is always only 1 in the third (like a first name
and a last name). Therefore, I need two subtotals, one for the first
row grouping, and one for the next two. I put the one for the 2nd and
3rd on the 2nd row grouping. When the first row group is hiding the
next two, the subtotal number appear correctly, but the word "Total" is
displayed with the numbers.
Example
The dataset returns
City, FirstName, LastName, ColumnName, Value
There is a rowgroup for each of the first three and a columngroup on
ColumnName.
The group visibility for FirstName and LastName is toggled by the city.
Column
NY Bob Smith 10
Jane Doe 20
Total 30
Here the total is for the 2nd rowgroup
When I toggle the City, it looks like this
Column
NY Total 30
But I don't want "Total" to be there. Is this Possible?
Thanks,
AbeIs there a way to know if your group visibility is set to hide the
header for the total row? Can you use InScope()? I've tried that a
few different ways and I haven't been able to come up with a solution.
Thanks,
Abe
Subscribe to:
Posts (Atom)