image datatype - get data out and create insert statement

I am needing to get the data out of a table that has an image datatype, and create an insert statement so that the data can be inserted into other databases(to be used as part of an upgrade script).

I have used the procedure InsertGenerator in the past to create insert statements out of tables that have data. This is the first time I have come across the image datatype. The image part of the procedure looks like this: (@.dataType, @.colName, and @.stringData are nvarchar)

IF @.dataType='image'
BEGIN
SET @.stringData=@.stringData+'''''''''+
isnull(cast(convert(varbinary,'+@.colName+')
as varchar(6)),''0'')+'''''',''+'
END

It successfully creates the insert statement. The insert statement runs successfully, but I am not sure if the image itself is ok. When I use the related application, it crashes and I cannot tell if it is because of the image. When I run a sql trace the last statement ran is pulling back the image as well as a few other columns. When I run the trace statement in query analyzer, it works, but in query analyzer it isnt displaying the image.

My question is will the above statement successfully convert an image to a value that can be inserted into another table and the image recreated when you run the application? Is there something else I need to do to get the image value in a table out so that it can be used in an insert statment.

BTW this is on SQL 2000.

Thank you so much,

Tracey

This thread (found by just looking down the message list without having to even change pages -WOW), may give you the information you desire.

Using a stored procedure to reinsert an image

|||

Perhaps I am not understanding, I am using a script to create and insert statement an example is below. The FileBinary column is the image. I cannot tell if this is correctly extracted as am image. The insert is successful when ran in query analyzer, but the image doesnt display in the application, the application crashes, so I cant tell if it is the image or not.
Creating an insert statement to the same database is not what Im trying to do. I am trying to create an insert statement that can be ran on other like databases not on the network, that are needing this piece of information for the latest version of our application.

insert into JTFile (FileID, Path, Name, Extension, Type, Description, HashValue, Seed, RecordActive,FileBinary) values('0588dbda3f4d483d84259626c251f072','','','.rpt','','test','','2055291722',1,'??')

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
> > > > >

IIS and SQL Server Persmission Issue

IIS and SQL Server Persmission Issue
I am getting an error trying to run a SQL Server SELECT statement from an
ASP Application.
I am learning ASP/IIS/SQL Server by writing a small ASP app in Dreamweaver.
I've created and tested the ODBC connection just fine. And when I create the
connection in Dreamweaver and run the query it works just fine. However when
I try to access the web page I get:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
/webprodmx/categories.asp, line 9
Code is:
Dim MM_webprodmx_STRING
MM_webprodmx_STRING = "dsn=DSNwebprodmxSQL;uid=IUSR_ATBSLAPTOP;
Set rsCategories = Server.CreateObject("ADODB.Recordset")
9: rsCategories.ActiveConnection = MM_webprodmx_STRING
rsCategories.Source = "SELECT * FROM dbo.categories ORDER BY category ASC"
rsCategories.CursorType = 0
The DSN is defined and working (testing outside of dreamweaver, via setup
directly). The database and table exist and have data present. Like I said
it works everyplace else except when going through IIS. I have read some of
the MS Support articles and made sure I am accessing my machine via (local)
so there is no network access. Everything is running on my single local
machine - even IIS and SQL Server 2000.
Any suggestions would be appreciated.
"Patrick24601" <patrick24601@.yahoo.com> wrote in message
news:ePPTc.4368$wu.1124@.okepread04...
> IIS and SQL Server Persmission Issue
> I am getting an error trying to run a SQL Server SELECT statement from an
> ASP Application.
> I am learning ASP/IIS/SQL Server by writing a small ASP app in
Dreamweaver.
> I've created and tested the ODBC connection just fine. And when I create
the
> connection in Dreamweaver and run the query it works just fine. However
when
> I try to access the web page I get:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
> /webprodmx/categories.asp, line 9
Your DSN is set up to use integrated authentication. Change that or give
'ATBSLAPTOP\IUSR_ATBSLAPTOP' rights to connect you your database. It works
outside of IIS because then it's you, not 'ATBSLAPTOP\IUSR_ATBSLAPTOP'
connecting to the database.
David
|||Thanks David.
I've gone in and check and that user has SELECT/INSERT/DELETE permissions on
all of the needed tables.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23mp8$gwgEHA.384@.TK2MSFTNGP10.phx.gbl...
> "Patrick24601" <patrick24601@.yahoo.com> wrote in message
> news:ePPTc.4368$wu.1124@.okepread04...
> Dreamweaver.
> the
> when
> Your DSN is set up to use integrated authentication. Change that or give
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP' rights to connect you your database. It
> works
> outside of IIS because then it's you, not 'ATBSLAPTOP\IUSR_ATBSLAPTOP'
> connecting to the database.
> David
>
|||Thanks all for your responses on this.
What I ended up doing (although maybe not the best solution) is to create an
explicitly new userid on the SQL server and use that for everything.
Patrick
"Patrick24601" <patrick24601@.yahoo.com> wrote in message
news:ePPTc.4368$wu.1124@.okepread04...
> IIS and SQL Server Persmission Issue
> I am getting an error trying to run a SQL Server SELECT statement from an
> ASP Application.
> I am learning ASP/IIS/SQL Server by writing a small ASP app in
> Dreamweaver. I've created and tested the ODBC connection just fine. And
> when I create the connection in Dreamweaver and run the query it works
> just fine. However when I try to access the web page I get:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
> /webprodmx/categories.asp, line 9
> Code is:
> Dim MM_webprodmx_STRING
> MM_webprodmx_STRING = "dsn=DSNwebprodmxSQL;uid=IUSR_ATBSLAPTOP;
> Set rsCategories = Server.CreateObject("ADODB.Recordset")
> 9: rsCategories.ActiveConnection = MM_webprodmx_STRING
> rsCategories.Source = "SELECT * FROM dbo.categories ORDER BY category ASC"
> rsCategories.CursorType = 0
> The DSN is defined and working (testing outside of dreamweaver, via setup
> directly). The database and table exist and have data present. Like I said
> it works everyplace else except when going through IIS. I have read some
> of the MS Support articles and made sure I am accessing my machine via
> (local) so there is no network access. Everything is running on my single
> local machine - even IIS and SQL Server 2000.
> Any suggestions would be appreciated.
>

IIS and SQL Server Persmission Issue

IIS and SQL Server Persmission Issue
I am getting an error trying to run a SQL Server SELECT statement from an
ASP Application.
I am learning ASP/IIS/SQL Server by writing a small ASP app in Dreamweaver.
I've created and tested the ODBC connection just fine. And when I create the
connection in Dreamweaver and run the query it works just fine. However when
I try to access the web page I get:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
user
'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
/webprodmx/categories.asp, line 9
Code is:
Dim MM_webprodmx_STRING
MM_webprodmx_STRING = " dsn=DSNwebprodmxSQL;uid=IUSR_ATBSLAPTOP;
Set rsCategories = Server.CreateObject("ADODB.Recordset")
9: rsCategories.ActiveConnection = MM_webprodmx_STRING
rsCategories.Source = "SELECT * FROM dbo.categories ORDER BY category ASC"
rsCategories.CursorType = 0
The DSN is defined and working (testing outside of dreamweaver, via setup
directly). The database and table exist and have data present. Like I said
it works everyplace else except when going through IIS. I have read some of
the MS Support articles and made sure I am accessing my machine via (local)
so there is no network access. Everything is running on my single local
machine - even IIS and SQL Server 2000.
Any suggestions would be appreciated."Patrick24601" <patrick24601@.yahoo.com> wrote in message
news:ePPTc.4368$wu.1124@.okepread04...
> IIS and SQL Server Persmission Issue
> I am getting an error trying to run a SQL Server SELECT statement from an
> ASP Application.
> I am learning ASP/IIS/SQL Server by writing a small ASP app in
Dreamweaver.
> I've created and tested the ODBC connection just fine. And when I create
the
> connection in Dreamweaver and run the query it works just fine. However
when
> I try to access the web page I get:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed fo
r user
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
> /webprodmx/categories.asp, line 9
Your DSN is set up to use integrated authentication. Change that or give
'ATBSLAPTOP\IUSR_ATBSLAPTOP' rights to connect you your database. It works
outside of IIS because then it's you, not 'ATBSLAPTOP\IUSR_ATBSLAPTOP'
connecting to the database.
David|||Thanks David.
I've gone in and check and that user has SELECT/INSERT/DELETE permissions on
all of the needed tables.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23mp8$gwgEHA.384@.TK2MSFTNGP10.phx.gbl...
> "Patrick24601" <patrick24601@.yahoo.com> wrote in message
> news:ePPTc.4368$wu.1124@.okepread04...
> Dreamweaver.
> the
> when
> Your DSN is set up to use integrated authentication. Change that or give
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP' rights to connect you your database. It
> works
> outside of IIS because then it's you, not 'ATBSLAPTOP\IUSR_ATBSLAPTOP'
> connecting to the database.
> David
>|||Thanks all for your responses on this.
What I ended up doing (although maybe not the best solution) is to create an
explicitly new userid on the SQL server and use that for everything.
Patrick
"Patrick24601" <patrick24601@.yahoo.com> wrote in message
news:ePPTc.4368$wu.1124@.okepread04...
> IIS and SQL Server Persmission Issue
> I am getting an error trying to run a SQL Server SELECT statement from an
> ASP Application.
> I am learning ASP/IIS/SQL Server by writing a small ASP app in
> Dreamweaver. I've created and tested the ODBC connection just fine. And
> when I create the connection in Dreamweaver and run the query it works
> just fine. However when I try to access the web page I get:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed fo
r user
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
> /webprodmx/categories.asp, line 9
> Code is:
> Dim MM_webprodmx_STRING
> MM_webprodmx_STRING = " dsn=DSNwebprodmxSQL;uid=IUSR_ATBSLAPTOP;
> Set rsCategories = Server.CreateObject("ADODB.Recordset")
> 9: rsCategories.ActiveConnection = MM_webprodmx_STRING
> rsCategories.Source = "SELECT * FROM dbo.categories ORDER BY category ASC"
> rsCategories.CursorType = 0
> The DSN is defined and working (testing outside of dreamweaver, via setup
> directly). The database and table exist and have data present. Like I said
> it works everyplace else except when going through IIS. I have read some
> of the MS Support articles and made sure I am accessing my machine via
> (local) so there is no network access. Everything is running on my single
> local machine - even IIS and SQL Server 2000.
> Any suggestions would be appreciated.
>

IIS and SQL Server Persmission Issue

IIS and SQL Server Persmission Issue
I am getting an error trying to run a SQL Server SELECT statement from an
ASP Application.
I am learning ASP/IIS/SQL Server by writing a small ASP app in Dreamweaver.
I've created and tested the ODBC connection just fine. And when I create the
connection in Dreamweaver and run the query it works just fine. However when
I try to access the web page I get:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
/webprodmx/categories.asp, line 9
Code is:
Dim MM_webprodmx_STRING
MM_webprodmx_STRING = "dsn=DSNwebprodmxSQL;uid=IUSR_ATBSLAPTOP;
Set rsCategories = Server.CreateObject("ADODB.Recordset")
9: rsCategories.ActiveConnection = MM_webprodmx_STRING
rsCategories.Source = "SELECT * FROM dbo.categories ORDER BY category ASC"
rsCategories.CursorType = 0
The DSN is defined and working (testing outside of dreamweaver, via setup
directly). The database and table exist and have data present. Like I said
it works everyplace else except when going through IIS. I have read some of
the MS Support articles and made sure I am accessing my machine via (local)
so there is no network access. Everything is running on my single local
machine - even IIS and SQL Server 2000.
Any suggestions would be appreciated."Patrick24601" <patrick24601@.yahoo.com> wrote in message
news:ePPTc.4368$wu.1124@.okepread04...
> IIS and SQL Server Persmission Issue
> I am getting an error trying to run a SQL Server SELECT statement from an
> ASP Application.
> I am learning ASP/IIS/SQL Server by writing a small ASP app in
Dreamweaver.
> I've created and tested the ODBC connection just fine. And when I create
the
> connection in Dreamweaver and run the query it works just fine. However
when
> I try to access the web page I get:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
> /webprodmx/categories.asp, line 9
Your DSN is set up to use integrated authentication. Change that or give
'ATBSLAPTOP\IUSR_ATBSLAPTOP' rights to connect you your database. It works
outside of IIS because then it's you, not 'ATBSLAPTOP\IUSR_ATBSLAPTOP'
connecting to the database.
David|||Thanks David.
I've gone in and check and that user has SELECT/INSERT/DELETE permissions on
all of the needed tables.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23mp8$gwgEHA.384@.TK2MSFTNGP10.phx.gbl...
> "Patrick24601" <patrick24601@.yahoo.com> wrote in message
> news:ePPTc.4368$wu.1124@.okepread04...
>> IIS and SQL Server Persmission Issue
>> I am getting an error trying to run a SQL Server SELECT statement from an
>> ASP Application.
>> I am learning ASP/IIS/SQL Server by writing a small ASP app in
> Dreamweaver.
>> I've created and tested the ODBC connection just fine. And when I create
> the
>> connection in Dreamweaver and run the query it works just fine. However
> when
>> I try to access the web page I get:
>> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
>> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
>> /webprodmx/categories.asp, line 9
> Your DSN is set up to use integrated authentication. Change that or give
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP' rights to connect you your database. It
> works
> outside of IIS because then it's you, not 'ATBSLAPTOP\IUSR_ATBSLAPTOP'
> connecting to the database.
> David
>|||Thanks all for your responses on this.
What I ended up doing (although maybe not the best solution) is to create an
explicitly new userid on the SQL server and use that for everything.
Patrick
"Patrick24601" <patrick24601@.yahoo.com> wrote in message
news:ePPTc.4368$wu.1124@.okepread04...
> IIS and SQL Server Persmission Issue
> I am getting an error trying to run a SQL Server SELECT statement from an
> ASP Application.
> I am learning ASP/IIS/SQL Server by writing a small ASP app in
> Dreamweaver. I've created and tested the ODBC connection just fine. And
> when I create the connection in Dreamweaver and run the query it works
> just fine. However when I try to access the web page I get:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
> /webprodmx/categories.asp, line 9
> Code is:
> Dim MM_webprodmx_STRING
> MM_webprodmx_STRING = "dsn=DSNwebprodmxSQL;uid=IUSR_ATBSLAPTOP;
> Set rsCategories = Server.CreateObject("ADODB.Recordset")
> 9: rsCategories.ActiveConnection = MM_webprodmx_STRING
> rsCategories.Source = "SELECT * FROM dbo.categories ORDER BY category ASC"
> rsCategories.CursorType = 0
> The DSN is defined and working (testing outside of dreamweaver, via setup
> directly). The database and table exist and have data present. Like I said
> it works everyplace else except when going through IIS. I have read some
> of the MS Support articles and made sure I am accessing my machine via
> (local) so there is no network access. Everything is running on my single
> local machine - even IIS and SQL Server 2000.
> Any suggestions would be appreciated.
>

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 Statements in Reporting Services

Afternoon All,
I'm working on a report and I'm trying to include an IIF Statement
since there's a possiblity that I could get a division by zero error.
Here is my calculation:
=IIF( Fields!acdcalls.Value = 0, #0:00:00#, (Fields!anstime.Value \
Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
Fields!acdcalls.Value)\60) Mod 60,"\:00") &
Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
Also tried:
=IIF( Fields!acdcalls.Value = 0, "0:00:00", (Fields!anstime.Value \
Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
Fields!acdcalls.Value)\60) Mod 60,"\:00") &
Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
This seems to work if the calculation is not as complex but doesn't
like this one at all.
I would appreciate any suggestions.
Thanks,
JodyFor complicated expressions I would do such calculations in custom code.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jody Baldwin" <jody.baldwin@.gmail.com> wrote in message
news:1137705771.369654.288710@.f14g2000cwb.googlegroups.com...
> Afternoon All,
> I'm working on a report and I'm trying to include an IIF Statement
> since there's a possiblity that I could get a division by zero error.
> Here is my calculation:
> =IIF( Fields!acdcalls.Value = 0, #0:00:00#, (Fields!anstime.Value \
> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
> Also tried:
> =IIF( Fields!acdcalls.Value = 0, "0:00:00", (Fields!anstime.Value \
> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
> This seems to work if the calculation is not as complex but doesn't
> like this one at all.
> I would appreciate any suggestions.
> Thanks,
> Jody
>|||Reporting services equates both sides of an if before it execute it there
for
=IIF( 1=0,0,10/0)
will give an error therefore the only solution I have found is to use custom
code as suggested by the previous poster.
Thanks
Dale
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:O7Rw3sWHGHA.2040@.TK2MSFTNGP14.phx.gbl...
> For complicated expressions I would do such calculations in custom code.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jody Baldwin" <jody.baldwin@.gmail.com> wrote in message
> news:1137705771.369654.288710@.f14g2000cwb.googlegroups.com...
>> Afternoon All,
>> I'm working on a report and I'm trying to include an IIF Statement
>> since there's a possiblity that I could get a division by zero error.
>> Here is my calculation:
>> =IIF( Fields!acdcalls.Value = 0, #0:00:00#, (Fields!anstime.Value \
>> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
>> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
>> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
>> Also tried:
>> =IIF( Fields!acdcalls.Value = 0, "0:00:00", (Fields!anstime.Value \
>> Fields!acdcalls.Value) \3600 & Format(((Fields!anstime.Value \
>> Fields!acdcalls.Value)\60) Mod 60,"\:00") &
>> Format((Fields!anstime.Value \ Fields!acdcalls.Value) Mod 60,"\:00"))
>> This seems to work if the calculation is not as complex but doesn't
>> like this one at all.
>> I would appreciate any suggestions.
>> Thanks,
>> Jody
>|||Thanks for the help... I created a custom function that fixed my
issues... Here is my code in case it can help someone else down the
road.
Public Function ConvertSecToTime(ByVal NumSec As Double, ByVal Calls As
Double) As String
Dim theTime As String
If NumSec = 0 Or Calls = 0 Then
theTime = "0:00:00"
Else
theTime = (NumSec \ Calls) \ 3600 & Format(((NumSec \ Calls) \ 60) Mod
60, "\:00") & Format((NumSec \ Calls) Mod 60, "\:00")
End If

IIF Statements

On Apr 24, 8:42 am, RSub <R...@.discussions.microsoft.com> wrote:
> Hi All,
> The below IIF statement is not working for me.
> =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> "Address")
>
> Could you please let me know where I am going wrong. I tried several other
> options such as writing custom code, switch, choose statements..I am
> migrating the report from crystal reports to Reporting services. Instead of
> the format in the above expression it was AddressLine1 in Crystal reports
> that had worked fine. Also does anybody know of an alternative for the
> NameFlip function of crystal rpts to use in Reporting svcs'
>
> Thanks in advance,
> RS
That IIF() call is pretty complex - is it possible for you to put some
of this logic in the database layer (e.g. by calling a view)?Thank you for your reply. I actually removed the variable "Address" and the
OR from the IIF statement and it is working fine now. Looks like IIF doesn't
work well with variables and those logical operators.
"Tokes" wrote:
> On Apr 24, 8:42 am, RSub <R...@.discussions.microsoft.com> wrote:
> > Hi All,
> > The below IIF statement is not working for me.
> > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> > "Address")
> >
> > Could you please let me know where I am going wrong. I tried several other
> > options such as writing custom code, switch, choose statements..I am
> > migrating the report from crystal reports to Reporting services. Instead of
> > the format in the above expression it was AddressLine1 in Crystal reports
> > that had worked fine. Also does anybody know of an alternative for the
> > NameFlip function of crystal rpts to use in Reporting svcs'
> >
> > Thanks in advance,
> > RS
> That IIF() call is pretty complex - is it possible for you to put some
> of this logic in the database layer (e.g. by calling a view)?
>|||IIF() works well "with logical operators and variables", FWIW.
So, here's a guess about why it didn't work, without reading your expression
very closely:
Assuming there was no actual error on your part, it's possible that Crystal
Reports interpreted the segments of your expression in a different order
than RS is doing. (Different compilers are like that <g>.)
To resolve this you can usually add some nested parentheses to make sure
that the order of evaluation is exactly what you expect, explicitly defined,
even though you got this order by default in your old environment.
However... a piece of advice: if you find yourself writing something like
this you may find it worth your while to write a little VB custom function
instead (embed it in the report) and then invoke the function
(=Code.MyFunc()) rather than writing the expression correctly. It's a lot
easier to read and maintain.
Also, you asked a second question about NameFlip... Does this flip two
values based on the appearance of a comma or something? I'm just guessing by
the name, but if so, something like this should work for you:
Function NameFlip(ByVal LastFirst As String) As String
Dim Result As String, Results As String()
Results = LastFirst.Split(",")
If Results.Length = 2 Then
Result = Results(1).Trim() & " " & Results(0).Trim()
Else
' don't make any assumptions if there are
' no commas or more than one comma
Result = LastFirst
End If
Results = Nothing
Return Result
End Function
If I guessed wrong, ask again, and I'll try to write something appropriate
<s>.
Hope this helps,
>L<
"RSub" <RSub@.discussions.microsoft.com> wrote in message
news:EDEAAEF8-8CD1-4518-9AE3-56FE9E714C50@.microsoft.com...
> Thank you for your reply. I actually removed the variable "Address" and
> the
> OR from the IIF statement and it is working fine now. Looks like IIF
> doesn't
> work well with variables and those logical operators.
> "Tokes" wrote:
>> On Apr 24, 8:42 am, RSub <R...@.discussions.microsoft.com> wrote:
>> > Hi All,
>> > The below IIF statement is not working for me.
>> > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
>> > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " "
>> > &
>> > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value &
>> > " " &
>> > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
>> > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " &
>> > Fields!State_2.Value
>> > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value),
>> > Trim("Address")
>> > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United
>> > States",
>> > "Address")
>> >
>> > Could you please let me know where I am going wrong. I tried several
>> > other
>> > options such as writing custom code, switch, choose statements..I am
>> > migrating the report from crystal reports to Reporting services.
>> > Instead of
>> > the format in the above expression it was AddressLine1 in Crystal
>> > reports
>> > that had worked fine. Also does anybody know of an alternative for the
>> > NameFlip function of crystal rpts to use in Reporting svcs'
>> >
>> > Thanks in advance,
>> > RS
>> That IIF() call is pretty complex - is it possible for you to put some
>> of this logic in the database layer (e.g. by calling a view)?
>>

IIF Statements

Hi All,
The below IIF statement is not working for me.
=IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
"Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
& " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
<> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
"Address")
Could you please let me know where I am going wrong. I tried several other
options such as writing custom code, switch, choose statements..I am
migrating the report from crystal reports to Reporting services. Instead of
the format in the above expression it was AddressLine1 in Crystal reports
that had worked fine. Also does anybody know of an alternative for the
NameFlip function of crystal rpts to use in Reporting svcs'
Thanks in advance,
RSAfter seeing the full syntax I think you have to use some more "iif's " in
between before "Address:" , if you can explain in plain language what exactly
you are trying to display. ie something like if the first conditions is true
then what and if false then what...
Amarnath
"RSub" wrote:
> Hi All,
> The below IIF statement is not working for me.
> =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> "Address")
> Could you please let me know where I am going wrong. I tried several other
> options such as writing custom code, switch, choose statements..I am
> migrating the report from crystal reports to Reporting services. Instead of
> the format in the above expression it was AddressLine1 in Crystal reports
> that had worked fine. Also does anybody know of an alternative for the
> NameFlip function of crystal rpts to use in Reporting svcs'
> Thanks in advance,
> RS|||Hi Amarnath,
My report uses a SQL query which is very complex and it has joins from
several diff tables. I was trying to add a calculated field(embedded) to the
data source and I need that to display the address which is addressline1, 2,
city state, zip etc based on some criteria which is the first part of the IIF
statement. I removed the variable Address and the OR and it is working fine.
The latter false part of the statement needs to remove US if it finds it in
the address and not display in the report. I'm trying to make that work now.
Thanks,
Roopa
"Amarnath" wrote:
> After seeing the full syntax I think you have to use some more "iif's " in
> between before "Address:" , if you can explain in plain language what exactly
> you are trying to display. ie something like if the first conditions is true
> then what and if false then what...
> Amarnath
>
> "RSub" wrote:
> > Hi All,
> > The below IIF statement is not working for me.
> > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> > "Address")
> >
> > Could you please let me know where I am going wrong. I tried several other
> > options such as writing custom code, switch, choose statements..I am
> > migrating the report from crystal reports to Reporting services. Instead of
> > the format in the above expression it was AddressLine1 in Crystal reports
> > that had worked fine. Also does anybody know of an alternative for the
> > NameFlip function of crystal rpts to use in Reporting svcs'
> >
> > Thanks in advance,
> > RS|||ok, so infact you can nest the iif as well, to get the desired results.
Amarnath
"RSub" wrote:
> Hi Amarnath,
> My report uses a SQL query which is very complex and it has joins from
> several diff tables. I was trying to add a calculated field(embedded) to the
> data source and I need that to display the address which is addressline1, 2,
> city state, zip etc based on some criteria which is the first part of the IIF
> statement. I removed the variable Address and the OR and it is working fine.
> The latter false part of the statement needs to remove US if it finds it in
> the address and not display in the report. I'm trying to make that work now.
> Thanks,
> Roopa
>
> "Amarnath" wrote:
> > After seeing the full syntax I think you have to use some more "iif's " in
> > between before "Address:" , if you can explain in plain language what exactly
> > you are trying to display. ie something like if the first conditions is true
> > then what and if false then what...
> >
> > Amarnath
> >
> >
> > "RSub" wrote:
> >
> > > Hi All,
> > > The below IIF statement is not working for me.
> > > =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> > > "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> > > Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> > > Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> > > Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> > > & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> > > <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> > > "Address")
> > >
> > > Could you please let me know where I am going wrong. I tried several other
> > > options such as writing custom code, switch, choose statements..I am
> > > migrating the report from crystal reports to Reporting services. Instead of
> > > the format in the above expression it was AddressLine1 in Crystal reports
> > > that had worked fine. Also does anybody know of an alternative for the
> > > NameFlip function of crystal rpts to use in Reporting svcs'
> > >
> > > Thanks in advance,
> > > RS|||Your first problem is that the IIf currently contains four parameters:
1: Trim(Fields!BillType.Value) = "IN" AND (Fields!User9.Value)= 1
2: "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value &
" " & Fields!City.Value & " " & Fields!State.Value & " " & Fields!
Zip.Value & " " & Fields!Country.Value) OR Format(Fields!Addr1_2.Value
& " " & Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " &
Fields!State_2.Value & " " & Fields!Zip_2.Value & " " & Fields!
Country_2.Value)
3: Trim("Address") <> "US" and Trim("Address") <> "USA" and
Trim("Address") <> "United States"
4: "Address"
Second, parameter 2 is altogether meaningless for several reasons:
* "Address:" = Format(... is testing if the result of your format
statement matches the string "Address:", which it almost certainly
won't.
* Format() takes two parameters, the object and the format type, and
you only pass one parameter each time.
* Format() is generally used to convert numbers, dates, etc to a
string: for example, Format(1.5, "C") returns $1.50 in the US. You
probably don't even need it for the addresses you're putting together.
* OR operates on two boolean values. Though you have one boolean value
from the "Address:" = Format(... comparison (by accident, I suspect),
I don't see what you're hoping to accomplish with the statement.
And third, parameter 3 will always return true - Trim("Address") will
always return "Address" which will never match the variations on "US".
On Apr 23, 5:42 pm, RSub <R...@.discussions.microsoft.com> wrote:
> Hi All,
> The below IIF statement is not working for me.
> =IIf(Trim(Fields!BillType.Value)= "IN" AND (Fields!User9.Value)= 1,
> "Address:" = Format(Fields!Addr1.Value & " " & Fields!Addr2.Value & " " &
> Fields!City.Value & " " & Fields!State.Value & " " & Fields!Zip.Value & " " &
> Fields!Country.Value) OR Format(Fields!Addr1_2.Value & " " &
> Fields!Addr2_2.Value & " " & Fields!City_2.Value & " " & Fields!State_2.Value
> & " " & Fields!Zip_2.Value & " " & Fields!Country_2.Value), Trim("Address")
> <> "US" and Trim("Address") <> "USA" and Trim("Address") <> "United States",
> "Address")
> Could you please let me know where I am going wrong. I tried several other
> options such as writing custom code, switch, choose statements..I am
> migrating the report from crystal reports to Reporting services. Instead of
> the format in the above expression it was AddressLine1 in Crystal reports
> that had worked fine. Also does anybody know of an alternative for the
> NameFlip function of crystal rpts to use in Reporting svcs'
> Thanks in advance,
> RS

IIF Statements

Hi All,

I have a normal IIF statement that controls what text i see in a text box depending on the returned value from the database. This is fine and all is working well.

My question is: Is it possible to have say the first line of the text in bold and a different size to that of the first bit of data?

=IIF(Fields!Code1.Value = "Developed","Developed: " & First(Fields!DevelopedText.Value, "ResourceTexts"),Fields!Code1.Value)

My example above shows this but what i want is the word Developed: to be bold and a different sizer to what follows. is this possible? I'm thinking maybe i have to insert a bold tag maybe?

Any help would be greatly appreciated.

Ta

Dave

No, different formats in the control is not supported. RTF support will be eventually added in later versions.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||http://www.sqlservercentral.com/columnists/bknight/reportingservicesconditionalformatting.asp

IIF Statement with Date checking

Hello, I am using the following to try to find date ranges and do some work.
Here is the statement
=iif(fields!mbr_join_date.value = "7/01/2003", mbt_code, "Not Grandfathered")
I have looked at the query result and I see alot of dates that are this date
but it's not putting the MBT_Code in for the value. Anyone have any
suggestions. Should I convert the Date in the Query to a different format
currently it shows as yyyy-mm-dd 00:00:00 should I trim off the time or
convert it to a string value?
Thank you in advanceScrocker,
I'm not 100% sure if your doing this in the SQL or in a cell on the
report. If its in the report you need to write it like:
iif(fields!mbr_join_date.value = "7/01/2003", FIELDS!mbt_cod.VALUE, "Not
Grandfathered")
You can't reference the field name in a control without usin gthe FIELDS!
collection.
If its in your SQL, well, IIF's dont' work, so I'm thinking your not doing
it, but if you are then use a CASE statement
CASE WHEN mbr_join_date = '7/01/2003' THEN mbr_code ELSE 'Not Grandfathered'
END as mbr_code
Michael C
"scrocker" wrote:
> Hello, I am using the following to try to find date ranges and do some work.
> Here is the statement
> =iif(fields!mbr_join_date.value = "7/01/2003", mbt_code, "Not Grandfathered")
> I have looked at the query result and I see alot of dates that are this date
> but it's not putting the MBT_Code in for the value. Anyone have any
> suggestions. Should I convert the Date in the Query to a different format
> currently it shows as yyyy-mm-dd 00:00:00 should I trim off the time or
> convert it to a string value?
> Thank you in advance
>|||Thanks Michael, I will give that a shot, Yes it's in the report not in SQL
Michael C wrote:
>Scrocker,
> I'm not 100% sure if your doing this in the SQL or in a cell on the
>report. If its in the report you need to write it like:
>iif(fields!mbr_join_date.value = "7/01/2003", FIELDS!mbt_cod.VALUE, "Not
>Grandfathered")
>You can't reference the field name in a control without usin gthe FIELDS!
>collection.
>If its in your SQL, well, IIF's dont' work, so I'm thinking your not doing
>it, but if you are then use a CASE statement
>CASE WHEN mbr_join_date = '7/01/2003' THEN mbr_code ELSE 'Not Grandfathered'
>END as mbr_code
>Michael C
>> Hello, I am using the following to try to find date ranges and do some work.
>> Here is the statement
>[quoted text clipped - 7 lines]
>> convert it to a string value?
>> Thank you in advance|||You might try:
=iif(fields!mbr_join_date.value < "7/02/2003", mbt_code, "Not
Grandfathered")
Adding one day to the date value - you might have to specify the date
as
"2003-07-02 00:00:00" (yyyy-mm-dd 00:00:00 format)
to ensure you are setting the date/time to the very start of the
following day.

Iif statement to prevent divide by zero?

Hi- I'm trying to create a calculated field that is the percentage difference
between two database fields. To prevent a divide by zero, I tried making it:
= Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
This should provide the % diff, or in the case that dsPrice is 0, 1 (100%).
When I try to run the report, however, it comes back as a divide by zero for
fields where dsPrice = 0. Does reporting services evaluate both portions of
the Iif, then output one? How do I avoid this divide by zero error?
Thanks in advance!
Peter L.iif always evaluates both sides. try using the short circuit operator
'andalso' or 'orelse' in a function and add it to the code and call it from
the expression.|||If the correct zero value is 100%, you can just move the pieces around like
this:
= Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
That way, the division doesn't happen at all until the values are replaced.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
> Hi- I'm trying to create a calculated field that is the percentage
> difference
> between two database fields. To prevent a divide by zero, I tried making
> it:
> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
> This should provide the % diff, or in the case that dsPrice is 0, 1
> (100%).
> When I try to run the report, however, it comes back as a divide by zero
> for
> fields where dsPrice = 0. Does reporting services evaluate both portions
> of
> the Iif, then output one? How do I avoid this divide by zero error?
> Thanks in advance!
> Peter L.|||Whoops, I think that should have been more like this:
= Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
Iif(Fields!dsPrice.Value = 0, 1, Fields!dsPrice.Value)
Anyway, you get the idea!! :-)
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:eGkBJLo8EHA.1264@.TK2MSFTNGP12.phx.gbl...
> If the correct zero value is 100%, you can just move the pieces around
> like this:
> = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
> That way, the division doesn't happen at all until the values are
> replaced.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "plandry@.newsgroups.nospam"
> <plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
> news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
>> Hi- I'm trying to create a calculated field that is the percentage
>> difference
>> between two database fields. To prevent a divide by zero, I tried making
>> it:
>> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
>> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
>> This should provide the % diff, or in the case that dsPrice is 0, 1
>> (100%).
>> When I try to run the report, however, it comes back as a divide by zero
>> for
>> fields where dsPrice = 0. Does reporting services evaluate both portions
>> of
>> the Iif, then output one? How do I avoid this divide by zero error?
>> Thanks in advance!
>> Peter L.
>|||That did the trick... Thanks a bunch!
I will file that away in the "ninja reporting tricks" :)
"Jeff A. Stucker" wrote:
> Whoops, I think that should have been more like this:
> = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> Iif(Fields!dsPrice.Value = 0, 1, Fields!dsPrice.Value)
> Anyway, you get the idea!! :-)
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:eGkBJLo8EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > If the correct zero value is 100%, you can just move the pieces around
> > like this:
> >
> > = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> > Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
> >
> > That way, the division doesn't happen at all until the values are
> > replaced.
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "plandry@.newsgroups.nospam"
> > <plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
> > news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
> >> Hi- I'm trying to create a calculated field that is the percentage
> >> difference
> >> between two database fields. To prevent a divide by zero, I tried making
> >> it:
> >> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
> >> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
> >> This should provide the % diff, or in the case that dsPrice is 0, 1
> >> (100%).
> >> When I try to run the report, however, it comes back as a divide by zero
> >> for
> >> fields where dsPrice = 0. Does reporting services evaluate both portions
> >> of
> >> the Iif, then output one? How do I avoid this divide by zero error?
> >>
> >> Thanks in advance!
> >> Peter L.
> >
> >
>
>

IIF Statement to Case but getting error

I tried converting the statement below, which is just one of many statements
in a view. This one poplulates one column in the view:
IF(DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
COUNT([CUSTOMER__])>=5,YES,NO)
to:
CASE WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
COUNT([CUSTOMER__])>=5 THEN 'YES' ELSE 'NO'
I'm getting an error that says the query designer does not support the CASE
sql construct. Any thoughts on how I can rewrite the IIF statement so that i
t
can work in a sql view? THANKS!!Mike,
Where are you creating the view?. Use Query analyzer.
AMB
"Mike C" wrote:

> I tried converting the statement below, which is just one of many statemen
ts
> in a view. This one poplulates one column in the view:
> IF(DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5,YES,NO)
> to:
> CASE WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5 THEN 'YES' ELSE 'NO'
> I'm getting an error that says the query designer does not support the CAS
E
> sql construct. Any thoughts on how I can rewrite the IIF statement so that
it
> can work in a sql view? THANKS!!|||Mike C a écrit :
> I tried converting the statement below, which is just one of many statemen
ts
> in a view. This one poplulates one column in the view:
> IF(DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5,YES,NO)
> to:
> CASE WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE())<=30 AND
> COUNT([CUSTOMER__])>=5 THEN 'YES' ELSE 'NO'
END missing in CAS structure :
CASE
WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE()) <=30
AND COUNT([CUSTOMER__]) >= 5 THEN 'YES'
ELSE 'NO'
END as YesNoCol

> I'm getting an error that says the query designer does not support the CAS
E
> sql construct. Any thoughts on how I can rewrite the IIF statement so that
it
> can work in a sql view? THANKS!!
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Alejandro,
Thank you. That worked. The problem I'm left with is how to run this report
automatically. I've been using DTS to export a view to an Excel sheet but it
looks like that won't work in this case. I guess I could try to put this in
an sp (which I haven't done much of and should probably start mastering) and
either DTS the sp result or I could just throw the results in a web-based
datagrid and export the datagrid to Excel on demand. Do you have any
recommendations on how to make the query results available to users? Thanks
again for the earlier suggestion.
MC
"Alejandro Mesa" wrote:
> Mike,
> Where are you creating the view?. Use Query analyzer.
>
> AMB
> "Mike C" wrote:
>|||I actually had END in the view but I forgot to type it into my question.
"SQLpro [MVP]" wrote:

> Mike C a écrit :
> END missing in CAS structure :
>
> CASE
> WHEN DATEDIFF(dd, MAX(INVOICE_DA), GETDATE()) <=30
> AND COUNT([CUSTOMER__]) >= 5 THEN 'YES'
> ELSE 'NO'
> END as YesNoCol
>
> A +
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQ
L
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
>

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 statement problem

Hello,
I'm having a problem with the iif statement. Seems that the conditional
never evaluates to true,
=iif(CountRows() = 0, "Detailed Report", Fields!Client_Name.value & "
Detailed Report")
Basically I'm checking if the recordcount is 0, if so - display "Detailed
Report", if not - then display "<Client Name> Detail Report"
Even though the CountRows value = 0, which I checked in a test textbox, it
still never displays "Detail Report" - it just comes up blank...any idea
what the problem is? thanksIf Fields collection is used in NoRows situation whole expression evaluates
to null (preview gives warning)
The workaround is:
Create textbox and set its value to
=Fields!Client_Name.Value
and make it hidden
When refer to that textbox instead of field:
=iif(CountRows() = 0, "Detailed Report", ReportItems!<textboxname>.Value & "
Detailed Report")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"RS Developer" <idroppededabomb@.hotmail.com> wrote in message
news:%238g1U25gEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I'm having a problem with the iif statement. Seems that the conditional
> never evaluates to true,
> =iif(CountRows() = 0, "Detailed Report", Fields!Client_Name.value & "
> Detailed Report")
> Basically I'm checking if the recordcount is 0, if so - display "Detailed
> Report", if not - then display "<Client Name> Detail Report"
> Even though the CountRows value = 0, which I checked in a test textbox, it
> still never displays "Detail Report" - it just comes up blank...any idea
> what the problem is? thanks
>|||That worked like a charm, thank you.
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:eo1atc8gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> If Fields collection is used in NoRows situation whole expression
evaluates
> to null (preview gives warning)
> The workaround is:
> Create textbox and set its value to
> =Fields!Client_Name.Value
> and make it hidden
> When refer to that textbox instead of field:
> =iif(CountRows() = 0, "Detailed Report", ReportItems!<textboxname>.Value &
"
> Detailed Report")
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "RS Developer" <idroppededabomb@.hotmail.com> wrote in message
> news:%238g1U25gEHA.1652@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > I'm having a problem with the iif statement. Seems that the conditional
> > never evaluates to true,
> >
> > =iif(CountRows() = 0, "Detailed Report", Fields!Client_Name.value & "
> > Detailed Report")
> >
> > Basically I'm checking if the recordcount is 0, if so - display
"Detailed
> > Report", if not - then display "<Client Name> Detail Report"
> >
> > Even though the CountRows value = 0, which I checked in a test textbox,
it
> > still never displays "Detail Report" - it just comes up blank...any idea
> > what the problem is? thanks
> >
> >
>

IIF Statement on Select

Is there a way in an SQL Select statement to set up something similar to the
IIf command in Access:
Select LastName, IIf(CodeA = "Y","Yes'","No")
From NameTablermcompute,
Try using a "case" expression.
Select LastName, case when CodeA = 'Y' then 'Yes' else 'No' end as CodeA
From NameTable
AMB
"rmcompute" wrote:

> Is there a way in an SQL Select statement to set up something similar to t
he
> IIf command in Access:
> Select LastName, IIf(CodeA = "Y","Yes'","No")
> From NameTable|||There is no IIF in SQL Server, check out the CASE expression in Books
Online. Also, suggest you stay away from " as string delimiters.
SELECT LastName, CodeA = CASE CodeA WHEN 'Y' THEN 'Yes' ELSE 'No' END
FROM NameTable
Or
SELECT LastName, CodeA = CASE WHEN CodeA = 'Y' THEN 'Yes' ELSE 'No' END
FROM NameTable
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:2DA1A89A-17DA-4E9C-AF6F-AC37C2337277@.microsoft.com...
> Is there a way in an SQL Select statement to set up something similar to
> the
> IIf command in Access:
> Select LastName, IIf(CodeA = "Y","Yes'","No")
> From NameTable|||Thank you.
"Alejandro Mesa" wrote:
> rmcompute,
> Try using a "case" expression.
> Select LastName, case when CodeA = 'Y' then 'Yes' else 'No' end as CodeA
> From NameTable
>
> AMB
> "rmcompute" wrote:
>|||Thank you.
"Aaron Bertrand [SQL Server MVP]" wrote:

> There is no IIF in SQL Server, check out the CASE expression in Books
> Online. Also, suggest you stay away from " as string delimiters.
> SELECT LastName, CodeA = CASE CodeA WHEN 'Y' THEN 'Yes' ELSE 'No' END
> FROM NameTable
> Or
> SELECT LastName, CodeA = CASE WHEN CodeA = 'Y' THEN 'Yes' ELSE 'No' END
> FROM NameTable
>
> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:2DA1A89A-17DA-4E9C-AF6F-AC37C2337277@.microsoft.com...
>
>

IIF Statement Not Working

I've added the following IIF statement to a table in one of my reports, and
the second IIF statement does not ever evaluate to True. I don't see anything
wrong with the statement. Can anyone help?
=IIF(Fields!DisplayOrder.Value = 0, Fields!Total.Value,
IIF(Fields!Successful.Value = -1, "Successful",Fields!Successful.Value))Are you sure that DisplayOrder and Successful are numeric values? Did you
try this:
=IIF( CInt(Fields!DisplayOrder.Value) = 0, Fields!Total.Value, IIF(
CInt(Fields!Successful.Value) = -1, "Successful", Fields!Successful.Value))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Collins" <Mike Collins@.discussions.microsoft.com> wrote in message
news:CC480CA6-B5A7-45DD-BD2E-40F706CA654C@.microsoft.com...
> I've added the following IIF statement to a table in one of my reports,
and
> the second IIF statement does not ever evaluate to True. I don't see
anything
> wrong with the statement. Can anyone help?
> =IIF(Fields!DisplayOrder.Value = 0, Fields!Total.Value,
> IIF(Fields!Successful.Value = -1, "Successful",Fields!Successful.Value))|||I've confirmed that DisplayOrder and Successful are defined as integer. I
also tried your suggestion with the same results.
"Robert Bruckner [MSFT]" wrote:
> Are you sure that DisplayOrder and Successful are numeric values? Did you
> try this:
> =IIF( CInt(Fields!DisplayOrder.Value) = 0, Fields!Total.Value, IIF(
> CInt(Fields!Successful.Value) = -1, "Successful", Fields!Successful.Value))
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Mike Collins" <Mike Collins@.discussions.microsoft.com> wrote in message
> news:CC480CA6-B5A7-45DD-BD2E-40F706CA654C@.microsoft.com...
> > I've added the following IIF statement to a table in one of my reports,
> and
> > the second IIF statement does not ever evaluate to True. I don't see
> anything
> > wrong with the statement. Can anyone help?
> >
> > =IIF(Fields!DisplayOrder.Value = 0, Fields!Total.Value,
> > IIF(Fields!Successful.Value = -1, "Successful",Fields!Successful.Value))
>
>

IIF statement issue

Here is the iif statement I have in the report:
=iif ((Sum(Fields!EXTENDED_MARGIN.Value) = 0) or
(SUM(Fields!TY_QTY.Value)=0), 0,
SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value))
If I put in just the basic iif statment evaluation returning a 1 for true
and a 2 for false, it works fine. If I put in the expression I have for
false it will work fine unless one of the fields is a 0 and the other has a
number, then I get an error (which is understandable). I put them together
and I get the same thing as if I just put the false expression in, an error
if one number is a 0 and the other is an actual number.
Can anyone shed some light on this please?Not sure if there is another work around, however, I have run into this
problem many times and have solved it in several different ways. The probelm
that I see is that the IIF statement is calculating both the true and false
parts of the statement. Thus, you have a divide bt zero whenever
Fields!TY_Qty.Value = 0 even though you are trying to trap this condition in
the first part of the expression. Note that testing for
Fields!Extended_margin.value = 0 does not need to be done.
The first way to applies if you are using a stored proc to return the
dataset. If this is the case then you can in many cases return a field that
is already populated with
SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value);.taking care to
handle the division by zero potential in your TSQL code.
The other way that I have handled the problem is by writing a procedure that
takes the two numbers and returns zero if SUM(Fields!TY_QTY.Value) = 0 or
else SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value). Then instead
of using the IIF statement in an expression you call you procedure like this
=Code.MyProcedure( SUM(Fields!EXTENDED_MARGIN.Value),
SUM(Fields!TY_QTY.Value)).
Like I said, there may be other solutions but perhaps this will help.
"TBraun" wrote:
> Here is the iif statement I have in the report:
> =iif ((Sum(Fields!EXTENDED_MARGIN.Value) = 0) or
> (SUM(Fields!TY_QTY.Value)=0), 0,
> SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value))
> If I put in just the basic iif statment evaluation returning a 1 for true
> and a 2 for false, it works fine. If I put in the expression I have for
> false it will work fine unless one of the fields is a 0 and the other has a
> number, then I get an error (which is understandable). I put them together
> and I get the same thing as if I just put the false expression in, an error
> if one number is a 0 and the other is an actual number.
> Can anyone shed some light on this please?|||Mark,
Thanks. I used your second suggestion and wrote a custom function and that
worked just fine. Appreciate the help!
"B. Mark McKinney" wrote:
> Not sure if there is another work around, however, I have run into this
> problem many times and have solved it in several different ways. The probelm
> that I see is that the IIF statement is calculating both the true and false
> parts of the statement. Thus, you have a divide bt zero whenever
> Fields!TY_Qty.Value = 0 even though you are trying to trap this condition in
> the first part of the expression. Note that testing for
> Fields!Extended_margin.value = 0 does not need to be done.
> The first way to applies if you are using a stored proc to return the
> dataset. If this is the case then you can in many cases return a field that
> is already populated with
> SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value);.taking care to
> handle the division by zero potential in your TSQL code.
> The other way that I have handled the problem is by writing a procedure that
> takes the two numbers and returns zero if SUM(Fields!TY_QTY.Value) = 0 or
> else SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value). Then instead
> of using the IIF statement in an expression you call you procedure like this
> =Code.MyProcedure( SUM(Fields!EXTENDED_MARGIN.Value),
> SUM(Fields!TY_QTY.Value)).
> Like I said, there may be other solutions but perhaps this will help.
> "TBraun" wrote:
> > Here is the iif statement I have in the report:
> >
> > =iif ((Sum(Fields!EXTENDED_MARGIN.Value) = 0) or
> > (SUM(Fields!TY_QTY.Value)=0), 0,
> > SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value))
> >
> > If I put in just the basic iif statment evaluation returning a 1 for true
> > and a 2 for false, it works fine. If I put in the expression I have for
> > false it will work fine unless one of the fields is a 0 and the other has a
> > number, then I get an error (which is understandable). I put them together
> > and I get the same thing as if I just put the false expression in, an error
> > if one number is a 0 and the other is an actual number.
> >
> > Can anyone shed some light on this please?

IIF Statement Issue

Hi there.
There's an iif statement that I'm struggling with on Reporting Services.
This statement is on a calulated field. The iif statement is as follows:
=iif(x=0, 0, ((y-z)/x)*100).
In other words, if x is zero then 0 must be the output. Else the above
formula must be the output. The syntax is perfect, and so is the logic (i
think...). When I run the report it gives me a warning message that it cannot
"divide by zero". What is obviously happening is that x is zero, but instead
of displaying zero (as it is instructed to do so), it's going straight to the
formula!
Can anyone please tell me what's happening, and how I can make it work?
Thanks in advance.
(By the way, this has to be in Reporting Services and not sql)
--
Regards,You might have some cases where x is null which would not be getting handled
in your expression. add another iif test to check for nulls
"crmLearner" <crmLearner@.discussions.microsoft.com> wrote in message
news:1F6784D8-CBD8-494A-81DB-098E9E922CB4@.microsoft.com...
> Hi there.
> There's an iif statement that I'm struggling with on Reporting Services.
> This statement is on a calulated field. The iif statement is as follows:
> =iif(x=0, 0, ((y-z)/x)*100).
> In other words, if x is zero then 0 must be the output. Else the above
> formula must be the output. The syntax is perfect, and so is the logic (i
> think...). When I run the report it gives me a warning message that it
> cannot
> "divide by zero". What is obviously happening is that x is zero, but
> instead
> of displaying zero (as it is instructed to do so), it's going straight to
> the
> formula!
> Can anyone please tell me what's happening, and how I can make it work?
> Thanks in advance.
> (By the way, this has to be in Reporting Services and not sql)
> --
> Regards,|||Wow, i can't believe i didn't think of that...i'll give it a try.
Thanks!
--
Regards,
"Dean" wrote:
> You might have some cases where x is null which would not be getting handled
> in your expression. add another iif test to check for nulls
> "crmLearner" <crmLearner@.discussions.microsoft.com> wrote in message
> news:1F6784D8-CBD8-494A-81DB-098E9E922CB4@.microsoft.com...
> > Hi there.
> >
> > There's an iif statement that I'm struggling with on Reporting Services.
> > This statement is on a calulated field. The iif statement is as follows:
> > =iif(x=0, 0, ((y-z)/x)*100).
> >
> > In other words, if x is zero then 0 must be the output. Else the above
> > formula must be the output. The syntax is perfect, and so is the logic (i
> > think...). When I run the report it gives me a warning message that it
> > cannot
> > "divide by zero". What is obviously happening is that x is zero, but
> > instead
> > of displaying zero (as it is instructed to do so), it's going straight to
> > the
> > formula!
> >
> > Can anyone please tell me what's happening, and how I can make it work?
> > Thanks in advance.
> >
> > (By the way, this has to be in Reporting Services and not sql)
> > --
> > Regards,
>
>|||another thing to check for is empty strings, depending on how the data was
entered there may be "spaces" in the fields which are not 0 nor are they
null.
"crmLearner" <crmLearner@.discussions.microsoft.com> wrote in message
news:55531FF7-63F6-44C5-9F31-83A2C32B5BFA@.microsoft.com...
> Wow, i can't believe i didn't think of that...i'll give it a try.
> Thanks!
> --
> Regards,
>
> "Dean" wrote:
>> You might have some cases where x is null which would not be getting
>> handled
>> in your expression. add another iif test to check for nulls
>> "crmLearner" <crmLearner@.discussions.microsoft.com> wrote in message
>> news:1F6784D8-CBD8-494A-81DB-098E9E922CB4@.microsoft.com...
>> > Hi there.
>> >
>> > There's an iif statement that I'm struggling with on Reporting
>> > Services.
>> > This statement is on a calulated field. The iif statement is as
>> > follows:
>> > =iif(x=0, 0, ((y-z)/x)*100).
>> >
>> > In other words, if x is zero then 0 must be the output. Else the above
>> > formula must be the output. The syntax is perfect, and so is the logic
>> > (i
>> > think...). When I run the report it gives me a warning message that it
>> > cannot
>> > "divide by zero". What is obviously happening is that x is zero, but
>> > instead
>> > of displaying zero (as it is instructed to do so), it's going straight
>> > to
>> > the
>> > formula!
>> >
>> > Can anyone please tell me what's happening, and how I can make it work?
>> > Thanks in advance.
>> >
>> > (By the way, this has to be in Reporting Services and not sql)
>> > --
>> > Regards,
>>|||The parameters to the IIF function are all evaluated in order to call the
function. So in your case the CLR calculates three values to be passed as
parameters...
(x=0)
,(0)
,((y-z)/x)*100)
...and would then have internal logic that says if the first parameter is
true return the second parameter else the third parameter. But that logic
won't get fired as your third parameter is giving a divided by zero exception
when x is zero.
You could write a code function to go in the Report Properties' Code tab...
Function SafeDividePercent (ByVal numerator As Integer, ByVal denominator As
Integer) As Object
If denominator = 0 Then
Return 0
Else
Return ( numerator / denominator) *100.0
End If
End Function
... and then use it like this for a text box's Expression.
=Code.SafeDividePercent( Fields!y.Value - Fields!z.Value, Fields!x.Value)
Note that I've assumed your variables are integers but that the result isn't.
Someone may have another way of dealing with it.
Hope that helps,
Andrew|||Please ignore my previous answer - it is completely and utterly WRONG!
You CAN safely use IIF() to prevent a divide by zero.
That'll teach me to try before I post.
Now, where did I put that hair shirt?
Sorry for any confusion,
Andrew