format phone numbers

how can i format a phone number to be displayed as follows in the report
(678) 444-4444
Thanks!"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!
Assuming all records are complete with 10 digits.
Try this:
=Format(cdec(PhoneNumber.Value),"(###) ###-####")|||= 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!|||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!
>
>|||=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!
>>|||THANKS!!!!!!!!!
"rybrown1818" wrote:
> how can i format a phone number to be displayed as follows in the report
> (678) 444-4444
> Thanks!|||OK -- this works to a point. I have real string data in my field that I want
to display if the field !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 need to change the format of the textbox to be an expression - if the
value is NOT numeric, then the format is BLANK. You cannot dispaly regular
text for objects that are, in this case, formatted for numeric display.
Hope this helps.
=-Chris
"msflinx" <msflinx@.discussions.microsoft.com> wrote in message
news:6A8AED73-5347-4A1B-9FEF-A4BAD968FCD6@.microsoft.com...
> OK -- this works to a point. I have real string data in my field that I
> want
> to display if the field !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'm not sure what I want right now. I want to be able to display the "N/A"
and $123,456.78 in the same textbox. So my options are:
1) If you cannot display a string and a number in the same textbox, can you
superimpose one textbox on top of another, with a switch as to the visible
textbox, based on the expression IsHidden =iif(isnumeric(Fields!Q4Amt.Values),True, False
2) Write a parsing function in the Report Parameters Code window and return
ONLY string values
3) Punt...
Any other ideas would be appreciated.
"Chris Conner" wrote:
> You need to change the format of the textbox to be an expression - if the
> value is NOT numeric, then the format is BLANK. You cannot dispaly regular
> text for objects that are, in this case, formatted for numeric display.
> Hope this helps.
> =-Chris
>
> "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
> news:6A8AED73-5347-4A1B-9FEF-A4BAD968FCD6@.microsoft.com...
> > OK -- this works to a point. I have real string data in my field that I
> > want
> > to display if the field !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!
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Thanks Chris. I posted this twice, as this thread did not move up the forum,
when I replied to it. I didn't think anyone was watching it.
The answer I received was option #2
This is the code I place 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 in the textbox Expression field, I entered:
=Code.NumericStringToCurrencyString( Fields!Q4Amt.Value)
This gave me the desired results.
Thank you for your assistance
Thank you ever so much.
"msflinx" wrote:
> I'm not sure what I want right now. I want to be able to display the "N/A"
> and $123,456.78 in the same textbox. So my options are:
> 1) If you cannot display a string and a number in the same textbox, can you
> superimpose one textbox on top of another, with a switch as to the visible
> textbox, based on the expression IsHidden => iif(isnumeric(Fields!Q4Amt.Values),True, False
> 2) Write a parsing function in the Report Parameters Code window and return
> ONLY string values
> 3) Punt...
> Any other ideas would be appreciated.
> "Chris Conner" wrote:
> > You need to change the format of the textbox to be an expression - if the
> > value is NOT numeric, then the format is BLANK. You cannot dispaly regular
> > text for objects that are, in this case, formatted for numeric display.
> >
> > Hope this helps.
> >
> > =-Chris
> >
> >
> >
> > "msflinx" <msflinx@.discussions.microsoft.com> wrote in message
> > news:6A8AED73-5347-4A1B-9FEF-A4BAD968FCD6@.microsoft.com...
> > > OK -- this works to a point. I have real string data in my field that I
> > > want
> > > to display if the field !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 Currency

Does anyone know how to change the currency as follows:
from: $2,332,765 to: $2.3
I've tried several combinations of ($#.#;$#.#;$0.0) and nothing seems
to work
Thanks in advance
LisaOn Jun 13, 12:19 pm, peashoe <peas...@.yahoo.com> wrote:
> Does anyone know how to change the currency as follows:
> from: $2,332,765 to: $2.3
> I've tried several combinations of ($#.#;$#.#;$0.0) and nothing seems
> to work
> Thanks in advance
> Lisa
You could try: $#.0 however, you might need to use an expression/etc
to divide the values by 1 million. (ie., =Fields!Currency.Value/
1000000) assuming that some values will not evaluate to zero. Also,
you might need to accommodate different currency ranges (ie.,
=iif(Fields!Currency.Value >= 100000, Fields!Currency.Value/1000000,
Fields!Currency.Value/100000). In this example case, if values will
fall below 0.0, then divide by a smaller value/denomination. Of
course, it would need to be based on your particular needs. Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
This was EXACTLY what I needed!
Thanks,
Lisa|||On Jun 13, 1:36 pm, peashoe <peas...@.yahoo.com> wrote:
> Enrique,
> This was EXACTLY what I needed!
> Thanks,
> Lisa
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant