Report Designer Dynamic Filter

I have a requirement to add the ability to filter a report by partial last name, at the report level (i.e. not at the database server). In case someone else needs to work out how to do this, here is what I did.

Add a Parameter to the report. In my case it is named LastName, is non-queried, allows Null or Blank, and has a default of null.

Add a filter to the dataset that contains the field to be searched. Set its Expression to:
=IIF(Parameters!LastName.Value = System.DBNull.Value OR Parameters!LastName.Value = "",
Fields!LastName.Value, Nothing) ,
its operator to "="
and its value to =IIF(Parameters!LastName.Value = Nothing OR Parameters!LastName.Value = "", Nothing, Parameters!LastName.Value & "*")

voila!

Helen

I'm not sure I fully understand. What exactly does this do?

Would this have the same effect as using a query with a parameter for lastname (@.lastname) where lastname LIKE @.lastname + "%"?

|||

It allows me to make entering a value for the parameter optional. If the user does not want to filter by last name, my filter expression still has to work. I got it to work by using an expression for both the filter and its value, and returning Nothing for both when the value is null or empty.

When using an expression for the filter, the LIKE option for the comparator is disabled, so I had to use = as the operator.

Report Builder Error

I have created a view with one table (CASEMASTER). When I look at the Visual
Studioâ's property of the Case Number field within table CASEMASTER, it is set
to AllowNull = False, which correctly corresponds with the property of this
field on the SQL Server.
However, I need this table to be replaced with a new named query because I
need to limit the data to only one county, plus I need to link other tables.
When I set it as a query, it changes the property of the Case Number field to
AllowNull = True. Consequently, when I try to deploy it, I get a "Nullable
Property Error".
Is there any way to change the AllowNull property OR prevent this from
happening so that I can deploy the view/model without errors?
Any suggestions are appreciated.
--
K. ThomasI removed the minOccurs="0" property in the code to get this to work properly.
--
K. Thomas
"K. Thomas" wrote:
> I have created a view with one table (CASEMASTER). When I look at the Visual
> Studioâ's property of the Case Number field within table CASEMASTER, it is set
> to AllowNull = False, which correctly corresponds with the property of this
> field on the SQL Server.
> However, I need this table to be replaced with a new named query because I
> need to limit the data to only one county, plus I need to link other tables.
> When I set it as a query, it changes the property of the Case Number field to
> AllowNull = True. Consequently, when I try to deploy it, I get a "Nullable
> Property Error".
> Is there any way to change the AllowNull property OR prevent this from
> happening so that I can deploy the view/model without errors?
> Any suggestions are appreciated.
> --
> K. Thomas

replication, mirroring and log ship

dear all,

I have a case.

I have 1 production server and wanto to add 1 more server.

my production server is using sql 2000 stnd edition.

I want to have an indentical database from my production server.

the problem is my second server is using sql 2005 stnd edition.

I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.

any idea or suggestion will be great....

regards,

-dedys

Transactional replication should work fine as it supports replicating between servers within two versions of each other.

Regards,

Matt Hollingsworth

SQL Server High Availability

|||

The only thing that would prevent you from using transactional replication is if you have tables that do not have primary keys.

As far as log shipping goes. You can configure log shipping from SQL Server 2000 to SQL Server 2005, because 2000 backups can be restored to 2005. You can not configure it the other way around.

|||

dear sir,

I already try to configure log ship on my server.

my primary server is on sql2000 and the secondary server is on sql 2005.

I find that on sql 2000 can't see the sql 2005.

do you have documentation regarding this. a web link ?

thanks

-dedys

|||You can't connect to and manage a 2005 instance from within Enterprise Manager. You can go the other way. Log shipping is nothing more than configuring jobs to backup the tran log, copy it to the secondary, and restore it on a recurring basis. From within Management Studio on 2005, you can point and click through the GUI and setup log shipping from 2000 to 2005. You can also just create three jobs manually and accomplish the same thing. I'm not sure what documentation you are looking for. The 2005 Books Online have step-by-step procedures for setting up log shipping.|||

hi,

I have tries to set log shipping from 2000 to 2005 but it seem can't be done.

example : Server A is 2005 and B is 2000

I'm using management studio from A and register B. but when I right click on the database B, I can't find log shipping.

so I can't configure log shipping from 2000 to 2005.

if you know how to set it. could you share it with me.

I also try to do it manually like you said. using 3 simple step.

step 1 and 2 is done. I have difficulties with step 3. that is restoring the transaction log.

do you know how to restore transaction log more that 2 files at the same time or the name is keep on changing ?

please advice

-dedys

|||You can't restore more than 1 tran log simultaneously. Just like you can't restore more than one of any backup at the same time to the same database. You have to restore 1 log and then the next and then the next etc.|||

hi mr michael,

I have tries but I still got problem.

the step that did,

restore fullbackup to other server.

then restore the transaction log one by one and at the last transaction log with recovery option.

that's part is ok.

but when I want add more transaction log, there is an error that said :

Msg 3117, Level 16, State 4, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

please advice

|||You have to restore all transaction logs using the NORECOVERY option. As soon as you recover the database, you can no longer restore tran logs.

replication, mirroring and log ship

dear all,

I have a case.

I have 1 production server and wanto to add 1 more server.

my production server is using sql 2000 stnd edition.

I want to have an indentical database from my production server.

the problem is my second server is using sql 2005 stnd edition.

I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.

any idea or suggestion will be great....

regards,

-dedys

Transactional replication should work fine as it supports replicating between servers within two versions of each other.

Regards,

Matt Hollingsworth

SQL Server High Availability

|||

The only thing that would prevent you from using transactional replication is if you have tables that do not have primary keys.

As far as log shipping goes. You can configure log shipping from SQL Server 2000 to SQL Server 2005, because 2000 backups can be restored to 2005. You can not configure it the other way around.

|||

dear sir,

I already try to configure log ship on my server.

my primary server is on sql2000 and the secondary server is on sql 2005.

I find that on sql 2000 can't see the sql 2005.

do you have documentation regarding this. a web link ?

thanks

-dedys

|||You can't connect to and manage a 2005 instance from within Enterprise Manager. You can go the other way. Log shipping is nothing more than configuring jobs to backup the tran log, copy it to the secondary, and restore it on a recurring basis. From within Management Studio on 2005, you can point and click through the GUI and setup log shipping from 2000 to 2005. You can also just create three jobs manually and accomplish the same thing. I'm not sure what documentation you are looking for. The 2005 Books Online have step-by-step procedures for setting up log shipping.|||

hi,

I have tries to set log shipping from 2000 to 2005 but it seem can't be done.

example : Server A is 2005 and B is 2000

I'm using management studio from A and register B. but when I right click on the database B, I can't find log shipping.

so I can't configure log shipping from 2000 to 2005.

if you know how to set it. could you share it with me.

I also try to do it manually like you said. using 3 simple step.

step 1 and 2 is done. I have difficulties with step 3. that is restoring the transaction log.

do you know how to restore transaction log more that 2 files at the same time or the name is keep on changing ?

please advice

-dedys

|||You can't restore more than 1 tran log simultaneously. Just like you can't restore more than one of any backup at the same time to the same database. You have to restore 1 log and then the next and then the next etc.|||

hi mr michael,

I have tries but I still got problem.

the step that did,

restore fullbackup to other server.

then restore the transaction log one by one and at the last transaction log with recovery option.

that's part is ok.

but when I want add more transaction log, there is an error that said :

Msg 3117, Level 16, State 4, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

please advice

|||You have to restore all transaction logs using the NORECOVERY option. As soon as you recover the database, you can no longer restore tran logs.

replication, mirroring and log ship

dear all,

I have a case.

I have 1 production server and wanto to add 1 more server.

my production server is using sql 2000 stnd edition.

I want to have an indentical database from my production server.

the problem is my second server is using sql 2005 stnd edition.

I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.

any idea or suggestion will be great....

regards,

-dedys

Transactional replication should work fine as it supports replicating between servers within two versions of each other.

Regards,

Matt Hollingsworth

SQL Server High Availability

|||

The only thing that would prevent you from using transactional replication is if you have tables that do not have primary keys.

As far as log shipping goes. You can configure log shipping from SQL Server 2000 to SQL Server 2005, because 2000 backups can be restored to 2005. You can not configure it the other way around.

|||

dear sir,

I already try to configure log ship on my server.

my primary server is on sql2000 and the secondary server is on sql 2005.

I find that on sql 2000 can't see the sql 2005.

do you have documentation regarding this. a web link ?

thanks

-dedys

|||You can't connect to and manage a 2005 instance from within Enterprise Manager. You can go the other way. Log shipping is nothing more than configuring jobs to backup the tran log, copy it to the secondary, and restore it on a recurring basis. From within Management Studio on 2005, you can point and click through the GUI and setup log shipping from 2000 to 2005. You can also just create three jobs manually and accomplish the same thing. I'm not sure what documentation you are looking for. The 2005 Books Online have step-by-step procedures for setting up log shipping.|||

hi,

I have tries to set log shipping from 2000 to 2005 but it seem can't be done.

example : Server A is 2005 and B is 2000

I'm using management studio from A and register B. but when I right click on the database B, I can't find log shipping.

so I can't configure log shipping from 2000 to 2005.

if you know how to set it. could you share it with me.

I also try to do it manually like you said. using 3 simple step.

step 1 and 2 is done. I have difficulties with step 3. that is restoring the transaction log.

do you know how to restore transaction log more that 2 files at the same time or the name is keep on changing ?

please advice

-dedys

|||You can't restore more than 1 tran log simultaneously. Just like you can't restore more than one of any backup at the same time to the same database. You have to restore 1 log and then the next and then the next etc.|||

hi mr michael,

I have tries but I still got problem.

the step that did,

restore fullbackup to other server.

then restore the transaction log one by one and at the last transaction log with recovery option.

that's part is ok.

but when I want add more transaction log, there is an error that said :

Msg 3117, Level 16, State 4, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

please advice

|||You have to restore all transaction logs using the NORECOVERY option. As soon as you recover the database, you can no longer restore tran logs.