City, State expression problem!

I have a report which lists the city and state. The problem is that
the city and state are in one field and were in Upper case. I used the
strconv but I don't know how to make the state portion Upper instead or
Proper.
Right now the output looks like this: Birminham, Al
Any help is appreciated.Try the following:
select replace(ColName,right(ColName,1), Upper(right(ColName,1)))
Good Luck
swtjen01 wrote:
> I have a report which lists the city and state. The problem is that
> the city and state are in one field and were in Upper case. I used the
> strconv but I don't know how to make the state portion Upper instead or
> Proper.
> Right now the output looks like this: Birminham, Al
> Any help is appreciated.

Chr(13) not working as intended

When I use Chr(13) in my textbox expression, it does not produce a carriage return in the text. For example if I use "Test 1" & Chr(13) & "Test2", Test1 and Test2 come in the same line but if I use just Chr(10) in the place of Chr(13), it works as intended. In Crystal reports, Chr(13) does produce a carriage return and the text come in 2 different lines.

Thanks,

Shyam

It is a common issue here and you are right the difference with crystal is apparent.

You have to use a line feed to get an actual line return (10) as the carraige return (13) just feeds the symbol for systems to translate as they feel and the HTML report viewer will not action a (13) whereas the crystal viewer does.

Cheking if report's rendering in excel

Hi,
Is there a way to use an expression to check if the report is in html or
Excel?
For instance, for displaying in html I could want some format, but in Excel
not.
Is there a way to test something like this':
=iif(ReportExcel, "GainsBoro", "White")
The part of ReportExcel, to test if Iâ'm in excel is the one I want to know
(ReportExcel should be an expression, that returns true of false).
Would be also nice to see if Iâ'm in html.
Basically, I want to avoid Actions in the excel file.
Thanks in advance,
Santiago E. ReilWhat you could do is have an extra parameter passed to the report lets say we pass temp.
Depending on if you are rendering in excel or HTML format pass 0 or 1 for the parameter.
Now you can use the value of temp to see if you are rendering in excel or HTML.
>--Original Message--
>Hi,
>Is there a way to use an expression to check if the report is in html or >Excel?
>For instance, for displaying in html I could want some format, but in Excel >not.
>Is there a way to test something like this':
>=3Diif(ReportExcel, "GainsBoro", "White")
>The part of ReportExcel, to test if I=E2?Tm in excel is the one I want to know >(ReportExcel should be an expression, that returns true of false).
>Would be also nice to see if I=E2?Tm in html.
>Basically, I want to avoid Actions in the excel file.
>Thanks in advance,
>Santiago E. Reil
>.
>

checking report expression syntax

Hello!

I have a report that I am writing that was displaying perfectly, until I had to add in group totals and "sum" some of the expressions. When I did that for some reason it over rode the "format percent" part of the expresson and is no longer "formatting as a percent"

Any tips of where I could go to get my syntax in order?

Any help would be appriciated!!

If you can, post the expression that you are using.

One quick idea is that for the format string to work, you need to have your expression return the correct type. For example, if your expression returns a string and you are formatting a float as a percent, it isn't going to work.

|||

This is one of the expressions Im using :

Sum(Val(FormatPercent((Fields!ID30_DaysCNT.Value/Fields!TOTAL_UPB_CNT.Value),0)))

This among many other things in this report are making me crazy!!

Thanks for any input :)

|||

try moving the FormatPercent to the outside of the sum expression:

FormatPercent(Sum(Fields!ID30_DaysCNT.Value/Fields!TOTAL_UPB_CNT.Value))

|||Ok that actually worked....awesome thanks!!|||But wait, what about the Val() that needs to go in there ? how do I do it with that ?|||I thought you said it works now. Is there a reason you need to use the Val function?|||It did work until I got towards the end of the document.....I need the Val because sporatically throughout my report there are NAN and Infinity's ...but sometimes the Zero's do show through ....

checking length and triming the value at ','!

Hi all!

I have a record coming from the database having of F_name,L_name, and M_name being concatenated and separated with comma. In an expression of a textbox control value I would like to check the length of this record and if it is more than 12 only to take the first values till the comma’,’ which appears prior to the 12^th character and truncate the rest. For example:

Record from the database= FirstName,LastName,MiddleName

1) GAUTHIER, JEFFREY, Jane Desired result GAUTHIER, b/c if Last name is added it will be more than 12.

2) Jane, JEFFREY, GAUTHIER Desired result Jane, b/c if Last name and first name are added it will be more than 12.

3) JEFFREY, Job, GAUTHIER Desired result JEFFREY, Job, b/c if First name is added it will be more than 12.

4) JEFFRE, Job, G Desired result JEFFREY, Job, G etc b/c all the values are not more than 12

So would you mind helping me on how the expression looks like?

Thank you in advance!

Try this expression:

=Iif(Fields!name.Value.ToString().Length() > 12, Iif(Fields!name.Value.ToString().Substring(0, 12).LastIndexOf(",") <> -1, Fields!name.Value.ToString().Substring(0, Fields!name.Value.ToString().Substring(0, 12).LastIndexOf(",")), Fields!name.Value.ToString().Substring(0, 12)), Fields!name.Value)

hehe, it's big and ugly but it does what you want. You could use embedded code to make it prettier.

|||

Hi Ryan,

That doesn't work if the three names' total length is less than 12. I just ran it on my name database and some come up as #error#.. Like "Hat, Jo, Ty" ...

pl

|||

Ugh...You're right. Ok, try this instead, add the following embedded code to your report:

Function TrimWithCommas(ByVal name As Object) As String

if (name is Nothing) then
TrimWithCommas = Nothing
end if

Dim nameString as String = name.ToString()
if (nameString.Length() > 12) then
Dim index as Integer = nameString.SubString(0, 12).LastIndexOf(",")
if (index <> -1) then
nameString = nameString.SubString(0, index)
else
nameString = nameString.SubString(0,12)
end if
end if
TrimWithCommas = nameString
End Function

Then use the following expression in the textbox you want the name to appear in:

=Code.TrimWithCommas(Fields!name.Value)

|||

Hi Ryan,

Excellent! That worked on 17000 employees! No errors!

Thanks for the lesson.

pl

|||

Hi,

I've been trying to write this up in the Expression editor, and, if the first 12 bytes of the name does not contain a comma, it gives an error. Here is the code:

=Iif( len(Fields!EMPLOYEE_NAME.Value) > 12,
Iif( instrrev( left( Fields!EMPLOYEE_NAME.Value.ToString, 12 ), ",",12) > 0,
left(Fields!EMPLOYEE_NAME.Value, instrrev( left( Fields!EMPLOYEE_NAME.Value.ToString, 12), ",",12 ) - 1 ) ,
left(Fields!EMPLOYEE_NAME.Value, 12)),
left(Fields!EMPLOYEE_NAME.Value,12))

It seems that the true and false clauses are evaluated every time, and the case where the "instrrev" fails (evaluates to zero), causes the whole condition to die...

I'm just starting to learn. Did I miss something?

Thanks.

Checking for NULL within an Expression

Hi All,

I would like an example of checking for NULL within an expression. I don't know the correct syntax. Checking for IsNull is incorrect so is IS Null. Can anyone help me?

Another C#er!

Try if(IsNothing(Myvalue)) or if(Myvvalue = Nothing)

hth

Helen

|||

Read this post, it should help

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1248764&SiteID=1

Checking for Null Values in a Table/Matrix Cell

What would be an equivalent expression for ISNULL(datafield, 0) for a table/matrix cell? I am using iif( Len().. to find out if there is something in the cell, and displaying zero in the cell if the length of cell item is 0, however am wondering if there is any better/elegant way of doing that?

=iif(fieldname = nothing, 0, fieldname)|||um, wouldn't that be =iif(fieldname = dbnull.value,0,fieldname) ?|||Actually, SSRS converts dbnull to an actual null in the report, so using:

iif(fields!fieldname.value = nothing , 0, fields!fieldname.value)

or

iif(IsNothing(fields!fieldname.value), 0, fields!fieldname.value)

seems to be the MSFT suggested way to trap for nulls.

iif(fields!fieldname.value = dbnull.value, 0, fields!fieldname.value) won't work. You might be able to get away with iif(fields!fieldname.value is system.dbnull.value, 0, fields!fieldname.value), but people have reported some issues with using System.DBNull and all the MSFT guys say to use "nothing".

Checking for NULL parameters in an expression

I have a report parameter that is NULLable. If it's NULL, I'd like a label on my report to say that it's null. If it's not NULL, the label will simply show the value. How do I check, in an expression, for a NULL parameter?IF whatever IS System.DBNull.Value then