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
> > > >> > >
> > > >> > >
> > > >> > >
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>
Friday, February 24, 2012
IIF Statement
Below I'm trying to return the larger column. If the SUM of UnitsInStock is
greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS
largerUnits and vice versa.
This is in northwind, can someone help me correct my syntax? I wasn't sure
how to do it with CASE.
CODE
SELECT IIf(SUM(UnitsInStock)>SUM(UnitsOnOrder), SUM(UnitsInStock) AS
largerUnits, SUM(UnitsOnOrder) AS largerUnits), ProductName
FROM Products
GROUP BY ProductNameTry this (untested):
SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
then SUM(UnitsInStock)
else SUM(UnitsOnOrder)
end AS largerUnits
,ProductName
FROM Products
GROUP BY ProductName
ML
http://milambda.blogspot.com/|||On Fri, 23 Dec 2005 18:22:17 -0600, Scott wrote:
> Below I'm trying to return the larger column. If the SUM of UnitsInStock i
s
>greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS
>largerUnits and vice versa.
>This is in northwind, can someone help me correct my syntax? I wasn't sure
>how to do it with CASE.
>CODE
>SELECT IIf(SUM(UnitsInStock)>SUM(UnitsOnOrder), SUM(UnitsInStock) AS
>largerUnits, SUM(UnitsOnOrder) AS largerUnits), ProductName
>FROM Products
>GROUP BY ProductName
>
SELECT CASE WHEN SUM(UnitsInStock) > SUM(UnitsOnOrder)
THEN SUM(UnitsInStock)
ELSE SUM(UnitsOnOrder) ) AS largerUnits),
ProductName
FROM Products
GROUP BY ProductName
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Forgive me for dipping my fly into your ointment, but your CASE expression i
s
missing its END. ;)
ML
http://milambda.blogspot.com/|||thanks.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:63517235-3C00-4A10-9FD3-1963051A3411@.microsoft.com...
> Try this (untested):
> SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
> then SUM(UnitsInStock)
> else SUM(UnitsOnOrder)
> end AS largerUnits
> ,ProductName
> FROM Products
> GROUP BY ProductName
>
> ML
> --
> http://milambda.blogspot.com/|||Just one thought - how will you ditinct between the two values in the client
application? After all, those are just numbers, but this query returns them
in a single column, although they originate in two different sources...?
Another CASE maybe?
SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
then SUM(UnitsInStock)
else SUM(UnitsOnOrder)
end AS largerUnits
,case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
then 'InStock'
else 'OnOrder'
end AS largerSource
,ProductName
FROM Products
GROUP BY ProductName
ML
http://milambda.blogspot.com/|||thanks, in my case, i just needed the larger of the 2.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3442E4F6-D752-4708-BB0A-A65416DE9755@.microsoft.com...
> Just one thought - how will you ditinct between the two values in the
> client
> application? After all, those are just numbers, but this query returns
> them
> in a single column, although they originate in two different sources...?
> Another CASE maybe?
> SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
> then SUM(UnitsInStock)
> else SUM(UnitsOnOrder)
> end AS largerUnits
> ,case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
> then 'InStock'
> else 'OnOrder'
> end AS largerSource
> ,ProductName
> FROM Products
> GROUP BY ProductName
>
> ML
> --
> http://milambda.blogspot.com/|||On Fri, 23 Dec 2005 17:05:02 -0800, ML wrote:
>Forgive me for dipping my fly into your ointment, but your CASE expression
is
>missing its END. ;)
Hi ML,
So it is. Thanks for the correction.
I really shouldn't write any more replies after 1 AM....
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||But then again - the level of inspiration is usually at its highest point at
1 AM.
ML
http://milambda.blogspot.com/|||For fun, I thought I would try a sql clr function to simulate IIF. Not
quite as elegant as the real IIF, but maybe more concise then a Case.
-- Usage example
declare @.n1 int
declare @.n2 int
set @.n1 = 1
set @.n2 = 2
select dbo.IIF(sum(@.n1), '>', sum(@.n2), 100, 200) -- Just to show using
sum(), does not make sense in this usage.
select dbo.IIF(@.n1, '>', @.n2, 'n1 is > n2', 'n1 is not > n2')
select dbo.IIF(@.n1, '<', @.n2, 'n1 is < n2', 'n1 is not < n2')
select dbo.IIF(@.n1, '>=', @.n2, 'n1 is >= n2', 'n1 is not >= n2');
select dbo.IIF(@.n1, '<=', @.n2, 'n1 is <= n2', 'n1 is not <= n2')
select dbo.IIF(@.n1, '==', @.n2, 'n1 is == n2', 'n1 is not == n2')
select dbo.IIF(@.n1, '!=', @.n2, 'n1 is != n2', 'n1 is not != n2')
select dbo.IIF(@.n1, '<>', @.n2, 'n1 is <> n2', 'n1 is not <> n2')
//
// The SQL Clr UDF IIF code.
//
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
/// <summary>
/// Returns one of two objects, depending on the evaluation of an
expression.
/// </summary>
/// <param name="lside">The left side comparand.</param>
/// <param name="op">The conditional operator to use for
testing.</param>
/// <param name="rside">The right side comparand.</param>
/// <param name="truePart">Returned if Expression evaluates to
True.</param>
/// <param name="falsePart">Returned if Expression evaluates to
False.</param>
/// <returns>Returns one of two objects, depending on the evaluation of
an expression. </returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static object IIF(object lside, string op, object rside, object
truePart, object falsePart)
{
if (lside == null || rside == null)
return falsePart;
if (lside is DBNull || rside is DBNull)
return falsePart;
if (op == null)
throw new ArgumentNullException("op");
IComparable cLeft = (IComparable)lside;
IComparable cRight = (IComparable)rside;
/*
CompareTo results:
Less than zero - This instance is less than obj.
Zero - This instance is equal to obj.
Greater than zero - This instance is greater than obj.
*/
switch (op)
{
case ">":
if (cLeft.CompareTo(cRight) > 0)
return truePart;
return falsePart;
case ">=":
if (cLeft.CompareTo(cRight) >=0)
return truePart;
return falsePart;
case "<":
if (cLeft.CompareTo(cRight) < 0)
return truePart;
return falsePart;
case "<=":
if (cLeft.CompareTo(cRight) <= 0)
return truePart;
return falsePart;
case "==":
if (cLeft.CompareTo(cRight) == 0)
return truePart;
return falsePart;
case "!=":
case "<>":
if (cLeft.CompareTo(cRight) == 0)
return falsePart;
return truePart;
default:
throw new ArgumentException("op");
}
}
};
William Stacey [MVP]
"Scott" <sbailey@.mileslumber.com> wrote in message
news:ua%230dECCGHA.216@.TK2MSFTNGP15.phx.gbl...
> Below I'm trying to return the larger column. If the SUM of UnitsInStock
> is greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS
> largerUnits and vice versa.
> This is in northwind, can someone help me correct my syntax? I wasn't sure
> how to do it with CASE.
> CODE
> SELECT IIf(SUM(UnitsInStock)>SUM(UnitsOnOrder), SUM(UnitsInStock) AS
> largerUnits, SUM(UnitsOnOrder) AS largerUnits), ProductName
> FROM Products
> GROUP BY ProductName
>
greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS
largerUnits and vice versa.
This is in northwind, can someone help me correct my syntax? I wasn't sure
how to do it with CASE.
CODE
SELECT IIf(SUM(UnitsInStock)>SUM(UnitsOnOrder), SUM(UnitsInStock) AS
largerUnits, SUM(UnitsOnOrder) AS largerUnits), ProductName
FROM Products
GROUP BY ProductNameTry this (untested):
SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
then SUM(UnitsInStock)
else SUM(UnitsOnOrder)
end AS largerUnits
,ProductName
FROM Products
GROUP BY ProductName
ML
http://milambda.blogspot.com/|||On Fri, 23 Dec 2005 18:22:17 -0600, Scott wrote:
> Below I'm trying to return the larger column. If the SUM of UnitsInStock i
s
>greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS
>largerUnits and vice versa.
>This is in northwind, can someone help me correct my syntax? I wasn't sure
>how to do it with CASE.
>CODE
>SELECT IIf(SUM(UnitsInStock)>SUM(UnitsOnOrder), SUM(UnitsInStock) AS
>largerUnits, SUM(UnitsOnOrder) AS largerUnits), ProductName
>FROM Products
>GROUP BY ProductName
>
SELECT CASE WHEN SUM(UnitsInStock) > SUM(UnitsOnOrder)
THEN SUM(UnitsInStock)
ELSE SUM(UnitsOnOrder) ) AS largerUnits),
ProductName
FROM Products
GROUP BY ProductName
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Forgive me for dipping my fly into your ointment, but your CASE expression i
s
missing its END. ;)
ML
http://milambda.blogspot.com/|||thanks.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:63517235-3C00-4A10-9FD3-1963051A3411@.microsoft.com...
> Try this (untested):
> SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
> then SUM(UnitsInStock)
> else SUM(UnitsOnOrder)
> end AS largerUnits
> ,ProductName
> FROM Products
> GROUP BY ProductName
>
> ML
> --
> http://milambda.blogspot.com/|||Just one thought - how will you ditinct between the two values in the client
application? After all, those are just numbers, but this query returns them
in a single column, although they originate in two different sources...?
Another CASE maybe?
SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
then SUM(UnitsInStock)
else SUM(UnitsOnOrder)
end AS largerUnits
,case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
then 'InStock'
else 'OnOrder'
end AS largerSource
,ProductName
FROM Products
GROUP BY ProductName
ML
http://milambda.blogspot.com/|||thanks, in my case, i just needed the larger of the 2.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3442E4F6-D752-4708-BB0A-A65416DE9755@.microsoft.com...
> Just one thought - how will you ditinct between the two values in the
> client
> application? After all, those are just numbers, but this query returns
> them
> in a single column, although they originate in two different sources...?
> Another CASE maybe?
> SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
> then SUM(UnitsInStock)
> else SUM(UnitsOnOrder)
> end AS largerUnits
> ,case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
> then 'InStock'
> else 'OnOrder'
> end AS largerSource
> ,ProductName
> FROM Products
> GROUP BY ProductName
>
> ML
> --
> http://milambda.blogspot.com/|||On Fri, 23 Dec 2005 17:05:02 -0800, ML wrote:
>Forgive me for dipping my fly into your ointment, but your CASE expression
is
>missing its END. ;)
Hi ML,
So it is. Thanks for the correction.
I really shouldn't write any more replies after 1 AM....
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||But then again - the level of inspiration is usually at its highest point at
1 AM.
ML
http://milambda.blogspot.com/|||For fun, I thought I would try a sql clr function to simulate IIF. Not
quite as elegant as the real IIF, but maybe more concise then a Case.
-- Usage example
declare @.n1 int
declare @.n2 int
set @.n1 = 1
set @.n2 = 2
select dbo.IIF(sum(@.n1), '>', sum(@.n2), 100, 200) -- Just to show using
sum(), does not make sense in this usage.
select dbo.IIF(@.n1, '>', @.n2, 'n1 is > n2', 'n1 is not > n2')
select dbo.IIF(@.n1, '<', @.n2, 'n1 is < n2', 'n1 is not < n2')
select dbo.IIF(@.n1, '>=', @.n2, 'n1 is >= n2', 'n1 is not >= n2');
select dbo.IIF(@.n1, '<=', @.n2, 'n1 is <= n2', 'n1 is not <= n2')
select dbo.IIF(@.n1, '==', @.n2, 'n1 is == n2', 'n1 is not == n2')
select dbo.IIF(@.n1, '!=', @.n2, 'n1 is != n2', 'n1 is not != n2')
select dbo.IIF(@.n1, '<>', @.n2, 'n1 is <> n2', 'n1 is not <> n2')
//
// The SQL Clr UDF IIF code.
//
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
/// <summary>
/// Returns one of two objects, depending on the evaluation of an
expression.
/// </summary>
/// <param name="lside">The left side comparand.</param>
/// <param name="op">The conditional operator to use for
testing.</param>
/// <param name="rside">The right side comparand.</param>
/// <param name="truePart">Returned if Expression evaluates to
True.</param>
/// <param name="falsePart">Returned if Expression evaluates to
False.</param>
/// <returns>Returns one of two objects, depending on the evaluation of
an expression. </returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static object IIF(object lside, string op, object rside, object
truePart, object falsePart)
{
if (lside == null || rside == null)
return falsePart;
if (lside is DBNull || rside is DBNull)
return falsePart;
if (op == null)
throw new ArgumentNullException("op");
IComparable cLeft = (IComparable)lside;
IComparable cRight = (IComparable)rside;
/*
CompareTo results:
Less than zero - This instance is less than obj.
Zero - This instance is equal to obj.
Greater than zero - This instance is greater than obj.
*/
switch (op)
{
case ">":
if (cLeft.CompareTo(cRight) > 0)
return truePart;
return falsePart;
case ">=":
if (cLeft.CompareTo(cRight) >=0)
return truePart;
return falsePart;
case "<":
if (cLeft.CompareTo(cRight) < 0)
return truePart;
return falsePart;
case "<=":
if (cLeft.CompareTo(cRight) <= 0)
return truePart;
return falsePart;
case "==":
if (cLeft.CompareTo(cRight) == 0)
return truePart;
return falsePart;
case "!=":
case "<>":
if (cLeft.CompareTo(cRight) == 0)
return falsePart;
return truePart;
default:
throw new ArgumentException("op");
}
}
};
William Stacey [MVP]
"Scott" <sbailey@.mileslumber.com> wrote in message
news:ua%230dECCGHA.216@.TK2MSFTNGP15.phx.gbl...
> Below I'm trying to return the larger column. If the SUM of UnitsInStock
> is greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS
> largerUnits and vice versa.
> This is in northwind, can someone help me correct my syntax? I wasn't sure
> how to do it with CASE.
> CODE
> SELECT IIf(SUM(UnitsInStock)>SUM(UnitsOnOrder), SUM(UnitsInStock) AS
> largerUnits, SUM(UnitsOnOrder) AS largerUnits), ProductName
> FROM Products
> GROUP BY ProductName
>
iif problem brings back #Error
I'm pulling a "Number (8)" data type field for a date (formatted YYYYMMDD) from a Oracle 9i database. When the value is inserted into the database it is set a zero (A non-null database for the most part), otherwise it is for example 20061224. If the date field has a value the date is displayed correctly, but if it is zero then I get the "#Error" message.
Here is one of many iif expressions I've tried.
=iif(Len(CStr(Fields!DTE_MAILED.Value)) = 8,
((CStr(Fields!DTE_MAILED.Value)).Substring(4,2) + "/" + Right(CStr(Fields!DTE_MAILED.Value), 2) + "/" + Left(CStr(Fields!DTE_MAILED.Value), 4))
,
Nothing)
Now I've returned the value with out any formatting done to the string, and it will return "0"(zero) or a number. I've returned the lengths of the returning value and it comes back "1" or "8". I read a lot of previous posts and I thought at first that it was because I was trying to do a substring function on the zero value getting a index error. So I've changed the iif test condition many different ways with no prevail. I read a previous post where someone ended up doing his work in his SQL, but I would like to find out how to do this in the report. This is a simple expression, so I feel like there is something obvious I don't know maybe something with the format mask.
All help will be appreciated!
hi nwyork,
i don't have Oracle db so i can't try.
but as far as the error that i've encountered,
e.g. iif (condition, true exp, false exp)
you might want to put your true exp and false exp returning the same data types...
if your true exp has CStr then the false exp should put CStr
|||Thanks for replying,
I thought that might be it so I tried returning (""), and after reading your post I tried using CStr function in the false part but I still get the error. I ran out of time so I used SubStr, Decode, and Length functions in the SQL.
|||Hi nwyork
The first problem im seeing is in the boolean check.
You are using 2 expressions on the field.....(Cstr and Len)
If the field containes a null value there will be problems as
Len(Null) = #error
Why not try restructuring your iif statement as follows:
=iif( Fields!DTE_MAILED.Value = Nothing
,0
,((CStr(Fields!DTE_MAILED.Value)).Substring(4,2) + "/" + Right(CStr(Fields!DTE_MAILED.Value), 2) + "/" + Left(CStr(Fields!DTE_MAILED.Value), 4)))
I'm not sure if this is specific to your datasource as i've never used Oracle before but it should still do
the trick. You can even add a second iif in the false part of the expression to make sure that the length is 8
,because as soon as the expression hits false part you know that there is no
null's and you should not get an error when trying to use expressions on the the field value.
If I was unclear in any way, be sure to point it out.
It may be 2 late now but it may help when a similar problem pops up in the future ;P
G
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. Visual Basic Language Specification
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
>>
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.
>>
IIF PROBLEM
in my table if ProductPrices_ID >15 then I want to change this field else
i want it to not change.
i wrote that code but it returns an error. how can i fix it ?
SELECT iif( ProductPrices_ID > 15 ; 0 ;ProductPrices_ID ) as pele FROM
ProductPricesThere is no IIF in T-SQL. For a comparable expression, look up CASE in SQL
Server Books Online.
Anith|||SELECT pele = CASE
WHEN ProductPrices_ID > 15 THEN 0
ELSE ProductPrices_ID
END
FROM ProductPrices
Coming from Access? (There is no IIF in SQL Server.) This article might
help:
http://www.aspfaq.com/2214
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:u2qnFnr6FHA.1248@.TK2MSFTNGP14.phx.gbl...
> in my table if ProductPrices_ID >15 then I want to change this field
> else i want it to not change.
> i wrote that code but it returns an error. how can i fix it ?
> SELECT iif( ProductPrices_ID > 15 ; 0 ;ProductPrices_ID ) as pele FROM
> ProductPrices
>
>
i want it to not change.
i wrote that code but it returns an error. how can i fix it ?
SELECT iif( ProductPrices_ID > 15 ; 0 ;ProductPrices_ID ) as pele FROM
ProductPricesThere is no IIF in T-SQL. For a comparable expression, look up CASE in SQL
Server Books Online.
Anith|||SELECT pele = CASE
WHEN ProductPrices_ID > 15 THEN 0
ELSE ProductPrices_ID
END
FROM ProductPrices
Coming from Access? (There is no IIF in SQL Server.) This article might
help:
http://www.aspfaq.com/2214
"Savas Ates" <savas@.indexinteractive.com> wrote in message
news:u2qnFnr6FHA.1248@.TK2MSFTNGP14.phx.gbl...
> in my table if ProductPrices_ID >15 then I want to change this field
> else i want it to not change.
> i wrote that code but it returns an error. how can i fix it ?
> SELECT iif( ProductPrices_ID > 15 ; 0 ;ProductPrices_ID ) as pele FROM
> ProductPrices
>
>
IIF problem
Hi all,
I have a problem with an IIF expression on a cell of my report (SSRS 2005).
I have a query that returns some datafields bigint that represent a date.
Because some values are null, I put this expression in my cell:
=IIF(Fields!LedgerReferenceDateIdMinus1.Value="","is null","is not null")
but on report rendering, I get this error:
#Error
in the case of not null.
What have I made wrong?
Thanks a lot.
LuigiOn Apr 14, 8:39 am, Luigi <ciupazNoSpamGra...@.inwind.it> wrote:
> Hi all,
> I have a problem with an IIF expression on a cell of my report (SSRS 2005).
> I have a query that returns some datafields bigint that represent a date.
It looks like you are getting your SQL syntax and your Expression
syntax mixed up when looking for Null values.
When using an expression for an RS field, you can check if a value is
null using the IsNull function, like so:
=IIF(IsNull(Fields!LedgerReferenceDateIDMinus1.value), '',
Fields!LedgerReferenceDateIDMinus1.value)
Or, you could check for Null values within your SQL query which is
probably better, because then you never have to deal with Null values
within that field once the dataset reaches your report:
CASE WHEN LedgerReferenceDateIDMinus1 IS NULL THEN '' ELSE
LedgerReferenceDateIDMinus1 END as LedgerReferenceDateIDMinus1
Good luck!
<
> =IIF(Fields!LedgerReferenceDateIdMinus1.Value="","is null","is not null")
> but on report rendering, I get this error:
> #Error
> in the case of not null.
> What have I made wrong?
> Thanks a lot.
> Luigi|||"Jerry H." wrote:
> When using an expression for an RS field, you can check if a value is
> null using the IsNull function, like so:
>
> =IIF(IsNull(Fields!LedgerReferenceDateIDMinus1.value), '',
> Fields!LedgerReferenceDateIDMinus1.value)
>
> Or, you could check for Null values within your SQL query which is
> probably better, because then you never have to deal with Null values
> within that field once the dataset reaches your report:
>
> CASE WHEN LedgerReferenceDateIDMinus1 IS NULL THEN '' ELSE
> LedgerReferenceDateIDMinus1 END as LedgerReferenceDateIDMinus1
>
> Good luck!
Hi Jerry, I'll try with IsNull in the report.
Thanks a lot for your detailed answer.
Luigi|||I slightly problem.
IfNull give me the "Unrecognized identifier" error.
This is my expression:
=IIF(IsNull(Fields!GrossDeltaMinus1.Value,''),FormatNumber(Fields!GrossDeltaMinus1,2))|||Move your first closing parentheses so that it is between the "e" in
Value and the first comma in your expression.
At the moment, you are passing two parameters over to IsNull, which
only takes one parameter.
On Apr 14, 9:39 am, Luigi <ciupazNoSpamGra...@.inwind.it> wrote:
> I slightly problem.
> IfNull give me the "Unrecognized identifier" error.
> This is my expression:
> =IIF(IsNull(Fields!GrossDeltaMinus1.Value,''),FormatNumber(Fields!GrossDeltaMinus1,2))|||"Jerry H." <boilersrock@.gmail.com> wrote in message
news:7edfeec7-a2fe-4d50-8cef-503e5798b1b5@.f36g2000hsa.googlegroups.com...
> Move your first closing parentheses so that it is between the "e" in
> Value and the first comma in your expression.
> At the moment, you are passing two parameters over to IsNull, which
> only takes one parameter.
> On Apr 14, 9:39 am, Luigi <ciupazNoSpamGra...@.inwind.it> wrote:
>> I slightly problem.
>> IfNull give me the "Unrecognized identifier" error.
>> This is my expression:
>> =IIF(IsNull(Fields!GrossDeltaMinus1.Value,''),FormatNumber(Fields!GrossDeltaMinus1,2))
>
I had that same problem, "Unrecognized identifier" error. and found
IsNothing worked instead.
I have a problem with an IIF expression on a cell of my report (SSRS 2005).
I have a query that returns some datafields bigint that represent a date.
Because some values are null, I put this expression in my cell:
=IIF(Fields!LedgerReferenceDateIdMinus1.Value="","is null","is not null")
but on report rendering, I get this error:
#Error
in the case of not null.
What have I made wrong?
Thanks a lot.
LuigiOn Apr 14, 8:39 am, Luigi <ciupazNoSpamGra...@.inwind.it> wrote:
> Hi all,
> I have a problem with an IIF expression on a cell of my report (SSRS 2005).
> I have a query that returns some datafields bigint that represent a date.
It looks like you are getting your SQL syntax and your Expression
syntax mixed up when looking for Null values.
When using an expression for an RS field, you can check if a value is
null using the IsNull function, like so:
=IIF(IsNull(Fields!LedgerReferenceDateIDMinus1.value), '',
Fields!LedgerReferenceDateIDMinus1.value)
Or, you could check for Null values within your SQL query which is
probably better, because then you never have to deal with Null values
within that field once the dataset reaches your report:
CASE WHEN LedgerReferenceDateIDMinus1 IS NULL THEN '' ELSE
LedgerReferenceDateIDMinus1 END as LedgerReferenceDateIDMinus1
Good luck!
<
> =IIF(Fields!LedgerReferenceDateIdMinus1.Value="","is null","is not null")
> but on report rendering, I get this error:
> #Error
> in the case of not null.
> What have I made wrong?
> Thanks a lot.
> Luigi|||"Jerry H." wrote:
> When using an expression for an RS field, you can check if a value is
> null using the IsNull function, like so:
>
> =IIF(IsNull(Fields!LedgerReferenceDateIDMinus1.value), '',
> Fields!LedgerReferenceDateIDMinus1.value)
>
> Or, you could check for Null values within your SQL query which is
> probably better, because then you never have to deal with Null values
> within that field once the dataset reaches your report:
>
> CASE WHEN LedgerReferenceDateIDMinus1 IS NULL THEN '' ELSE
> LedgerReferenceDateIDMinus1 END as LedgerReferenceDateIDMinus1
>
> Good luck!
Hi Jerry, I'll try with IsNull in the report.
Thanks a lot for your detailed answer.
Luigi|||I slightly problem.
IfNull give me the "Unrecognized identifier" error.
This is my expression:
=IIF(IsNull(Fields!GrossDeltaMinus1.Value,''),FormatNumber(Fields!GrossDeltaMinus1,2))|||Move your first closing parentheses so that it is between the "e" in
Value and the first comma in your expression.
At the moment, you are passing two parameters over to IsNull, which
only takes one parameter.
On Apr 14, 9:39 am, Luigi <ciupazNoSpamGra...@.inwind.it> wrote:
> I slightly problem.
> IfNull give me the "Unrecognized identifier" error.
> This is my expression:
> =IIF(IsNull(Fields!GrossDeltaMinus1.Value,''),FormatNumber(Fields!GrossDeltaMinus1,2))|||"Jerry H." <boilersrock@.gmail.com> wrote in message
news:7edfeec7-a2fe-4d50-8cef-503e5798b1b5@.f36g2000hsa.googlegroups.com...
> Move your first closing parentheses so that it is between the "e" in
> Value and the first comma in your expression.
> At the moment, you are passing two parameters over to IsNull, which
> only takes one parameter.
> On Apr 14, 9:39 am, Luigi <ciupazNoSpamGra...@.inwind.it> wrote:
>> I slightly problem.
>> IfNull give me the "Unrecognized identifier" error.
>> This is my expression:
>> =IIF(IsNull(Fields!GrossDeltaMinus1.Value,''),FormatNumber(Fields!GrossDeltaMinus1,2))
>
I had that same problem, "Unrecognized identifier" error. and found
IsNothing worked instead.
Iif optimization
Just a quick question:
Will the following mdx make the server calculate <mdxstatement> twice?
iif(<mdxstatement>=0,
NULL,
<somestatment> / <mdxstatement>
)
See my reply to your post on the 'Analysis Services Stored Procedure' thread...
Chris
Subscribe to:
Posts (Atom)