December 7, 2005 at 8:19 am
Help for a newbie! I am attempting to parse out the phone number on a report. In the properties of the report for Format I have:
Phone1=convert(varchar(15),'('+convert(varchar(3)left(phone,3))+')'+'convert (varchar(3),substr(phone,4,3))+'-'+convert(varchar(4),substr(phone,7,4)))
My actual field in the data is Phone. The value in the value field of properties is =Field!Phone.value.
What is it I need to do to get the phone number parsed out? I am getting the original phone field. I attempted to change the field on the report to Phone1 and also the value but get an error. Any assistance greatly appreciated -
Thank you
melakeside
December 7, 2005 at 8:41 am
Question,
From your dataset Filed!Phone.Value what does this look like?
What do you mean you need to parse it out.
This is corrected if your doing it in t-sql (substr) is not a valid function , use substring. you also had some formatting issues.
declare @phone varchar(15)
set @Phone = '1112222222'
select Phone1=convert(varchar(15),'('+convert(varchar(3),left(@phone,3))+')'+ convert (varchar(3),substring(@phone,4,3))+'-'+convert(varchar(4),substring(@phone,7,4)))
December 7, 2005 at 9:58 am
Hi -
Within the report on the layout page we are attempting to change the phone number from 3331311344 to 333-131-1344. The field in the data with this number is named Phone. On the report layout, for this field, it has =Field!Phone.value. Within the Format box on the properties tab it has the convert statement. So, what I need to do is change the format field in the properties tab to put "select" in front of the Phone1? I don't understand one thing - in Access for example, if I change the field name to Phone1 that is my new field. In Reporting Services - I attempted to change the value to =Field!Phone1.value and got an error. ??
Thank you!
December 7, 2005 at 3:43 pm
What was the error?
go to the data tab, and Select the dataset this field is in. and Click the Refresh Fields Icon.
Then click the elipses (...) to edit the dataset.
Does that field show up in the fields tab?
Hopefully it does, sometimes you have to refresh the fields to get it to show up, specially after changes.
I don't think that statement will work in the format box. That code has to be VB script.
I just used and tested this
PUt this in the Value field in the properties
="(" & Mid(Fields!Phone1.Value,1,3) & ") " & Mid(Fields!Phone1.Value,4,3) & "-" & Mid(Fields!Phone1.Value,7,10)
There are only a few things that can go in the Format line in properties, and what you had is not one of them.
December 8, 2005 at 7:09 am
Thank you for the response. I did not attempt the mid function on the report because I attempted it within the query and it balked.
On the report this is the error I am now getting (I did the refresh as suggested and the Phone field is in the dataset).
"The format expression for the textbox "Phone" refers to the field "Phone". Report item expressions can only refer to fields within the current data scope....."
I first put in your suggestion as Phone1. Left the Value of the field Phone. Then changed the value of the Field to Phone1 and got the same error. I then changed the everything to Phone1 and got the same error. Thank you for your assistance!
December 8, 2005 at 12:48 pm
Have you tried ###-###-#### as the Format?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply