detecting whether all items are selected in a multi-value paramete

Is there an easy way to detect whether the user has selected all possible
items in a multi-value parameter? If they've selected everything I want to
put "All Regions" as a label in the header of the report. If they've selected
only some, I want to list the regions they've selected [accomplished by doing
Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in the
header, I can't just compare Parameters!Region.Label.Count to
Count(Fields!Region.Value,"RegionsDataset") because you can't use fields in
the header. (The workaround of adding a hidden field to the body of the
report then referencing it by ReportItems!HiddenField.Value is just too
kludgy for me... plus the hidden field's gotta repeat on every page.)
If anyone has any good suggestions, I'd be interested in hearing them.
The solution I decided upon was builiding a hidden parameter which is
dependent upon the multi-value parameter... it's query figures out if they've
selected all possible options. But it seems silly to have to fire off another
query to the DB to accomplish this.If you know the count of parameters you can use that:
This is something I do in a textbox where I show the parameters selected:
="Containers= " & iif(Parameters!Container.Count > 8," more than 8
Selected",Join(Parameters!Container.Value, ", "))
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:709E330A-6E97-4F37-9F2B-50A417CFF15C@.microsoft.com...
> Is there an easy way to detect whether the user has selected all possible
> items in a multi-value parameter? If they've selected everything I want to
> put "All Regions" as a label in the header of the report. If they've
> selected
> only some, I want to list the regions they've selected [accomplished by
> doing
> Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in the
> header, I can't just compare Parameters!Region.Label.Count to
> Count(Fields!Region.Value,"RegionsDataset") because you can't use fields
> in
> the header. (The workaround of adding a hidden field to the body of the
> report then referencing it by ReportItems!HiddenField.Value is just too
> kludgy for me... plus the hidden field's gotta repeat on every page.)
> If anyone has any good suggestions, I'd be interested in hearing them.
> The solution I decided upon was builiding a hidden parameter which is
> dependent upon the multi-value parameter... it's query figures out if
> they've
> selected all possible options. But it seems silly to have to fire off
> another
> query to the DB to accomplish this.|||Bruce-
That would work if the list of items in the multi-value parameter was a
constant set. But the list can change over time, so I can't hard code it to a
specific count. Any other suggestions? Thanks for the reply, though.
"Bruce L-C [MVP]" wrote:
> If you know the count of parameters you can use that:
> This is something I do in a textbox where I show the parameters selected:
> ="Containers= " & iif(Parameters!Container.Count > 8," more than 8
> Selected",Join(Parameters!Container.Value, ", "))
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
> news:709E330A-6E97-4F37-9F2B-50A417CFF15C@.microsoft.com...
> > Is there an easy way to detect whether the user has selected all possible
> > items in a multi-value parameter? If they've selected everything I want to
> > put "All Regions" as a label in the header of the report. If they've
> > selected
> > only some, I want to list the regions they've selected [accomplished by
> > doing
> > Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in the
> > header, I can't just compare Parameters!Region.Label.Count to
> > Count(Fields!Region.Value,"RegionsDataset") because you can't use fields
> > in
> > the header. (The workaround of adding a hidden field to the body of the
> > report then referencing it by ReportItems!HiddenField.Value is just too
> > kludgy for me... plus the hidden field's gotta repeat on every page.)
> >
> > If anyone has any good suggestions, I'd be interested in hearing them.
> >
> > The solution I decided upon was builiding a hidden parameter which is
> > dependent upon the multi-value parameter... it's query figures out if
> > they've
> > selected all possible options. But it seems silly to have to fire off
> > another
> > query to the DB to accomplish this.
>
>|||If your list is based on a query you could have a query that returns a count
and then you can use that count to compare to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:EAFC9B4C-3371-4159-8917-4CAA8595FC7E@.microsoft.com...
> Bruce-
> That would work if the list of items in the multi-value parameter was a
> constant set. But the list can change over time, so I can't hard code it
> to a
> specific count. Any other suggestions? Thanks for the reply, though.
> "Bruce L-C [MVP]" wrote:
>> If you know the count of parameters you can use that:
>> This is something I do in a textbox where I show the parameters selected:
>> ="Containers= " & iif(Parameters!Container.Count > 8," more than 8
>> Selected",Join(Parameters!Container.Value, ", "))
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
>> news:709E330A-6E97-4F37-9F2B-50A417CFF15C@.microsoft.com...
>> > Is there an easy way to detect whether the user has selected all
>> > possible
>> > items in a multi-value parameter? If they've selected everything I want
>> > to
>> > put "All Regions" as a label in the header of the report. If they've
>> > selected
>> > only some, I want to list the regions they've selected [accomplished by
>> > doing
>> > Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in
>> > the
>> > header, I can't just compare Parameters!Region.Label.Count to
>> > Count(Fields!Region.Value,"RegionsDataset") because you can't use
>> > fields
>> > in
>> > the header. (The workaround of adding a hidden field to the body of the
>> > report then referencing it by ReportItems!HiddenField.Value is just too
>> > kludgy for me... plus the hidden field's gotta repeat on every page.)
>> >
>> > If anyone has any good suggestions, I'd be interested in hearing them.
>> >
>> > The solution I decided upon was builiding a hidden parameter which is
>> > dependent upon the multi-value parameter... it's query figures out if
>> > they've
>> > selected all possible options. But it seems silly to have to fire off
>> > another
>> > query to the DB to accomplish this.
>>

Destination resets when changing selected databases

I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.

I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).

Thanks in advance.

This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.

If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.

I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.

Thanks.

|||

If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),

Navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

and modify the BackupDirectory value, you'll have this as the new default.

Note that this permanently modifies the default location for ALL TSQL backups!

|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||

Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.

Thanks again for the info though.

Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.

I guess we'll just have to wait and see if it is ever acknowledged.

|||

Actually, this is a server-side setting.

If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!

|||

Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.

For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.

Anyway, thanks again for the big tip! I'm sure it will help others as well.

P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.

|||

It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.

Thank you for the great assistance!

|||

The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.

It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.

Destination resets when changing selected databases

I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.

I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).

Thanks in advance.

This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.

If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.

I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.

Thanks.

|||

If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),

Navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

and modify the BackupDirectory value, you'll have this as the new default.

Note that this permanently modifies the default location for ALL TSQL backups!

|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||

Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.

Thanks again for the info though.

Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.

I guess we'll just have to wait and see if it is ever acknowledged.

|||

Actually, this is a server-side setting.

If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!

|||

Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.

For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.

Anyway, thanks again for the big tip! I'm sure it will help others as well.

P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.

|||

It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.

Thank you for the great assistance!

|||

The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.

It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.

Destination resets when changing selected databases

I discovered that everytime I need to add a database to my backup Maintenance Plan, after I select the new database from the drop-down of databases, the Destination automatically resets back to a default location.

I'm assuming this is a bug that will be resolved at some point, but in the meantime, I need to see if there is a way I can deal with this permanently. I am not the only one adding databases to the backup routine so I can't verify that this setting is properly changed every time we have a new database (which is about once a week).

Thanks in advance.

This is my last effort to get an answer on this. At this point using the maintenance plans is causing more problems than it solves because we add new databases most weeks. Everytime a database is added we have to change the backup maintenance plan and remember to change the backup path after it resets to a default path.

If there is even a place that I can change the default backup location, I will take that as a good workaround. I just can't continue this way since I am not always adding the databases to the backup and therefore can't validate that the path is reset every time. If there isn't a solution, I'll have to build a front-end to manage the backup scripts which I really don't want to do.

I am asking that a Microsoft representative PLEASE give me an anwer on this so I can move on.

Thanks.

|||

If you go into RegEdit (with ALL of the caveats and cautions that always accompany manually modifying the registry!),

Navigate to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

and modify the BackupDirectory value, you'll have this as the new default.

Note that this permanently modifies the default location for ALL TSQL backups!

|||But is Microsoft going to fix this bug? I submitted it via the feedback site and was told they couldn't reproduce it. We don't add databases as often but it is still a pain to have to remember to look at the path and make sure it is right. I can't use the registry fix as each database goes to it's own location so every maint. plan is different.|||

Thanks for the idea Kevin. Unfortunately this is a client-side solution and I need a server-side solution for the default. Otherwise I would have to go make this change to every PC, laptop, and home PC via VPN for all administrators who can add backups. That just can't happen.

Thanks again for the info though.

Heather, so they said they can't reproduce it? I can't find an installation where it doesn't work this way, and we are on SP1 too.

I guess we'll just have to wait and see if it is ever acknowledged.

|||

Actually, this is a server-side setting.

If you make the registry setting on the server, and then connect to it from another node using Management Studio, that instance of Management studio will see the new default location for the backups. While we can't customize it for each database (I'm not sure how we'd do that since there's nothing to remember), we can at least get the proper parent directory, and keep backups off the C: drive!

|||

Thank you Kevin!!! That is great. I will go make the setting (carefully of course) now and we should be good to go.

For what it's worth, I would recommend that Microsoft add a backup path setup option to the server properties dialogue, maybe along with default mdb and ldb paths. Since custom backup paths to other media are common, even encouraged, I just think it makes sense to turn it into an accessible option. Obviously it is not necessary to accomplish even the most complex backup plans because the flexibility in SQL 2005 rocks, but it would make it easier to accuratly configure multiple maintenance plans using the Managment Studio tools without error.

Anyway, thanks again for the big tip! I'm sure it will help others as well.

P.S. I've made the change now and it works like a charm. I didn't even have to disconnect/reconnect my local Management Studio for it to take effect locally when configuring a maintenance plan.

|||

It worked beautifully for me as well. But I also agree this setting should be something simple in the Management Studio, not just a registry hack.

Thank you for the great assistance!

|||

The problem is not trying to set a different default destination for each database, the problem is that anytime you open the backup step properties, the destination folder is reset to the server default instead of remembering the value that you set it to manually.

It had to look at the saved properties to get the database list, it could just as easily load the saved value of the destination folder instead of going to the registry.

Design Preview Vs Print Preview

My report that I am writing looks great and pages properly (have the option selected to keep a table together ) when it is in the Reporting Services development preview. But as soon as I click on the print preview, which includes exporting it to a PDF, I get bold lines, other weirdities and the table in the report will split across pages etc.

Ideally, I would like it to print exactly as it looks in the RS preview.

Any ideas?

dotnetnow

While in design-view, right-click and select Properties then Layout tab. This is where you can set margins and page size/orientation, anything you do here will affect the output to PDF.