Does RS support to change the report size automatically?-URGENT!!!

I will use MSRS to create reports in a .Net Smart Client project later.

For some kinds of report, there are some customized columns which may be displayed or not. I design these reports like this: first create all the columns, then set the "Visibility-Hidden" property of those customized columns using expression which including some report parameters.Thus, these customized columns can be displayed or hidden by setting the value of the report parameters at the runtime. But another problem emerged.The size of report backgroud can not changed automatically along with the length of DataTable.So if there are some columns be hidden, there will be some margin on the right of the report and the Title of report was still in the center of the original report,not changed to the center of the new one. Due to the "Size " property of report and the "Size"&"Location"properties of textbox does not support expression.

Is there any one can give me some solution?

I am not sure whether the current version of MSRS support such kind of requirement?If not,will the MSRS final release comes out on November 7 support it?

Thanks!

Hi!

I also have this problem! I am building subreports that are getting more and more generic, but now I have noticed that the (optionally) hidden rows in the subreport's table is showing up as blank space on the main report. That makes this useful way of making the subreports nothing more than useless.

I have found no way to set the size of the subreport "background" or rectangle smaller than the designed area.

The reporting services seems more and more tied down to me. It has to have much more abilities than it currently has to survive. At the moment automation of Word seems much more flexible, but is client based. Still, in many situations the current RS will not deliver enough to fulfill the requirements. I hope this will change soon.

/Michael

|||The report never shrinks the size of the body so hiding a bunch of columns won't help your report. If you want a title to span the items in a table, put it in the table header, similar to how this matrix report was done: http://blogs.msdn.com/bwelcker/archive/2005/05/11/416720.aspx.|||But I am using a "table" as a way of displaying different checkboxes and texts, as a form, and sometimes one or more of the rows in these (generic) subreports are not to be used and I hide them (that's what makes it more generic).

I am not using it to show a list of information but as a way of showing a dynamic one-page form.

When I hide a row it shows up as approximately half a row of blank space on the main report. In this case the Visiblity setting is useless since there is too much space between subreports on the main report.

Does RS support to change the report size automatically?-URGENT!!!

I will use MSRS to create reports in a .Net Smart Client project later.

For some kinds of report, there are some customized columns which may be displayed or not. I design these reports like this: first create all the columns, then set the "Visibility-Hidden" property of those customized columns using expression which including some report parameters.Thus, these customized columns can be displayed or hidden by setting the value of the report parameters at the runtime. But another problem emerged.The size of report backgroud can not changed automatically along with the length of DataTable.So if there are some columns be hidden, there will be some margin on the right of the report and the Title of report was still in the center of the original report,not changed to the center of the new one. Due to the "Size " property of report and the "Size"&"Location"properties of textbox does not support expression.

Is there any one can give me some solution?

I am not sure whether the current version of MSRS support such kind of requirement?If not,will the MSRS final release comes out on November 7 support it?

Thanks!

Hi!

I also have this problem! I am building subreports that are getting more and more generic, but now I have noticed that the (optionally) hidden rows in the subreport's table is showing up as blank space on the main report. That makes this useful way of making the subreports nothing more than useless.

I have found no way to set the size of the subreport "background" or rectangle smaller than the designed area.

The reporting services seems more and more tied down to me. It has to have much more abilities than it currently has to survive. At the moment automation of Word seems much more flexible, but is client based. Still, in many situations the current RS will not deliver enough to fulfill the requirements. I hope this will change soon.

/Michael

|||The report never shrinks the size of the body so hiding a bunch of columns won't help your report. If you want a title to span the items in a table, put it in the table header, similar to how this matrix report was done: http://blogs.msdn.com/bwelcker/archive/2005/05/11/416720.aspx.|||But I am using a "table" as a way of displaying different checkboxes and texts, as a form, and sometimes one or more of the rows in these (generic) subreports are not to be used and I hide them (that's what makes it more generic).

I am not using it to show a list of information but as a way of showing a dynamic one-page form.

When I hide a row it shows up as approximately half a row of blank space on the main report. In this case the Visiblity setting is useless since there is too much space between subreports on the main report.sql

Does Interactive Sort works with Snapshots and Cached Reports?

I have created reports in RS 2005 using interactive sort on some columns.
When the reports are rendered real time, the sort works perfectly. When the
execution is changed to Snapshot, the sorting no longer works. When the
execution is set to Cached, the sort works the first time the report is
viewed, but not in subsequent viewings.
If this is a known bug, I'll give up on including this feature in my
reports. Can anyone confirm if this is supposed to work in RTM or SP1, or if
it's still on the bug list?
Thanks.I have this exact problem in designer mode with Visual 2005!
I have no clue what is going on - at times with RS2005 - was so stable with
2000 - now I am getting frustrated!
"Denise" wrote:
> I have created reports in RS 2005 using interactive sort on some columns.
> When the reports are rendered real time, the sort works perfectly. When the
> execution is changed to Snapshot, the sorting no longer works. When the
> execution is set to Cached, the sort works the first time the report is
> viewed, but not in subsequent viewings.
> If this is a known bug, I'll give up on including this feature in my
> reports. Can anyone confirm if this is supposed to work in RTM or SP1, or if
> it's still on the bug list?
> Thanks.

Does Identity columns... create an index by default

Hi,
If I create an identity column on a sql server table.... does "sql server"
create an index too...
Or do I have to create an index explicitly.
Thanks
Nalaka"Nalaka" <nalaka12@.nospam.nospam> wrote in message
news:OXPawNnGIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Hi,
> If I create an identity column on a sql server table.... does "sql
> server" create an index too...
> Or do I have to create an index explicitly.
> Thanks
> Nalaka
>
>
No it doesn't. Assuming, you want the column to be unique you should create
a PRIMARY KEY or UNIQUE constraint on the column. A constraint does
automatically create an index.
--
David Portas|||Thanks David
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O23KLQnGIHA.5980@.TK2MSFTNGP04.phx.gbl...
> "Nalaka" <nalaka12@.nospam.nospam> wrote in message
> news:OXPawNnGIHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> If I create an identity column on a sql server table.... does "sql
>> server" create an index too...
>> Or do I have to create an index explicitly.
>> Thanks
>> Nalaka
>>
>>
> No it doesn't. Assuming, you want the column to be unique you should
> create a PRIMARY KEY or UNIQUE constraint on the column. A constraint does
> automatically create an index.
> --
> David Portas
>

Does FTS in SQL 7 have known issues with not indexing records?

I've been testing using a full text index on a few columns in one of my
databases, and I'm having trouble with the index appearing to miss records.
I have stopped all updates on my database, and done a full population of the
SearchTitle field in my STK table. I have then waited until the full text
update has completed, and there are no errors in the event log. I then tried
the following queries:
SELECT STK.ID, STK.SearchTitle FROM STK WHERE
CONTAINS(STK.SearchTitle,'"being" and "jordan"')
Result is zero records. I also tried
SELECT STK.ID, STK.SearchTitle FROM STK WHERE
CONTAINS(STK.SearchTitle,'"being jordan"')
Again, zero records. I then tried
SELECT STK.ID, STK.SearchTitle FROM STK WHERE STK.SearchTitle LIKE '% being
%' and STK.SearchTitle LIKE '% jordan %')
and get 1 result, which is what I expect.
The SearchTitle field contains a stripped down version of book titles in my
table, all fields have a space followed by the words in the table followed
by an ending space (this is so that the current searches I do via the last
example work on whole words without ever finding partial matches). In the
above case the SearchTitle field contains just ' being jordan ' (without the
quotes).
SearchTitle is a varchar(255) field, and there are just under 365572 rows in
the table. The FT index shows 339632 items with 380112 unique words. I have
emptied the noise word files because they were causing problems with
searches, so I know it's not a noise word issue. This indicates that FTS has
skipped around 26000 records. I am currently running another full population
to see if the problem is a temporary one, but I was wondering if there are
known issues with FT indexing that I might be experiencing.
Dan
Being could be a noise word for the noise word list. Do you get the same
number of hits if you search on Jordan as you get if you search on Like '%
Jordan %'?
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:urZxCI5rEHA.1204@.TK2MSFTNGP12.phx.gbl...
> I've been testing using a full text index on a few columns in one of my
> databases, and I'm having trouble with the index appearing to miss
> records.
> I have stopped all updates on my database, and done a full population of
> the
> SearchTitle field in my STK table. I have then waited until the full text
> update has completed, and there are no errors in the event log. I then
> tried
> the following queries:
> SELECT STK.ID, STK.SearchTitle FROM STK WHERE
> CONTAINS(STK.SearchTitle,'"being" and "jordan"')
> Result is zero records. I also tried
> SELECT STK.ID, STK.SearchTitle FROM STK WHERE
> CONTAINS(STK.SearchTitle,'"being jordan"')
> Again, zero records. I then tried
> SELECT STK.ID, STK.SearchTitle FROM STK WHERE STK.SearchTitle LIKE '%
> being
> %' and STK.SearchTitle LIKE '% jordan %')
> and get 1 result, which is what I expect.
> The SearchTitle field contains a stripped down version of book titles in
> my
> table, all fields have a space followed by the words in the table followed
> by an ending space (this is so that the current searches I do via the last
> example work on whole words without ever finding partial matches). In the
> above case the SearchTitle field contains just ' being jordan ' (without
> the
> quotes).
> SearchTitle is a varchar(255) field, and there are just under 365572 rows
> in
> the table. The FT index shows 339632 items with 380112 unique words. I
> have
> emptied the noise word files because they were causing problems with
> searches, so I know it's not a noise word issue. This indicates that FTS
> has
> skipped around 26000 records. I am currently running another full
> population
> to see if the problem is a temporary one, but I was wondering if there are
> known issues with FT indexing that I might be experiencing.
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u71T0k6rEHA.3076@.TK2MSFTNGP10.phx.gbl...
> Being could be a noise word for the noise word list. Do you get the same
> number of hits if you search on Jordan as you get if you search on Like '%
> Jordan %'?
No, instead of getting the expected 58 titles, I get 54. I have cleared the
noise word list before generating the index - SQL was throwing out errors if
1 noise word was passed into the search even if there were other non-noise
words, so I decided that rather than parsing out the noise words and getting
in-exact matches for what customers enter in their searches I'd just index
everything.
Dan
|||After running a full population again it appears to have now indexed
everything. I'll be doing some more preliminary testing before putting this
live though, last thing I want is for customers not to be able to find items
in our database (the example I gave of Being Jordan was the top selling book
a few weeks ago, not having that listed in search results would have been
disastrous for us.
Dan
|||"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23jpG03CsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> After running a full population again it appears to have now indexed
> everything. I'll be doing some more preliminary testing before putting
this
> live though, last thing I want is for customers not to be able to find
items
> in our database (the example I gave of Being Jordan was the top selling
book
> a few weeks ago, not having that listed in search results would have been
> disastrous for us.
Looking at the event logs I've noticed that despite the item count being
correct, in the information event at completion of an incremental update
there is:
"Detected end of incremental crawl for project <SQLServer SQL0002300005>.
Successfully processed 365614 documents, 0K. Failed to filter 13 documents.
Modified 282 documents."
Followed by a warning event with ID 3051 containing:
"Detected 13 URLs that could not be reached or were denied access in project
<SQLServer SQL0002300005>."
I assume that for some reason 13 items couldn't be accessed when running the
incremental search. I'm running another one now to see if I get the same
messages, as it's only taking around 20 mins to run the incremental compared
to 4 hours running the full population.
I've just looked back at the full population I ran yesterday, and have
noticed that it also logged a warning event (I have updated some records
since this was built, hence the difference in the item counts). Here's the
information one first:
"Detected end of crawl for project <SQLServer SQL0002300005>. Successfully
processed 365652 documents, 0K. Failed to filter 0 documents."
Followed by a warning event:
"Detected 365452 URLs that could not be reached or were denied access in
project <SQLServer SQL0002300005>."
Whereas the previous pair of errors made sense in that the information
message indicates that 13 records couldn't be indexed, and the warning seems
to confirm this, the pair for the full population are confusing in that they
don't match. Does this indicate a potential problem in the indexing system?
Or is the logging of mismatched item counts in the event log a normal
occurrence?
Dan
|||make sure your noise word list has a single space in it, otherwise it will
be using the noise word list found in %windir%\system32.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:urhcK2CsEHA.324@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u71T0k6rEHA.3076@.TK2MSFTNGP10.phx.gbl...
'%
> No, instead of getting the expected 58 titles, I get 54. I have cleared
the
> noise word list before generating the index - SQL was throwing out errors
if
> 1 noise word was passed into the search even if there were other non-noise
> words, so I decided that rather than parsing out the noise words and
getting
> in-exact matches for what customers enter in their searches I'd just index
> everything.
> Dan
>
|||I strongly suggest you move to SQL 2000 for performance and scalability
reasons.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23jpG03CsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> After running a full population again it appears to have now indexed
> everything. I'll be doing some more preliminary testing before putting
this
> live though, last thing I want is for customers not to be able to find
items
> in our database (the example I gave of Being Jordan was the top selling
book
> a few weeks ago, not having that listed in search results would have been
> disastrous for us.
> Dan
>
|||You can get these errors for a variety of reasons.
You get 0 rows could not be indexed typically for the below reasons.
1) the account SQL Server runs under is not registered with MSSearch. You
can get this when you change the SQL Server service account through control
panel instead of via Enterprise Manager. This will cause the entire table
not to be indexed.
2) verify that the login BUILTIN\Administrator exists in the login folder.
If it does not exist add it in.
You will get xxx rows could not be indexed typically for the below reasons
1) one or more rows were deleted since the last population
2) a row was locked
3) a row could contain a very large amount of data which could not be
extracted in the time MSSearch waits to extract each row.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:u4xryIDsEHA.1816@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:%23jpG03CsEHA.3748@.TK2MSFTNGP09.phx.gbl...
> this
> items
> book
been
> Looking at the event logs I've noticed that despite the item count being
> correct, in the information event at completion of an incremental update
> there is:
> "Detected end of incremental crawl for project <SQLServer SQL0002300005>.
> Successfully processed 365614 documents, 0K. Failed to filter 13
documents.
> Modified 282 documents."
> Followed by a warning event with ID 3051 containing:
> "Detected 13 URLs that could not be reached or were denied access in
project
> <SQLServer SQL0002300005>."
> I assume that for some reason 13 items couldn't be accessed when running
the
> incremental search. I'm running another one now to see if I get the same
> messages, as it's only taking around 20 mins to run the incremental
compared
> to 4 hours running the full population.
>
> I've just looked back at the full population I ran yesterday, and have
> noticed that it also logged a warning event (I have updated some records
> since this was built, hence the difference in the item counts). Here's the
> information one first:
> "Detected end of crawl for project <SQLServer SQL0002300005>. Successfully
> processed 365652 documents, 0K. Failed to filter 0 documents."
> Followed by a warning event:
> "Detected 365452 URLs that could not be reached or were denied access in
> project <SQLServer SQL0002300005>."
> Whereas the previous pair of errors made sense in that the information
> message indicates that 13 records couldn't be indexed, and the warning
seems
> to confirm this, the pair for the full population are confusing in that
they
> don't match. Does this indicate a potential problem in the indexing
system?
> Or is the logging of mismatched item counts in the event log a normal
> occurrence?
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eitvdMFsEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I strongly suggest you move to SQL 2000 for performance and scalability
> reasons.
Unfortunately this is not an option at present due to cost - I would need
not only the SQL Server 2000 license, but also 2 SQL Server processor
licenses (dual processor server) so that the database is licensed for use on
my web sites. Last time I looked that was a hefty sum.
Dan
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eNpYhLFsEHA.2732@.TK2MSFTNGP09.phx.gbl...
> make sure your noise word list has a single space in it, otherwise it will
> be using the noise word list found in %windir%\system32.
Yes, I did leave a single space in the noise word files after reading a few
posts in here about it.
Dan

Does DB Size decrease when I delete a huge table ?

Hi,
My DB size (Right click on DB Name, Data Files tab, Space Allocated field) was 10914 MB.
I delete a huge table (1.2 million records * 15 columns).
I checked the db size again. It didnt change.
Shouldn't it decrease because I delete a huge table ??The size will only decrease if you use the shrinkdb command.|||Originally posted by rokslide
The size will only decrease if you use the shrinkdb command.

When and why should we use Shrink DB ??|||Originally posted by rokslide
The size will only decrease if you use the shrinkdb command.

Would the deleted data be still on the database physical file ?|||The use of shrinkdb depends on who you talk to,... I really don't have a theory about it except to say that if the database is taking up too much space, shrink it...

As for the data still being in the database file... I would guess (and it's just a guess) that it would still be there but it would be unaccessible via the database. I think the delete would work much like deleting a file off your hard drive, all that is really deleted is the file header information, the data is still there, but you can't access it unless you use a tool to specificly locate the data.

Does that make sense? Can anyone else offer more advise??|||Originally posted by rokslide
The use of shrinkdb depends on who you talk to,... I really don't have a theory about it except to say that if the database is taking up too much space, shrink it...

As for the data still being in the database file... I would guess (and it's just a guess) that it would still be there but it would be unaccessible via the database. I think the delete would work much like deleting a file off your hard drive, all that is really deleted is the file header information, the data is still there, but you can't access it unless you use a tool to specificly locate the data.

Does that make sense? Can anyone else offer more advise??

Can u give a quick example of how I use ShrinkDB ?|||Originally posted by forXLDB
Can u give a quick example of how I use ShrinkDB ?
How do I know what size to reduce to ?
(If the size doesnt decrease even after deleting tables, how do i know what must be the exact size occupied by all the tables)...
I would have deleted and created lots of huge files involving around a million records atleast 10 times..

and above all, does the size effect the sql server performance in any way ?|||straight from the books online

This example decreases the size of the files in the UserDB user database to allow 10 percent free space in the files of UserDB.

DBCC SHRINKDATABASE (UserDB, 10)
GO

or you can do it through the EM|||Originally posted by rokslide
straight from the books online

or you can do it through the EM

i just did use for another test database, it shrunk more than half the size...

thx anyway !!1

Still looking for the answer on any performance degradation if the db file size is more ?|||Guessing once again I would say that it shouldn't affect the speed to any great extent,.. fragmentation of the database file would... there are some commands/functions that you can use to find out this kinda stuff but I have never used them so I can't really help alot I'm sorry.|||Originally posted by rokslide
Guessing once again I would say that it shouldn't affect the speed to any great extent,.. fragmentation of the database file would... there are some commands/functions that you can use to find out this kinda stuff but I have never used them so I can't really help alot I'm sorry.
thx again...

lemme know any functions !!!|||Since the log file takes the big size of the database, you can use the following script to reduce the file size:

USE DatabaseName
GO
DBCC SHRINKFILE ('DatabaseName_Log', Size)
GO

Here "Size" is the number of MB, i.e., if you put 100, that means the file will be shrinked to 100 MB.|||Since you are already working in em - when you right click on the database go to all task ->shrink database ->files... Under database file, look at the data file and log file. What is the current size/space used ? Are you backing up the database/transaction log ? What is the recovery model ?

What is the performance issue ?|||You can use "dbcc showcontig" to display fragmentation info. Yes performance will be affected - you need to either drop/recreate the indexes, use dbcc dbreindex or dbcc indexdefrag. Since you deleted so many records (more than half), fragmentation is a good start for performance issues.|||Do you have access to Books Online (BOL) ?|||An addition to rnealejr's post on dbcc indexdefrag and recreating indexes. If you have tables with a large amount of data input or modification take a good look at the indexes. If you use a non-seed value for your index then increasing the fillfactor number can help to reduce fragmentation in the future. I.E if your customer table uses first four characters of the name plus the zipcode for a customerID then you would be inserting new records into the middle of the table frequently. Once the fillfactor is used up then the next record inserted will create a new page and now your data is getting fragmented which does impact performance as mentioned be other posters.

Now that you've opened this pandoras box, you could spend a week in BOL learning how to tweak each tables index based on usage, calculating how many records fit on a page, use of varchar over char, benifits/penalties of seed indexes...............|||Originally posted by rnealejr
Do you have access to Books Online (BOL) ?
Yes. I can read BOL

Does Column Order Affect Clustered Index Performance?

In a table definition, does the physical location of the columns that make up the clustered index affect the performance of a clustered index?

Table A
Name varchar(30) Not NULL
Home_Phone char(10) NULL
Other_Phone char(10) NULL
Company_ID char(9) NOT NULL <--PRIMARY KEY
Location_# int NOT NULL <--PRIMARY KEY
Sex char(1) NOT NULL
Age int NOT NULL

Table B
Company_ID char(9) NOT NULL <--PRIMARY KEY
Location_# int NOT NULL <--PRIMARY KEY
Name varchar(30) Not NULL
Home_Phone char(10) NULL
Other_Phone char(10) NULL
Sex char(1) NOT NULL
Age int NOT NULL

I always thought it did, but I can't find any documentation to back this up. Perhaps I'm mistaken.

Thanks, DaveI don't believe the order of the columns make a difference. You just want to know that the clustered indexes is stored in sorted order at the leaf level, on inserts, updates, the clustered index is taken into consideration.

HTH

Documentation of the Tables in SQL Server

Hi all
I have 15 tables in my database and I want to create a pdf file with description about all the columns of these tables(Data Model)
Few questions
How can I get all the column names in the excel or pdf file?
Is there any tool which can give me the output template for these columns and then I just have to fill the description for each column?
ThanksYou can query the Information _Schema tables to get a listing of tables and their columns.|||Did you ask your dba?|||Did you ask your dba?

Better yet ... did you buy your dba lunch?|||My preference would be to create a script that will create the tables (complete with constraints, etc) and then comment that script liberally. You can use Visual Studio, SQL Enterprise Mangler, or SQL-DMO to make the script if you don't already have one.

Once you've done this, check the fully commented script into source code control too!

-PatP|||Thanks guys for the responses
Here I am talking about an already built system and I am trying to document the database|||You can look on Red Gate i think they have a tool for that|||You can look on Red Gate i think they have a tool for that

so do I, and it's better than redgate's. see the link in my sig. :)|||Thanks
I wish there was any free/open source solution!|||Pat's solution (post #5) is free. I have a binder on my desk that's full of well commented "script" files of all my table definitions. Columns, indexes, triggers, etc. Every time I change a table, I regenerate the script and put new comments in. The extra 5 minutes is worth the effort.|||I don't consider a pile of commented source files as documentation. Would the the source code to SQL Server (heavily commented) tell you how to use it? Not really.

There are some free ones out there (but you get what you pay for). for example:

http://www.codeplex.com/datadictionary