problem with IIF statement

I've got this iif statement
=iif(Fields!Month.Value =13,"YTD",MonthName(Fields!Month.Value))
in a matrix header field that has the month numbers in it. it will
display ytd if i drop the month name function but with the monthname in
it throws an error. Also theres a warning that says
[rsRuntimeErrorInExpression] The Value expression for the textbox
'textbox47' contains an error: Argument 'Month' is not a valid
value.
any ideas on how to get it to display the month name and the YTD text?
Thanks for the help
MathiasI saw some other post as well, IIF evaluates both the truw and false
expression and then goes for comparison. so MonthName(13) will give error
since there is no 13. So reframe your conditions.
Amarnath.
"Mathias" wrote:
> I've got this iif statement
> =iif(Fields!Month.Value =13,"YTD",MonthName(Fields!Month.Value))
> in a matrix header field that has the month numbers in it. it will
> display ytd if i drop the month name function but with the monthname in
> it throws an error. Also theres a warning that says
> [rsRuntimeErrorInExpression] The Value expression for the textbox
> 'textbox47' contains an error: Argument 'Month' is not a valid
> value.
> any ideas on how to get it to display the month name and the YTD text?
> Thanks for the help
> Mathias
>|||so umm care to point out those posts or tell me something i don't
already know?
any hint as to how to reframe my condition's would be of great help.|||Mathias,
As far as posts go, just search for "IIF error" or "IIF doesn't work"
and you'll come up with tons of 'em.
My experience with this issue comes from trying to do divide by zero
error checking. For example, =IIF(exp2 = 0,0,exp1/exp2); SSRS
evaluates both T and F and blows up when exp2 = 0.
The only way I've found to work around is to create a custom code
function then use that function in your expression. For your situation
the function would be something like:
Public Function MonthValue (Exp1)
If Exp1 = 13 Then
MonthValue = "YTD"
Else MonthValue = MonthName(Exp1)
End If
End Function
Your expression would then be:
=code.MonthValue(Fields!Month.Value)
Good luck
toolman|||Thanks for the help. don't know why i never thought to look for iif
error. I'll give that custom code a shot and see what I come up with.
Thanks
Mathias
toolman wrote:
> Mathias,
> As far as posts go, just search for "IIF error" or "IIF doesn't work"
> and you'll come up with tons of 'em.
> My experience with this issue comes from trying to do divide by zero
> error checking. For example, =IIF(exp2 = 0,0,exp1/exp2); SSRS
> evaluates both T and F and blows up when exp2 = 0.
> The only way I've found to work around is to create a custom code
> function then use that function in your expression. For your situation
> the function would be something like:
> Public Function MonthValue (Exp1)
> If Exp1 = 13 Then
> MonthValue = "YTD"
> Else MonthValue = MonthName(Exp1)
> End If
> End Function
> Your expression would then be:
> =code.MonthValue(Fields!Month.Value)
> Good luck
> toolmansql

Problem with IIF and Like comparison

I am having a problem with the following expression

=IIf(Fields!client_short_name.Value Like "Capital% ", 1,0)

I am wanting to get a value of 1 if the field has any of the valid values that begin with Capital but it always returns 0. Any ideas?

You may have better luck with the Instr command.|||

Do you know what the proper syntax would be in this example? I'm struggling.

|||

Did you try to use LIKE "Capital" (without using %)

I don't know try that.

|||

I believe it would be

Code Snippet

=IIf(Instr(Fields!client_short_name.Value,"Capital") = 1,1,0)

Of course, this isn't exactly like your expression because the instr looks for the word anyware in the string. You could also use the left function

Code Snippet

=IIF(Left(Fields!client_short_name.Value,7) = "Capital",1,0)

Problem with Expression for a added field in datasource.

I added a new field to my datasource called "TotalCostAssum". The expression for the datasource is as follows:

Iif(Previous(Fields!PNumber.Value) = Nothing Or Previous(Fields!PNumber.Value) <> Fields!PNumber.Value, Fields!TotalCost, 0)

But this expression gave me trouble. I even couldn't go to the Preview page because every time when I clicked the Preview button, the Visual Studio.NET was shut down by asking me if I need to send error report to Microsoft. When I got rid of the above expression, everything is fine. I was wondering if that's because the word "Previous" is not allowed here. But I have to access the previous data row to determine the value here. I was bothered by this the whole morning and couldn't get any hint by searching on the internet. Any anybody help me out? Thanks in advance.

Mistake. The above expression is for the field that I manually added to the dataset.

Problem with datetime expression

I have the following expression in a textbox in a table based on a dataset:
=IIF(Fields!Opened.Value="No","No",Format(Fields!Opened.Value,"yyyy-MM-dd
HH:mm:ss"))
The result I recieve when there is supposed to be a date is the date
mask (yyyy-MM-dd HH:mm:ss) instead of the actual value of the date.
Any ideas?
Best regards,
Peter!On Dec 7, 7:17 am, Peter Larsson <scape...@.hotmail.com> wrote:
> I have the following expression in a textbox in a table based on a dataset:
> =IIF(Fields!Opened.Value="No","No",Format(Fields!Opened.Value,"yyyy-MM-dd
> HH:mm:ss"))
> The result I recieve when there is supposed to be a date is the date
> mask (yyyy-MM-dd HH:mm:ss) instead of the actual value of the date.
> Any ideas?
> Best regards,
> Peter!
I assume that Fields!Opened.Value is a string value (because you are
using it twice there) that either contains the text "No" or a Date.
Try wrapping the second Value in a CDate() function to force a
conversion to DateTime. If the Format command is fed a value that it
can't convert, it returns the formatting string, not the value.
= IIF( Fields!Opened.Value = "No", "No", Format( CDate(Fields!
Opened.Value), "yyyy-MM-dd HH:mm:ss") )
-- Scott|||Orne wrote:
> On Dec 7, 7:17 am, Peter Larsson <scape...@.hotmail.com> wrote:
>> I have the following expression in a textbox in a table based on a dataset:
>> =IIF(Fields!Opened.Value="No","No",Format(Fields!Opened.Value,"yyyy-MM-dd
>> HH:mm:ss"))
>> The result I recieve when there is supposed to be a date is the date
>> mask (yyyy-MM-dd HH:mm:ss) instead of the actual value of the date.
>> Any ideas?
>> Best regards,
>> Peter!
> I assume that Fields!Opened.Value is a string value (because you are
> using it twice there) that either contains the text "No" or a Date.
> Try wrapping the second Value in a CDate() function to force a
> conversion to DateTime. If the Format command is fed a value that it
> can't convert, it returns the formatting string, not the value.
> = IIF( Fields!Opened.Value = "No", "No", Format( CDate(Fields!
> Opened.Value), "yyyy-MM-dd HH:mm:ss") )
> -- Scott
Hi Scott!
Thanks for the tip, now the date works but I get the #Error on the When
the value contains "No".
/Peter|||On Dec 7, 11:05 am, Peter Larsson <scape...@.hotmail.com> wrote:
> Orne wrote:
> > On Dec 7, 7:17 am, Peter Larsson <scape...@.hotmail.com> wrote:
> >> I have the following expression in a textbox in a table based on a dataset:
> >> =IIF(Fields!Opened.Value="No","No",Format(Fields!Opened.Value,"yyyy-MM-dd
> >> HH:mm:ss"))
> >> The result I recieve when there is supposed to be a date is the date
> >> mask (yyyy-MM-dd HH:mm:ss) instead of the actual value of the date.
> >> Any ideas?
> >> Best regards,
> >> Peter!
> > I assume that Fields!Opened.Value is a string value (because you are
> > using it twice there) that either contains the text "No" or a Date.
> > Try wrapping the second Value in a CDate() function to force a
> > conversion to DateTime. If the Format command is fed a value that it
> > can't convert, it returns the formatting string, not the value.
> > = IIF( Fields!Opened.Value = "No", "No", Format( CDate(Fields!
> > Opened.Value), "yyyy-MM-dd HH:mm:ss") )
> > -- Scott
> Hi Scott!
> Thanks for the tip, now the date works but I get the #Error on the When
> the value contains "No".
> /Peter- Hide quoted text -
> - Show quoted text -
Ok, just made some test data. I think that the second half of the IIF
statement is still being evaluated, and for those rows where the value
is "No", the CDate is failing, therefore the whole IIF is failing.
So, before we do the CDate, we have to check again if the value is a
non-convertable date, then if it is not convertable, set it to
Nothing. CDate(Nothing) is still Nothing, so then the second half of
the IIF would succeed.
The following works the way I think you want it to:
=IIF( Fields!Opened.Value = "No", "No", Format( CDate( IIF(Fields!
Opened.Value = "No", Nothing, Fields!Opened.Value) ), "yyyy-MM-dd
HH:mm:ss" ) )
-- Scott