Formatting text

I need to format a text in a text box to show a text field truncated.
In other terms, I must show only 40 characters of a very long string.
Do I use the statement "format"?
How can I solve this issue?
Many thanksI would do it in the SQL statement. As in left(fieldname, 40)
Richard
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:2550C968-0324-4FC8-A50D-67645F99C4BD@.microsoft.com...
>I need to format a text in a text box to show a text field truncated.
> In other terms, I must show only 40 characters of a very long string.
> Do I use the statement "format"?
> How can I solve this issue?
> Many thanks|||Or in the report itself:
Field => expression => LEFT(strVariable, 40)|||OK! Many thanks
"Vyv" wrote:
> Or in the report itself:
> Field => expression => LEFT(strVariable, 40)
>

Formatting question

Hi All, I must be overlooking something obvious, but I could reallt use some
guidance.
I can't find or figure out how to format a string in my report. The
datacolumn is just a string containing numbers like 123456789 and I need to
display it in the report as 123-45-6789 .
From the books on-line I figured I should set the format property of the
textbox to ###-##-#### but that does not seem to work. Can someone please
point me in the right direction?
Thanks
Ed###-##-#### works fine - and i get the right result.
Make sure you select the Custom option button , not the standard one.
and make sure you have a "=" in the value box.
"Ed Richard" wrote:
> Hi All, I must be overlooking something obvious, but I could reallt use some
> guidance.
> I can't find or figure out how to format a string in my report. The
> datacolumn is just a string containing numbers like 123456789 and I need to
> display it in the report as 123-45-6789 .
> From the books on-line I figured I should set the format property of the
> textbox to ###-##-#### but that does not seem to work. Can someone please
> point me in the right direction?
> Thanks
> Ed
>
>|||Ed,
You need to convert the string to a number.
Try =CDec("12345678") in the value then ###-##-### in the format
property.
Chris
Ramani wrote:
> ###-##-#### works fine - and i get the right result.
> Make sure you select the Custom option button , not the standard one.
> and make sure you have a "=" in the value box.
> "Ed Richard" wrote:
> > Hi All, I must be overlooking something obvious, but I could reallt
> > use some guidance.
> >
> > I can't find or figure out how to format a string in my report. The
> > datacolumn is just a string containing numbers like 123456789 and I
> > need to display it in the report as 123-45-6789 .
> > From the books on-line I figured I should set the format property
> > of the textbox to ###-##-#### but that does not seem to work. Can
> > someone please point me in the right direction?
> >
> > Thanks
> > Ed
> >
> >
> >|||That was it Chris, thanks very much!
Ed
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:OMk8dCXgFHA.2444@.tk2msftngp13.phx.gbl...
> Ed,
> You need to convert the string to a number.
> Try =CDec("12345678") in the value then ###-##-### in the format
> property.
> Chris
>
> Ramani wrote:
>> ###-##-#### works fine - and i get the right result.
>> Make sure you select the Custom option button , not the standard one.
>> and make sure you have a "=" in the value box.
>> "Ed Richard" wrote:
>> > Hi All, I must be overlooking something obvious, but I could reallt
>> > use some guidance.
>> >
>> > I can't find or figure out how to format a string in my report. The
>> > datacolumn is just a string containing numbers like 123456789 and I
>> > need to display it in the report as 123-45-6789 .
>> > From the books on-line I figured I should set the format property
>> > of the textbox to ###-##-#### but that does not seem to work. Can
>> > someone please point me in the right direction?
>> >
>> > Thanks
>> > Ed
>> >
>> >
>> >
>

Formatting Issues

Hi all,
Im using the custom code to format the string returned by the
stored procedure in the reports.
Public Function ResourceCategory(ByVal stat As String) As String
Dim str As String
Dim arr As String()
arr = stat.Split(",")
Dim i As Integer
For i = 0 To arr.Length -1
If arr(i) = "'PART'" Then
str = str & "Partner/"
End If
If arr(i) = "'ADM'" Then
str = str & "Administration/"
End If
If arr(i) = "'DIR'" Then
str = str & "Director/"
End If
If arr(i) = "'ENG'" Then
str = str & "Engineer/"
End If
if arr(i) = "'PM'" Then
str = str & "Project Manager/"
End If
if arr(i) = "'SE'" Then
str = str & "Senior Engineer/"
End If
if arr(i) = "'VALGRE'" Then
str = str & "Validation Engineer Greenville/"
End If
if arr(i) = "'VALSEN'" Then
str = str & "Senior Validation Engineer SFO/"
End If
if arr(i) = "'VALSFO'" Then
str = str & "Validation Engineer/"
End If
if arr(i) = "All" Then
str = str & "All/"
End If
Next
str = str.Remove(str.LastIndexOf("/"), 1)
Return str
End Function
But if the condition fails the report prints "#Error"
Can I in any way avoid this'
hanks In Advance,Provide an Else condition for anything that does NOT satisfy the other
conditions like "NA"... But if the function fails, you can not override the
error...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:8871344E-22EB-45C9-BF69-6A2D68E38093@.microsoft.com...
> Hi all,
> Im using the custom code to format the string returned by the
> stored procedure in the reports.
> Public Function ResourceCategory(ByVal stat As String) As String
> Dim str As String
> Dim arr As String()
> arr = stat.Split(",")
> Dim i As Integer
> For i = 0 To arr.Length -1
> If arr(i) = "'PART'" Then
> str = str & "Partner/"
> End If
> If arr(i) = "'ADM'" Then
> str = str & "Administration/"
> End If
> If arr(i) = "'DIR'" Then
> str = str & "Director/"
> End If
> If arr(i) = "'ENG'" Then
> str = str & "Engineer/"
> End If
> if arr(i) = "'PM'" Then
> str = str & "Project Manager/"
> End If
> if arr(i) = "'SE'" Then
> str = str & "Senior Engineer/"
> End If
> if arr(i) = "'VALGRE'" Then
> str = str & "Validation Engineer Greenville/"
> End If
> if arr(i) = "'VALSEN'" Then
> str = str & "Senior Validation Engineer SFO/"
> End If
> if arr(i) = "'VALSFO'" Then
> str = str & "Validation Engineer/"
> End If
> if arr(i) = "All" Then
> str = str & "All/"
> End If
> Next
> str = str.Remove(str.LastIndexOf("/"), 1)
> Return str
> End Function
> But if the condition fails the report prints "#Error"
> Can I in any way avoid this'
> hanks In Advance,|||Thanks Wayne ,
this is wat im trying to accomplish.
Hope u will help me out in this'
Im displaying the parameters selected by the user in a textbox.
The selection mode is multiple.
Im passing the code selected to the stored procedure,so it displays the
code.
if i give,
Parameters!Category.Value
So i wrote the code which i posted previously,
IS there a way i can load the description from the database '
Can i connect to the Database from the Code to achieve this'
"Wayne Snyder" wrote:
> Provide an Else condition for anything that does NOT satisfy the other
> conditions like "NA"... But if the function fails, you can not override the
> error...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:8871344E-22EB-45C9-BF69-6A2D68E38093@.microsoft.com...
> > Hi all,
> > Im using the custom code to format the string returned by the
> > stored procedure in the reports.
> >
> > Public Function ResourceCategory(ByVal stat As String) As String
> > Dim str As String
> > Dim arr As String()
> > arr = stat.Split(",")
> > Dim i As Integer
> > For i = 0 To arr.Length -1
> > If arr(i) = "'PART'" Then
> > str = str & "Partner/"
> > End If
> > If arr(i) = "'ADM'" Then
> > str = str & "Administration/"
> > End If
> > If arr(i) = "'DIR'" Then
> > str = str & "Director/"
> > End If
> > If arr(i) = "'ENG'" Then
> > str = str & "Engineer/"
> > End If
> > if arr(i) = "'PM'" Then
> > str = str & "Project Manager/"
> > End If
> > if arr(i) = "'SE'" Then
> > str = str & "Senior Engineer/"
> > End If
> > if arr(i) = "'VALGRE'" Then
> > str = str & "Validation Engineer Greenville/"
> > End If
> > if arr(i) = "'VALSEN'" Then
> > str = str & "Senior Validation Engineer SFO/"
> > End If
> > if arr(i) = "'VALSFO'" Then
> > str = str & "Validation Engineer/"
> > End If
> > if arr(i) = "All" Then
> > str = str & "All/"
> > End If
> > Next
> > str = str.Remove(str.LastIndexOf("/"), 1)
> > Return str
> > End Function
> >
> > But if the condition fails the report prints "#Error"
> > Can I in any way avoid this'
> > hanks In Advance,
>

Formatting a string with color/bold?

I have a string in which I am combining several fields. Is there a way to add formatting to the string as well

="Baud: " & Fields!BAUDRATE.Value & " DL Status: " & Fields!COMMSTATUS.Value

Example, I want my fields to appear in bold and string in normal.. I can't do this in an individual textbox due to space constraints

I think you are suggesting mixed formatting within a textbox which is not possible. When applying a format style it applies to the entire contents of a textbox.

Formatting a string from the code

I have a textbox that displays the amout of free disk space in megabytes.
I'm working on converting it to gigabytes and if it's less that one gig, I
want to display the string "Critical" bold and in red. Can this be done?
Thanks.This can certainly be done. Where do you want to display the text
'Critical'? If you have another textbox you would use an expression to
determine the value of that checkbox. for example if you were getting the
value from a dataset you would have a similar code to the following in the
expression editor:
=IIf(Fields!Number_Of_Megs < 1024,"","Critical")
Hope this helps
"chalexan" wrote:
> I have a textbox that displays the amout of free disk space in megabytes.
> I'm working on converting it to gigabytes and if it's less that one gig, I
> want to display the string "Critical" bold and in red. Can this be done?
> Thanks.|||Bret or anyone else,
Thanks for the reply. Any suggestions on changing the font's color to red
if it's critical and making it bold?
--Chad|||You will have to do this from the report designer. You can select the
textbox that the text is in and go to the textbox properties. (right click
and properties) From here you can select the format for this text box... A
nice feature is using the Expression Editor to select the font color. This
will allow you to use an IIF() statement to have the font red or black. You
can allow do this with the font weight.
Example of formatting font color in expression editor: (you would put this
in the format expression for the font-color in the designer)
=IIf(Fields!Number_Of_Megs < 1024,"black","red")
"chalexan" wrote:
> Bret or anyone else,
> Thanks for the reply. Any suggestions on changing the font's color to red
> if it's critical and making it bold?
> --Chad|||Bret,
Wow! I thought that the "IIf(" was a typo. That functionality can be very
useful. Thank you so much for explaining that to me.
--Chad

Formatting a SSN in Reporting Services

I've got a report of peoples' SSN's and Names. The SSN is stored as a string
(nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
IOW, with hyphens.
In my report, the expression to display the data looks like:
=Fields!SSN.Value
When I try to format it like this...
=Format(Fields!SSN.Value,"###-##-####")
...I see only puond signs and hyphens, with no numbers.
So I convert the string to a decimal like this...
=Format(cdec(Fields!SSN.Value),"###-##-####")
...and it displays correctly in my report. But to my mind, this isn't an
elegant solution. Isn't there some way I could format the string, itself,
without having to convert it to a decimal?
TIA,
__BirmAn alternative is to use string manipulation functions. E.g.
=Left(Fields!SSN.Value, 3) & "-" & Mid(Fields!SSN.Value, 4, 2) & " - "
Right(Fields!SSN.Value, 4)
More information about these functions is available on MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctLeft.asp
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctMid.asp
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctRight.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
"Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> I've got a report of peoples' SSN's and Names. The SSN is stored as a
> string
> (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> IOW, with hyphens.
> In my report, the expression to display the data looks like:
> =Fields!SSN.Value
> When I try to format it like this...
> =Format(Fields!SSN.Value,"###-##-####")
> ...I see only puond signs and hyphens, with no numbers.
>
> So I convert the string to a decimal like this...
> =Format(cdec(Fields!SSN.Value),"###-##-####")
> ...and it displays correctly in my report. But to my mind, this isn't an
> elegant solution. Isn't there some way I could format the string, itself,
> without having to convert it to a decimal?
> TIA,
> __Birm
>|||You could use the substring function in your query if it is a shared
dataset, that way you only have to do it once.
"Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> I've got a report of peoples' SSN's and Names. The SSN is stored as a
> string
> (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> IOW, with hyphens.
> In my report, the expression to display the data looks like:
> =Fields!SSN.Value
> When I try to format it like this...
> =Format(Fields!SSN.Value,"###-##-####")
> ...I see only puond signs and hyphens, with no numbers.
>
> So I convert the string to a decimal like this...
> =Format(cdec(Fields!SSN.Value),"###-##-####")
> ...and it displays correctly in my report. But to my mind, this isn't an
> elegant solution. Isn't there some way I could format the string, itself,
> without having to convert it to a decimal?
> TIA,
> __Birm
>|||Robert,
That worked like a champ. (That slapping sound you hear BTW, is my palm
hitting my forehead.) I tried a similar method, using Substring in stead of
the Mid() function, and -- here's my biggest mistake -- using a plus sign as
the concatenator instead of using the ampersand. To much C# in my immediate
past, I guess. <g>
Thanks a lot for the pointer. I really appreciate your taking the time.
__Birm
"Robert Bruckner [MSFT]" wrote:
> An alternative is to use string manipulation functions. E.g.
> =Left(Fields!SSN.Value, 3) & "-" & Mid(Fields!SSN.Value, 4, 2) & " - "
> Right(Fields!SSN.Value, 4)
> More information about these functions is available on MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctLeft.asp
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctMid.asp
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctRight.asp
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
> news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> > I've got a report of peoples' SSN's and Names. The SSN is stored as a
> > string
> > (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> > IOW, with hyphens.
> >
> > In my report, the expression to display the data looks like:
> >
> > =Fields!SSN.Value
> >
> > When I try to format it like this...
> >
> > =Format(Fields!SSN.Value,"###-##-####")
> >
> > ...I see only puond signs and hyphens, with no numbers.
> >
> >
> > So I convert the string to a decimal like this...
> >
> > =Format(cdec(Fields!SSN.Value),"###-##-####")
> >
> > ...and it displays correctly in my report. But to my mind, this isn't an
> > elegant solution. Isn't there some way I could format the string, itself,
> > without having to convert it to a decimal?
> >
> > TIA,
> >
> > __Birm
> >
>
>|||MPF,
Yup...and that would be the elegant solution, wouldn't it? Hmmm...it's
not a shared dataset, but still...mebbe I'm going to do exactly that. Just
for yucks and education if nothing else.
Thanks!
__Birm
"MPF" wrote:
> You could use the substring function in your query if it is a shared
> dataset, that way you only have to do it once.
>
> "Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
> news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> > I've got a report of peoples' SSN's and Names. The SSN is stored as a
> > string
> > (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> > IOW, with hyphens.
> >
> > In my report, the expression to display the data looks like:
> >
> > =Fields!SSN.Value
> >
> > When I try to format it like this...
> >
> > =Format(Fields!SSN.Value,"###-##-####")
> >
> > ...I see only puond signs and hyphens, with no numbers.
> >
> >
> > So I convert the string to a decimal like this...
> >
> > =Format(cdec(Fields!SSN.Value),"###-##-####")
> >
> > ...and it displays correctly in my report. But to my mind, this isn't an
> > elegant solution. Isn't there some way I could format the string, itself,
> > without having to convert it to a decimal?
> >
> > TIA,
> >
> > __Birm
> >
>
>

Formatting a portion of a String in a TextBox

I have a report that shows a string containing "wildcard" values. I
would like to be able to change the formatting (Bold, Italicized, Red)
for the wildcard values within the string. I've been examining the
conditional formatting functionality, but it seems only to apply to the
whole textbox value not a portion of it.
Has anyone tackled this before?
TIA,
EricI had similar issue when combining string and number field and solved it with
Format function:
=Fields!industry.Value & " (" & Format(Fields!marketPct.Value, "#0.0%") & ")"
You can probably something similar for color.
"Ferd Biffle" wrote:
> I have a report that shows a string containing "wildcard" values. I
> would like to be able to change the formatting (Bold, Italicized, Red)
> for the wildcard values within the string. I've been examining the
> conditional formatting functionality, but it seems only to apply to the
> whole textbox value not a portion of it.
> Has anyone tackled this before?
> TIA,
> Eric
>

Formatting a Javascript string for SQL - Help please

I am well stuck in the mud right now.

Here is the problem. I am trying to write this: strLink2 = "<a href='JavaScript:OpenFile(" & strFileName & ")'>" & strFileName & "</a>" to SQL and then return it to a datagrid when I want it.

Easy enough, and as such all is well except for the fact that the Javascript doesn't work. In the grid it gets written as JavaScript:OpenFile(myfile). I need to add the single quotes around the file name so it writes JavaScript:Open('myfile').

I now know many ways that do not work. Any help would be greatly appreciated.Replace ' with two of them to cancel out.|||Thanks for the help, but it doesn't work. Atleast not in VStudio. Here is what does work right now:
strLink2 = "<a href='JavaScript:OpenFile(" & strFileName & ")'>" & strFileName & "</a>"

Before I put the string into SQL i need the strFileName. That works currently. But I need to add a ' to both sides of the strFileName in order for javascript to do it's thing when I pull this out of SQL and into a datagrid.

If I put (" & ' ' strFileName ' ' & ") the line is really just comjmented out.
note: the space between apotsrophes is for clarity here, there is no space when coding.

If you open up Visual Studio and paste this in and start adding ' you will see the problem.

Any ideas.|||Try ('" & strFileName & "')

Notice the single quotes are between the parentheses and the double quotes in each case.|||Thanks for your help.

Unfortunately the problem persists. While the string gets written to SQL the way I want. SQL sees the ' as an escape character and thus the resulting javascript comes out into the datagrid as: JavaScript:Open(

Instead of JavaScript:Open('myfile.xyz')

This is very frustrating. Is this so unusual a problem?|||Oops.. I should add that the solution worked...partly. So a big thanks for that.

The problem now is this.
I need to get rid of the ' that appear in the href. 'JavaScr... and )'>
strLink2 = "<a href='JavaScript:OpenFile(" & strFileName & ")'>" & strFileName & "</a>"

They need to be " instead of '.

Getitng closer

formating string to time

Hello,

I have the following question.

I have a report where i do a lot of time calculation

in one colomn i have values in seconds.

for example i have a total of 246 seconds

how can i format this to view as 00:04:06 or even 4 mins 6 secs

Vincent

Hello Vincent,

Try putting this in your textbox's expression...

=cStr(Floor(Fields!TotalSeconds.Value / 60)) + " mins "
+ cStr(Fields!TotalSeconds.Value - Floor(Fields!TotalSeconds.Value / 60) * 60) + " secs"

Hope this helps.

Jarret

|||

Hello Jarret,

This works great.

Thank you.

Vincent

|||

Hi,

How can i add the hours also to it ?

I'm still really quite new to all this stuff, but i'm working on it.

Thanks

Vincent

|||

For hours, try this...

=cStr(Floor(Fields!TotalSeconds.Value / 3600)) + " hours "
+ cStr(Floor(Fields!TotalSeconds.Value / 60) - Floor(Fields!TotalSeconds.Value / 3600) * 60) + " mins "
+ cStr(Fields!TotalSeconds.Value - Floor(Fields!TotalSeconds.Value / 60) * 60) + " secs"

Jarret

formating integer

hello. A question please. I am new on Sqlserver. Could anyone say me how must I format an integer to string. example

I have:

0001 (integer field of 4 positions) and I'd want to see it "0001" (string)

Thanks...

It seems like there is a better way of doing this, but one way is something like:

select right('000'+convert(varchar(4), 1), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0001

|||

Thanks. The problem is that I gave an example for the first field. (0001). But It could be 0010...

|||

What am I missing?

select right('000'+convert(varchar(4), 10), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0010

|||

Waldrop wrote:

What am I missing?

select right('000'+convert(varchar(4), 10), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0010

Nothing. That works fine.

SELECT right('0000' + cast([your_column] as varchar(4)),4)
FROM your_table
|||

I'm NEW !!!! Sorry !!!!. Another question please. I must put your statement in a string var. How can I do the '000' in my string var ?

Thanks...

|||

declare @.myString varchar(4)

set @.myString = right('000'+convert(varchar(4), 7), 4)

set @.myString = right('0000' + cast([your_column] as varchar(4)),4)

set @.myString = '0007'

|||

I'm so sorry. I don't understand. I forgot to say that the statement goes in an store procedure. My problem is how do I put the (') in a string var.

Thanks...

|||

An example of adding quote characters into the string variable:

declare @.myString varchar(6)
declare @.quoteChar char(1)

set @.myString = '''0007''' -- Loads '0007' (including quotes) into @.myString variable
set @.quoteChar = char(39) -- Loads a quote char ' into @.quoteChar variable
set @.quoteChar = '''' -- Loads a quote char ' into @.quoteChar variable

select @.myString as [@.myString],
@.quoteChar as [@.quoteChar]

-- - Output: -

-- @.myString @.quoteChar
-- -
-- '0007' '

declare @.anotherString varchar(4)

set @.anotherString = '0007' -- Loads '0007' (without quotes) into @.anotherString variable
set @.myString = char(39) + @.anotherString + '''' -- concatenates quote characters around '0007'

select @.anotherString as [@.anotherString],
@.myString as [@.myString]

-- - Output: -

-- @.anotherString @.myString
-- --
-- 0007 '0007'

|||Thanks. It works !!!!!

formating in crystal reports

How to suppress the display of a template field or the text field in crystal report on its value being NULL or empty string.

This works only for numbers, it was not possible for me to do so for strings.

Can anybody help me in this regard.

This is a forum for discussion of SQL Server Reporting Services, not Crystal Reports...you might get lucky, but I don't think anyone will be able to answer your question here. Try the businessobjects website and see if they have some sort of public forum system -- I bet you'll have better responses there?

Formating for a phone number

I have a report in which I'd like to display a phone number.
Currrently, in SQL Server, the phone numbers appear as a string a numbers eg
"4255551212"
I'd like to display them with area code, then number such as "(425) 555-1212"
I've tried playing aroud with formating options and I'm stumped...
Can someone lend a hand?Hi, ReportDude,
Try
=Format(CDbl("4255551212"), "(000) 000-0000")
HTH,
Andrei.
"ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
news:2C7B4CA4-2C40-4823-AF31-6E703F308FB4@.microsoft.com...
> I have a report in which I'd like to display a phone number.
> Currrently, in SQL Server, the phone numbers appear as a string a numbers
eg
> "4255551212"
> I'd like to display them with area code, then number such as "(425)
555-1212"
> I've tried playing aroud with formating options and I'm stumped...
> Can someone lend a hand?|||Create a function i.e. and put it in the code tab section under report
properties.
public function FormatNumber(StrPhone as String) as String
dim sRetString as String = ""
if IsNothing(StrPhone)
sRetString = ""
else
sRetString = "(" & StrPhone.SubString(0, 2) & ") " &
StrPhone.SubString(3,3) & "-" & StrPhone.SubString(6,4)
end if
return sRetString
end function
Then in your grid, lets say... for the expression of the text control...
Code.FormatNumber(Fields!Phone.Value)
Now the code I just gave you might have bugs in it - as I am in a hurry :)
So test it and correct where needed - but you get the idea.
=-Chris
"ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
news:2C7B4CA4-2C40-4823-AF31-6E703F308FB4@.microsoft.com...
>I have a report in which I'd like to display a phone number.
> Currrently, in SQL Server, the phone numbers appear as a string a numbers
> eg
> "4255551212"
> I'd like to display them with area code, then number such as "(425)
> 555-1212"
> I've tried playing aroud with formating options and I'm stumped...
> Can someone lend a hand?|||I get an error that says "There is an error on line 5 of custom code:
Expression Expected"
I don't understand this - the code does have an expression...
any ideas?
"Christopher Conner" wrote:
> Create a function i.e. and put it in the code tab section under report
> properties.
> public function FormatNumber(StrPhone as String) as String
> dim sRetString as String = ""
> if IsNothing(StrPhone)
> sRetString = ""
> else
> sRetString = "(" & StrPhone.SubString(0, 2) & ") " &
> StrPhone.SubString(3,3) & "-" & StrPhone.SubString(6,4)
> end if
> return sRetString
> end function
> Then in your grid, lets say... for the expression of the text control...
> Code.FormatNumber(Fields!Phone.Value)
> Now the code I just gave you might have bugs in it - as I am in a hurry :)
> So test it and correct where needed - but you get the idea.
> =-Chris
> "ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
> news:2C7B4CA4-2C40-4823-AF31-6E703F308FB4@.microsoft.com...
> >I have a report in which I'd like to display a phone number.
> >
> > Currrently, in SQL Server, the phone numbers appear as a string a numbers
> > eg
> > "4255551212"
> >
> > I'd like to display them with area code, then number such as "(425)
> > 555-1212"
> >
> > I've tried playing aroud with formating options and I'm stumped...
> > Can someone lend a hand?
>
>|||That is because of a line break in the code... I have recopied it from dev
studio, copy this and paste it into the code block between the function name
and end function...
Dim sRetString As String = ""
If IsNothing(StrPhone) Then
sRetString = ""
Else
sRetString = "(" & StrPhone.Substring(0, 2) & ") " & StrPhone.Substring(3,
3) & "-" & StrPhone.Substring(6, 4)
End If
Return sRetString
"ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
news:B66A1226-50F4-484C-B203-8D047E97F523@.microsoft.com...
>I get an error that says "There is an error on line 5 of custom code:
> Expression Expected"
> I don't understand this - the code does have an expression...
> any ideas?
> "Christopher Conner" wrote:
>> Create a function i.e. and put it in the code tab section under report
>> properties.
>> public function FormatNumber(StrPhone as String) as String
>> dim sRetString as String = ""
>> if IsNothing(StrPhone)
>> sRetString = ""
>> else
>> sRetString = "(" & StrPhone.SubString(0, 2) & ") " &
>> StrPhone.SubString(3,3) & "-" & StrPhone.SubString(6,4)
>> end if
>> return sRetString
>> end function
>> Then in your grid, lets say... for the expression of the text control...
>> Code.FormatNumber(Fields!Phone.Value)
>> Now the code I just gave you might have bugs in it - as I am in a hurry
>> :)
>> So test it and correct where needed - but you get the idea.
>> =-Chris
>> "ReportDude" <ReportDude@.discussions.microsoft.com> wrote in message
>> news:2C7B4CA4-2C40-4823-AF31-6E703F308FB4@.microsoft.com...
>> >I have a report in which I'd like to display a phone number.
>> >
>> > Currrently, in SQL Server, the phone numbers appear as a string a
>> > numbers
>> > eg
>> > "4255551212"
>> >
>> > I'd like to display them with area code, then number such as "(425)
>> > 555-1212"
>> >
>> > I've tried playing aroud with formating options and I'm stumped...
>> > Can someone lend a hand?
>>

Formating a numeric string, that is sometimes text

The old string didn't move up when I replied to it, so copied the information
into this new question
----
OK -- this works to a point. I have real string data in my field that I want
to display, if the field is not "isnumeric". So, here is the code I am using:
=iif(isnumeric(Fields!Q4Amt.Value),
Format(convert.ToDouble(Fields!Q4Amt.Value),
"$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
Every time the N/A is supposed to be displayed, I get an error message.
Thanks for any and all help
"Teo Lachev [MVP]" wrote:
> =Iif (Fields!phone.Value Is Nothing, Nothing,
> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.' ####"))
>
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
> > okay great. I used the first example. Thanks!
> > Now i have discovered that there are some rows without phone numbers.
> > How do I do an IIF statement, that will return the formatted phone number
> > and not available when there is no phone number?
> >
> > "Teo Lachev [MVP]" wrote:
> >
> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
> >> ####")
> >>
> >> or use a regular expression, sth like
> >>
> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
> >>
> >> --
> >> HTH,
> >> ---
> >> Teo Lachev, MVP, MCSD, MCT
> >> "Microsoft Reporting Services in Action"
> >> "Applied Microsoft Analysis Services 2005"
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> >> > how can i format a phone number to be displayed as follows in the
> >> > report
> >> > (678) 444-4444
> >> >
> >> > Thanks!
> >>
> >>
> >>
>
>
>This will happen because of the nature of IIF. IIF calculates both the true
and false values before choosing which one to display. So it actually is
failing because in the background it trys to convert the string to a double
and return the value. Writing Custom Code to take advantage of the real IF
function is a way around this.
If you know VB its pretty simple.
report->report properties-> code tab->
Public Function checkstr(ByVal testval As String)
IF Char.IsNumber(testval, 0) THEN
return CDbl(testval)
ELSE
return "N/A"
END IF
End Function
Then apply the formatting in the textbox you want N/A Displayed in and have
your text box state the following
=Code.checkstr(Fields!Q4Amt.Value)
-Michael Abair
Programmer Analyst
Chicos FAS Inc.
"msflinx" <msflinx@.discussions.microsoft.com> wrote in message
news:7F943183-B6BD-46CF-9CAF-AFF73834DAE5@.microsoft.com...
> The old string didn't move up when I replied to it, so copied the
> information
> into this new question
> ----
> OK -- this works to a point. I have real string data in my field that I
> want
> to display, if the field is not "isnumeric". So, here is the code I am
> using:
> =iif(isnumeric(Fields!Q4Amt.Value),
> Format(convert.ToDouble(Fields!Q4Amt.Value),
> "$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
> Every time the N/A is supposed to be displayed, I get an error message.
> Thanks for any and all help
> "Teo Lachev [MVP]" wrote:
>> =Iif (Fields!phone.Value Is Nothing, Nothing,
>> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.'
>> ####"))
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
>> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
>> > okay great. I used the first example. Thanks!
>> > Now i have discovered that there are some rows without phone numbers.
>> > How do I do an IIF statement, that will return the formatted phone
>> > number
>> > and not available when there is no phone number?
>> >
>> > "Teo Lachev [MVP]" wrote:
>> >
>> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
>> >> ####")
>> >>
>> >> or use a regular expression, sth like
>> >>
>> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
>> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
>> >>
>> >> --
>> >> HTH,
>> >> ---
>> >> Teo Lachev, MVP, MCSD, MCT
>> >> "Microsoft Reporting Services in Action"
>> >> "Applied Microsoft Analysis Services 2005"
>> >> Home page and blog: http://www.prologika.com/
>> >> ---
>> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
>> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
>> >> > how can i format a phone number to be displayed as follows in the
>> >> > report
>> >> > (678) 444-4444
>> >> >
>> >> > Thanks!
>> >>
>> >>
>> >>
>>
>|||I like your solution better than mine Michael... kudos! :)
=-Chris
"Michael Abair" <michael.abair@.chicos.com> wrote in message
news:OTxLAxH9GHA.4224@.TK2MSFTNGP02.phx.gbl...
> This will happen because of the nature of IIF. IIF calculates both the
> true and false values before choosing which one to display. So it
> actually is failing because in the background it trys to convert the
> string to a double and return the value. Writing Custom Code to take
> advantage of the real IF function is a way around this.
> If you know VB its pretty simple.
> report->report properties-> code tab->
>
> Public Function checkstr(ByVal testval As String)
> IF Char.IsNumber(testval, 0) THEN
> return CDbl(testval)
> ELSE
> return "N/A"
> END IF
> End Function
> Then apply the formatting in the textbox you want N/A Displayed in and
> have your text box state the following
> =Code.checkstr(Fields!Q4Amt.Value)
> -Michael Abair
> Programmer Analyst
> Chicos FAS Inc.
>
> "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
> news:7F943183-B6BD-46CF-9CAF-AFF73834DAE5@.microsoft.com...
>> The old string didn't move up when I replied to it, so copied the
>> information
>> into this new question
>> ----
>> OK -- this works to a point. I have real string data in my field that I
>> want
>> to display, if the field is not "isnumeric". So, here is the code I am
>> using:
>> =iif(isnumeric(Fields!Q4Amt.Value),
>> Format(convert.ToDouble(Fields!Q4Amt.Value),
>> "$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
>> Every time the N/A is supposed to be displayed, I get an error message.
>> Thanks for any and all help
>> "Teo Lachev [MVP]" wrote:
>> =Iif (Fields!phone.Value Is Nothing, Nothing,
>> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.'
>> ####"))
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
>> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
>> > okay great. I used the first example. Thanks!
>> > Now i have discovered that there are some rows without phone numbers.
>> > How do I do an IIF statement, that will return the formatted phone
>> > number
>> > and not available when there is no phone number?
>> >
>> > "Teo Lachev [MVP]" wrote:
>> >
>> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
>> >> ####")
>> >>
>> >> or use a regular expression, sth like
>> >>
>> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
>> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
>> >>
>> >> --
>> >> HTH,
>> >> ---
>> >> Teo Lachev, MVP, MCSD, MCT
>> >> "Microsoft Reporting Services in Action"
>> >> "Applied Microsoft Analysis Services 2005"
>> >> Home page and blog: http://www.prologika.com/
>> >> ---
>> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
>> >> > how can i format a phone number to be displayed as follows in the
>> >> > report
>> >> > (678) 444-4444
>> >> >
>> >> > Thanks!
>> >>
>> >>
>> >>
>>
>>
>|||Thank you Michael. Didn't quite do to trick, but put me in the right
direction.
This is the code I ended up with in the Report Property's Code Tab:
Public Function NumericStringToCurrencyString(ByVal strPhrase as string) as
string
if isnumeric(strPhrase) then
NumericStringToCurrencyString =format(cdec(strPhrase),"$###,###,###.##;($###,###,###.##);$0.00")
else
NumericStringToCurrencyString = strPhrase
end if
end function
Then I called it like you wrote in the field and Viola!!! It worked.
Thank you ever so much.
"Michael Abair" wrote:
> This will happen because of the nature of IIF. IIF calculates both the true
> and false values before choosing which one to display. So it actually is
> failing because in the background it trys to convert the string to a double
> and return the value. Writing Custom Code to take advantage of the real IF
> function is a way around this.
> If you know VB its pretty simple.
> report->report properties-> code tab->
>
> Public Function checkstr(ByVal testval As String)
> IF Char.IsNumber(testval, 0) THEN
> return CDbl(testval)
> ELSE
> return "N/A"
> END IF
> End Function
> Then apply the formatting in the textbox you want N/A Displayed in and have
> your text box state the following
> =Code.checkstr(Fields!Q4Amt.Value)
> -Michael Abair
> Programmer Analyst
> Chicos FAS Inc.
>
> "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
> news:7F943183-B6BD-46CF-9CAF-AFF73834DAE5@.microsoft.com...
> > The old string didn't move up when I replied to it, so copied the
> > information
> > into this new question
> > ----
> >
> > OK -- this works to a point. I have real string data in my field that I
> > want
> > to display, if the field is not "isnumeric". So, here is the code I am
> > using:
> >
> > =iif(isnumeric(Fields!Q4Amt.Value),
> > Format(convert.ToDouble(Fields!Q4Amt.Value),
> > "$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
> >
> > Every time the N/A is supposed to be displayed, I get an error message.
> >
> > Thanks for any and all help
> >
> > "Teo Lachev [MVP]" wrote:
> >
> >> =Iif (Fields!phone.Value Is Nothing, Nothing,
> >> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.'
> >> ####"))
> >>
> >> --
> >> HTH,
> >> ---
> >> Teo Lachev, MVP, MCSD, MCT
> >> "Microsoft Reporting Services in Action"
> >> "Applied Microsoft Analysis Services 2005"
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> >> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
> >> > okay great. I used the first example. Thanks!
> >> > Now i have discovered that there are some rows without phone numbers.
> >> > How do I do an IIF statement, that will return the formatted phone
> >> > number
> >> > and not available when there is no phone number?
> >> >
> >> > "Teo Lachev [MVP]" wrote:
> >> >
> >> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
> >> >> ####")
> >> >>
> >> >> or use a regular expression, sth like
> >> >>
> >> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
> >> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
> >> >>
> >> >> --
> >> >> HTH,
> >> >> ---
> >> >> Teo Lachev, MVP, MCSD, MCT
> >> >> "Microsoft Reporting Services in Action"
> >> >> "Applied Microsoft Analysis Services 2005"
> >> >> Home page and blog: http://www.prologika.com/
> >> >> ---
> >> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
> >> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
> >> >> > how can i format a phone number to be displayed as follows in the
> >> >> > report
> >> >> > (678) 444-4444
> >> >> >
> >> >> > Thanks!
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >
>
>|||Much Appreciated!
"Chris Conner" <Chris.Conner@.NOSPAMPolarisLibrary.com> wrote in message
news:O0$$x0H9GHA.3264@.TK2MSFTNGP04.phx.gbl...
>I like your solution better than mine Michael... kudos! :)
> =-Chris
> "Michael Abair" <michael.abair@.chicos.com> wrote in message
> news:OTxLAxH9GHA.4224@.TK2MSFTNGP02.phx.gbl...
>> This will happen because of the nature of IIF. IIF calculates both the
>> true and false values before choosing which one to display. So it
>> actually is failing because in the background it trys to convert the
>> string to a double and return the value. Writing Custom Code to take
>> advantage of the real IF function is a way around this.
>> If you know VB its pretty simple.
>> report->report properties-> code tab->
>>
>> Public Function checkstr(ByVal testval As String)
>> IF Char.IsNumber(testval, 0) THEN
>> return CDbl(testval)
>> ELSE
>> return "N/A"
>> END IF
>> End Function
>> Then apply the formatting in the textbox you want N/A Displayed in and
>> have your text box state the following
>> =Code.checkstr(Fields!Q4Amt.Value)
>> -Michael Abair
>> Programmer Analyst
>> Chicos FAS Inc.
>>
>> "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
>> news:7F943183-B6BD-46CF-9CAF-AFF73834DAE5@.microsoft.com...
>> The old string didn't move up when I replied to it, so copied the
>> information
>> into this new question
>> ----
>> OK -- this works to a point. I have real string data in my field that I
>> want
>> to display, if the field is not "isnumeric". So, here is the code I am
>> using:
>> =iif(isnumeric(Fields!Q4Amt.Value),
>> Format(convert.ToDouble(Fields!Q4Amt.Value),
>> "$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
>> Every time the N/A is supposed to be displayed, I get an error message.
>> Thanks for any and all help
>> "Teo Lachev [MVP]" wrote:
>> =Iif (Fields!phone.Value Is Nothing, Nothing,
>> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.'
>> ####"))
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
>> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
>> > okay great. I used the first example. Thanks!
>> > Now i have discovered that there are some rows without phone numbers.
>> > How do I do an IIF statement, that will return the formatted phone
>> > number
>> > and not available when there is no phone number?
>> >
>> > "Teo Lachev [MVP]" wrote:
>> >
>> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
>> >> ####")
>> >>
>> >> or use a regular expression, sth like
>> >>
>> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
>> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
>> >>
>> >> --
>> >> HTH,
>> >> ---
>> >> Teo Lachev, MVP, MCSD, MCT
>> >> "Microsoft Reporting Services in Action"
>> >> "Applied Microsoft Analysis Services 2005"
>> >> Home page and blog: http://www.prologika.com/
>> >> ---
>> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
>> >> > how can i format a phone number to be displayed as follows in the
>> >> > report
>> >> > (678) 444-4444
>> >> >
>> >> > Thanks!
>> >>
>> >>
>> >>
>>
>>
>>
>|||You know, it never even occured to me that the logical IIF would do that...
that is evaluate the expression, and the false case...
i.e.
if a = 5 then is this fine...
=iif(IsNumeric(a), Format(Convert.ToDouble(a), "#"), "N/A")
if a = 'test' I would expect "N/A" to print out...
Of course I HAD to create a report to test it... and sure enough, it broke
the moment I put a text character in instead of a value.
Thanks for the great tip.
=-Chris
"Michael Abair" <michael.abair@.chicos.com> wrote in message
news:OTxLAxH9GHA.4224@.TK2MSFTNGP02.phx.gbl...
> This will happen because of the nature of IIF. IIF calculates both the
> true and false values before choosing which one to display. So it
> actually is failing because in the background it trys to convert the
> string to a double and return the value. Writing Custom Code to take
> advantage of the real IF function is a way around this.
> If you know VB its pretty simple.
> report->report properties-> code tab->
>
> Public Function checkstr(ByVal testval As String)
> IF Char.IsNumber(testval, 0) THEN
> return CDbl(testval)
> ELSE
> return "N/A"
> END IF
> End Function
> Then apply the formatting in the textbox you want N/A Displayed in and
> have your text box state the following
> =Code.checkstr(Fields!Q4Amt.Value)
> -Michael Abair
> Programmer Analyst
> Chicos FAS Inc.
>
> "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
> news:7F943183-B6BD-46CF-9CAF-AFF73834DAE5@.microsoft.com...
>> The old string didn't move up when I replied to it, so copied the
>> information
>> into this new question
>> ----
>> OK -- this works to a point. I have real string data in my field that I
>> want
>> to display, if the field is not "isnumeric". So, here is the code I am
>> using:
>> =iif(isnumeric(Fields!Q4Amt.Value),
>> Format(convert.ToDouble(Fields!Q4Amt.Value),
>> "$###,###,##0.00;($###,###,##0.00);$0.00"), "N/A")
>> Every time the N/A is supposed to be displayed, I get an error message.
>> Thanks for any and all help
>> "Teo Lachev [MVP]" wrote:
>> =Iif (Fields!phone.Value Is Nothing, Nothing,
>> Format(Convert.ToDouble(Fields!phone.Value), "(###) ###-#### 'Ext.'
>> ####"))
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in message
>> news:03C42C02-DBFD-44AA-A297-FEA74C6ACB29@.microsoft.com...
>> > okay great. I used the first example. Thanks!
>> > Now i have discovered that there are some rows without phone numbers.
>> > How do I do an IIF statement, that will return the formatted phone
>> > number
>> > and not available when there is no phone number?
>> >
>> > "Teo Lachev [MVP]" wrote:
>> >
>> >> = Format(Convert.ToDouble("90155585850000"), "(###) ###-#### 'Ext.'
>> >> ####")
>> >>
>> >> or use a regular expression, sth like
>> >>
>> >> =System.Text.RegularExpressions.Regex.Replace(Fields!phone.Value,
>> >> "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
>> >>
>> >> --
>> >> HTH,
>> >> ---
>> >> Teo Lachev, MVP, MCSD, MCT
>> >> "Microsoft Reporting Services in Action"
>> >> "Applied Microsoft Analysis Services 2005"
>> >> Home page and blog: http://www.prologika.com/
>> >> ---
>> >> "rybrown1818" <rybrown1818@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:E8FFA4A2-0CEF-4AF5-9CCB-ED08E8DCBE51@.microsoft.com...
>> >> > how can i format a phone number to be displayed as follows in the
>> >> > report
>> >> > (678) 444-4444
>> >> >
>> >> > Thanks!
>> >>
>> >>
>> >>
>>
>>
>

Format Table Row into XML and Insert into Another Table?

First I am using SQL Server 2000 (I think this is easy to do in 2005)

I am trying to do the following:

- Format a table row into an XML string

- Insert the XML string into another table.

Here is what I want to do in a nutshell:

insert into Table1(xml_data)

select * from customers

where customerid = 'alfki'

for xml auto, elements

However, I get the following error: The FOR XML clause is not allowed in a INSERT statement.

I tried a few other formats, such as User Defined Function and etc.

Can this be done? Any ideas?

R

This is not possible in SQL Server 2000 without going through lot of pain on the server-side. So best is to get the XML data to the client and then insert from there.

Format String for SSN

I need to format a char(9) field as ###-##-####. What format string do I
use in the Format property of the Text Box?Try
= Format(Convert.ToDouble(<your field>), "###-##-####")
It is a .NET hack but it works.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"John Taylor" <jtaylor@.uab.edu> wrote in message
news:O3j#WptfEHA.3928@.TK2MSFTNGP11.phx.gbl...
> I need to format a char(9) field as ###-##-####. What format string do I
> use in the Format property of the Text Box?
>|||Thanks! If you think that's a hack, I would hate for you to see the rest of
my code! : )
John
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:%231qNXEufEHA.712@.TK2MSFTNGP09.phx.gbl...
> Try
> = Format(Convert.ToDouble(<your field>), "###-##-####")
> It is a .NET hack but it works.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "John Taylor" <jtaylor@.uab.edu> wrote in message
> news:O3j#WptfEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > I need to format a char(9) field as ###-##-####. What format string do
I
> > use in the Format property of the Text Box?
> >
> >
>

format string for dutch language

Hi everyone,

I have some measure with the following format string : #,#0.00

Now in Reporting Services my value is 1,975,647,808.55 instead of 1.975.647.808,55

I know that I can't change the format string to #.#0,00

This gives bad results in reporting Services

Any idea or solution?

thanks!!

Filip

Hello. I do not have a complete solution. We are struggling with this problem in Sweden as well.

You can use this format string '### ### ### ###.##' in SSAS2005.

It will help with avoiding the problem that all numbers will be text when you export to Excel.

The downside is that you will have to accept a dot (.) as a decimal pointer.

I have found no other way to solve this,

Kind Regards

Thomas Ivarsson

Format string

Hii
can somebody please tell me what format string i should use to bring 3
digits after decimal in any textbox.
thanx in advanceformat(Field!Number.Value,"0.000")
"Techotsav" wrote:
> Hii
> can somebody please tell me what format string i should use to bring 3
> digits after decimal in any textbox.
> thanx in advance
>|||many many thanks (sorry didnt get ur label), i placed =format("0.000")
in formatstring textbox n bang, it works!

Format String

What is the difference between "#,#0.00" and "#,#.00"?

In the 2nd case, numbers between 0 and 1 won't have a leading 0. So, for example, "0.31" (1st string) will display as ".31" (2nd string):

http://msdn2.microsoft.com/en-us/library/ms146084.aspx

>>

0

Represents a digit placeholder that displays a digit or a zero (0). If the number has a digit in the position where the zero appears in the format string, the formatted value displays the digit. Otherwise, the formatted value displays a zero in that position. If the number has fewer digits than there are zeros (on either side of the decimal) in the format string, the formatted value displays leading or trailing zeros. If the number has more digits to the right of the decimal separator than there are zeros to the right of the decimal separator in the format expression, the formatted value rounds the number to as many decimal places as there are zeros. If the number has more digits to the left of the decimal separator than there are zeros to the left of the decimal separator in the format expression, the formatted value displays the additional digits without modification.

#

Represents a digit placeholder that displays a digit or nothing. If the expression has a digit in the position where the number sign (#) appears in the format string, the formatted value displays the digit. Otherwise, the formatted value displays nothing in that position. The number sign (#) placeholder works like the zero (0) digit placeholder except that leading and trailing zeros are not displayed if the number has the same or fewer digits than there are # characters on either side of the decimal separator in the format expression.

>>

Format String

Hi all
I am using a control that i hand a record set to.
One of the columns in the record set displays totals.
I need to format these totals into currency.
What is the best way of doing this?
I would like it to look like this ￡2,324.40
If possible.
Thanks Heaps
IanWhy not format it in the control? That way the control can apply the
user's preferred currency format settings, which might be harder to do
in the database. In general presentational stuff is best kept separate
from data retrieval. Otherwise, you'll want to take a look at the
various style options of the CONVERT function.
David Portas
SQL Server MVP
--|||Thanks David
I don't have control over the Grid control. Some one else developed it.
Ian
"David Portas" wrote:

> Why not format it in the control? That way the control can apply the
> user's preferred currency format settings, which might be harder to do
> in the database. In general presentational stuff is best kept separate
> from data retrieval. Otherwise, you'll want to take a look at the
> various style options of the CONVERT function.
> --
> David Portas
> SQL Server MVP
> --
>

Format Question (Currency)

I know that you can set a textbox to be of format Currency, but how
can I set the text of a textbox equal to a string that has formatted
strings within it?
What I mean is, lets say that I am getting back 2 values: MyFieldA and
MyFieldB, which are both floats (say 12.3123 and 43.2234). I want to
make the text "MyFieldA - MyFieldB", where both are formatted as
currency, so it would look like "$12.31 - $43.22".
Is there a way, with in the section where I specify
Fields!MyFieldA.Value to do something like
Fields!MyFieldA.Value.Format("C") or something like that? anyone
know?Chris,
Maybe something like this:
=(String.Format("{$###,##0.00}"),Fields!MyFieldA.Value) & " - " &
(String.Format("{$###,##0.00}"),Fields!MyFieldB.Value)
I had thought about using cdec, but I don't think that works in RS.
I'm pretty new to the RS world, so I don't know it this will help but
it doesn't hurt to try. Good Luck.
Jonathan
cmay@.walshgroup.com (Chris) wrote in message news:<44f6e099.0410261338.3f991c5@.posting.google.com>...
> I know that you can set a textbox to be of format Currency, but how
> can I set the text of a textbox equal to a string that has formatted
> strings within it?
> What I mean is, lets say that I am getting back 2 values: MyFieldA and
> MyFieldB, which are both floats (say 12.3123 and 43.2234). I want to
> make the text "MyFieldA - MyFieldB", where both are formatted as
> currency, so it would look like "$12.31 - $43.22".
> Is there a way, with in the section where I specify
> Fields!MyFieldA.Value to do something like
> Fields!MyFieldA.Value.Format("C") or something like that? anyone
> know?