Conditionally showing/hiding a parameter

I have a report that in most cases takes 4 parameters. In a few special cases, though, it takes 5 parameters. I have my code modified enough such that it can handle this exception, however I cannot figure out how to conditionally show the 5th parameter based on the 4th parameter. Is this possible?

how to conditionally show the 5th parameter based on the 4th parameter. Is this possible?

Yes, this is possible. Lets say your 5th parameter is driven off of a query, then you can pass what ever is selected in the 4th parameter as an input to the query. Also in the report parameter your 5th parameter should be below the 4th parameter meaning the order of the parameters is important.

Let me know if you would want me to elaborate this further.

|||

You're describing how to change the available values of the 5th parameter based on the choice made for the 4th parameter. I think the original question is it possible to show or hide the 5th parameter based on the choice made for the 4th parameter.

I have never found a way to conditionally hide parameters and don't believe it's possible. If I'm wrong then I'd love to hear how to do it. When we've encountered cases like this we've had to just change the values of the 5th parameter (as Techquest has described) to have only a single item available such as "n/a" and then defaulted to that value.

I'd also love to hear if there's a way to conditionally change the label for parameters. I don't believe this is possible either but there's a lot of smart people out there and it would be great if someone could show me that I'm wrong.

tia

-bruce

|||Bruce, that's what I wanted to do.

I haven't been working with SSRS for too long, I wonder if it's possible to write a script or extension to allow more flexibility in the parameters.

I like the N/A idea though, that'll work if I can't figure out anything new.

Thanks,

Erik

Conditional Split - Expression Evaluates to Null

Hi everyone!

I'm using a conditional split to discriminate modified records. My expression looks like this:

col1_source != col1_dest || col2_source != col2_des.....and so on. I use OLE DB Command afterward to update modified records.

It all works fine if no columns evaluate to null. If any (source or dest.) evaluates to null, component fails.

Any tips how to solve a problem?

It has to work like this:

If colX_source is null and colX_dest is not null --> Update

If colX_source is not null and colX_dest is null --> Update

If both colX_source and colX_dest are null --> No update

p.s. i apologize if a similar thread exists, I haven't found something of use to me.

Use the ISNULL() function to test your columns for NULLs.

For instance:

ISNULL(colX_source) && !ISNULL(colX_dest)

|||

Simply add to your expression:

(IsNull(colX_Source) && IsNull(colX_dest)) || ((IsNull(colX_source) == false) && (IsNull(colX_dest)))

etc...

Or, use a derived column before the conditional split to eliminate the nulls:

Output1 / add as new column / IsNull(colX_Source) ? "" : colX_Source

|||

Thank you both for a quick response.

I understood the derived column idea but not the isnull idea.

If I want to cover all the cases in which I want the update to execute, my expression (if I haven’t missunderstood sth) should look like this:

isNull(col1_source) && !IsNull(col1_dest) || !isNull(col1_source) && IsNull(col1_dest) || col1_source != col1_dest

Isn’t it true that again, the last part of the expression col1_source != col1_dest evaluates to null if one of the columns is null? And that the only solution to the problem is a derived column which transforms null values in “”?

I’m new to SSIS and slowly learning that programming logic is not always the SSIS logic.

|||

The way it is written, it will only get to the last branch if both columns are NULL, which would render the last branch a moot point anyway because they would have to be "equal."

Use the derived column idea to change the NULLs to empty strings and then your conditional split will be easier to write/maintain. Plus, if you're not supposed to have NULL data, then it would be the proper thing to do to clean it up.

|||

I think my initial post is a little misguiding because I omitted a condition I have to test (which in fact is the last branch (a moot point as you say )).

If both colX_source and colX_dest are not null and different --> also Update!

+ the one I mentioned in the 1st post:

If colX_source is null and colX_dest is not null --> Update

If colX_source is not null and colX_dest is null --> Update

If both colX_source and colX_dest are null --> No update

|||( !ISNULL(colX_source) && !ISNULL(colX_dest) ) && (colX_source != colX_dest)|||

Wow, it works! Thank you for your patience!