Parameters: Users must choose one or both parameters

I have two date parameters in my report. The user must make a selction on at
least one of the two parameters (either one), but does not have to make a
selection on both.
How do I verify that at least one parameters has a value? I guess this must
be verified on a pre-Execute event on the View Report button.
Thanks in advance for your help.Allow Null on both dates. Then in your sproc (which is essentially what the
query turns into) check both are not null, else raise an error.
--
William Stacey [MVP]
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:0F4F4ED4-CF0F-4955-8265-EC47576F0186@.microsoft.com...
|I have two date parameters in my report. The user must make a selction on
at
| least one of the two parameters (either one), but does not have to make a
| selection on both.
|
| How do I verify that at least one parameters has a value? I guess this
must
| be verified on a pre-Execute event on the View Report button.
|
| Thanks in advance for your help.|||Do I understand you correct when I sum up your answer like this:
In the receiving procedure on the SQL Server I will have to check if at
least one of the two parameters has a value. If not I raise an error in the
stored procedure. This error is automatically caught by Reporting Services
and displayed to the end user.
"William Stacey [MVP]" wrote:
> Allow Null on both dates. Then in your sproc (which is essentially what the
> query turns into) check both are not null, else raise an error.
> --
> William Stacey [MVP]
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:0F4F4ED4-CF0F-4955-8265-EC47576F0186@.microsoft.com...
> |I have two date parameters in my report. The user must make a selction on
> at
> | least one of the two parameters (either one), but does not have to make a
> | selection on both.
> |
> | How do I verify that at least one parameters has a value? I guess this
> must
> | be verified on a pre-Execute event on the View Report button.
> |
> | Thanks in advance for your help.
>
>|||Right. And the report page will display the error text as you entered it.
A dialog box would be better (maybe), but that is what we have.
--
William Stacey [MVP]
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:9A2E1E77-BC99-466C-A856-F4DD48DD2B64@.microsoft.com...
| Do I understand you correct when I sum up your answer like this:
|
| In the receiving procedure on the SQL Server I will have to check if at
| least one of the two parameters has a value. If not I raise an error in
the
| stored procedure. This error is automatically caught by Reporting Services
| and displayed to the end user.
|
|
|
|
| "William Stacey [MVP]" wrote:
|
| > Allow Null on both dates. Then in your sproc (which is essentially what
the
| > query turns into) check both are not null, else raise an error.
| >
| > --
| > William Stacey [MVP]
| >
| > "Billy" <Billy@.discussions.microsoft.com> wrote in message
| > news:0F4F4ED4-CF0F-4955-8265-EC47576F0186@.microsoft.com...
| > |I have two date parameters in my report. The user must make a selction
on
| > at
| > | least one of the two parameters (either one), but does not have to
make a
| > | selection on both.
| > |
| > | How do I verify that at least one parameters has a value? I guess this
| > must
| > | be verified on a pre-Execute event on the View Report button.
| > |
| > | Thanks in advance for your help.
| >
| >
| >

Parameters visibility

Hi,

In Report Parameters, there is a settings "Hidden". Once it is clicked, the parameter will be hidden from users.

However, can I make its visibility according to some conditions? Such as when some items in the reports being clicked, or when the parameter drop down list only contain one option?

Thanks in advance.

If you are using the reportviewer controls, you can do this by using the ReportViewer.ServerReport.SetParameters method. The ReportParameter object has a property for visibility.

|||Can you elaborate on this? I'm not sure how/where to use the SetParameters method.

I assumed there would be the ability to use expressions for parameter visibility.
|||

There are two places you can control parameter visibility.

The first is from the report definition itself. This is the hidden flag you mentioned. This setting is constant for the report and can't be set to an expression.

The second place to set visibility depends on how you are viewing the report. If you are using URL access directly to the report server or Report Manager, you can't set parameter visibility on an individual parameters basis (you can turn off the entire parameters area using url access to the server). If you are using the ReportViewer controls (either winforms or webforms) that ship with VS 2005, you can use the ReportViewer.ServerReport.SetParameters() call to tell the control to override the visibility setting in the definition. Note that you can only force the parameter hidden, you can't force a parameter to be visible if the report definition has hidden set.

Parameters visibility

Hi,

In Report Parameters, there is a settings "Hidden". Once it is clicked, the parameter will be hidden from users.

However, can I make its visibility according to some conditions? Such as when some items in the reports being clicked, or when the parameter drop down list only contain one option?

Thanks in advance.

If you are using the reportviewer controls, you can do this by using the ReportViewer.ServerReport.SetParameters method. The ReportParameter object has a property for visibility.

|||Can you elaborate on this? I'm not sure how/where to use the SetParameters method.

I assumed there would be the ability to use expressions for parameter visibility.
|||

There are two places you can control parameter visibility.

The first is from the report definition itself. This is the hidden flag you mentioned. This setting is constant for the report and can't be set to an expression.

The second place to set visibility depends on how you are viewing the report. If you are using URL access directly to the report server or Report Manager, you can't set parameter visibility on an individual parameters basis (you can turn off the entire parameters area using url access to the server). If you are using the ReportViewer controls (either winforms or webforms) that ship with VS 2005, you can use the ReportViewer.ServerReport.SetParameters() call to tell the control to override the visibility setting in the definition. Note that you can only force the parameter hidden, you can't force a parameter to be visible if the report definition has hidden set.

Parameters in URL

I know that you can amend the URL of a report and set parameters that way but is there a way you can do the reverse? I need users to be able to save reports to their favourites while retaining the currently selected parameters. Therefore I need the URL to display the parameters without the users having to manually type them in. Is there a way of configuring this?Isn't this what 'my reports' is for?

Never used it so maybe someone else can enlighten us.

If they are always using the same params then just set up linked reports.

For a given parameter on a report you can:
- accept the default
- override the default
- hide or show the param with either of the above

To create a linked report, go to properties (on the report) where you'll see a button to do it.
Enter the description for the new report and which folder you would like it to appear in.

Then within the linked report, go to 'properties' 'parameters' where you can set them as you wish.

A faster way to create linked reports is to use the "RS linked report generator" (free from sqldbatips.com)

The user does not realise they are running a linked report, it saves them time and it can give you better control over security (by either using groups on the folder permissions or within a report using the "globals!reportfolder" parameter, which will show the linked report path and not the 'master' report path.|||

Neither of these options meet our requirements.

As I understand it MyReports is an ad-hoc query builder / an area that rdls can be uploaded to for each user. We have lots of non IT literate Reps who will need their own particular set of parameters applied to each report. I can't see them setting these up. I don't want to have to manage linked reports for every user or to set up linked reports either. Both of these options are onerous on the admin side.

The save to favourites would be the easiest solution though thinking it through, I don't think it will be possible. Thanks anyway.

|||'non IT literate reps' if ever there was an oxymoron...

Why can't you just make the report dynamically figure out who it is?

e.g. using the globals!username, pass it through to sql to filter the results, then just give everyone the same report

I have found that using the two RS tools on the sqldbatips site, along with some clever scripting &copy/paste find/replace, can create all the reports you need without having to touch report manager.|||

true say - the moron bit is also applicable

I had considered this option - we also have users who aren't reps but I guess that's not the end of the world. For reps, we'd also need to know their Sales Division which we don't store in AD. I was just looking for a quick fix to be honest.

What kind of things are you doing? Are you scripting the report from scratch using code? How are you delivering them?

|||

You're talking about "saving to favorites" in the browser favorites list, right?

Do the parameters show in the url/address bar/location at the time you want to save them?

I think this may be possible, but I'm not clear on the action sequence you're talking about -- are they sitting in the browser having selected some parameters, are you giving them the URL in an email (or documentation), or what?

I definitely have parameters embedded in URLs that I give to users and they definitely save them that way to favorites. The only problem is constructing the personal values for them, right?

I have done this by using a bit of javascript, in a page that asks them for the stuff I need to add to the base URL, such as Sales Div, constructing it and showing it to them, with instructions in the page to click on the link and then add to Favorites...

But, here's a thought -- assuming that you do have all your parameter values (such as Sales Div) stored *somewhere* , whether active directory or not, it would be cool to set up a meta-report that listed people's names with constructed URLs next to them, each with the properly constructed URL. You could call it something like the "Favorites Center" report, and it could have different columns next to each person for the different reports you wanted to expose in this way.

>L<

|||

I am talking about adding to browser favourites. The situation is that they open the report and then select a set of parameters pertinent to them. The report is accessed by clicking on a link on an html menu page.

Thanks for the ideas. I think I like the solution that you're currenlty running best. I will see what I can pull together based on your tip.

|||

Have fun! You can get the parameters for a report from the Catalog table of the ReportServer database, btw. (or from a SOAP call, probably a better idea, but hadn't thought about automating this part until just now <g>)

>L<

|||Oh yeah - thanks.|||I'm just getting my feet wet with Vista and don't know about parameters; however, I should not need them. I only want to save some web pages to Favorites and I only get unspecified error. and there it stops of course. It seems a simple thing to ask and a very stupid excuse for not doing it!! Sure would like to resolve this problem. Thankx Merri|||

What does the URL look like and what do you see when you get the "unspecified error"? Do you see a report manager page, just a "dead" browser, or what?

>L<

Parameters in URL

I know that you can amend the URL of a report and set parameters that way but is there a way you can do the reverse? I need users to be able to save reports to their favourites while retaining the currently selected parameters. Therefore I need the URL to display the parameters without the users having to manually type them in. Is there a way of configuring this?Isn't this what 'my reports' is for?

Never used it so maybe someone else can enlighten us.

If they are always using the same params then just set up linked reports.

For a given parameter on a report you can:
- accept the default
- override the default
- hide or show the param with either of the above

To create a linked report, go to properties (on the report) where you'll see a button to do it.
Enter the description for the new report and which folder you would like it to appear in.

Then within the linked report, go to 'properties' 'parameters' where you can set them as you wish.

A faster way to create linked reports is to use the "RS linked report generator" (free from sqldbatips.com)

The user does not realise they are running a linked report, it saves them time and it can give you better control over security (by either using groups on the folder permissions or within a report using the "globals!reportfolder" parameter, which will show the linked report path and not the 'master' report path.|||

Neither of these options meet our requirements.

As I understand it MyReports is an ad-hoc query builder / an area that rdls can be uploaded to for each user. We have lots of non IT literate Reps who will need their own particular set of parameters applied to each report. I can't see them setting these up. I don't want to have to manage linked reports for every user or to set up linked reports either. Both of these options are onerous on the admin side.

The save to favourites would be the easiest solution though thinking it through, I don't think it will be possible. Thanks anyway.

|||'non IT literate reps' if ever there was an oxymoron...

Why can't you just make the report dynamically figure out who it is?

e.g. using the globals!username, pass it through to sql to filter the results, then just give everyone the same report

I have found that using the two RS tools on the sqldbatips site, along with some clever scripting &copy/paste find/replace, can create all the reports you need without having to touch report manager.|||

true say - the moron bit is also applicable

I had considered this option - we also have users who aren't reps but I guess that's not the end of the world. For reps, we'd also need to know their Sales Division which we don't store in AD. I was just looking for a quick fix to be honest.

What kind of things are you doing? Are you scripting the report from scratch using code? How are you delivering them?

|||

You're talking about "saving to favorites" in the browser favorites list, right?

Do the parameters show in the url/address bar/location at the time you want to save them?

I think this may be possible, but I'm not clear on the action sequence you're talking about -- are they sitting in the browser having selected some parameters, are you giving them the URL in an email (or documentation), or what?

I definitely have parameters embedded in URLs that I give to users and they definitely save them that way to favorites. The only problem is constructing the personal values for them, right?

I have done this by using a bit of javascript, in a page that asks them for the stuff I need to add to the base URL, such as Sales Div, constructing it and showing it to them, with instructions in the page to click on the link and then add to Favorites...

But, here's a thought -- assuming that you do have all your parameter values (such as Sales Div) stored *somewhere* , whether active directory or not, it would be cool to set up a meta-report that listed people's names with constructed URLs next to them, each with the properly constructed URL. You could call it something like the "Favorites Center" report, and it could have different columns next to each person for the different reports you wanted to expose in this way.

>L<

|||

I am talking about adding to browser favourites. The situation is that they open the report and then select a set of parameters pertinent to them. The report is accessed by clicking on a link on an html menu page.

Thanks for the ideas. I think I like the solution that you're currenlty running best. I will see what I can pull together based on your tip.

|||

Have fun! You can get the parameters for a report from the Catalog table of the ReportServer database, btw. (or from a SOAP call, probably a better idea, but hadn't thought about automating this part until just now <g>)

>L<

|||Oh yeah - thanks.|||I'm just getting my feet wet with Vista and don't know about parameters; however, I should not need them. I only want to save some web pages to Favorites and I only get unspecified error. and there it stops of course. It seems a simple thing to ask and a very stupid excuse for not doing it!! Sure would like to resolve this problem. Thankx Merri

|||

What does the URL look like and what do you see when you get the "unspecified error"? Do you see a report manager page, just a "dead" browser, or what?

>L<

Parameters in Jump Reports

Good morning all:
I am having an issue when jumping from one report to the next. The issue is
that the users want the reports to run without having to enter any
additional information but may want to change some of the parameters after
the report has run. I have accomplished the first part of the requirement
but the parameters are not showing themselves for the second part of the
requirement.
When I run the reports with out jumping to it the parameters are exposed and
the user can select them but when it is called from another report they
don't show at all.
Is there some setting somewhere that will force the parameters to show even
if they don't need to be modified?
Thanks,
AlNever mind mystery solved. It turns out that the parameter area of the
report is collapsed/hidden and the user will just have to expand it.
Thanks,
Al
"Al" <no@.no.com> wrote in message
news:%234JxHTWbHHA.2088@.TK2MSFTNGP05.phx.gbl...
> Good morning all:
> I am having an issue when jumping from one report to the next. The issue
> is that the users want the reports to run without having to enter any
> additional information but may want to change some of the parameters after
> the report has run. I have accomplished the first part of the requirement
> but the parameters are not showing themselves for the second part of the
> requirement.
> When I run the reports with out jumping to it the parameters are exposed
> and the user can select them but when it is called from another report
> they don't show at all.
> Is there some setting somewhere that will force the parameters to show
> even if they don't need to be modified?
> Thanks,
> Al
>|||Also, you can use Jump to URL. When you do that you can specify that the
parameter section is not collapsed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Al" <no@.no.com> wrote in message
news:uOzaIaWbHHA.4720@.TK2MSFTNGP04.phx.gbl...
> Never mind mystery solved. It turns out that the parameter area of the
> report is collapsed/hidden and the user will just have to expand it.
> Thanks,
> Al
> "Al" <no@.no.com> wrote in message
> news:%234JxHTWbHHA.2088@.TK2MSFTNGP05.phx.gbl...
>> Good morning all:
>> I am having an issue when jumping from one report to the next. The issue
>> is that the users want the reports to run without having to enter any
>> additional information but may want to change some of the parameters
>> after the report has run. I have accomplished the first part of the
>> requirement but the parameters are not showing themselves for the second
>> part of the requirement.
>> When I run the reports with out jumping to it the parameters are exposed
>> and the user can select them but when it is called from another report
>> they don't show at all.
>> Is there some setting somewhere that will force the parameters to show
>> even if they don't need to be modified?
>> Thanks,
>> Al
>>
>|||I can see that coming up. Thanks,
Al
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eQNiHzWbHHA.4544@.TK2MSFTNGP03.phx.gbl...
> Also, you can use Jump to URL. When you do that you can specify that the
> parameter section is not collapsed.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Al" <no@.no.com> wrote in message
> news:uOzaIaWbHHA.4720@.TK2MSFTNGP04.phx.gbl...
>> Never mind mystery solved. It turns out that the parameter area of the
>> report is collapsed/hidden and the user will just have to expand it.
>> Thanks,
>> Al
>> "Al" <no@.no.com> wrote in message
>> news:%234JxHTWbHHA.2088@.TK2MSFTNGP05.phx.gbl...
>> Good morning all:
>> I am having an issue when jumping from one report to the next. The
>> issue is that the users want the reports to run without having to enter
>> any additional information but may want to change some of the parameters
>> after the report has run. I have accomplished the first part of the
>> requirement but the parameters are not showing themselves for the second
>> part of the requirement.
>> When I run the reports with out jumping to it the parameters are exposed
>> and the user can select them but when it is called from another report
>> they don't show at all.
>> Is there some setting somewhere that will force the parameters to show
>> even if they don't need to be modified?
>> Thanks,
>> Al
>>
>>
>|||Yeah, sometimes it is easy to make a change than teaching people how to
expand the section.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Al" <no@.no.com> wrote in message
news:uuG8uFYbHHA.3584@.TK2MSFTNGP02.phx.gbl...
>I can see that coming up. Thanks,
> Al
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eQNiHzWbHHA.4544@.TK2MSFTNGP03.phx.gbl...
>> Also, you can use Jump to URL. When you do that you can specify that the
>> parameter section is not collapsed.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Al" <no@.no.com> wrote in message
>> news:uOzaIaWbHHA.4720@.TK2MSFTNGP04.phx.gbl...
>> Never mind mystery solved. It turns out that the parameter area of the
>> report is collapsed/hidden and the user will just have to expand it.
>> Thanks,
>> Al
>> "Al" <no@.no.com> wrote in message
>> news:%234JxHTWbHHA.2088@.TK2MSFTNGP05.phx.gbl...
>> Good morning all:
>> I am having an issue when jumping from one report to the next. The
>> issue is that the users want the reports to run without having to enter
>> any additional information but may want to change some of the
>> parameters after the report has run. I have accomplished the first part
>> of the requirement but the parameters are not showing themselves for
>> the second part of the requirement.
>> When I run the reports with out jumping to it the parameters are
>> exposed and the user can select them but when it is called from another
>> report they don't show at all.
>> Is there some setting somewhere that will force the parameters to show
>> even if they don't need to be modified?
>> Thanks,
>> Al
>>
>>
>>
>

Parameters for Report Builder

Is there a way to set parameters for a Report Model? I have a database that includes data for a lot of users and I want to be able to use a Report Model but only allow the user to see data related to their UserID when they are using Report Builder to build a report.

Is this at all possible? Would I have to set up some custom authentication?

Thanks for any help.

Please take a look at Model Item Security.

http://msdn2.microsoft.com/en-us/library/ms156505.aspx

|||Ok, I understand I can limit access to certain parts of a report or datasource, but I want to be able to filter data based on what user is doing the ad hoc querying. So I can do a Select * From tbl_UserData Where UserID = @.UserID. I want to be able to set this @.UserID paramater somehow, but not have the user select it.

Is this possible? Or would I need to set up a different report model or data view for each user who can do ad hoc querying and only allow users to use the report model assigned to them?

If this was explained in the link you posted, I'm sorry but I didn't see how.

Thanks for any help.|||

This post should help:

http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx

parameters filters

Hi,
I have created a simple report that uses the parameter customer_code as
selection criteria. When users use the report they can only select one
customer code at a time. How can I change this to accept an asterisk for a
range of customers? Is Reporting services able to do this?
xp_aol@.discussions.com> I have created a simple report that uses the parameter customer_code as
> selection criteria. When users use the report they can only select one
> customer code at a time. How can I change this to accept an asterisk for a
> range of customers? Is Reporting services able to do this?
Here is an example how to use IN operator with list of values as a
parameter:
http://solidqualitylearning.com/blogs/dejan/archive/2004/10/22/200.aspx.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Parameters Disabled with Snapshot Report Execution

Is there a way to schedule report snapshots and still give users the option to change the parameters? I found that report parameters are disabled when I set the execution options to use a snapshot. I fixed the problem by setting the report to use cached data that expires on a custom schedule and by scheduling an e-mail subscription that creates a new report cache soon after the cache expires. I'm guessing there must be a better way to provide good report performance with the default parameters as well as flexibility.

You'd need to change from using Query Parameters the query to using Report Filters.

You can read about it here:

http://msdn2.microsoft.com/en-us/library/aa255838(SQL.80).aspx

I would only recommend doing this for filtering relatively small amounts of data. The SSRS is not as efficient at filtering data as a stored proc on a DB server.

Parameters - A OR B

Probably a pretty easy question: How do I implement 2 parameters in a OR situation? I want the users to be able to either choose a value for A OR a value for B, i.e. select the product name OR the product code. I have 3 datasets defined, 1 for each parameter and 1 for the report output. The last one has ...WHERE A=@.A or B = @.B. When testing, it seems to require both. I want them to be mutually exclusive, the user can only pick one.Hi,

you probably would be able to make one parameter disbled (using a dependency) if the one is chosen, but you won′t be able to do this for bither having circular references. What you can do is to implement both parameters in your report and make them selectable in the GUI and implement the logic for differenting the cases in the query code.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the information. I did some research and I updated my query's WHERE clause: WHERE (D.name = ISNULL(@.name, D.name)) AND (D.tracking_number = ISNULL(@.tracking_number, D.tracking_number))

I also checked the "allow null value" for each of the 2 parameters. I also have the default value to be null. However, if I only select a name, I'm still getting the prompt: Please select a value for the parameter 'Tracking Number'.

Any ideas what else I need to do? I thought the "allow null value" would do it.

|||

I had somewhat-similiar report

User pick a date range, and pick which date column to use (data is coming from a Stored Proc)

I can't think of an easy way to do it in StorProc unless I copy code twice (once for each date column), or dynamic query

so I used the filter in SSRS, on the table

=IIF(Parameters!UseImportedDateRange.Value = "Imported", Fields!ImportedDate.Value, Fields!MostRecentCallDate.Value)

> @.start_date

Parameterized queries - works in Access but not SQLS2k?

I have an application where users can enter data into any (or all) of 6 search fields,
to produce a filtered query.

This works fine using my Access version(see code below),
but as SQLS2k cannot use "IIF", I tried to replace these bits with
"CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields
as these cannot be "wild-carded" in the same way as Access allows.

Can anyone suggest a way forward that does not involve coding all the
possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?

Hoping you can help
Alex


PARAMETERS
CurrentType Text,
CurrentCategoryID Long,
CurrentProductID Long,
CurrentClientID Long,
CurrentContractID Long,
FromDate DateTime,
ToDate DateTime;

SELECT
tAudit.AuditID,
tAudit.ActionType,
tAudit.ClientID,
tClients.ContactCompanyName,
tAudit.ContractID,
tContracts.ClientRef,
tAudit.ProductID,
tProducts.ProductName,
tAudit.CategoryID,
tCategories.CategoryName,
tAudit.Acknowledged,
tAudit.ValueAmount,
tAudit.DateStamp

FROM (((tAudit
LEFT JOIN tCategories
ON tAudit.CategoryID = tCategories.CategoryID)
LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID)
LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID)
LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID

WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType]))
AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID]))
AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID]))
AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID]))
AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID]))
AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));(tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID])
Mdaaa...
tAudit.ContractID = isnull([CurrentContractID],tAudit.ContractID)
???|||Thanks Buser - works fine - much obliged to you!

Regards
Alex