Conditional Visability in a table

Hi,
I'm new to RS and want to know how to write an expression which will set
visablility of a table group header to false if group1.value = "Account".
Any ideas?
Thanks
Jonthe basics of using an expression to hide a text box is this:
Type an expression that evaluates to a Boolean: True to hide the item and
False to show the item. Click the expression (fx) button to edit the
expression.
Remember, true = hidden, false = show
I don't know if that will work for your table group header as well, but
thats how it works with text boxes.
Karl
"jonwolds" wrote:
> Hi,
> I'm new to RS and want to know how to write an expression which will set
> visablility of a table group header to false if group1.value = "Account".
> Any ideas?
> Thanks
> Jon

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!

Conditional Processing from a Common Table Expression (CTE)

I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:

WITH Orders_CTE (TerritoryId, ContactId)
AS
(
SELECT TerritoryId, ContactId
FROM Sales.SalesOrderHeader
WHERE (ContactId < 200)
)
IF Orders_CTE.TerritoryId > 3
BEGIN
/* Do some processing here */

END
ELSE
BEGIN
/* Do something else here */

END

When I try this, I get a syntax error near the keyword 'IF'

Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!

WITH statement is a part of SELECT/INSERT/UPDATE/DELETE statement. As result you code doesn't work.

What processing do you need?

|||

What exactly you want to do on the /* Do Some processing here */. It might help you to give the rite solution.

You can end the CTE expression with INSERT/DELETE/UPDATE/SELECT. Othere than this 4 statement none of them are allowed.

|||

Thanks for the quick response.

All I need to do in each part of the IF ...ELSE..... is to perform a select, but which select statement to use depends on a field in the CTE. One select pulls data from a table in current database, whereas the other pulls data from a table on a linked server. The tables have similar but not exactly the same structures.

As I mentioned, I have what I need working using a cursor, I was just wondering if this were possible with a CTE, but based on what you and other repsondents have stated, it appears unlikely.

Conditional Formatting With DateDiff

I am attemting to write an expression that changes the background color if the difference between two dates is less than 60 days. I have this expression but it does not work. The color never changes.

=IIF (DateDiff("Day", Fields!pract_start_date.Value, Fields!project_start_date.Value) < 60,"Yellow","White")

Maybe you should wrap the DateDiff() with an ABS() in case the dates are in the wrong order in your function, resulting in a DateDiff of -60 rather than 60?

|||

That's exactly what was wrong. I had them in the wrong order. I thought I had checked for that but, evidently, did not. Thanks.

Conditional Font Weight

I need to conditionally set the font weight of my data when a calculation is equal to or less than 70. I am using the following expression in the font weight property:
=iif(( Fields!Answer1.Value/ Fields!SumStudents.Value)*100 <=70, "Extra Bold", "Normal")
I get no errors when I run my report, however it does not apply the correct weith to my display. I am positive the calculation is correct since I use it in other conditions and they work fine.
Any help would be apreciated.Extra Bold maps to 800. You can check this by placing a textbox on the
design surface and setting the Font Weight to Extra Bold. Next open the code
view for the report and check the value serialized for the textbox font
weight.
=iif(( Fields!Answer1.Value/ Fields!SumStudents.Value)*100
<=70, "800", "Normal")
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"CarrieWells" <CarrieWells@.discussions.microsoft.com> wrote in message
news:4AFCE118-56E0-4B52-A7B8-B8D551290D6E@.microsoft.com...
> I need to conditionally set the font weight of my data when a calculation
is equal to or less than 70. I am using the following expression in the font
weight property:
> =iif(( Fields!Answer1.Value/ Fields!SumStudents.Value)*100 <=70, "Extra
Bold", "Normal")
> =iif(Fields!Quantity.Value > 10, "Normal", "Bold")
> I get no errors when I run my report, however it does not apply the
correct weith to my display. I am positive the calculation is correct since
I use it in other conditions and they work fine.
> Any help would be apreciated.
>

Conditional expressions - isnull(A) OR isnull(B)

I am trying to reproduce an expression in my access front-end database
in an SQL view (using Visual Studio 2005 view definition). The
expression is:
SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
dbo_T200PEOPLE.PasswordHint
FROM dbo_T200PEOPLE;
Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
gratefully received!Take a look at CASE expression in the BOL
"neilr" <neilryder@.yahoo.com> wrote in message
news:1148376579.910713.297590@.i40g2000cwc.googlegroups.com...
>I am trying to reproduce an expression in my access front-end database
> in an SQL view (using Visual Studio 2005 view definition). The
> expression is:
> SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
> dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
> dbo_T200PEOPLE.PasswordHint
> FROM dbo_T200PEOPLE;
> Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
> gratefully received!
>|||OK that did it thanks. For anyone else interested, it now looks like
this:
CASE
WHEN PEP.Password IS NULL OR
PEP.PasswordHint IS NULL OR
PEP.Salutation IS NULL OR
PEP.FirstName IS NULL OR
PEP.JobTitle IS NULL
THEN 'No'
ELSE 'Yes'
END
AS DataComplete

Conditional expressions - isnull(A) OR isnull(B)

I am trying to reproduce an expression in my access front-end database
in an SQL view (using Visual Studio 2005 view definition). The
expression is:
SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
dbo_T200PEOPLE.PasswordHint
FROM dbo_T200PEOPLE;
Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
gratefully received!Take a look at CASE expression in the BOL
"neilr" <neilryder@.yahoo.com> wrote in message
news:1148376579.910713.297590@.i40g2000cwc.googlegroups.com...
>I am trying to reproduce an expression in my access front-end database
> in an SQL view (using Visual Studio 2005 view definition). The
> expression is:
> SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
> dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password
,
> dbo_T200PEOPLE.PasswordHint
> FROM dbo_T200PEOPLE;
> Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
> gratefully received!
>|||OK that did it thanks. For anyone else interested, it now looks like
this:
CASE
WHEN PEP.Password IS NULL OR
PEP.PasswordHint IS NULL OR
PEP.Salutation IS NULL OR
PEP.FirstName IS NULL OR
PEP.JobTitle IS NULL
THEN 'No'
ELSE 'Yes'
END
AS DataComplete

Conditional Expression quirk?

When using the conditional expression in a derived column transformation, I found that the following expression:

[F1Depth]==3 ? [F2Name] + "--" + [F1Name] : [F1Name]

is invalid while

[F1Depth]==3 ? [F2Name] + "--" + [F1Name] : "" + [F1Name]

is valid.

In both cases, the output type is set to Unicode String (DT_WSTR) with 4000 characters. The error in the first case is:

Error at Data Flow Task [Derived Column (2784)]: Failed to set property "Expression"on "output column" "FactorName" (2918).

Would this be considered a bug, or is there a reasonable explanation?

Thanks,

Anna.

Some more information: The columns F1Name & F2Name are output columns from a lookup that is based on a SELECT statement and their underlying data type/length are varchar (7900)

It seems like the Expression compiler is unable to map this varchar length to any data type?

|||

Is it possible that you have NULL data?|||Expressions are limited to 4000 characters.|||

Phil,

Thanks for your response. However, adding an empty string before the string column makes the expression valid. Why is that?

Thanks,

Anna.

|||Could it have something to do with the ANSI to Unicode conversion? If you explicitly cast F1 to Unicode, does it work?

|||

Thanks for the suggestion.

Either of these works fine:

(DT_STR, 4000, 1252) (F1Depth == 3 ?F2Name + "--" + F1Name : F1Name) and the output set to string [DT_STR]

or

(DT_WSTR, 4000) (F1Depth == 3 ?F2Name + "--" + F1Name : F1Name) and the output set to Unicode string [DT_WSTR]

Therefore, I don't think this is related to ANSI to Unicode conversion. It seems like when the source string is > 4000 characters, I need to either explicitly cast it? Adding an empty string "" to the actual string also seems to force the type.

I do get a warning about the length in both cases, but that is not really an issue.

Is this related more to the length of the source column and the 4000 character restriction in the expression?

|||

Annapurni wrote:

Therefore, I don't think this is related to ANSI to Unicode conversion. It seems like when the source string is > 4000 characters, I need to either explicitly cast it? Adding an empty string "" to the actual string also seems to force the type.

I think you hit the problem right there - In your original code, it thinks you are trying to return a DT_WSTR, but you are returning a DT_STR. Since any literal string value is interpreted as Unicode by SSIS, appending an empty string is forcing the conversion of the DT_STR to a DT_WSTR.

I think. Not 100% positive, though.

Conditional Expression - i.e., IIF in Access

I have a query with a conditional expression that I can do just fine in Access but I am having a bear of a time trying to create a similar SQL View. Baiscally I want to say, if column A is null, use value B else use value C.

In Access the SQL is this:

SELECT IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]) AS Casnumber, Sum(qryweldingrod3a.CFume) AS CFume, Sum(qryweldingrod3a.cslag) AS cSlag
FROM qryweldingrod3a INNER JOIN tblconstituents ON qryweldingrod3a.CASNumber = tblconstituents.CASNumber
GROUP BY IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]);

But I know you can't use the IIF statement in SQL so I was trying CASE and was still coming up empty handed. Here is what I produced in SQL but it didn't work:

SELECT SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CFume) AS CFume, SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CSlag) AS cSlag,

CASNumber = CASE Type
WHEN categoryCode IS NULL THEN dbo.tblConstituents.CASNumber ELSE CategoryCode
END,
FROM dbo.tblConstituents INNER JOIN
dbo.RecycleWR_qryWeldingRod3a_LBS ON dbo.tblConstituents.CASNumber = dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber
GROUP BY dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber

Any ideas would be greatly appreciated.SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END|||Originally posted by Brett Kaiser
SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END

Well, when I do that, I get
"The Query Designer does not support the CASE SQL construct."

Can you even use CASE in a view?|||Also, I need to assign an alias to that column.|||What are you using?

Aren't you using query analyzer?

If you're using Access you may need to make it a PASS THRU query

SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END AS NewCol|||I was creating the query in VIEW but I got around it using a function. Took me awhile but its working fine now. Thanks for your help|||What do you mean in VIEW?

Are you doing this in Enterprise Manager?

I would recommend against that.|||Wether you use the designer in access or in EM, you'll loose the graphical representation of your query when you use CASE (and a bunch of other constructs). This is what the error message says. The query should run fine, anyway and you should see and be able to modify the sql source in access.
However, beware of the designer, especially if you have complex where clauses. All sorts of weird things may happen to your sql ;)|||What s/he said...

Use QA though for SQL Server development...

You'll have a lot less headaches...

Conditional Expression

I have an expression that looks like
this;"=Fields!Quantity.Value-Fields!QuantityAvailable.Value-Fields!QTYONORD.Value"
The field returns fine, but I want negative values to be returned as 0 when
the value is <=0.
Any ideas on how to do this? Would I add a IIF to the expression? Any help
is appreciated.
Thanks!
RyanOn May 24, 1:19 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> I have an expression that looks like
> this;"=Fields!Quantity.Value-Fields!QuantityAvailable.Value-Fields!QTYONORD.Value"
> The field returns fine, but I want negative values to be returned as 0 when
> the value is <=0.
> Any ideas on how to do this? Would I add a IIF to the expression? Any help
> is appreciated.
> Thanks!
> Ryan
This should work.
=iif(Fields!Quantity.Value-Fields!QuantityAvailable.Value-Fields!
QTYONORD.Value <= 0, 0, Fields!Quantity.Value-Fields!
QuantityAvailable.Value-Fields!QTYONORD.Value)
Regards,
Enrique Martinez
Sr. Software Consultant

Conditional Expression

I have the following being applied as an expression:

=iif(sum(Fields!Percentile.Value) > 10, "Yellow", "Black")

I want my percents to show up yellow if they are above 10.00%, and black if they are under 10%, doesn't appear to be working right?one other question I will have with this is to find out if i can make the entire block yellow instead of just the text?

Conditional Count in RDLC Expression

I'd like to put a count in a textbox but only a count where a column
doesnt equal certain values. Here is what I tried:
=Count(Fields!ReportStatus.Value<>"MAILED" AND Fields!
ReportStatus.Value<>"PRINTED" AND Fields!ReportStatus<>"TRANSMIT")
Obviously this didn't work. Can anyone point me in the right
direction?
Thanks,
JasonOn Mar 14, 3:36=A0pm, Jason Wilson <wils...@.ausrad.com> wrote:
> I'd like to put a count in a textbox but only a count where a column
> doesnt equal certain values. =A0Here is what I tried:
> =3DCount(Fields!ReportStatus.Value<>"MAILED" AND Fields!
> ReportStatus.Value<>"PRINTED" AND Fields!ReportStatus<>"TRANSMIT")
> Obviously this didn't work. =A0Can anyone point me in the right
> direction?
> Thanks,
> Jason
See if this works:
=3DCOUNT(IIF(Fields!ReportStatus.Value<>"MAILED" AND Fields!
ReportStatus.Value<>"PRINTED" AND Fields!
ReportStatus.Value<>"TRANSMIT",1,0))
HTH
toolman|||On Mar 14, 3:36=A0pm, Jason Wilson <wils...@.ausrad.com> wrote:
> I'd like to put a count in a textbox but only a count where a column
> doesnt equal certain values. =A0Here is what I tried:
> =3DCount(Fields!ReportStatus.Value<>"MAILED" AND Fields!
> ReportStatus.Value<>"PRINTED" AND Fields!ReportStatus<>"TRANSMIT")
> Obviously this didn't work. =A0Can anyone point me in the right
> direction?
> Thanks,
> Jason
See if this works:
=3DSUM(IIF(Fields!ReportStatus.Value<>"MAILED" AND Fields!
ReportStatus.Value<>"PRINTED" AND Fields!
ReportStatus.Value<>"TRANSMIT",1,0))
HTH
toolman|||It did thanks