Adjusting column width in queried dataset

I am trying to eliminate the extra space in columns that have been returned from a DB query...the tables have character lengths of 40, I would like to return just the necessary characters.

Thanks in advance.

Mark.

Did you try rtrim(ltrim(columnname) in the query?

cheers,

Andrew

|||

Thanks Andrew,

rtrim(columnname) did it.

Cheers,

Mark.

Additional area in main report after sub-report

I have a main report that has 17 sub-reports in it. I have had some problems with additional space in the main report, after sub-reports that have no data in them. In the actual sub reports I have expressions set up for each line in the tables to hide the row if there is an empty string in it. (IIF(Fields!FieldName.Value = "", True, False)).

Is there a way to set up in the main report is the sub report has no data in it to hide the sub-report, and not show the additional space in them. I have tried putting the sub reports in the main report by themselves, and also put them into a table, and have encountered the same results each time.

I did find a site that suggests referencing a Field in the expression for that row, but this would involve us changing the stored procedure and adding additional Fields into the main report stored procedure. Also each sub-report has its own stored procedure to return the data needed for that sub report.

Any ideas?

Hi,

Though the post is a year old and I dint find any answer to ur post so I was just wondering if you were able to find out a fix for this. I am facing a similar problem. I also have a subreport that has some fields where as their visibility is dependent on a parameter. If that's set to yes then the field will show up else not. I can acheive this in the subreport but not in the main report. THe main report doesn't show the field at all. Any suggestions?

Thanks,

Rashi

|||

Another way is to create a custom assembly in .net and have a shared variable. In the subreport, set the shared variable value to 0 based on your condition. In your main report, refer to that shared variable and set the visibility of the subreport based on that.

Shyam

|||

Thanks!

Rgds,

Rashi

|||Can you please mark the post as answer?|||Sure, I can but I dint implement it since I had some other problem related to my report..

Additional area in main report after sub-report

I have a main report that has 17 sub-reports in it. I have had some problems with additional space in the main report, after sub-reports that have no data in them. In the actual sub reports I have expressions set up for each line in the tables to hide the row if there is an empty string in it. (IIF(Fields!FieldName.Value = "", True, False)).

Is there a way to set up in the main report is the sub report has no data in it to hide the sub-report, and not show the additional space in them. I have tried putting the sub reports in the main report by themselves, and also put them into a table, and have encountered the same results each time.

I did find a site that suggests referencing a Field in the expression for that row, but this would involve us changing the stored procedure and adding additional Fields into the main report stored procedure. Also each sub-report has its own stored procedure to return the data needed for that sub report.

Any ideas?

Hi,

Though the post is a year old and I dint find any answer to ur post so I was just wondering if you were able to find out a fix for this. I am facing a similar problem. I also have a subreport that has some fields where as their visibility is dependent on a parameter. If that's set to yes then the field will show up else not. I can acheive this in the subreport but not in the main report. THe main report doesn't show the field at all. Any suggestions?

Thanks,

Rashi

|||

Another way is to create a custom assembly in .net and have a shared variable. In the subreport, set the shared variable value to 0 based on your condition. In your main report, refer to that shared variable and set the visibility of the subreport based on that.

Shyam

|||

Thanks!

Rgds,

Rashi

|||Can you please mark the post as answer?|||Sure, I can but I dint implement it since I had some other problem related to my report..

Adding vertical space between rows

I am trying to create a mailing labels report but can't seem to get any vertical space between the rows. I see a cellpadding setting but nothing equivalent to cellspacing. Can anyone help me out?

Thanks.Try adding a text box, turning off can increase and can decrease in properties, under advanced, choose format, and work with the 'amount of space to leave on each side of report item.
Also try adding characters into the box, and set the color to be the same as background so that it is invisible.

adding new volume and swapping drive letters....

Hello - I have a SQL 2000 server which has a D: drive that contains all of my databases (system and user). I am running out of space on this volume and need to migrate the contents of this volume to a larger one. My initial plan was to introduce a new volume to the server (say a K: drive). Backup all databases (of course), and then stop all SQL services. Copy all data from D: to K:. Once data is copied, swap drive letter names (D: to I: and then K: to D. Then restart SQL services. SQL should not know any better since everything was on the D: drive when it went down, and everythiing is still on the D: drive when it came back up, correct?

The other option mentioned is to detatch the databases, copy the data and then reattach them in their new locations. I understand this method, but it seems more involved (and riskier) than just renaming the drives. Does anyone have an opinion regarding these two migration methods? Thanks for your help.

Chris
I would use the attach / detach method, would be the best solution for me in this case.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||As you indicated, the drive rename method may be less effort. As long as you are using the Disk Management applet -and not just mapping drives, it should work just fine.

Adding New Disk

When I add a new disk and put a file on it and make it part of the primary
filegroup, how does disk space get used? Basically my current disk is 90%
full and I'd like to relieve it as much as possible.
Files within a file group get filled in a proportion fill method that is
based of the amount of free space in the file. So if you added another file
some new data would be inserted into the existing one(s) but most would go
into the new one. At least until they leveled off. If your concerned it
would be best to BCP out all the data, truncate the tables, add the new
file(s) and then BCP the data back in. That would fill them more evenly and
place more of the existing data on the new files.
Andrew J. Kelly SQL MVP
"Brad" <me@.privacy.net> wrote in message
news:MPG.1b435bcab46a01ee98bdd7@.news...
> When I add a new disk and put a file on it and make it part of the primary
> filegroup, how does disk space get used? Basically my current disk is 90%
> full and I'd like to relieve it as much as possible.

Adding New Disk

When I add a new disk and put a file on it and make it part of the primary
filegroup, how does disk space get used? Basically my current disk is 90%
full and I'd like to relieve it as much as possible.Files within a file group get filled in a proportion fill method that is
based of the amount of free space in the file. So if you added another file
some new data would be inserted into the existing one(s) but most would go
into the new one. At least until they leveled off. If your concerned it
would be best to BCP out all the data, truncate the tables, add the new
file(s) and then BCP the data back in. That would fill them more evenly and
place more of the existing data on the new files.
Andrew J. Kelly SQL MVP
"Brad" <me@.privacy.net> wrote in message
news:MPG.1b435bcab46a01ee98bdd7@.news...
> When I add a new disk and put a file on it and make it part of the primary
> filegroup, how does disk space get used? Basically my current disk is 90%
> full and I'd like to relieve it as much as possible.

Adding New Disk

When I add a new disk and put a file on it and make it part of the primary
filegroup, how does disk space get used? Basically my current disk is 90%
full and I'd like to relieve it as much as possible.Files within a file group get filled in a proportion fill method that is
based of the amount of free space in the file. So if you added another file
some new data would be inserted into the existing one(s) but most would go
into the new one. At least until they leveled off. If your concerned it
would be best to BCP out all the data, truncate the tables, add the new
file(s) and then BCP the data back in. That would fill them more evenly and
place more of the existing data on the new files.
--
Andrew J. Kelly SQL MVP
"Brad" <me@.privacy.net> wrote in message
news:MPG.1b435bcab46a01ee98bdd7@.news...
> When I add a new disk and put a file on it and make it part of the primary
> filegroup, how does disk space get used? Basically my current disk is 90%
> full and I'd like to relieve it as much as possible.

Adding empty space

I want to add some spaces on the starting feild value. Please see the below example

" " & Fields!Name.Value

This is working on my BIDS. But the spaces are removed automatically when I deployed the report to the report manager. What should be the problem.

Rather than inserting spaces, can you adjust the padding property on the textbox? If you goal is to have the field indented, that should work.|||

Try using StrDup(3, Chr(32)) & Fields!Name.Value or Space(3) & Fields!Name.Value

Shyam

|||

I used padding property to indent the field. its working fine.

Thanks jwelch.

|||

Hi Folks,

I'm attempting to add empty spaces to format my report. I have one subreport that's a part of my report. I used the "Space()" function and it worked nicely within VS2005, however when I moved my report to the Report Server all my heading and detail data had one space in between them. I also tried "strDUP(3, " ")" and it too worked fine in VS2005 but not on the Report Server. Below is a sample of my code in what I trying to accomplish.

="DIR #" & StrDup(5, " ") & "ST" & StrDup(4, " ") &

"DIR NAME" & StrDup(20, " ") & "PUBCO" &

StrDup(7, " ") & "CLOSE" & StrDup(10, " ") & "ISSUE"

|||Spaces don't work very well when you are rendering as HTML, as browsers tend to ignore repeated white space. Any reason you couldn't use a table instead?|||

Hi John,

I'm currently using a table and it consist of one large column because my subreport is a part of it.

Best regards

|||How about using seperate columns for displaying this info, and merging the cells for the subreport?|||

John - I followed your advice and added a table within my table and it works fine now.

Thanks for your help.

Adding empty space

I want to add some spaces on the starting feild value. Please see the below example

" " & Fields!Name.Value

This is working on my BIDS. But the spaces are removed automatically when I deployed the report to the report manager. What should be the problem.

Rather than inserting spaces, can you adjust the padding property on the textbox? If you goal is to have the field indented, that should work.|||

Try using StrDup(3, Chr(32)) & Fields!Name.Value or Space(3) & Fields!Name.Value

Shyam

|||

I used padding property to indent the field. its working fine.

Thanks jwelch.

|||

Hi Folks,

I'm attempting to add empty spaces to format my report. I have one subreport that's a part of my report. I used the "Space()" function and it worked nicely within VS2005, however when I moved my report to the Report Server all my heading and detail data had one space in between them. I also tried "strDUP(3, " ")" and it too worked fine in VS2005 but not on the Report Server. Below is a sample of my code in what I trying to accomplish.

="DIR #" & StrDup(5, " ") & "ST" & StrDup(4, " ") &

"DIR NAME" & StrDup(20, " ") & "PUBCO" &

StrDup(7, " ") & "CLOSE" & StrDup(10, " ") & "ISSUE"

|||Spaces don't work very well when you are rendering as HTML, as browsers tend to ignore repeated white space. Any reason you couldn't use a table instead?|||

Hi John,

I'm currently using a table and it consist of one large column because my subreport is a part of it.

Best regards

|||How about using seperate columns for displaying this info, and merging the cells for the subreport?|||

John - I followed your advice and added a table within my table and it works fine now.

Thanks for your help.

adding disk

I have to add SAN space to an existing cluster. what are the steps?
thanks
Create LUN on SAN
Present LUN to Host computers via SAN Masking software
On one host:
Rescan disks in Disk Administrator on host. (may require host reboot)
Partition disk (may require offsetting the start of the partition to align
LUN boundaries)
Create disk resource on temporary cluster group.
Rescan disks on remaining host nodes (again, ay require reboot of specific
nodes.
Test disk access on all nodes, shifting disk resource as necessary.
On the host for the SQL Instance you wish to add the disk to:
Using Cluster Administrator, Stop the SQL Service, leave the remaining
resources online
Make the SQL service dependent on the new disk resource (see existing disk
resource dependencies)
Start SQL Service using Cluster Administrator
Add folders and data.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"vision" <vision@.discussions.microsoft.com> wrote in message
news:192B4B9C-D1BB-4A46-9322-771B4C2F3987@.microsoft.com...
>I have to add SAN space to an existing cluster. what are the steps?
> thanks
|||thanks'
I am not sure what a temporary disk resource is.
"Geoff N. Hiten" wrote:

> Create LUN on SAN
> Present LUN to Host computers via SAN Masking software
> On one host:
> Rescan disks in Disk Administrator on host. (may require host reboot)
> Partition disk (may require offsetting the start of the partition to align
> LUN boundaries)
> Create disk resource on temporary cluster group.
> Rescan disks on remaining host nodes (again, ay require reboot of specific
> nodes.
> Test disk access on all nodes, shifting disk resource as necessary.
> On the host for the SQL Instance you wish to add the disk to:
> Using Cluster Administrator, Stop the SQL Service, leave the remaining
> resources online
> Make the SQL service dependent on the new disk resource (see existing disk
> resource dependencies)
> Start SQL Service using Cluster Administrator
> Add folders and data.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "vision" <vision@.discussions.microsoft.com> wrote in message
> news:192B4B9C-D1BB-4A46-9322-771B4C2F3987@.microsoft.com...
>
>
|||Temporary resource group, not temporary disk resource.
This is just a group you made up to hold the resource while you are testing
it. Once you have confirmed it moves between hosts as you expect, you moe
the disk resource into the SQL group before setting the dependencies.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"vision" <vision@.discussions.microsoft.com> wrote in message
news:9CDCB135-2CBF-417E-B158-FC3BA891D421@.microsoft.com...[vbcol=seagreen]
> thanks'
> I am not sure what a temporary disk resource is.
> "Geoff N. Hiten" wrote:
|||If you need to replace the disk, or want to swap it out, use the Cluster
Recovery Utility to change the resource dependencies. Then you copy the
contents of the old volume to the new and swap the drive letters.
The SQL Server resources will have to be offline during this process, but
you won't have to hack the registry to refer to a new drive letter.
The download contains a reference document.
http://www.microsoft.com/downloads/details.aspx?familyid=2BE7EBF0-A408-4232-9353-64AAFD65306D&displaylang=en
Sincerely,
Anthony Thomas

"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:eNEP0ieAHHA.4472@.TK2MSFTNGP03.phx.gbl...
> Temporary resource group, not temporary disk resource.
> This is just a group you made up to hold the resource while you are
testing
> it. Once you have confirmed it moves between hosts as you expect, you moe
> the disk resource into the SQL group before setting the dependencies.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "vision" <vision@.discussions.microsoft.com> wrote in message
> news:9CDCB135-2CBF-417E-B158-FC3BA891D421@.microsoft.com...
>