Problem with IIF and Like comparison

I am having a problem with the following expression

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

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

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

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

|||

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

I don't know try that.

|||

I believe it would be

Code Snippet

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

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

Code Snippet

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

Problem with GROUP BY syntax and expression

I am really struggling with this code and would appreciate knowing how
to group by the expression (constants) in the SELECT clause:
DECLARE @.LO INT
DECLARE @.HI INT
DECLARE @.StartDate varchar(10)
DECLARE @.EndDate varchar(10)
SELECT @.StartDate = '01/01/2005'
SELECT @.EndDate = '06/30/2005'
SELECT @.LO = 250
SELECT @.HI = 333
SELECT
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDate
, JumboAmount = SUM(JumboAmount)
, JumboMortgages = SUM(JumboMortgages)
, JumboFIXMortgages = SUM(JumboFIXMortgages)
, JumboFIXAmount = SUM(JumboFIXAmount)
, JumboARMMortgages = SUM(JumboARMMortgages)
, JumboARMAmount = SUM(JumboARMAmount)
FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred Z
ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
Z.ZipCode
GROUP BY
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDateRemove the aliases from the GROUP BY.
GROUP BY
StateCD
,CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
,'JumboRange:' + Convert(varchar(4),@.LO) + '-' + Convert(varchar(4),@.HI)
,@.StartDate
,@.EndDate
"JJA" <johna@.cbmiweb.com> wrote in message
news:1123780031.845579.256660@.o13g2000cwo.googlegroups.com...
> I am really struggling with this code and would appreciate knowing how
> to group by the expression (constants) in the SELECT clause:
> DECLARE @.LO INT
> DECLARE @.HI INT
> DECLARE @.StartDate varchar(10)
> DECLARE @.EndDate varchar(10)
> SELECT @.StartDate = '01/01/2005'
> SELECT @.EndDate = '06/30/2005'
> SELECT @.LO = 250
> SELECT @.HI = 333
> SELECT
> StateCD
> , CountyCD
> , Zip
> , Z.CityName
> , Z.StateCode
> , Z.CountyName
> , 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
> Convert(varchar(4),@.HI)
> , 'StartingDate' = @.StartDate
> , 'ThruDate' = @.EndDate
> , JumboAmount = SUM(JumboAmount)
> , JumboMortgages = SUM(JumboMortgages)
> , JumboFIXMortgages = SUM(JumboFIXMortgages)
> , JumboFIXAmount = SUM(JumboFIXAmount)
> , JumboARMMortgages = SUM(JumboARMMortgages)
> , JumboARMAmount = SUM(JumboARMAmount)
> FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred Z
> ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
> Z.ZipCode
> GROUP BY
> StateCD
> , CountyCD
> , Zip
> , Z.CityName
> , Z.StateCode
> , Z.CountyName
> , 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
> Convert(varchar(4),@.HI)
> , 'StartingDate' = @.StartDate
> , 'ThruDate' = @.EndDate
>|||I would use a table expression, like this:
create table t(i int, d money)
insert into t values(1, 1.00)
insert into t values(1, 2.00)
insert into t values(2, 3.00)
insert into t values(2, 4.00)
select t.*, 'Criteria' = 'Some text here'
from (select i, sum(d) sumd from t group by i) t
drop table t
Besides, what's the point of grouping by both CountyCD and CountyName?
If you group by only by StateCD and CountyCD, the query might run much
faster. The rest columns could be retrieved after grouping by, like
this:
create table t(i int, d money)
insert into t values(1, 1.00)
insert into t values(1, 2.00)
insert into t values(2, 3.00)
insert into t values(2, 4.00)
create table s(i int, sname char(5))
insert into s values(1,'One')
insert into s values(2,'Two')
select s.sname, t.sumd, 'Criteria' = 'Some text here'
from (select i, sum(d) sumd from t group by i) t
join s on s.i=t.i
sname sumd Criteria
-- -- --
One 3.0000 Some text here
Two 7.0000 Some text here
drop table t
drop table s|||Thank you so much. Great idea...I now have it working per your
suggestion.

Problem with Format() Function in expression

Hi,
I have a textbox with an expression to format integer data:
="HH: " + format(Fields!ANZHH.Value,"#.###.") + ", EW: " +
format(Fields!ANZEW.Value,"#.###.")
I want that integer values appear as: HH: 1.000, EW: 3.890
("." = group digit )
However the custom format style that I use does not return the expected
result, instead RS shows HH: 1000, EW: 3890
Any ideas how to troubleshoot?
--
Thanks in advance
BodoI finally got it solved:
1) modified expression to:
="HH: " + format(Fields!ANZHH.Value,"#,###.") + ", EW: " +
format(Fields!ANZEW.Value,"#,###.")
2) set report property Language to Default.
As a result values are formated in the culture specified in IE language
settings.
Bodo|||I'm guessing I'm not the first to ask this, but haven't found anything
either here or via googling around.
Per the subject, some of our database fields have html code in them that we
would like to render inside the report (using the table control) as actual
html instead of just code, is this at all possible with some toggles or
perhaps expression injection? Its mostly stuff like fonts colors/sizes/etc,
nothing drastic, but of course its showing up as a bunch of
<this><that>...etc rathern than actual html.
Please let me know either way, or perhaps point me to an example somewhere?
TIA.
andy|||Apologies for the above :(
"Andrzej E. Raczynski" <andy@.aea13.org> wrote in message
news:9CA7AD98-7309-4C21-B000-46E7CDFD429C@.microsoft.com...
> I'm guessing I'm not the first to ask this, but haven't found anything
> either here or via googling around.
> Per the subject, some of our database fields have html code in them that
> we would like to render inside the report (using the table control) as
> actual html instead of just code, is this at all possible with some
> toggles or perhaps expression injection? Its mostly stuff like fonts
> colors/sizes/etc, nothing drastic, but of course its showing up as a bunch
> of <this><that>...etc rathern than actual html.
> Please let me know either way, or perhaps point me to an example
> somewhere?
> TIA.
> andy

Problem with expression window in derived column

Hi,

i am facing problem to dervie one column value from another column using either if and case statements in expression window of dervived columns transformation.

let me give the exapmle. i get 1 column from source system name as "col a" and i want to insert 2 columns into my destination as col A and col B. based on the values of col A i want to derive the values of col B,like if col A value is 0 then col B value is Good else BAD.

Can any one asssit in this regard how to achive it? and is it possible to use IF and CASE statement in this dervived column tranformation?

Sreenivas

Select the Add New column option, call it ColB or whatever, then use an expression like this -

ColA == 0 ? "GOOD" : "BAD"

This uses the conditional operator, as documented in Books Online - http://msdn2.microsoft.com/en-us/library/d38e6890-7338-4ce0-a837-2dbb41823a37(SQL.90).aspx

Logicaly it reads like this -

If ColA Equals 0 Then

Return "GOOD"

Else

Return "BAD"

End If

Problem with Expression for a added field in datasource.

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

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

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

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

Problem with datetime expression

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