Holidays in SQL Server

Hi!
I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
to select all rows from that table, excluding days which fall on holidays or
weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not
in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.
Suggestions are welcome!
Sincerely,
Nils Magnus EnglundCreating a holidays/calendar table is a good thing. This will surely simplif
y
your search. Remember, your holidays might not be the same as mine so having
the
table will eliminate such.
-oj
http://www.rac4sql.net
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I wan
t
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where no
t
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||I think it is a good idea to have a calendar table.
Roji. P. Thomas
SQL Server Programmer
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||select * from TableName where DATETIME not in (select DATETIME from holidays
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> glsD
:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||Use a calendar table. See the "more advanced example" at
http://www.aspfaq.com/2453
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>

Holidays in SQL Server

Hi!
I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
to select all rows from that table, excluding days which fall on holidays or
weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not
in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.
Suggestions are welcome!
Sincerely,
Nils Magnus EnglundCreating a holidays/calendar table is a good thing. This will surely simplify
your search. Remember, your holidays might not be the same as mine so having the
table will eliminate such.
--
-oj
http://www.rac4sql.net
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
> to select all rows from that table, excluding days which fall on holidays or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||I think it is a good idea to have a calendar table.
--
Roji. P. Thomas
SQL Server Programmer
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||select * from TableName where DATETIME not in (select DATETIME from holidays
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> ¼¶¼g©ó¶l¥ó·s»D
:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>|||Use a calendar table. See the "more advanced example" at
http://www.aspfaq.com/2453
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.
> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund
>

Holidays in SQL Server

Hi!

I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
to select all rows from that table, excluding days which fall on holidays or
weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not
in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.

Suggestions are welcome!

Sincerely,
Nils Magnus Englund"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:2rT%b.103$72.176991232@.news.telia.no...
> Hi!
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want
> to select all rows from that table, excluding days which fall on holidays
or
> weekends. What is the best way to accomplish this? I considered creating a
> new table called "holidays" and then selecting all rows (sort of "where
not
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.

That's probably your best idea.

Your holidays may not be mine.

> Suggestions are welcome!
>
> Sincerely,
> Nils Magnus Englund|||Nils Magnus Englund (nils.magnus.englund@.orkfin.no) writes:
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
> want to select all rows from that table, excluding days which fall on
> holidays or weekends. What is the best way to accomplish this? I
> considered creating a new table called "holidays" and then selecting all
> rows (sort of "where not in (select * from holidays)") , but I was
> looking for a better solution since that implies that I have to populate
> the "holidays" table.

And how would you expect SQL Server to know about syttende maj or when
Midsummer is?

You can of course make the holidays table more or less sophisticated.
You can just put in all Mondays to Fridays that are not dates from now
to 2020 or whatever.

You can also write a stored procedure that fills in the table given the
rules about currently known holidays. You would need to find data on
where Easter falls, to determine days for Easter, Whitsun and Ascenion Day.

Yet an alternative is to put all days in that table, and then a flag
whether the day is a working day or not, no matter whether it's Friday
or Sunday.

And finally, for the SELECT it self I prefer:

SELECT *
FROM tbl t
WHERE NOT EXISTS (SELECT *
FROM holidays h
WHERE t.date = h.date)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

History rows

I have job history for other jobs in the same server
Perhaps those other jobs execute so frequently so that you get to 1000 rows between executions of
this job?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:e1lCD$EGGHA.2652@.tk2msftngp13.phx.gbl...
>I have job history for other jobs in the same server
>

History rows

I have job history for other jobs in the same serverPerhaps those other jobs execute so frequently so that you get to 1000 rows between executions of
this job?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:e1lCD$EGGHA.2652@.tk2msftngp13.phx.gbl...
>I have job history for other jobs in the same server
>sql

History rows

I have job history for other jobs in the same serverPerhaps those other jobs execute so frequently so that you get to 1000 rows
between executions of
this job?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:e1lCD$EGGHA.2652@.tk2msftngp13.phx.gbl...[v
bcol=seagreen]
>I have job history for other jobs in the same server
>[/vbcol]

High fragmentation with 0 rows in the table

I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
rows into an empty table and then deletes them and keeps going in the loop
for about 6 hours. If the table is not truncated before the job starts again
the following night, the job performance degrades considerably. It turns out
that after the job is done running, there are hundreds of data pages
allocated for this table with high extent fragmentation.
My question is why these pages are not de-allocated when there are no rows
in the table to begin with? If I truncate the table and add 6 rows manually,
then only one data page is allocated to the table. The table has about 6
columns with 4 columns of data type smallint, one decimal and a couple of
char(5).
Hi,
There could be several reasons. Are you commiting your transactions after
each loop? Also, the delete statement is a logged transaction, that means
everytime your script deletes the data, it is written in the transaction log
that causes space (data pages) to be allocated. Not sure how your script is
coded, so can't really be too specific.
The truncate statement is NOT logged so when you issue it, it wipes out the
table clean. That is why you are seeing this behaviour.
HTH
DeeJay Puar
MCDBA
"Adam" wrote:

> I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts again
> the following night, the job performance degrades considerably. It turns out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>
|||do you have any indexes on this table, specificaly a clustered one, or is
this a heap ?
and how is the "delete" done, with a 'delete tablename' ?
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts
> again
> the following night, the job performance degrades considerably. It turns
> out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows
> manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>
|||On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:
>do you have any indexes on this table, specificaly a clustered one, or is
>this a heap ?
>and how is the "delete" done, with a 'delete tablename' ?
What he said.
Would a simple "truncate table" be a quick fix?
(not that it's so hard to defrag anyway)
J.
|||I am not 100% if the app is doing a commit after each delete but even if that
was not the case, after the final commit at the end of the 6th hour,
shouldn't the empty pages get deallocated?
"DeeJay Puar" wrote:
[vbcol=seagreen]
> Hi,
> There could be several reasons. Are you commiting your transactions after
> each loop? Also, the delete statement is a logged transaction, that means
> everytime your script deletes the data, it is written in the transaction log
> that causes space (data pages) to be allocated. Not sure how your script is
> coded, so can't really be too specific.
> The truncate statement is NOT logged so when you issue it, it wipes out the
> table clean. That is why you are seeing this behaviour.
> HTH
> DeeJay Puar
> MCDBA
> "Adam" wrote:
|||At the moment, there are no indexes and yes, my next plan of attack is to put
a clustered index to see how that pans out. I don't know for sure if that
will solve the problem 100% because now, the delete would also have to do
more IOs to maintain the clustered index. Also, a table with the clustered
index will get fragmented over time.
The SQL is doing "delete table".
"David J. Cartwright" wrote:

> do you have any indexes on this table, specificaly a clustered one, or is
> this a heap ?
> and how is the "delete" done, with a 'delete tablename' ?
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>
>
|||The current fix in fact is to truncate the table before the batch starts,
however, if SQL Server is accumulating empty pages with 0 rows in the table
then the performance starts to degrade over time while the 6 hour job is
running. If I can figure out the reason and the solution to why SQL Server is
not deallocating empty pages, then I can stop it from taking the time for
doing table scan through these empty pages and can tweek many other nightly
jobs that I am sure are facing a similar situation.
thoughts?
"jxstern" wrote:

> On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
> <davidcartwright@.hotmail.com> wrote:
> What he said.
> Would a simple "truncate table" be a quick fix?
> (not that it's so hard to defrag anyway)
> J.
>
>
|||On Thu, 15 Dec 2005 06:21:02 -0800, "Adam"
<Adam@.discussions.microsoft.com> wrote:
>At the moment, there are no indexes and yes, my next plan of attack is to put
>a clustered index to see how that pans out.
It should.
If you delete from a table without a clustered index, SQLServer leaves
the pages deallocated until ... The Ghost wakes up and reclaims them,
which it does when it feels like it, and then it takes as long as it
takes.
Fun, huh?
Add the clustered index, if for no other reason than this.
Google the newsgroup archives or the web for more about the ghost,
there's very little about it in MSDN or BOL.
Josh

High fragmentation with 0 rows in the table

I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
rows into an empty table and then deletes them and keeps going in the loop
for about 6 hours. If the table is not truncated before the job starts again
the following night, the job performance degrades considerably. It turns out
that after the job is done running, there are hundreds of data pages
allocated for this table with high extent fragmentation.
My question is why these pages are not de-allocated when there are no rows
in the table to begin with? If I truncate the table and add 6 rows manually,
then only one data page is allocated to the table. The table has about 6
columns with 4 columns of data type smallint, one decimal and a couple of
char(5).Hi,
There could be several reasons. Are you commiting your transactions after
each loop? Also, the delete statement is a logged transaction, that means
everytime your script deletes the data, it is written in the transaction log
that causes space (data pages) to be allocated. Not sure how your script is
coded, so can't really be too specific.
The truncate statement is NOT logged so when you issue it, it wipes out the
table clean. That is why you are seeing this behaviour.
HTH
DeeJay Puar
MCDBA
"Adam" wrote:
> I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts again
> the following night, the job performance degrades considerably. It turns out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||do you have any indexes on this table, specificaly a clustered one, or is
this a heap ?
and how is the "delete" done, with a 'delete tablename' ?
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts
> again
> the following night, the job performance degrades considerably. It turns
> out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows
> manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:
>do you have any indexes on this table, specificaly a clustered one, or is
>this a heap ?
>and how is the "delete" done, with a 'delete tablename' ?
What he said.
Would a simple "truncate table" be a quick fix?
(not that it's so hard to defrag anyway)
J.|||I am not 100% if the app is doing a commit after each delete but even if that
was not the case, after the final commit at the end of the 6th hour,
shouldn't the empty pages get deallocated?
"DeeJay Puar" wrote:
> Hi,
> There could be several reasons. Are you commiting your transactions after
> each loop? Also, the delete statement is a logged transaction, that means
> everytime your script deletes the data, it is written in the transaction log
> that causes space (data pages) to be allocated. Not sure how your script is
> coded, so can't really be too specific.
> The truncate statement is NOT logged so when you issue it, it wipes out the
> table clean. That is why you are seeing this behaviour.
> HTH
> DeeJay Puar
> MCDBA
> "Adam" wrote:
> > I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> > rows into an empty table and then deletes them and keeps going in the loop
> > for about 6 hours. If the table is not truncated before the job starts again
> > the following night, the job performance degrades considerably. It turns out
> > that after the job is done running, there are hundreds of data pages
> > allocated for this table with high extent fragmentation.
> >
> > My question is why these pages are not de-allocated when there are no rows
> > in the table to begin with? If I truncate the table and add 6 rows manually,
> > then only one data page is allocated to the table. The table has about 6
> > columns with 4 columns of data type smallint, one decimal and a couple of
> > char(5).
> >|||At the moment, there are no indexes and yes, my next plan of attack is to put
a clustered index to see how that pans out. I don't know for sure if that
will solve the problem 100% because now, the delete would also have to do
more IOs to maintain the clustered index. Also, a table with the clustered
index will get fragmented over time.
The SQL is doing "delete table".
"David J. Cartwright" wrote:
> do you have any indexes on this table, specificaly a clustered one, or is
> this a heap ?
> and how is the "delete" done, with a 'delete tablename' ?
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
> >I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> > rows into an empty table and then deletes them and keeps going in the loop
> > for about 6 hours. If the table is not truncated before the job starts
> > again
> > the following night, the job performance degrades considerably. It turns
> > out
> > that after the job is done running, there are hundreds of data pages
> > allocated for this table with high extent fragmentation.
> >
> > My question is why these pages are not de-allocated when there are no rows
> > in the table to begin with? If I truncate the table and add 6 rows
> > manually,
> > then only one data page is allocated to the table. The table has about 6
> > columns with 4 columns of data type smallint, one decimal and a couple of
> > char(5).
> >
>
>|||The current fix in fact is to truncate the table before the batch starts,
however, if SQL Server is accumulating empty pages with 0 rows in the table
then the performance starts to degrade over time while the 6 hour job is
running. If I can figure out the reason and the solution to why SQL Server is
not deallocating empty pages, then I can stop it from taking the time for
doing table scan through these empty pages and can tweek many other nightly
jobs that I am sure are facing a similar situation.
thoughts?
"jxstern" wrote:
> On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
> <davidcartwright@.hotmail.com> wrote:
> >do you have any indexes on this table, specificaly a clustered one, or is
> >this a heap ?
> >and how is the "delete" done, with a 'delete tablename' ?
> What he said.
> Would a simple "truncate table" be a quick fix?
> (not that it's so hard to defrag anyway)
> J.
>
>|||On Thu, 15 Dec 2005 06:21:02 -0800, "Adam"
<Adam@.discussions.microsoft.com> wrote:
>At the moment, there are no indexes and yes, my next plan of attack is to put
>a clustered index to see how that pans out.
It should.
If you delete from a table without a clustered index, SQLServer leaves
the pages deallocated until ... The Ghost wakes up and reclaims them,
which it does when it feels like it, and then it takes as long as it
takes.
Fun, huh?
Add the clustered index, if for no other reason than this.
Google the newsgroup archives or the web for more about the ghost,
there's very little about it in MSDN or BOL.
Josh

High fragmentation with 0 rows in the table

I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
rows into an empty table and then deletes them and keeps going in the loop
for about 6 hours. If the table is not truncated before the job starts again
the following night, the job performance degrades considerably. It turns out
that after the job is done running, there are hundreds of data pages
allocated for this table with high extent fragmentation.
My question is why these pages are not de-allocated when there are no rows
in the table to begin with? If I truncate the table and add 6 rows manually,
then only one data page is allocated to the table. The table has about 6
columns with 4 columns of data type smallint, one decimal and a couple of
char(5).Hi,
There could be several reasons. Are you commiting your transactions after
each loop? Also, the delete statement is a logged transaction, that means
everytime your script deletes the data, it is written in the transaction log
that causes space (data pages) to be allocated. Not sure how your script is
coded, so can't really be too specific.
The truncate statement is NOT logged so when you issue it, it wipes out the
table clean. That is why you are seeing this behaviour.
HTH
DeeJay Puar
MCDBA
"Adam" wrote:

> I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to
6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts aga
in
> the following night, the job performance degrades considerably. It turns o
ut
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows manuall
y,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||do you have any indexes on this table, specificaly a clustered one, or is
this a heap ?
and how is the "delete" done, with a 'delete tablename' ?
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts
> again
> the following night, the job performance degrades considerably. It turns
> out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows
> manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:
>do you have any indexes on this table, specificaly a clustered one, or is
>this a heap ?
>and how is the "delete" done, with a 'delete tablename' ?
What he said.
Would a simple "truncate table" be a quick fix?
(not that it's so hard to defrag anyway)
J.|||I am not 100% if the app is doing a commit after each delete but even if tha
t
was not the case, after the final commit at the end of the 6th hour,
shouldn't the empty pages get deallocated?
"DeeJay Puar" wrote:
[vbcol=seagreen]
> Hi,
> There could be several reasons. Are you commiting your transactions after
> each loop? Also, the delete statement is a logged transaction, that means
> everytime your script deletes the data, it is written in the transaction l
og
> that causes space (data pages) to be allocated. Not sure how your script i
s
> coded, so can't really be too specific.
> The truncate statement is NOT logged so when you issue it, it wipes out th
e
> table clean. That is why you are seeing this behaviour.
> HTH
> DeeJay Puar
> MCDBA
> "Adam" wrote:
>|||At the moment, there are no indexes and yes, my next plan of attack is to pu
t
a clustered index to see how that pans out. I don't know for sure if that
will solve the problem 100% because now, the delete would also have to do
more IOs to maintain the clustered index. Also, a table with the clustered
index will get fragmented over time.
The SQL is doing "delete table".
"David J. Cartwright" wrote:

> do you have any indexes on this table, specificaly a clustered one, or is
> this a heap ?
> and how is the "delete" done, with a 'delete tablename' ?
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>
>|||The current fix in fact is to truncate the table before the batch starts,
however, if SQL Server is accumulating empty pages with 0 rows in the table
then the performance starts to degrade over time while the 6 hour job is
running. If I can figure out the reason and the solution to why SQL Server i
s
not deallocating empty pages, then I can stop it from taking the time for
doing table scan through these empty pages and can tweek many other nightly
jobs that I am sure are facing a similar situation.
thoughts?
"jxstern" wrote:

> On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
> <davidcartwright@.hotmail.com> wrote:
> What he said.
> Would a simple "truncate table" be a quick fix?
> (not that it's so hard to defrag anyway)
> J.
>
>|||On Thu, 15 Dec 2005 06:21:02 -0800, "Adam"
<Adam@.discussions.microsoft.com> wrote:
>At the moment, there are no indexes and yes, my next plan of attack is to p
ut
>a clustered index to see how that pans out.
It should.
If you delete from a table without a clustered index, SQLServer leaves
the pages deallocated until ... The Ghost wakes up and reclaims them,
which it does when it feels like it, and then it takes as long as it
takes.
Fun, huh?
Add the clustered index, if for no other reason than this.
Google the newsgroup archives or the web for more about the ghost,
there's very little about it in MSDN or BOL.
Josh

Hiding without grouping

hi,
I'm using a matrix to try and display web traffic. For the rows I
can drill down by date. For the columns I am trying to make them be
able to expand based on categories that I define. For example, I would
like to be able to expand/collapse all columns that have to do with
traffic from google. However, I can't seem to check the toggle box. I'm
assuming it's because the columns are not grouped by the item that
toggles them, the way dates are grouped by year. The item that toggles
their visibility is a static text box. The closest I can get is to
toggle the visibility of the column names. Any suggestions?Nevermind, I was just using the wrong table. turns out you can hide
entire columns in the tabular table based on arbitrary toggle items.
You just have to do it in the property window on the side, rather than
right clicking. Has anyone noticed that there's extra unsupported chart
types there? What's a polar graph?|||"What's a polar graph?"
A circular plot showing amplitudes as a function of angle.
http://www.dplot.com/polar.htm

Hiding tables in a report

Hi,

Can anyone help me with the expression I need to use in order to hide a table with no rows? I have put my table inside a rectangle with the idea that I would just hide the rectangle if there weren't any rows but I can't find any examples of what expression I can use to specify the no rows condition.

Thanks!

Debi

Something like for the Hidden property of the rectangle:

=iif(RowCount("NameofDatasetPopulatingTheTabe") > 0, "false", "true")|||

Andy,

Thanks for the posting. RowCount doesn't work for me (unrecognized identifier). That may be because I am using the ReportViewer control in an ASP page and not actually using SQL Server Reporting Services. I probably should have included that in my original posting. Also, if it makes a difference this is a subreport that is using a filter based on a parameter passed from the main report. I'm not sure if that will makes a difference but the datasource will usually have records but a table may not have any rows due to the filter. I want to eliminate the header that gets displayed when there are no rows to show.

Thanks!

Debi

|||

I have made some progress on this. The correct syntax is:

=IIf(CountRows("table1") > 0, false, true)

I added this to the visibility of the table and it does hide the table header if there aren't any rows. The only problem is that it still leaves the white space that would have been occupied by the table header. I had read some tips about creating a rectangle and then moving the table into the rectangle and setting the visibility for the rectangle but I can't get it to work. If I use the same expression I get the following error:

Error 1 The Hidden expression for the rectangle ‘rectangle1’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

It seems that table1 is out of scope in this instance but I can't figure out how the scope should be defined. Any one have any suggestions?

Also, will this even accomplish what I want (eliminating the whitespace)? This rectangle/table is in a subreport if that makes any difference.

Thanks!

Debi

|||You can use the same logic for a rectangle, but your scope parameter needs to be the name of the dataset, not the name of the table.

So if your table uses a dataset like "MyDataSet", then use that instead of "table1". The Rectangle will be aware of that scope.|||

Just thought I would mention that there is also the ability to set a message when a table has no rows. This automatically hides the table and displays your message. Setting the "No Rows" property expression to = "" will also hide the table with no message.

Simone

Hiding Table Rows

Is it possible to hide full table rows depending on data in a record?Yes. Highlight a table row in Layout view. Expand the Visibility property.
Underneath there is a Hidden property. You can set this to an expression. So
you could do something like:
=iif(MyCondition, True, False)
HTH
Charles Kangai, MCT, MCDBA
"MER78" wrote:
> Is it possible to hide full table rows depending on data in a record?|||When I do that... it hides the row... but instead I just get blank space
where the row would have been.
"Charles Kangai" wrote:
> Yes. Highlight a table row in Layout view. Expand the Visibility property.
> Underneath there is a Hidden property. You can set this to an expression. So
> you could do something like:
> =iif(MyCondition, True, False)|||I see. Then do the following:
1) Remove all previous expressions.
2) Highlight the entire detail row again.
3) In the Properties sheet, select the Grouping property and click on the
ellipsis button to its right.
4) On the General tab, select a unique expression in the Expression column.
You want a unique expression for each row so that each row is in its own
group. You are not really grouping, but just pretending to, so that we can
use the Visibility feature.
5) Now click on the Visibility tab. For the Initial visibility radio button,
select Expression.
6) Now enter your expression, such as =IIF(Fields!MyRevenue.Value<100,True,
False)
This will now hide the (entire) row if MyRevenue is less than 100. You will
not get blank rows like before.
HTH this time
Charles Kangai, MCT, MCDBA
"MER78" wrote:
> When I do that... it hides the row... but instead I just get blank space
> where the row would have been.
> "Charles Kangai" wrote:
> > Yes. Highlight a table row in Layout view. Expand the Visibility property.
> > Underneath there is a Hidden property. You can set this to an expression. So
> > you could do something like:
> >
> > =iif(MyCondition, True, False)

hiding Table Rows

Is it possible to determine whether or not another row in a table is visible? The example is in a multiple detail row table, where I want to display a 'header' row (really just another detail row), if any of the other detail rows in the 'section' are visible.  Is there some syntax like ReportItems!TableRow7.property("Hidden")=?? that I could use to determine the visibility state of a row?Thanks
Anil

Hi,
in Layout Tab, select the row and in the Property Panel select Visibility -> Hidden -> Expression.
Best Regards
|||

Thanks for your response but it does not solve my problem.

My Problem is I have master detail records.

If detail records are not existing then I have to make the master row invisible

Could anybody help me out fom this problem.

Ofcourse I solved it by modifying the database query but still want the solution of this problem

Thanks

Anil

|||

Hi...Yes u can hide the Rows using If Expressions...do onething...what ever the textbox u want hide...go.
1)..selct Property Window(F4) ...
2) then click Visibility -> hidden -> Select <Expressin..>
then write like this....I am writting One Example Only.....use this...

=iif(ReportItems!textbox2.Value="Y",False,True)
False-- Visible
True- Hide of textbox
U cAN WRITE FOR ANY THING....
Ok..Good Luck...

Hiding subtotal rows when there is only 1 row in the group

How could one do this? I understand you could use the COUNT() function, but I'm not sure which object's visibility would best support this. All that I've tried (subtotal area, group visibility) do not seem to work.

If you change the visible property on the subtotal textbox that RS adds, it will only 'blank out' the area where the subtotal row would have been - this doesn't achieve the desired effect of saving space.

Try this:

Click on the whole row for your group footer
Go to the properties
Put this in the 'Visibility - Hidden' expression.

=IIf(CountRows("GroupName") > 1, False, True)

I tried this on one of my reports and it removed the space used by the group footer, it didn't just blank it out. If there was only one row in that group, the footer wasn't shown, but if there were more than 1 row, it would. Just as a test, you might want to create a new row below your group footer and just add some text in there so that it will show below your subtotals (if you have any). In my case, the row below my group footer was 'moved up' to be directly below the details if there was only one row displayed, otherwise, it was displayed directly below the subtotals.

Hope this helps.

Jarret

|||

It's in a matrix, so group headers and footers aren't apparent options. :(

I could see how that would work in a table though.

|||

When you choose the subtotal option for a group in a matrix, a row does get added. To affect only the subtotal cell in a matrix you need to use the InScope() function. The main thing to understand in the logic is that the subtotal cell for a group is not in scope of that group and hence the function return false for the cell.

For example, say on your rows you have 2 groups called region_group and country_group. You right-click the country textbox and select Subtotal. This adds an additional row containing just the header textbos for the subtotal. You now 3 stages for hiding the subtotal.

1.You now need to add an expression to the details cell for the Visibility -> Hidden property. The expression should be:

=Not InScope("country_group")

This should evaluate to Hidden = True for the total row as it is not in scope of the country_group. If you run this you will probably find that the details cell disappears but the heading remains.

2.Now if you try applying the same expression for Visibility to the subtotal header textbox it should also disappear but will probably leave a blank gap in it's place.

3.If you can apply this same expression to the entire subtotal row (by clicking on the row header) then this should also remove the visible gap.

I'm not sure if the last step is possible as I am unable to test this at the moment (on client site), the first 2 steps should work though.

Hope this helps. Please post the results of your attempts.

|||

Those are great suggestions, but there are no header or footer rows in a matrix.
If I select the entire row that contains the subtotal, a visible property is not exposed.

There are also column groupings after the one I'm mentioning - and if I mess with the group visibility, the successive columns are hidden or blanked out.
I'll see what else can be done to acheive the row hiding.

|||Try taking a look at Actions

Hiding Sub Total Rows depending upon the certain fields

I am trying to hide a sub total row depending upon certain fields . How would i go about doing that?

Thanks in Advance

This is how the report looks like......

Jan Feb March

Revenue R1 2 3 4

want to hide this line subtotal 2 3 4

R2 5 6 7

Total 7 9 11

Hi,

For the table row has visibility property.Write the condition in the expression of the Visibilty Property for that row.

Hope this helps

|||Its not a table . Its a matrix|||

You can select edit the matrix group, in the visibility property, select expression and used the 'runningvalue' to determine when you want not to displace your group subtotal

Hiding Rows....

I have the following in a cell...
=iif(Fields!TRANSDATE.Value >= Parameters!Report_Parameter_Mon.Value,
Fields!TRANSDATE.Value,Nothing)
What I want it to do is if the TRANSDATE is greater than or equal to the
Parameter display the TRANSDATE... otherwise don't display it... The above
code works to a point... but it leaves huge spaces as if it is displaying
the Nothing Rows... e.g.
Parameter = 01/06/04
02/06/04
05/06/04
How do I remove that massive blank area?Place your expression on the "rows visibility" instead of the fields'
contents.
=iif(Fields!TRANSDATE.Value >= Parameters!Report_Parameter_Mon.Value,"True"
, "False")
Click on the left table margin button, examine the properties of that row
and place your expression under the rows visibility property
HTH,
Greg|||Thanks that worked great. Now to figure out a way to get the subtotal of
the group to understand what needs to be totalled.
"Greg Rowland" <greg@.waveltd.com> wrote in message
news:euBy4oKdEHA.3916@.TK2MSFTNGP11.phx.gbl...
> Place your expression on the "rows visibility" instead of the fields'
> contents.
> =iif(Fields!TRANSDATE.Value >=Parameters!Report_Parameter_Mon.Value,"True"
> , "False")
> Click on the left table margin button, examine the properties of that row
> and place your expression under the rows visibility property
> HTH,
> Greg
>|||Click on the left table margin button, of the row in question.
Right click, then click insert row below.
Examine the properties of the newly created row.
Select "Data, grouping/sorting" click the (Ellipsis) button at the right.
Under name enter the group name.
In Group on, Expression, enter the field or fields you wish to discriminate
by.
Example;
Group name
Employees
Expression
=Fields!EmployeeNumber_.Value|||Finally
Add Field Expressions to the newly created rows cells.
Example:
=Sum(Fields!GrossPay.Value)
or
=Iif(Sum(Fields! GrossPay.Value, "Employees")<>0, Sum(Fields!
GrossPay.Value, "Employees"), "")
Other variations;
Font
Font expression =Iif(Sum(Fields!GrossPay.Value, "Employees")<>0, "Bold",
"Normal")
TextDecoration
=Iif(Fields! Sum(Fields!GrossPay.Value, "Employees")<>0, "Underline",
"Normal")|||Fantastic. thanks.
"Greg Rowland" <greg@.waveltd.com> wrote in message
news:uQSQ9RZdEHA.3380@.TK2MSFTNGP12.phx.gbl...
> Finally
> Add Field Expressions to the newly created rows cells.
> Example:
> =Sum(Fields!GrossPay.Value)
> or
> =Iif(Sum(Fields! GrossPay.Value, "Employees")<>0, Sum(Fields!
> GrossPay.Value, "Employees"), "")
> Other variations;
> Font
> Font expression =Iif(Sum(Fields!GrossPay.Value, "Employees")<>0, "Bold",
> "Normal")
> TextDecoration
> =Iif(Fields! Sum(Fields!GrossPay.Value, "Employees")<>0, "Underline",
> "Normal")
>
>
>

Hiding Rows in Matrix

Hi,

I have a report which has got 52 rows of Week1/Week52. Depending on a field Duration (no. of weeks), I want tot display only those rows. i.e. If duration is 3 then display Row of week1, week2 and week3.

I tried to look in the visibility properties of rows in matrix report. But there is no option. Am I doing something wrong?

any help will be appreciated.

regards

Josh

Edit your row group and go to Visibility tab and use whatever expression you want there.

Shyam

|||

Thanks for your reply.

I tried that, but there is no option of visibility when I select the rows. any idea as to what might be wrong?

thanks

Josh

|||

Right click on any of your row and click on Edit Group and then go to Visibility tab.

Shyam

|||

But it shows the values as blank, does not hide the row.

regards

Josh

|||

Try using the same in visibility expressions of all the textboxes in the row.

Shyam

|||

It works then. But still it leaves the blank space. So in essence if there is data for week1 to week4, it is displayed and then for week5 to week52 there is a blank space.

Any idea how this could be avoided?

regards

Josh

Hiding Rows in a Table

Is it possible to hide a row of data if for instance no data exists in that
row. so my report isn't 300 pages long when it should just be 30?
I was looking at the visibility element but didn't see anything to hide
based on expression.
Thanks,
CJThis should be fairly straight forward to accomplish. If we assume that you
want to hide a detail row when fieldx is null you would set the visibility
on the detail row as follows:
=iif(Fields!fieldsx.Value is Nothing, true, false)
Table row visibility is exposed in the Properties window.
The attached report demonstrates this technique.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> Is it possible to hide a row of data if for instance no data exists in
> that
> row. so my report isn't 300 pages long when it should just be 30?
> I was looking at the visibility element but didn't see anything to hide
> based on expression.
> Thanks,
> CJ
>
HidingTableDetailRow.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Company Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Region</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Region">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Region</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Region.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Visibility>
<Hidden>=iif(Fields!Region.Value is Nothing, true,
false)</Hidden>
</Visibility>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<Width>3.33334in</Width>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.875in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>5.00001in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT *
FROM Customers</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||Hi,
Have you tried the properties box for the row/cells? If you right-click the
row/cell in question, you can click on the Advanced button. This will bring
up several tabs, one of which will be Visibility. In the first grouping of
choices, "Initial Visibility", there is an Expression option that you could
use. I'm guessing you could enter your expression there to determine the
row/cell's initial visibility.
I'm pretty new as RS, so I'm not sure if I've helped. :)
-Kelly
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> Is it possible to hide a row of data if for instance no data exists in
that
> row. so my report isn't 300 pages long when it should just be 30?
> I was looking at the visibility element but didn't see anything to hide
> based on expression.
> Thanks,
> CJ
>|||Why was the row created, if there is no data? Perhaps you need to work on
the underlying SQL?
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> Is it possible to hide a row of data if for instance no data exists in
that
> row. so my report isn't 300 pages long when it should just be 30?
> I was looking at the visibility element but didn't see anything to hide
> based on expression.
> Thanks,
> CJ
>|||Not every report is just *simple* SQL. It's a recursive sub report...
I have a complex
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> Why was the row created, if there is no data? Perhaps you need to work on
> the underlying SQL?
>
> "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > Is it possible to hide a row of data if for instance no data exists in
> that
> > row. so my report isn't 300 pages long when it should just be 30?
> >
> > I was looking at the visibility element but didn't see anything to hide
> > based on expression.
> >
> > Thanks,
> > CJ
> >
> >
>|||Regardless, creating and then hiding blank rows implies a fundamental design
flaw. Both take unneccesary time.
Jeff
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> Not every report is just *simple* SQL. It's a recursive sub report...
>
> I have a complex
> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > Why was the row created, if there is no data? Perhaps you need to work
on
> > the underlying SQL?
> >
> >
> > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > Is it possible to hide a row of data if for instance no data exists in
> > that
> > > row. so my report isn't 300 pages long when it should just be 30?
> > >
> > > I was looking at the visibility element but didn't see anything to
hide
> > > based on expression.
> > >
> > > Thanks,
> > > CJ
> > >
> > >
> >
> >
>|||How do you figure?
Say we have a tree structure we are trying to report, such as?
Line A
-- Line B (child to a)
-- Line C (child to b)
-- Line D( child to A)
-- blank (no children)
Thats a fundamental design flaw?
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:ewPtflftEHA.1048@.tk2msftngp13.phx.gbl...
> Regardless, creating and then hiding blank rows implies a fundamental
design
> flaw. Both take unneccesary time.
> Jeff
> "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> > Not every report is just *simple* SQL. It's a recursive sub report...
> >
> >
> >
> > I have a complex
> > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > > Why was the row created, if there is no data? Perhaps you need to work
> on
> > > the underlying SQL?
> > >
> > >
> > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > > Is it possible to hide a row of data if for instance no data exists
in
> > > that
> > > > row. so my report isn't 300 pages long when it should just be 30?
> > > >
> > > > I was looking at the visibility element but didn't see anything to
> hide
> > > > based on expression.
> > > >
> > > > Thanks,
> > > > CJ
> > > >
> > > >
> > >
> > >
> >
> >
>|||This is a classic drill down. Doing a drill down you will not have any
blanks. In drill down you can set it either be expanded or not (for instance
if you wanted to show line A and they have to click on the + to expand it.
Or you can show Line A, Line B, Line D and they click on the + for Line B to
expand it.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:u4V6oSgtEHA.1216@.TK2MSFTNGP10.phx.gbl...
> How do you figure?
> Say we have a tree structure we are trying to report, such as?
> Line A
> -- Line B (child to a)
> -- Line C (child to b)
> -- Line D( child to A)
> -- blank (no children)
> Thats a fundamental design flaw?
> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> news:ewPtflftEHA.1048@.tk2msftngp13.phx.gbl...
> > Regardless, creating and then hiding blank rows implies a fundamental
> design
> > flaw. Both take unneccesary time.
> >
> > Jeff
> >
> > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> > > Not every report is just *simple* SQL. It's a recursive sub report...
> > >
> > >
> > >
> > > I have a complex
> > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > > > Why was the row created, if there is no data? Perhaps you need to
work
> > on
> > > > the underlying SQL?
> > > >
> > > >
> > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > > > Is it possible to hide a row of data if for instance no data
exists
> in
> > > > that
> > > > > row. so my report isn't 300 pages long when it should just be 30?
> > > > >
> > > > > I was looking at the visibility element but didn't see anything to
> > hide
> > > > > based on expression.
> > > > >
> > > > > Thanks,
> > > > > CJ
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yes a flaw, thankyou. Don't show blanks when there are no children (')
Takes time to build a blank row, and time to hide it.
Duh
Jeff
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:u4V6oSgtEHA.1216@.TK2MSFTNGP10.phx.gbl...
> How do you figure?
> Say we have a tree structure we are trying to report, such as?
> Line A
> -- Line B (child to a)
> -- Line C (child to b)
> -- Line D( child to A)
> -- blank (no children)
> Thats a fundamental design flaw?
> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> news:ewPtflftEHA.1048@.tk2msftngp13.phx.gbl...
> > Regardless, creating and then hiding blank rows implies a fundamental
> design
> > flaw. Both take unneccesary time.
> >
> > Jeff
> >
> > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> > > Not every report is just *simple* SQL. It's a recursive sub report...
> > >
> > >
> > >
> > > I have a complex
> > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > > > Why was the row created, if there is no data? Perhaps you need to
work
> > on
> > > > the underlying SQL?
> > > >
> > > >
> > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > > > Is it possible to hide a row of data if for instance no data
exists
> in
> > > > that
> > > > > row. so my report isn't 300 pages long when it should just be 30?
> > > > >
> > > > > I was looking at the visibility element but didn't see anything to
> > hide
> > > > > based on expression.
> > > > >
> > > > > Thanks,
> > > > > CJ
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Super, thanks for being so courteous about it...
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:esNoOFitEHA.3788@.TK2MSFTNGP09.phx.gbl...
> Yes a flaw, thankyou. Don't show blanks when there are no children (')
> Takes time to build a blank row, and time to hide it.
> Duh
> Jeff
> "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> news:u4V6oSgtEHA.1216@.TK2MSFTNGP10.phx.gbl...
> > How do you figure?
> >
> > Say we have a tree structure we are trying to report, such as?
> >
> > Line A
> > -- Line B (child to a)
> > -- Line C (child to b)
> > -- Line D( child to A)
> > -- blank (no children)
> >
> > Thats a fundamental design flaw?
> >
> > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > news:ewPtflftEHA.1048@.tk2msftngp13.phx.gbl...
> > > Regardless, creating and then hiding blank rows implies a fundamental
> > design
> > > flaw. Both take unneccesary time.
> > >
> > > Jeff
> > >
> > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> > > > Not every report is just *simple* SQL. It's a recursive sub
report...
> > > >
> > > >
> > > >
> > > > I have a complex
> > > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > > news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > > > > Why was the row created, if there is no data? Perhaps you need to
> work
> > > on
> > > > > the underlying SQL?
> > > > >
> > > > >
> > > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > > > > Is it possible to hide a row of data if for instance no data
> exists
> > in
> > > > > that
> > > > > > row. so my report isn't 300 pages long when it should just be
30?
> > > > > >
> > > > > > I was looking at the visibility element but didn't see anything
to
> > > hide
> > > > > > based on expression.
> > > > > >
> > > > > > Thanks,
> > > > > > CJ
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Learning can be hard.
"CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
news:#l04yBqtEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Super, thanks for being so courteous about it...
>
> "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> news:esNoOFitEHA.3788@.TK2MSFTNGP09.phx.gbl...
> > Yes a flaw, thankyou. Don't show blanks when there are no children (')
> > Takes time to build a blank row, and time to hide it.
> >
> > Duh
> >
> > Jeff
> > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > news:u4V6oSgtEHA.1216@.TK2MSFTNGP10.phx.gbl...
> > > How do you figure?
> > >
> > > Say we have a tree structure we are trying to report, such as?
> > >
> > > Line A
> > > -- Line B (child to a)
> > > -- Line C (child to b)
> > > -- Line D( child to A)
> > > -- blank (no children)
> > >
> > > Thats a fundamental design flaw?
> > >
> > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > news:ewPtflftEHA.1048@.tk2msftngp13.phx.gbl...
> > > > Regardless, creating and then hiding blank rows implies a
fundamental
> > > design
> > > > flaw. Both take unneccesary time.
> > > >
> > > > Jeff
> > > >
> > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> > > > > Not every report is just *simple* SQL. It's a recursive sub
> report...
> > > > >
> > > > >
> > > > >
> > > > > I have a complex
> > > > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > > > news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > > > > > Why was the row created, if there is no data? Perhaps you need
to
> > work
> > > > on
> > > > > > the underlying SQL?
> > > > > >
> > > > > >
> > > > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > > > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > > > > > Is it possible to hide a row of data if for instance no data
> > exists
> > > in
> > > > > > that
> > > > > > > row. so my report isn't 300 pages long when it should just be
> 30?
> > > > > > >
> > > > > > > I was looking at the visibility element but didn't see
anything
> to
> > > > hide
> > > > > > > based on expression.
> > > > > > >
> > > > > > > Thanks,
> > > > > > > CJ
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Alright, so then I don't understand the drill down then... How does this
create a visual heirachial grouping?
does that make sense? at least in report services? I can do it in sql no
problem, just how to get reporting to respond accordinginly... curretnly,
I'm doing it with a subreport, within a sub report (that references the
parent subreport to create the recursive definition)
"Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
news:uouebyrtEHA.1272@.TK2MSFTNGP10.phx.gbl...
> Learning can be hard.
>
> "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> news:#l04yBqtEHA.2624@.TK2MSFTNGP11.phx.gbl...
> > Super, thanks for being so courteous about it...
> >
> >
> > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > news:esNoOFitEHA.3788@.TK2MSFTNGP09.phx.gbl...
> > > Yes a flaw, thankyou. Don't show blanks when there are no children
(')
> > > Takes time to build a blank row, and time to hide it.
> > >
> > > Duh
> > >
> > > Jeff
> > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > news:u4V6oSgtEHA.1216@.TK2MSFTNGP10.phx.gbl...
> > > > How do you figure?
> > > >
> > > > Say we have a tree structure we are trying to report, such as?
> > > >
> > > > Line A
> > > > -- Line B (child to a)
> > > > -- Line C (child to b)
> > > > -- Line D( child to A)
> > > > -- blank (no children)
> > > >
> > > > Thats a fundamental design flaw?
> > > >
> > > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in message
> > > > news:ewPtflftEHA.1048@.tk2msftngp13.phx.gbl...
> > > > > Regardless, creating and then hiding blank rows implies a
> fundamental
> > > > design
> > > > > flaw. Both take unneccesary time.
> > > > >
> > > > > Jeff
> > > > >
> > > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > > news:edGjn3QtEHA.1336@.tk2msftngp13.phx.gbl...
> > > > > > Not every report is just *simple* SQL. It's a recursive sub
> > report...
> > > > > >
> > > > > >
> > > > > >
> > > > > > I have a complex
> > > > > > "Jeff Dillon" <jeff@.removeemergencyreporting.com> wrote in
message
> > > > > > news:ejERQKwsEHA.2072@.tk2msftngp13.phx.gbl...
> > > > > > > Why was the row created, if there is no data? Perhaps you need
> to
> > > work
> > > > > on
> > > > > > > the underlying SQL?
> > > > > > >
> > > > > > >
> > > > > > > "CJ Taylor" <[cege] at [tavayn] dit commmmm> wrote in message
> > > > > > > news:e6JtN0ssEHA.3984@.TK2MSFTNGP09.phx.gbl...
> > > > > > > > Is it possible to hide a row of data if for instance no data
> > > exists
> > > > in
> > > > > > > that
> > > > > > > > row. so my report isn't 300 pages long when it should just
be
> > 30?
> > > > > > > >
> > > > > > > > I was looking at the visibility element but didn't see
> anything
> > to
> > > > > hide
> > > > > > > > based on expression.
> > > > > > > >
> > > > > > > > Thanks,
> > > > > > > > CJ
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Hiding rows in a matrix and still showing subtotals for that row

Hi
I'm trying to tidy up a report that has a detail row that's not needed but
the subtotal row for that row is... hope that makes sense. The subtotal is
in fact is an average of the detail row. If I hide the detail row, the AVG
row also disappears! This is more or less what it looks like:
Machine Name | Cloth | Utilised Looms | 1 3 5 6
Available Looms | 6 4 7 6
Total Utilised Looms | 1 3 5 6
Available Looms | 6 4 7 6
What I'm trying to do is hide the second row...
Any ideas would be appreciated.
Many thanks
Rob
--
Message posted via http://www.sqlmonster.comIf you edit the properties of de detail-cel an set the visibility/hidden
property =TRUE it should work
"robhob via SQLMonster.com" wrote:
> Hi
> I'm trying to tidy up a report that has a detail row that's not needed but
> the subtotal row for that row is... hope that makes sense. The subtotal is
> in fact is an average of the detail row. If I hide the detail row, the AVG
> row also disappears! This is more or less what it looks like:
> Machine Name | Cloth | Utilised Looms | 1 3 5 6
> Available Looms | 6 4 7 6
> Total Utilised Looms | 1 3 5 6
> Available Looms | 6 4 7 6
> What I'm trying to do is hide the second row...
> Any ideas would be appreciated.
> Many thanks
> Rob
> --
> Message posted via http://www.sqlmonster.com
>

hiding null rows from a matrix

All,
I have a matrix that is being populated by a sproc.
The sproc returns data which are broken into sections by fields and
then sorted by another field.
The matrix is group on the section designator.
However, each section is getting an initial row that is null.
I want to drop out the null rows from the matrix.
I am not allowed to change the sproc.
Thanks in advance,
MI have a couple of typos and things to add.
I was trying to say that the matrix is grouped on the section
designator.
within each section this is a null row showing up.
I just want the matrix to filter out the nulls.
I don't have that option to do it from within the sproc.|||Hi Max,
Did you find a solution for this, even I am facing the same problem, I need
to hide the empty rows in a matrix.
Please reply back if you have found any solution for this.
Thanks,
Kiran