|
|
You're in the
sql server reporting services
group:Formating a numeric string, that is sometimes text
sql server reporting services:
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 [quoted text, click to view] "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. [quoted text, click to view] "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! > >> >> > >> >> > >> >> > >> > >> > >> > > > >
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. [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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! >>> >> >>> >> >>> >> >>> >>> >>> >> > >
Much Appreciated!
[quoted text, click to view] "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! >>>> >> >>>> >> >>>> >> >>>> >>>> >>>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |