IME?

"Derived column transformation editor"

drag and drop a column into the bottom pane

right click on the 'expression' column and it has a menu item called "open IME"

but nothing happens when I do it.

Is it an expression editor?

Strategic thinking:

Microsoft BI

Reporting Services Expression language: VB.net

Integration Services Expression language: "similar to the syntax that the C and C# languages use"

great!

IME - Microsoft Global Input Method Editors (IMEs) - http://www.microsoft.com/windows/ie/ie6/downloads/recommended/ime/default.mspx

It is nothing to do with SSIS, it is just an option that Windows can provide for text boxes. There is no expression editor available for derived columns, just that free entry text box.

As for the new SSIS expression syntax, yes it is a bit annoying it is yet another syntax, but the benefit is that it is very fast and baked into SSIS. Anything else would not be as fast, and in ETL that counts.

sql

Image And Expression

hi there,
i using an expression at the data value with the IIF statement to call the
image which i have embeded 2 images and wanna to display them on the table
textbox when they they satify the IIF statement. and i encounter this error;
The value expression for the image â'image6â' contains an error: [BC30451] Name
'check' is not declared.
how can i resolve this problem or is there any other alternative for mi to
this issue.because wat i want is that within the IIF statement i wan to
display the different image.
thanks in advance
Angelacan you post the expression
"Angela" wrote:
> hi there,
> i using an expression at the data value with the IIF statement to call the
> image which i have embeded 2 images and wanna to display them on the table
> textbox when they they satify the IIF statement. and i encounter this error;
> The value expression for the image â'image6â' contains an error: [BC30451] Name
> 'check' is not declared.
> how can i resolve this problem or is there any other alternative for mi to
> this issue.because wat i want is that within the IIF statement i wan to
> display the different image.
> thanks in advance
> Angela
>|||=iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
check , checked )
(the image name is check and checked)
"Antoon" wrote:
> can you post the expression
> "Angela" wrote:
> > hi there,
> > i using an expression at the data value with the IIF statement to call the
> > image which i have embeded 2 images and wanna to display them on the table
> > textbox when they they satify the IIF statement. and i encounter this error;
> > The value expression for the image â'image6â' contains an error: [BC30451] Name
> > 'check' is not declared.
> > how can i resolve this problem or is there any other alternative for mi to
> > this issue.because wat i want is that within the IIF statement i wan to
> > display the different image.
> >
> > thanks in advance
> > Angela
> >|||ok,
- The name of the image has to be between quotes.
- You can not put this in a textbox, you can put the expression in an
image-box. So first put an image-box on you report (or in a table cell) and
than change the value of the image to the expression.
- In the expression you've given the Code-value of each row of the table is
compared to the "First" allergyRout_Code-value. This migth be correct, but if
you want to check against every value of allergyRout_Code, you should ommit
the "First"- function (if they are in the same dataset)
=iif( Fields!allergyRoute_CODE.Value = Fields!Code.Value ,
"check" , "checked" )
"Angela" wrote:
> =iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
> check , checked )
> (the image name is check and checked)
> "Antoon" wrote:
> > can you post the expression
> >
> > "Angela" wrote:
> >
> > > hi there,
> > > i using an expression at the data value with the IIF statement to call the
> > > image which i have embeded 2 images and wanna to display them on the table
> > > textbox when they they satify the IIF statement. and i encounter this error;
> > > The value expression for the image â'image6â' contains an error: [BC30451] Name
> > > 'check' is not declared.
> > > how can i resolve this problem or is there any other alternative for mi to
> > > this issue.because wat i want is that within the IIF statement i wan to
> > > display the different image.
> > >
> > > thanks in advance
> > > Angela
> > >|||hi Antoon,
for the value to compare, they are from 2 different dataset. bcause i want
to compare the actual value with the master values. so if the answer matches
then i will display the correct image. And is there another other way apart
from making them to be the same dataset?
ur regards
Angela
"Antoon" wrote:
> ok,
> - The name of the image has to be between quotes.
> - You can not put this in a textbox, you can put the expression in an
> image-box. So first put an image-box on you report (or in a table cell) and
> than change the value of the image to the expression.
> - In the expression you've given the Code-value of each row of the table is
> compared to the "First" allergyRout_Code-value. This migth be correct, but if
> you want to check against every value of allergyRout_Code, you should ommit
> the "First"- function (if they are in the same dataset)
> =iif( Fields!allergyRoute_CODE.Value = Fields!Code.Value ,
> "check" , "checked" )
>
> "Angela" wrote:
> > =iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
> > check , checked )
> >
> > (the image name is check and checked)
> >
> > "Antoon" wrote:
> >
> > > can you post the expression
> > >
> > > "Angela" wrote:
> > >
> > > > hi there,
> > > > i using an expression at the data value with the IIF statement to call the
> > > > image which i have embeded 2 images and wanna to display them on the table
> > > > textbox when they they satify the IIF statement. and i encounter this error;
> > > > The value expression for the image â'image6â' contains an error: [BC30451] Name
> > > > 'check' is not declared.
> > > > how can i resolve this problem or is there any other alternative for mi to
> > > > this issue.because wat i want is that within the IIF statement i wan to
> > > > display the different image.
> > > >
> > > > thanks in advance
> > > > Angela
> > > >|||Yes there are, but if you really wan't to compare
First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value
then
=iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
"check" , "checked" )
should work fine (in an image-box-in-a-table-field)
"Angela" wrote:
> hi Antoon,
> for the value to compare, they are from 2 different dataset. bcause i want
> to compare the actual value with the master values. so if the answer matches
> then i will display the correct image. And is there another other way apart
> from making them to be the same dataset?
> ur regards
> Angela
> "Antoon" wrote:
> > ok,
> > - The name of the image has to be between quotes.
> > - You can not put this in a textbox, you can put the expression in an
> > image-box. So first put an image-box on you report (or in a table cell) and
> > than change the value of the image to the expression.
> > - In the expression you've given the Code-value of each row of the table is
> > compared to the "First" allergyRout_Code-value. This migth be correct, but if
> > you want to check against every value of allergyRout_Code, you should ommit
> > the "First"- function (if they are in the same dataset)
> > =iif( Fields!allergyRoute_CODE.Value = Fields!Code.Value ,
> > "check" , "checked" )
> >
> >
> > "Angela" wrote:
> >
> > > =iif( First(Fields!allergyRoute_CODE.Value, "DataSet1") = Fields!Code.Value ,
> > > check , checked )
> > >
> > > (the image name is check and checked)
> > >
> > > "Antoon" wrote:
> > >
> > > > can you post the expression
> > > >
> > > > "Angela" wrote:
> > > >
> > > > > hi there,
> > > > > i using an expression at the data value with the IIF statement to call the
> > > > > image which i have embeded 2 images and wanna to display them on the table
> > > > > textbox when they they satify the IIF statement. and i encounter this error;
> > > > > The value expression for the image â'image6â' contains an error: [BC30451] Name
> > > > > 'check' is not declared.
> > > > > how can i resolve this problem or is there any other alternative for mi to
> > > > > this issue.because wat i want is that within the IIF statement i wan to
> > > > > display the different image.
> > > > >
> > > > > thanks in advance
> > > > > Angela
> > > > >

iif/switch statement does not work in input parameter expression

Hi There,

One of the parameters that i need to pass to a stored procedure of my
report, i wish to dynamically calculate from another input default parameter

lets say parameter 'DrillDown' = "ABCDEFG"

paramenter 'MyLevel' is calculated by either of the following a switch or
iif statement:

=switch
(left(Parameters!DrillDown.Value,1)="A",1,
left(Parameters!DrillDown.Value,1)="B",2,
left(Parameters!DrillDown.Value,1)="C",3,
left(Parameters!DrillDown.Value,1)="D",4,
left(Parameters!DrillDown.Value,1)="E",5,
left(Parameters!DrillDown.Value,1)="F",6,
left(Parameters!DrillDown.Value,1)="G",7)
--or--
=iif(left(Parameters!RepLvl.Value,1) = "A",1,
iif(left(Parameters!RepLvl.Value,1) = "B",2,5))

Trouble is none of the 2 above work!

i get the following error: The value expression for the report parameter
‘MyLevel’ contains an error: [BC30201] Expression expected.

Wierd thing is if i just have '=iif(left(Parameters!RepLvl.Value,1) =
"A",1,5)' without an 'else if' it works fine !

Can any one help please!

Cheers

Dave

why not use custom code?

I have found for complicated IIF statements, it's easier and safer to create a custom code function.

|||

I would use a code function but for input parameters on start of a report you cant assign them to a 'code.' parameter. Can you? I get an error when i try this.

I got it to work , by taking out all the spaces ?!?!?

Wierdly it works with the spaces in if i have the expression elsewhere in the report, for intsance in a text box .

Anyhow thanks for the reply

Cheers

Dave

IIF String Contains a value?

I want to create an IIF expression that changes the color of a field based on if a string value contains a 4 or 5 Any ideas on how to accomplish this?

Hi,

You can write an iif() expression in the background color property of that field.

The following expression will help:

iif(instr("string5",5) or instr("string4",4),"Gray","White")

You can change the color by choosing what u require from the constants provided else custon color.

Somiya

|||Thanks, but I think I need a little more help. The value I need to look for will be in a string like "3,4,6,8" so I think I need the Like conparison function, but I'm not sure of the syntax

The psudo code is this:

If instrI(String) contains a 4 or instrI(String) contains 5 display in Red else Black

Do you know what the syntax should be for IIF with a like conparison?|||

i think the same expression should work in a way similar to Like.

An iff() expression with Like operator would be in this case:

iif(("3,4,6,8" Like "*4*") or ("3,4,6,8" Like "*5*") ,"Red",Black")

* is for any 4 preceeding and followed with any number of characters

Somiya

iif statement problem

hi all,

i've browsed some of the smilar iif expression problems but no idea how to solve mine.

i get an #Error when i wrote the following expr :

=sum(iif(Fields!revenue_type.Value="R", Fields!amount.Value,0))

but no error when it's

=sum(iif(Fields!revenue_type.Value="R", Fields!amount.Value,0) - iif(Fields!revenue_type.Value="T", Fields!amount.Value,0))

If i filter the dataset and just take in data where revenue_type = "R" then there won't be error... how come?

did i do something wrong? please help... this seems so simple yet i couldn't get it..

oh btw, i wrote the expression in table footer|||Maybe somewhere Fields!revenue_type is "null", then accessing Fields!revenue_type.Value will generate error. Try checking (IsNothing(Fields!revenue_type.Value) = false and Fields!revenue_type.Value = "R").|||

hi Maciej, thanks for your reply but it still return #Error.

there's no null value for revenue_type

|||

Ok, after your post I've tried the same thing in one of my reports and got the same error (tip - it is good to read the "error list" ). Mine says:

Warning 1 [rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘textbox116’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type. c:\...\Obroty producentow.v1.2.rdl

So the problem is in aggregation varying data types. Simplest way to fix it:

=Sum(iff(<your bool>,CDbl(<your sum field>),CDbl(0))

CDbl is used only as a example (it will conver every number to "double" data type). You can use different conversion method (from Common functions\Conversion).

I hope this will help

Maciej

|||

thanks Maciej. it works! but i don't get why the expr needs to be converted to double data type...

cos i got that warning as well but didn't get it...

|||Maybe RS is not so clever Your field if decimal or duble or something and "0" is integer so RS gets lost |||You hit the nail on the head with "Maybe RS is not so clever." I experienced the same #error issue in some of my calculated columns in a table in my report but oddly not until i added 2 text boxes above the table to my report. The text boxes display a begin and end time from a separate data set query. When I added those, I got the #error issue. But when i remove them, it goes away. I did not edit the table with the calculated columns in any way. I was able to get the #error issue to go away by using the CDbl solution you described (THANKS!) and leave the text boxes with the datetimes on the report. But there has to be a bug somewhere because that makes no sense. MICROSOFT> PLEASE NOTE AND FIX!!!

iif statement problem

hi all,

i've browsed some of the smilar iif expression problems but no idea how to solve mine.

i get an #Error when i wrote the following expr :

=sum(iif(Fields!revenue_type.Value="R", Fields!amount.Value,0))

but no error when it's

=sum(iif(Fields!revenue_type.Value="R", Fields!amount.Value,0) - iif(Fields!revenue_type.Value="T", Fields!amount.Value,0))

If i filter the dataset and just take in data where revenue_type = "R" then there won't be error... how come?

did i do something wrong? please help... this seems so simple yet i couldn't get it..

oh btw, i wrote the expression in table footer|||Maybe somewhere Fields!revenue_type is "null", then accessing Fields!revenue_type.Value will generate error. Try checking (IsNothing(Fields!revenue_type.Value) = false and Fields!revenue_type.Value = "R").|||

hi Maciej, thanks for your reply but it still return #Error.

there's no null value for revenue_type

|||

Ok, after your post I've tried the same thing in one of my reports and got the same error (tip - it is good to read the "error list" ). Mine says:

Warning 1 [rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘textbox116’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type. c:\...\Obroty producentow.v1.2.rdl

So the problem is in aggregation varying data types. Simplest way to fix it:

=Sum(iff(<your bool>,CDbl(<your sum field>),CDbl(0))

CDbl is used only as a example (it will conver every number to "double" data type). You can use different conversion method (from Common functions\Conversion).

I hope this will help

Maciej

|||

thanks Maciej. it works! but i don't get why the expr needs to be converted to double data type...

cos i got that warning as well but didn't get it...

|||Maybe RS is not so clever Your field if decimal or duble or something and "0" is integer so RS gets lost |||You hit the nail on the head with "Maybe RS is not so clever." I experienced the same #error issue in some of my calculated columns in a table in my report but oddly not until i added 2 text boxes above the table to my report. The text boxes display a begin and end time from a separate data set query. When I added those, I got the #error issue. But when i remove them, it goes away. I did not edit the table with the calculated columns in any way. I was able to get the #error issue to go away by using the CDbl solution you described (THANKS!) and leave the text boxes with the datetimes on the report. But there has to be a bug somewhere because that makes no sense. MICROSOFT> PLEASE NOTE AND FIX!!!

IIf problem

I'm using that expression in a select statment in generic query designer, but there is a problem with it. I'm wondering what would that be.

IIf(Parameters!StartDate.Value = "" or Parameters!EndDate.Value = "", "", "where (Date between '" & Parameters!StartDate.Value & "' and '" & Parameters!EndDate.Value & "')")

A double-quote character within a string literal is escaped by a preceding double-quote. So, the second argument of IIf() should look like:

"where (Date between '"" & Parameters!StartDate.Value & ""' and '"" & Parameters!EndDate.Value & ""')"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbls7/html/vblrfvbspec2_4_4.asp
>>

Visual Basic Language Specification

2.4.4 String Literals

A string literal is a sequence of zero or more Unicode characters beginning and ending with an ASCII double-quote character, a Unicode left double-quote character, or a Unicode right double-quote character. Within a string, a sequence of two double-quote characters is an escape sequence representing a double quote in the string.
>>

|||Thank you Deepak for your help. Actually it worked with one double quote, the problem was in the condition (Parameters!StartDate.Value = ""), I replaced it by IsNothing(Parameters!StartDate.Value) and it worked fine.

IIF problem

Hi all,
I have a problem with an IIF expression on a cell of my report (SSRS 2005).
I have a query that returns some datafields bigint that represent a date.
Because some values are null, I put this expression in my cell:
=IIF(Fields!LedgerReferenceDateIdMinus1.Value="","is null","is not null")
but on report rendering, I get this error:
#Error
in the case of not null.
What have I made wrong?
Thanks a lot.
LuigiOn Apr 14, 8:39 am, Luigi <ciupazNoSpamGra...@.inwind.it> wrote:
> Hi all,
> I have a problem with an IIF expression on a cell of my report (SSRS 2005).
> I have a query that returns some datafields bigint that represent a date.
It looks like you are getting your SQL syntax and your Expression
syntax mixed up when looking for Null values.
When using an expression for an RS field, you can check if a value is
null using the IsNull function, like so:
=IIF(IsNull(Fields!LedgerReferenceDateIDMinus1.value), '',
Fields!LedgerReferenceDateIDMinus1.value)
Or, you could check for Null values within your SQL query which is
probably better, because then you never have to deal with Null values
within that field once the dataset reaches your report:
CASE WHEN LedgerReferenceDateIDMinus1 IS NULL THEN '' ELSE
LedgerReferenceDateIDMinus1 END as LedgerReferenceDateIDMinus1
Good luck!
<
> =IIF(Fields!LedgerReferenceDateIdMinus1.Value="","is null","is not null")
> but on report rendering, I get this error:
> #Error
> in the case of not null.
> What have I made wrong?
> Thanks a lot.
> Luigi|||"Jerry H." wrote:
> When using an expression for an RS field, you can check if a value is
> null using the IsNull function, like so:
>
> =IIF(IsNull(Fields!LedgerReferenceDateIDMinus1.value), '',
> Fields!LedgerReferenceDateIDMinus1.value)
>
> Or, you could check for Null values within your SQL query which is
> probably better, because then you never have to deal with Null values
> within that field once the dataset reaches your report:
>
> CASE WHEN LedgerReferenceDateIDMinus1 IS NULL THEN '' ELSE
> LedgerReferenceDateIDMinus1 END as LedgerReferenceDateIDMinus1
>
> Good luck!
Hi Jerry, I'll try with IsNull in the report.
Thanks a lot for your detailed answer.
Luigi|||I slightly problem.
IfNull give me the "Unrecognized identifier" error.
This is my expression:
=IIF(IsNull(Fields!GrossDeltaMinus1.Value,''),FormatNumber(Fields!GrossDeltaMinus1,2))|||Move your first closing parentheses so that it is between the "e" in
Value and the first comma in your expression.
At the moment, you are passing two parameters over to IsNull, which
only takes one parameter.
On Apr 14, 9:39 am, Luigi <ciupazNoSpamGra...@.inwind.it> wrote:
> I slightly problem.
> IfNull give me the "Unrecognized identifier" error.
> This is my expression:
> =IIF(IsNull(Fields!GrossDeltaMinus1.Value,''),FormatNumber(Fields!GrossDeltaMinus1,2))|||"Jerry H." <boilersrock@.gmail.com> wrote in message
news:7edfeec7-a2fe-4d50-8cef-503e5798b1b5@.f36g2000hsa.googlegroups.com...
> Move your first closing parentheses so that it is between the "e" in
> Value and the first comma in your expression.
> At the moment, you are passing two parameters over to IsNull, which
> only takes one parameter.
> On Apr 14, 9:39 am, Luigi <ciupazNoSpamGra...@.inwind.it> wrote:
>> I slightly problem.
>> IfNull give me the "Unrecognized identifier" error.
>> This is my expression:
>> =IIF(IsNull(Fields!GrossDeltaMinus1.Value,''),FormatNumber(Fields!GrossDeltaMinus1,2))
>
I had that same problem, "Unrecognized identifier" error. and found
IsNothing worked instead.

iif inside iif

I'd error message on this expression ,

It is written in hidden property of an image

Can you help me to correct it?

thanks all.

=iif(Parameters!Direction.Value = "none", True, iif(Parameters!Direction.Value = "Ascending",True,False )

You are missing a closing ")" and when comparing strings I would use the "equals" method.

Here is what the statement should be:

=iif(Parameters!Direction.Value.equals("none"), True, iif(Parameters!Direction.Value.equals("Ascending"),True,False ))

iif expression...need to add another condition...

Ok,
This expression for the font color is great:
=IIf(Fields!Purchase.Value < 0, "Red", "CornflowerBlue")
It changes negatives to red, positives to CornflowerBlue.
However, I need 0 (zero values) to be White, so that there is the third
color.
Any help is appreciated.
Thanks,
TrintMy only suggestion is to have a nested "iff" I do this quite a bit.
=IIf(Fields!Purchase.Value < 0, "Red", IIF(Fields!Purchase.Value = 0,
"White","CornflowerBlue"))
Hope that works.
"trint" wrote:
> Ok,
> This expression for the font color is great:
> =IIf(Fields!Purchase.Value < 0, "Red", "CornflowerBlue")
> It changes negatives to red, positives to CornflowerBlue.
> However, I need 0 (zero values) to be White, so that there is the third
> color.
> Any help is appreciated.
> Thanks,
> Trint
>|||You might find it easier to maintain with this kind of approach:
=getPurchaseColour(Fields!Purchase.Value)
... and have a function like this (pseudocode)..
friend function getPurchaseColour(value) as string
if value<0 return "Red"
if value=0 return "White"
return "CornflowerBlue"
end function

IIF Expression with SELECT statement

Is it possible to use a SELECT statement as the expression in an IIF function?
I Cannot get it to work.
i.e. IIF((SELECT COUNT(column) FROM table WHERE column = Fields!Variable.Value) > 0, "BAD","GOOD")
--
Thanks in advance
GHLooks like you can't do that because query can be only inside of data set in
Query element.
Victor
"GH" wrote:
> Is it possible to use a SELECT statement as the expression in an IIF function?
> I Cannot get it to work.
> i.e. IIF((SELECT COUNT(column) FROM table WHERE column => Fields!Variable.Value) > 0, "BAD","GOOD")
> --
> Thanks in advance
> GH|||Nope. Selects can only be used when defining datasets. However, you can use
count (and sum etc) in expressions. From BOL,
>>>>>>>>
The following code example provides a count of employees in the outermost
data region:
Count(Fields!EmployeeID.Value, Nothing)The following code example provides a
count of all orders in the Orders grouping or data region:
Count(Fields!OrderID.Value, "Orders")>>>>>>>>>>--
Bruce Loehle-Conger MVP SQL Server Reporting Services"GH"
<vakar@.community.nospam> wrote in message
news:90A8523F-42B6-41DB-BB71-DD78F77C8B18@.microsoft.com...
> Is it possible to use a SELECT statement as the expression in an IIF
function?
> I Cannot get it to work.
> i.e. IIF((SELECT COUNT(column) FROM table WHERE column => Fields!Variable.Value) > 0, "BAD","GOOD")
> --
> Thanks in advance
> GH|||="select count (column) from table where
column='"+iif(fields!variable.value>0, "BAD","GOOD")+"'"
hope works
regards
"GH" <vakar@.community.nospam> wrote in message
news:90A8523F-42B6-41DB-BB71-DD78F77C8B18@.microsoft.com...
> Is it possible to use a SELECT statement as the expression in an IIF
function?
> I Cannot get it to work.
> i.e. IIF((SELECT COUNT(column) FROM table WHERE column => Fields!Variable.Value) > 0, "BAD","GOOD")
> --
> Thanks in advance
> GH|||You should do this in "Generic Query Designer"
"saglamtimur" <bsaglamtimur@.mayanet.com.tr> wrote in message
news:OExB7I#2EHA.2804@.TK2MSFTNGP15.phx.gbl...
> ="select count (column) from table where
> column='"+iif(fields!variable.value>0, "BAD","GOOD")+"'"
> hope works
> regards
>
> "GH" <vakar@.community.nospam> wrote in message
> news:90A8523F-42B6-41DB-BB71-DD78F77C8B18@.microsoft.com...
> > Is it possible to use a SELECT statement as the expression in an IIF
> function?
> >
> > I Cannot get it to work.
> >
> > i.e. IIF((SELECT COUNT(column) FROM table WHERE column => > Fields!Variable.Value) > 0, "BAD","GOOD")
> > --
> > Thanks in advance
> > GH
>|||Thanks guys.
I had to get 'creative' with my data set ... not sure of the cost ... but
getting to where I want to go.
Thanks again.
"saglamtimur" wrote:
> You should do this in "Generic Query Designer"
>
> "saglamtimur" <bsaglamtimur@.mayanet.com.tr> wrote in message
> news:OExB7I#2EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > ="select count (column) from table where
> > column='"+iif(fields!variable.value>0, "BAD","GOOD")+"'"
> >
> > hope works
> >
> > regards
> >
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:90A8523F-42B6-41DB-BB71-DD78F77C8B18@.microsoft.com...
> > > Is it possible to use a SELECT statement as the expression in an IIF
> > function?
> > >
> > > I Cannot get it to work.
> > >
> > > i.e. IIF((SELECT COUNT(column) FROM table WHERE column => > > Fields!Variable.Value) > 0, "BAD","GOOD")
> > > --
> > > Thanks in advance
> > > GH
> >
> >
>
>

IIF expression problem

Hello all,

If anyone could give me a hand on this it would be greatly appreciated. The problem i am having is as follows: On a rdlc report I have a column which I am generating a currency value based on a nested IIF statement. What is happening is if I don't have the if statement for the column and just calculate like so : =Fields!Amount.value * 0.02 the number comes out correctly, however if I have it in the following iif statement it automatically rounds the values.

=FormatCurrency(IIF(Fields!Amount.Value = 114.00,((Fields!Amount.Value * .02)* 2),0)Or

IIF(Fields!Amount.Value = 20.00,((Fields!Amount.Value * .02) * 12),0)Or

IIF(Fields!Amount.Value = 216.00,(Fields!Amount.Value * .02),0))

Any insight would be great

Thanks

whats the datatype of Fields!Amount.value ... is it an integer or decimal

IIF - Newbie

Hi,

just for understanding a simple iif statement:
I like to set a numeric value to 0, if it is smaller than 0.
This expression shall be valid over all dimensions for a specific measure

like Iif([Measures].[Provision Ankauf]<0,0,[Measures].[Provision Ankauf] )

it's not working, Pivottable returns ##VALUE everywheremake sure [Measures].[Provision Ankauf] is returning a number, and is not null.

also to make sure you're not crazy try: IIF(5<0,0,5) and see what happens|||IIF is not supported in SQL Server

use CASE instead|||As Rudy pointed out, you need to translate your VB (actually Jet) syntax to:CASE
WHEN [Measures].[Provision Ankauf] < 0 THEN 0
ELSE [Measures].[Provision Ankauf]
END-PatP|||Just to be ornery:

([Measures].[Provision Ankauf]+abs([Measures].[Provision Ankauf]))/2|||Just to be ornery:Ornery ?!?! That's deviant, low down, and high smellin' !!! I LIKE it!

In reality, the CASE statement is something that the original poster needs to know how to use since it opens all kinds of other possibilities that they'll need someday, but your bit of nifty math is both kinky and effective, so it is lots of fun the play with!

-PatP|||blindman, brilliant as usual!!|||Yeah, he needs to know CASE.

For some reason, possibly (probably) unjustified, I always feel like logic statements are less efficient than formula solutions. Just my bias, though I have to agree that the CASE statement is more readable. Without a comment, somebody looking at my solution wouldn't be able to immediately see its purpose.|||actually, now that i take a closer look, dajm probablu just needs IIF

i think he/she is using access, based on Pivottable and ##VALUE, and simply posted in the wrong forum

also knowing CASE isn't that bad an idea, though, eh

Ignoring expressions

I have an write an expression which let me select a value if the condition is
met, but if not then the expression should be ignored.
i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
The 0 here is supposed to ignore the expression, but it does not. What can I
use to ignore the expression.
Any help will be appriciated.Where is this expression, in a filter?
"Sumi" wrote:
> I have an write an expression which let me select a value if the condition is
> met, but if not then the expression should be ignored.
> i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
> The 0 here is supposed to ignore the expression, but it does not. What can I
> use to ignore the expression.
> Any help will be appriciated.|||Yes. This expression is in a filter.
"Antoon" wrote:
> Where is this expression, in a filter?
> "Sumi" wrote:
> > I have an write an expression which let me select a value if the condition is
> > met, but if not then the expression should be ignored.
> > i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
> >
> > The 0 here is supposed to ignore the expression, but it does not. What can I
> > use to ignore the expression.
> >
> > Any help will be appriciated.|||Did you try 1 instead of 0
--
"Everyone knows something you don't know"
"Sumi" wrote:
> Yes. This expression is in a filter.
> "Antoon" wrote:
> > Where is this expression, in a filter?
> >
> > "Sumi" wrote:
> >
> > > I have an write an expression which let me select a value if the condition is
> > > met, but if not then the expression should be ignored.
> > > i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
> > >
> > > The 0 here is supposed to ignore the expression, but it does not. What can I
> > > use to ignore the expression.
> > >
> > > Any help will be appriciated.|||Filter expression
switch(Parameter!Industry.value <> '_ALL_' and Parameter!Industry.value =Industry.value, 1, true, 0)
Filter value
=1
in this expression "Industry.value" is your query value that carresponds to
you parameter
"Sumi" wrote:
> I have an write an expression which let me select a value if the condition is
> met, but if not then the expression should be ignored.
> i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
> The 0 here is supposed to ignore the expression, but it does not. What can I
> use to ignore the expression.
> Any help will be appriciated.