Iif Statment, IsNumeric, FormatCurrency

I noticed that regardless of the IsNumeric evaluation, the
Formatcurrency still fires. If I remove the FormatCurrency, and just
display the data, no problem. With the FormatCurrency in place, I get
an #error in the field if it is Alpha, but the proper information if it
is Numeric.
Example:
Iif(IsNumeric(Fields!item1.Value),FormatCurrency(Fields!item1.Value),"")
Has anyone else seen this before? If so, is there a workaround or is
there a problem with my logic?Howdy,
Both branches of the IIF function are evaluated without regard to the
logical expression. IIF just picks one of the evaluations to return.
(search this group for 'divide by zero error with iif' for more details
on the practicalities of this function)
Try something along the lines of
FormatCurrency(Iif(IsNumeric(Fields!item1.Value),Fields!item1.Value,-1))
HTH,
Sean G.|||Sean G.,
Thanks, I appreciate the assistance.
-Mal

IIF Statment to compare two values in joined tables

Hi

I'm trying to compare two varchars to check if they are the same, if they are the same then the color must turn red, if not then they must remain black

SELECT *

from members m, client c

where C.ClientID = m.ClientID

AND c.ClientID in (87,86)

AND m.email in ('dassd@.fdskjh.com','asdfas@.sdfd.net', etc...)

my results will give me two of the same email addresses but with different ClientID's, now when it

finds the same email it needs to make them both "RED"

Please help, any advice would be helpful

Kind Regards

Carel Greaves

Two ways occur to me:

- add a COUNT(c.ClientId) and GROUP BY m.email to your sql query, then check that count when displaying the data in your report - if the count is greater than 1 then you can colour the email field red

- use a row group in your report, grouping by email address, then have the other fields following - but if you use this approach you won't need to colour the email addresses red, as it will be instantly obvious which addresses are assigned to more than one client

IIF statment in an SQL statment.

I am trying to convert a logical and date fields into number fields. I am
using IIF() but I can't seem to get the syntax correct. Help Please.
SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
FROM Order2
GROUP BY MagazineName
The overall goal is to
1) count then number of records
2) count how many are renewal's
3) count how many are canceled
example:
CHILD 50 5 1
Scott BurkeHey Scott,
Use the CASE statement in SQL, not IIF. Like this:
SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag =false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
1 END as CLRFL
FROM Order2
GROUP BY MagazineName
Michael C
"Scott Burke" wrote:
> I am trying to convert a logical and date fields into number fields. I am
> using IIF() but I can't seem to get the syntax correct. Help Please.
> SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> FROM Order2
> GROUP BY MagazineName
> The overall goal is to
> 1) count then number of records
> 2) count how many are renewal's
> 3) count how many are canceled
> example:
> CHILD 50 5 1
> Scott Burke|||Hi Michael. Thanks for the suggestion. It worked exactly the way I wonted to.
The case statement looks a lot like an IIF() statement to me.
Time to do some research.
Thanks again.
Scott Burke
"Michael C" wrote:
> Hey Scott,
> Use the CASE statement in SQL, not IIF. Like this:
>
> SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag => false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
> 1 END as CLRFL
> FROM Order2
> GROUP BY MagazineName
>
> Michael C
> "Scott Burke" wrote:
> > I am trying to convert a logical and date fields into number fields. I am
> > using IIF() but I can't seem to get the syntax correct. Help Please.
> >
> > SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> > 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> > FROM Order2
> > GROUP BY MagazineName
> >
> > The overall goal is to
> > 1) count then number of records
> > 2) count how many are renewal's
> > 3) count how many are canceled
> >
> > example:
> > CHILD 50 5 1
> >
> > Scott Burke

IIF Statement Failing... Why?

I think I am getting this error because it cant interpet the Dataset option?
Is this a bug or am I doing somthing wrong?
Statment:
=IIF (ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
Fields!SetupPieces.Value) > (Fields!TrussPrices.Value, "DataSet1") /
(Fields!Pieces.Value + Fields!SetupPieces.Value)
,"Red","White"))
Error:
w:\trussworks reports\plant reports\Piece Compare.rdl The background color
expression for the textbox â'textbox13â' contains an error: [BC30455] Argument
not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As
Boolean, TruePart As Object, FalsePart As Object) As Object'.
--
Thank You, LeoTry some more parends. It looks like it is including the > as part of the
calculation of what to divide. Add a set of parends on either side of the >.
=IIF ((ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
Fields!SetupPieces.Value)) > ((Fields!TrussPrices.Value, "DataSet1") /
(Fields!Pieces.Value + Fields!SetupPieces.Value))
,"Red","White"))
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"TrussworksLeo" <Leo@.noemail.noemail> wrote in message
news:A9C729D2-FFF6-4A08-A920-C48BA786CA8A@.microsoft.com...
> I think I am getting this error because it cant interpet the Dataset
option?
> Is this a bug or am I doing somthing wrong?
> Statment:
> =IIF (ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> Fields!SetupPieces.Value) > (Fields!TrussPrices.Value, "DataSet1") /
> (Fields!Pieces.Value + Fields!SetupPieces.Value)
> ,"Red","White"))
> Error:
> w:\trussworks reports\plant reports\Piece Compare.rdl The background color
> expression for the textbox 'textbox13' contains an error: [BC30455]
Argument
> not specified for parameter 'FalsePart' of 'Public Function IIf(Expression
As
> Boolean, TruePart As Object, FalsePart As Object) As Object'.
>
>
> --
> Thank You, Leo|||Bruce,
That is not working. It still says
Argument not specified for parameter 'FalsePart' of 'Public Function
IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As
Object'.
I try to compare two fields from two different data sets and get:
w:\trussworks reports\plant reports\Piece Compare.rdl The background color
expression for the textbox â'PPPSâ' refers to the report item â'JPPPSâ'. Report
item expressions can only refer to other report items within the same
grouping scope or a containing grouping scope.
Thanks, Leo
"Bruce L-C [MVP]" wrote:
> Try some more parends. It looks like it is including the > as part of the
> calculation of what to divide. Add a set of parends on either side of the >.
> =IIF ((ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> Fields!SetupPieces.Value)) > ((Fields!TrussPrices.Value, "DataSet1") /
> (Fields!Pieces.Value + Fields!SetupPieces.Value))
> ,"Red","White"))
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> news:A9C729D2-FFF6-4A08-A920-C48BA786CA8A@.microsoft.com...
> > I think I am getting this error because it cant interpet the Dataset
> option?
> > Is this a bug or am I doing somthing wrong?
> >
> > Statment:
> > =IIF (ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> > Fields!SetupPieces.Value) > (Fields!TrussPrices.Value, "DataSet1") /
> > (Fields!Pieces.Value + Fields!SetupPieces.Value)
> > ,"Red","White"))
> >
> > Error:
> > w:\trussworks reports\plant reports\Piece Compare.rdl The background color
> > expression for the textbox 'textbox13' contains an error: [BC30455]
> Argument
> > not specified for parameter 'FalsePart' of 'Public Function IIf(Expression
> As
> > Boolean, TruePart As Object, FalsePart As Object) As Object'.
> >
> >
> >
> >
> > --
> > Thank You, Leo
>
>|||Ahh yes, but notice that the error has changed. What I showed you was the
first problem. Now you are seeing a scoping issue. How many rows are in
these datasets. If there is just a single row in each then use First
function first(fields!trussprices.value, "DataSet1").
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"TrussworksLeo" <Leo@.noemail.noemail> wrote in message
news:E839C314-C674-42CD-8560-7756EA5F7BDC@.microsoft.com...
> Bruce,
> That is not working. It still says
> Argument not specified for parameter 'FalsePart' of 'Public Function
> IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As
> Object'.
> I try to compare two fields from two different data sets and get:
> w:\trussworks reports\plant reports\Piece Compare.rdl The background color
> expression for the textbox 'PPPS' refers to the report item 'JPPPS'.
Report
> item expressions can only refer to other report items within the same
> grouping scope or a containing grouping scope.
> Thanks, Leo
> "Bruce L-C [MVP]" wrote:
> > Try some more parends. It looks like it is including the > as part of
the
> > calculation of what to divide. Add a set of parends on either side of
the >.
> >
> > =IIF ((ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> > Fields!SetupPieces.Value)) > ((Fields!TrussPrices.Value, "DataSet1") /
> > (Fields!Pieces.Value + Fields!SetupPieces.Value))
> > ,"Red","White"))
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> > news:A9C729D2-FFF6-4A08-A920-C48BA786CA8A@.microsoft.com...
> > > I think I am getting this error because it cant interpet the Dataset
> > option?
> > > Is this a bug or am I doing somthing wrong?
> > >
> > > Statment:
> > > =IIF (ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> > > Fields!SetupPieces.Value) > (Fields!TrussPrices.Value, "DataSet1") /
> > > (Fields!Pieces.Value + Fields!SetupPieces.Value)
> > > ,"Red","White"))
> > >
> > > Error:
> > > w:\trussworks reports\plant reports\Piece Compare.rdl The background
color
> > > expression for the textbox 'textbox13' contains an error: [BC30455]
> > Argument
> > > not specified for parameter 'FalsePart' of 'Public Function
IIf(Expression
> > As
> > > Boolean, TruePart As Object, FalsePart As Object) As Object'.
> > >
> > >
> > >
> > >
> > > --
> > > Thank You, Leo
> >
> >
> >|||There are multiple rows.
I only get the scope error when I use the ReportItems Collection. Not when I
try without it.
"Bruce L-C [MVP]" wrote:
> Ahh yes, but notice that the error has changed. What I showed you was the
> first problem. Now you are seeing a scoping issue. How many rows are in
> these datasets. If there is just a single row in each then use First
> function first(fields!trussprices.value, "DataSet1").
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> news:E839C314-C674-42CD-8560-7756EA5F7BDC@.microsoft.com...
> > Bruce,
> >
> > That is not working. It still says
> > Argument not specified for parameter 'FalsePart' of 'Public Function
> > IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As
> > Object'.
> >
> > I try to compare two fields from two different data sets and get:
> > w:\trussworks reports\plant reports\Piece Compare.rdl The background color
> > expression for the textbox 'PPPS' refers to the report item 'JPPPS'.
> Report
> > item expressions can only refer to other report items within the same
> > grouping scope or a containing grouping scope.
> >
> > Thanks, Leo
> > "Bruce L-C [MVP]" wrote:
> >
> > > Try some more parends. It looks like it is including the > as part of
> the
> > > calculation of what to divide. Add a set of parends on either side of
> the >.
> > >
> > > =IIF ((ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> > > Fields!SetupPieces.Value)) > ((Fields!TrussPrices.Value, "DataSet1") /
> > > (Fields!Pieces.Value + Fields!SetupPieces.Value))
> > > ,"Red","White"))
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> > > news:A9C729D2-FFF6-4A08-A920-C48BA786CA8A@.microsoft.com...
> > > > I think I am getting this error because it cant interpet the Dataset
> > > option?
> > > > Is this a bug or am I doing somthing wrong?
> > > >
> > > > Statment:
> > > > =IIF (ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> > > > Fields!SetupPieces.Value) > (Fields!TrussPrices.Value, "DataSet1") /
> > > > (Fields!Pieces.Value + Fields!SetupPieces.Value)
> > > > ,"Red","White"))
> > > >
> > > > Error:
> > > > w:\trussworks reports\plant reports\Piece Compare.rdl The background
> color
> > > > expression for the textbox 'textbox13' contains an error: [BC30455]
> > > Argument
> > > > not specified for parameter 'FalsePart' of 'Public Function
> IIf(Expression
> > > As
> > > > Boolean, TruePart As Object, FalsePart As Object) As Object'.
> > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Thank You, Leo
> > >
> > >
> > >
>
>|||I'm having trouble understanding what you are trying to do. RS can have
multiple datasets but they each are in their own region and if you cross
regions you need to use First, or Sum or something like that. When I look at
the formula you have how would the system know which row you are referencing
if the datasets have multiple rows.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"TrussworksLeo" <Leo@.noemail.noemail> wrote in message
news:5FAABABF-5AD0-45D6-9F8A-B1B11DB67B95@.microsoft.com...
> There are multiple rows.
> I only get the scope error when I use the ReportItems Collection. Not when
> I
> try without it.
> "Bruce L-C [MVP]" wrote:
>> Ahh yes, but notice that the error has changed. What I showed you was the
>> first problem. Now you are seeing a scoping issue. How many rows are in
>> these datasets. If there is just a single row in each then use First
>> function first(fields!trussprices.value, "DataSet1").
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
>> news:E839C314-C674-42CD-8560-7756EA5F7BDC@.microsoft.com...
>> > Bruce,
>> >
>> > That is not working. It still says
>> > Argument not specified for parameter 'FalsePart' of 'Public Function
>> > IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As
>> > Object'.
>> >
>> > I try to compare two fields from two different data sets and get:
>> > w:\trussworks reports\plant reports\Piece Compare.rdl The background
>> > color
>> > expression for the textbox 'PPPS' refers to the report item 'JPPPS'.
>> Report
>> > item expressions can only refer to other report items within the same
>> > grouping scope or a containing grouping scope.
>> >
>> > Thanks, Leo
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > Try some more parends. It looks like it is including the > as part of
>> the
>> > > calculation of what to divide. Add a set of parends on either side of
>> the >.
>> > >
>> > > =IIF ((ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
>> > > Fields!SetupPieces.Value)) > ((Fields!TrussPrices.Value, "DataSet1")
>> > > /
>> > > (Fields!Pieces.Value + Fields!SetupPieces.Value))
>> > > ,"Red","White"))
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > >
>> > > "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
>> > > news:A9C729D2-FFF6-4A08-A920-C48BA786CA8A@.microsoft.com...
>> > > > I think I am getting this error because it cant interpet the
>> > > > Dataset
>> > > option?
>> > > > Is this a bug or am I doing somthing wrong?
>> > > >
>> > > > Statment:
>> > > > =IIF (ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
>> > > > Fields!SetupPieces.Value) > (Fields!TrussPrices.Value, "DataSet1")
>> > > > /
>> > > > (Fields!Pieces.Value + Fields!SetupPieces.Value)
>> > > > ,"Red","White"))
>> > > >
>> > > > Error:
>> > > > w:\trussworks reports\plant reports\Piece Compare.rdl The
>> > > > background
>> color
>> > > > expression for the textbox 'textbox13' contains an error: [BC30455]
>> > > Argument
>> > > > not specified for parameter 'FalsePart' of 'Public Function
>> IIf(Expression
>> > > As
>> > > > Boolean, TruePart As Object, FalsePart As Object) As Object'.
>> > > >
>> > > >
>> > > >
>> > > >
>> > > > --
>> > > > Thank You, Leo
>> > >
>> > >
>> > >
>>|||I have the datasets in such an order where the data lines up like I would
like(Production Order to Sales Order) I need to compare a value from one to
the other and change the backround color if they dont match or if the wrong
one is greater than the other.
I cant use the first statement because it is not the only record returned. I
would like it to compare the current row against the current row.
Shouldnt this statement work?
=IIF(Fields!TrussPrices.Value, "DataSet1") / ((Fields!SetupPieces.Value,
"DataSet1") + (Fields!Pieces.Value, "DataSet1")))
>
(Fields!TrussPrices.Value, "ProductionOrders") / ((Fields!SetupPieces.Value,
"ProductionOrders") + (Fields!Pieces.Value, "ProductionOrders")))
Why am I getting this error? Cant it handle the Datset Parameter?
w:\trussworks reports\plant reports\Piece Compare.rdl The background color
expression for the textbox â'PPPSâ' contains an error: [BC30455] Argument not
specified for parameter 'FalsePart' of 'Public Function IIf(Expression As
Boolean, TruePart As Object, FalsePart As Object) As Object'.
To try to get around this problem I tried the ReportItems Collection but
this will not work either?
Thanks for the Help...
Leo
"Bruce L-C [MVP]" wrote:
> I'm having trouble understanding what you are trying to do. RS can have
> multiple datasets but they each are in their own region and if you cross
> regions you need to use First, or Sum or something like that. When I look at
> the formula you have how would the system know which row you are referencing
> if the datasets have multiple rows.
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> news:5FAABABF-5AD0-45D6-9F8A-B1B11DB67B95@.microsoft.com...
> > There are multiple rows.
> >
> > I only get the scope error when I use the ReportItems Collection. Not when
> > I
> > try without it.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Ahh yes, but notice that the error has changed. What I showed you was the
> >> first problem. Now you are seeing a scoping issue. How many rows are in
> >> these datasets. If there is just a single row in each then use First
> >> function first(fields!trussprices.value, "DataSet1").
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> >> news:E839C314-C674-42CD-8560-7756EA5F7BDC@.microsoft.com...
> >> > Bruce,
> >> >
> >> > That is not working. It still says
> >> > Argument not specified for parameter 'FalsePart' of 'Public Function
> >> > IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As
> >> > Object'.
> >> >
> >> > I try to compare two fields from two different data sets and get:
> >> > w:\trussworks reports\plant reports\Piece Compare.rdl The background
> >> > color
> >> > expression for the textbox 'PPPS' refers to the report item 'JPPPS'.
> >> Report
> >> > item expressions can only refer to other report items within the same
> >> > grouping scope or a containing grouping scope.
> >> >
> >> > Thanks, Leo
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> > > Try some more parends. It looks like it is including the > as part of
> >> the
> >> > > calculation of what to divide. Add a set of parends on either side of
> >> the >.
> >> > >
> >> > > =IIF ((ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> >> > > Fields!SetupPieces.Value)) > ((Fields!TrussPrices.Value, "DataSet1")
> >> > > /
> >> > > (Fields!Pieces.Value + Fields!SetupPieces.Value))
> >> > > ,"Red","White"))
> >> > >
> >> > > --
> >> > > Bruce Loehle-Conger
> >> > > MVP SQL Server Reporting Services
> >> > >
> >> > >
> >> > > "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> >> > > news:A9C729D2-FFF6-4A08-A920-C48BA786CA8A@.microsoft.com...
> >> > > > I think I am getting this error because it cant interpet the
> >> > > > Dataset
> >> > > option?
> >> > > > Is this a bug or am I doing somthing wrong?
> >> > > >
> >> > > > Statment:
> >> > > > =IIF (ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> >> > > > Fields!SetupPieces.Value) > (Fields!TrussPrices.Value, "DataSet1")
> >> > > > /
> >> > > > (Fields!Pieces.Value + Fields!SetupPieces.Value)
> >> > > > ,"Red","White"))
> >> > > >
> >> > > > Error:
> >> > > > w:\trussworks reports\plant reports\Piece Compare.rdl The
> >> > > > background
> >> color
> >> > > > expression for the textbox 'textbox13' contains an error: [BC30455]
> >> > > Argument
> >> > > > not specified for parameter 'FalsePart' of 'Public Function
> >> IIf(Expression
> >> > > As
> >> > > > Boolean, TruePart As Object, FalsePart As Object) As Object'.
> >> > > >
> >> > > >
> >> > > >
> >> > > >
> >> > > > --
> >> > > > Thank You, Leo
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>|||Although it makes sense to you because you know that the two datasets line
up, there is no way for RS to know this. There is no way to take the value
from row N from one table and row N from another table and do a calculation.
RS does not allow this sort of thing between two different data regions. You
need to look at the problem in some other way to solve it. Perhaps a stored
procedure? Also you can create a mega dataset and use lists. I have seen
people solve the two dataset problem this way but it is not a technique I
have personally used so I can't be of much more help (other than letting you
know that you can't solve it in the direction you are currently going).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"TrussworksLeo" <Leo@.noemail.noemail> wrote in message
news:C1B943CA-4B5E-48F3-A86D-74E98F6A72B5@.microsoft.com...
> I have the datasets in such an order where the data lines up like I would
> like(Production Order to Sales Order) I need to compare a value from one
to
> the other and change the backround color if they dont match or if the
wrong
> one is greater than the other.
> I cant use the first statement because it is not the only record returned.
I
> would like it to compare the current row against the current row.
> Shouldnt this statement work?
> =IIF(Fields!TrussPrices.Value, "DataSet1") / ((Fields!SetupPieces.Value,
> "DataSet1") + (Fields!Pieces.Value, "DataSet1")))
> >
> (Fields!TrussPrices.Value, "ProductionOrders") /
((Fields!SetupPieces.Value,
> "ProductionOrders") + (Fields!Pieces.Value, "ProductionOrders")))
> Why am I getting this error? Cant it handle the Datset Parameter?
> w:\trussworks reports\plant reports\Piece Compare.rdl The background color
> expression for the textbox 'PPPS' contains an error: [BC30455] Argument
not
> specified for parameter 'FalsePart' of 'Public Function IIf(Expression As
> Boolean, TruePart As Object, FalsePart As Object) As Object'.
> To try to get around this problem I tried the ReportItems Collection but
> this will not work either?
> Thanks for the Help...
> Leo
>
>
> "Bruce L-C [MVP]" wrote:
> > I'm having trouble understanding what you are trying to do. RS can have
> > multiple datasets but they each are in their own region and if you cross
> > regions you need to use First, or Sum or something like that. When I
look at
> > the formula you have how would the system know which row you are
referencing
> > if the datasets have multiple rows.
> >
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> > news:5FAABABF-5AD0-45D6-9F8A-B1B11DB67B95@.microsoft.com...
> > > There are multiple rows.
> > >
> > > I only get the scope error when I use the ReportItems Collection. Not
when
> > > I
> > > try without it.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> Ahh yes, but notice that the error has changed. What I showed you was
the
> > >> first problem. Now you are seeing a scoping issue. How many rows are
in
> > >> these datasets. If there is just a single row in each then use First
> > >> function first(fields!trussprices.value, "DataSet1").
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >>
> > >> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> > >> news:E839C314-C674-42CD-8560-7756EA5F7BDC@.microsoft.com...
> > >> > Bruce,
> > >> >
> > >> > That is not working. It still says
> > >> > Argument not specified for parameter 'FalsePart' of 'Public
Function
> > >> > IIf(Expression As Boolean, TruePart As Object, FalsePart As Object)
As
> > >> > Object'.
> > >> >
> > >> > I try to compare two fields from two different data sets and get:
> > >> > w:\trussworks reports\plant reports\Piece Compare.rdl The
background
> > >> > color
> > >> > expression for the textbox 'PPPS' refers to the report item
'JPPPS'.
> > >> Report
> > >> > item expressions can only refer to other report items within the sa
me
> > >> > grouping scope or a containing grouping scope.
> > >> >
> > >> > Thanks, Leo
> > >> > "Bruce L-C [MVP]" wrote:
> > >> >
> > >> > > Try some more parends. It looks like it is including the > as
part of
> > >> the
> > >> > > calculation of what to divide. Add a set of parends on either
side of
> > >> the >.
> > >> > >
> > >> > > =IIF ((ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> > >> > > Fields!SetupPieces.Value)) > ((Fields!TrussPrices.Value,
"DataSet1")
> > >> > > /
> > >> > > (Fields!Pieces.Value + Fields!SetupPieces.Value))
> > >> > > ,"Red","White"))
> > >> > >
> > >> > > --
> > >> > > Bruce Loehle-Conger
> > >> > > MVP SQL Server Reporting Services
> > >> > >
> > >> > >
> > >> > > "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> > >> > > news:A9C729D2-FFF6-4A08-A920-C48BA786CA8A@.microsoft.com...
> > >> > > > I think I am getting this error because it cant interpet the
> > >> > > > Dataset
> > >> > > option?
> > >> > > > Is this a bug or am I doing somthing wrong?
> > >> > > >
> > >> > > > Statment:
> > >> > > > =IIF (ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> > >> > > > Fields!SetupPieces.Value) > (Fields!TrussPrices.Value,
"DataSet1")
> > >> > > > /
> > >> > > > (Fields!Pieces.Value + Fields!SetupPieces.Value)
> > >> > > > ,"Red","White"))
> > >> > > >
> > >> > > > Error:
> > >> > > > w:\trussworks reports\plant reports\Piece Compare.rdl The
> > >> > > > background
> > >> color
> > >> > > > expression for the textbox 'textbox13' contains an error:
[BC30455]
> > >> > > Argument
> > >> > > > not specified for parameter 'FalsePart' of 'Public Function
> > >> IIf(Expression
> > >> > > As
> > >> > > > Boolean, TruePart As Object, FalsePart As Object) As Object'.
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > > --
> > >> > > > Thank You, Leo
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> > >>
> >
> >
> >|||Thank You....I appreciate your help.
"Bruce L-C [MVP]" wrote:
> Although it makes sense to you because you know that the two datasets line
> up, there is no way for RS to know this. There is no way to take the value
> from row N from one table and row N from another table and do a calculation.
> RS does not allow this sort of thing between two different data regions. You
> need to look at the problem in some other way to solve it. Perhaps a stored
> procedure? Also you can create a mega dataset and use lists. I have seen
> people solve the two dataset problem this way but it is not a technique I
> have personally used so I can't be of much more help (other than letting you
> know that you can't solve it in the direction you are currently going).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> news:C1B943CA-4B5E-48F3-A86D-74E98F6A72B5@.microsoft.com...
> > I have the datasets in such an order where the data lines up like I would
> > like(Production Order to Sales Order) I need to compare a value from one
> to
> > the other and change the backround color if they dont match or if the
> wrong
> > one is greater than the other.
> >
> > I cant use the first statement because it is not the only record returned.
> I
> > would like it to compare the current row against the current row.
> >
> > Shouldnt this statement work?
> > =IIF(Fields!TrussPrices.Value, "DataSet1") / ((Fields!SetupPieces.Value,
> > "DataSet1") + (Fields!Pieces.Value, "DataSet1")))
> > >
> > (Fields!TrussPrices.Value, "ProductionOrders") /
> ((Fields!SetupPieces.Value,
> > "ProductionOrders") + (Fields!Pieces.Value, "ProductionOrders")))
> >
> > Why am I getting this error? Cant it handle the Datset Parameter?
> > w:\trussworks reports\plant reports\Piece Compare.rdl The background color
> > expression for the textbox 'PPPS' contains an error: [BC30455] Argument
> not
> > specified for parameter 'FalsePart' of 'Public Function IIf(Expression As
> > Boolean, TruePart As Object, FalsePart As Object) As Object'.
> >
> > To try to get around this problem I tried the ReportItems Collection but
> > this will not work either?
> >
> > Thanks for the Help...
> >
> > Leo
> >
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I'm having trouble understanding what you are trying to do. RS can have
> > > multiple datasets but they each are in their own region and if you cross
> > > regions you need to use First, or Sum or something like that. When I
> look at
> > > the formula you have how would the system know which row you are
> referencing
> > > if the datasets have multiple rows.
> > >
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> > > news:5FAABABF-5AD0-45D6-9F8A-B1B11DB67B95@.microsoft.com...
> > > > There are multiple rows.
> > > >
> > > > I only get the scope error when I use the ReportItems Collection. Not
> when
> > > > I
> > > > try without it.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > >> Ahh yes, but notice that the error has changed. What I showed you was
> the
> > > >> first problem. Now you are seeing a scoping issue. How many rows are
> in
> > > >> these datasets. If there is just a single row in each then use First
> > > >> function first(fields!trussprices.value, "DataSet1").
> > > >>
> > > >> --
> > > >> Bruce Loehle-Conger
> > > >> MVP SQL Server Reporting Services
> > > >>
> > > >>
> > > >> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> > > >> news:E839C314-C674-42CD-8560-7756EA5F7BDC@.microsoft.com...
> > > >> > Bruce,
> > > >> >
> > > >> > That is not working. It still says
> > > >> > Argument not specified for parameter 'FalsePart' of 'Public
> Function
> > > >> > IIf(Expression As Boolean, TruePart As Object, FalsePart As Object)
> As
> > > >> > Object'.
> > > >> >
> > > >> > I try to compare two fields from two different data sets and get:
> > > >> > w:\trussworks reports\plant reports\Piece Compare.rdl The
> background
> > > >> > color
> > > >> > expression for the textbox 'PPPS' refers to the report item
> 'JPPPS'.
> > > >> Report
> > > >> > item expressions can only refer to other report items within the sa
> me
> > > >> > grouping scope or a containing grouping scope.
> > > >> >
> > > >> > Thanks, Leo
> > > >> > "Bruce L-C [MVP]" wrote:
> > > >> >
> > > >> > > Try some more parends. It looks like it is including the > as
> part of
> > > >> the
> > > >> > > calculation of what to divide. Add a set of parends on either
> side of
> > > >> the >.
> > > >> > >
> > > >> > > =IIF ((ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> > > >> > > Fields!SetupPieces.Value)) > ((Fields!TrussPrices.Value,
> "DataSet1")
> > > >> > > /
> > > >> > > (Fields!Pieces.Value + Fields!SetupPieces.Value))
> > > >> > > ,"Red","White"))
> > > >> > >
> > > >> > > --
> > > >> > > Bruce Loehle-Conger
> > > >> > > MVP SQL Server Reporting Services
> > > >> > >
> > > >> > >
> > > >> > > "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> > > >> > > news:A9C729D2-FFF6-4A08-A920-C48BA786CA8A@.microsoft.com...
> > > >> > > > I think I am getting this error because it cant interpet the
> > > >> > > > Dataset
> > > >> > > option?
> > > >> > > > Is this a bug or am I doing somthing wrong?
> > > >> > > >
> > > >> > > > Statment:
> > > >> > > > =IIF (ReportItems!JobPriceS.Value / (Fields!Pieces.Value +
> > > >> > > > Fields!SetupPieces.Value) > (Fields!TrussPrices.Value,
> "DataSet1")
> > > >> > > > /
> > > >> > > > (Fields!Pieces.Value + Fields!SetupPieces.Value)
> > > >> > > > ,"Red","White"))
> > > >> > > >
> > > >> > > > Error:
> > > >> > > > w:\trussworks reports\plant reports\Piece Compare.rdl The
> > > >> > > > background
> > > >> color
> > > >> > > > expression for the textbox 'textbox13' contains an error:
> [BC30455]
> > > >> > > Argument
> > > >> > > > not specified for parameter 'FalsePart' of 'Public Function
> > > >> IIf(Expression
> > > >> > > As
> > > >> > > > Boolean, TruePart As Object, FalsePart As Object) As Object'.
> > > >> > > >
> > > >> > > >
> > > >> > > >
> > > >> > > >
> > > >> > > > --
> > > >> > > > Thank You, Leo
> > > >> > >
> > > >> > >
> > > >> > >
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>

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.