June 8, 2009 at 7:36 am
I have a series of address columns which I'd like to concatenate together to form a single address column as part of a view.
I can do this easily using the string concatenation method, but I'd like to embed a CR & LF at the end of each line.
This is to help with displaying the column in a Reporting Rervices report - I can do this in the report definition itself by using the expression shown at the end of this post, but for reasons of simplicity of use by the user when using Report Builder I'd like to have this already formatted into a pre-defined column on the view that wil be used in the Report Model.
Can anyone help me to di this please?
Best Regards
Colin Graham
Here is the Report Services field expression:
= iif(Fields!OrganisationName.Value is nothing or Len(Fields!OrganisationName.Value)=0, "", Fields!OrganisationName.Value & vbcrlf ) &
iif(Fields!DepartmentName.Value is nothing or Len(Fields!DepartmentName.Value)=0, "", Fields!DepartmentName.Value & vbcrlf ) &
iif(Fields!SubBuildingName.Value is nothing or Len(Fields!SubBuildingName.Value)=0, "", Fields!SubBuildingName.Value & vbcrlf ) &
iif(Fields!BuildingName.Value is nothing or Len(Fields!BuildingName.Value)=0, "", Fields!BuildingName.Value & vbcrlf ) &
iif(Fields!BuildingNumber.Value is nothing or Len(Fields!BuildingNumber.Value)=0, "", Fields!BuildingNumber.Value & vbcrlf ) &
iif(Fields!DependantStreet.Value is nothing or Len(Fields!DependantStreet.Value)=0, "", Fields!DependantStreet.Value & vbcrlf ) &
iif(Fields!Street.Value is nothing or Len(Fields!Street.Value)=0, "", Fields!Street.Value & vbcrlf ) &
iif(Fields!DependantLocality.Value is nothing or Len(Fields!DependantLocality.Value)=0, "", Fields!DependantLocality.Value & vbcrlf ) &
iif(Fields!Locality.Value is nothing or Len(Fields!Locality.Value)=0, "", Fields!Locality.Value & vbcrlf ) &
iif(Fields!PostTown.Value is nothing or Len(Fields!PostTown.Value)=0, "", Fields!PostTown.Value & vbcrlf ) &
iif(Fields!County.Value is nothing or Len(Fields!County.Value)=0, "", Fields!County.Value & vbcrlf ) &
iif(Fields!PostCode.Value is nothing or Len(Fields!PostCode.Value)=0, "", Fields!PostCode.Value )
June 8, 2009 at 7:51 am
Please post your current View definition. And just so you know, I doubt that this will be the panacea that you are hoping for. Output specific formatting like this really does belong in the presentation layer.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 8, 2009 at 8:04 am
Hello,
Here's my full view as it stands at the moment (still under development). The columns are after the 'Longitude' alias.
SELECT
DCu.PrimaryCustCode as 'Customer Code',
DCu.PrimaryCustomer as 'Customer',
DCu.BillingCustCode as 'Bill Customer Code',
DCu.BillingCustomer as 'Bill Customer',
DMa.ManagementArea as 'Mgmnt Area',
DMa.ManagementSubArea as 'Mgmnt Sub-Area',
DPr.UPRN as 'UPRN',
DPr.EntUPRN as 'Ent. UPRN',
DPr.CustomerRef as 'Customer Reference',
DPr.ContractStatus as 'Customer Status',
DPr.OrganisationName as 'Organisation',
DPr.DepartmentName as 'Department',
DPr.SubBuildingName as 'Sub-Building Name',
DPr.BuildingName as 'Buliding Name',
DPr.BuildingNumber as 'No.',
DPr.DependantStreet as 'Dependant Street',
DPr.Street as 'Street',
DPr.DoubleDependantLocality as 'Double Dependant Locality',
DPr.Locality as 'Locality',
DPr.PostTown as 'Town',
DPr.Postcode as 'Post Code',
DPr.WGS84Lat as 'Latitude',
DPr.WGS84Long as 'Longitude',
--
-- I need to put CR/LF codes here
--
(
isnull(DPr.BuildingNumber,'') +
', ' +
isnull(DPr.BuildingName,'') +
', ' +
isnull(DPr.Street,'') +
', ' +
isnull(DPr.Locality,'') +
', ' +
isnull(DPr.PostTown,'') +
', ' +
isnull(DPr.PostCode,'')
) as 'Full Address',
--
--
DPi.PriorityCode as 'Priority Code',
DPi.Priority as 'Priority',
DPi.CustomerPriority as 'Customer Priority',
DJo.JobType as 'Job Type',
DJo.JobSubtype as 'Job Sub-Type',
DOs.OrderStatusCode as 'Status Code',
DOs.OrderStatusDesc as 'Status',
DSk.SkillCode as 'Skill Code',
DSk.SkillDesc as 'Skill',
FOd.OrderRef as 'Ent. Order No.',
FOd.OrderClientRef as 'Client Reference',
FOd.VisitCount as 'No. of Visits',
isnull(FOd.TravelTime,0) as 'Travel Minutes',
isnull(CONVERT(CHAR(5),FOd.TravelTime,108),'') as 'Travel Time',
isnull(FOd.WorkingTime,0) as 'Working Minutes',
isnull(CONVERT(CHAR(5),FOd.WorkingTime,108),'') as 'Working Time',
CASE WHEN cast(FOd.FirstVisitPass as int) = 1 THEN 'Yes' ELSE 'No' END as 'First Visit Done',
CASE WHEN cast(FOd.FirstTimeFix as int) = 1 THEN 'Yes' ELSE 'No' END as 'FFP Achieved',
FOd.InvoiceSeqNo as 'Invoice Seq. No.',
CONVERT(Decimal(13,2),OrderCost) as 'Order Cost',
CONVERT(Decimal(13,2),OrderValue) as 'Order Value',
CONVERT(Decimal(13,2),BaseLabourCosts) as 'Base Labour Cost',
CONVERT(Decimal(13,2),BaseMaterialCosts) as 'Base Material Cost',
CONVERT(Decimal(13,2),BasePlantCosts) as 'Base Plant Cost',
CONVERT(Decimal(13,2),SubbyEstimates) as 'Sub-Contractor Estimate',
CONVERT(Decimal(13,2),BaseSubbyCosts) as 'Base Sub-Contractor Cost',
CONVERT(Decimal(13,2),BaseDamageCosts) as 'Base Damage Cost',
CONVERT(Decimal(13,2),BaseOtherCosts) as 'Base Other Cost',
FOd.ReceivedDateTime as 'Received Date/Time',
CONVERT(CHAR(10),FOd.ReceivedDateTime,120) as 'Received Date',
SUBSTRING(CONVERT(CHAR(10),FOd.ReceivedDateTime,120),12,5) as 'Received Time',
FOd.RequiredStartDate as 'Required Start Date/Time',
CONVERT(CHAR(10),FOd.RequiredStartDate,120) as 'Required Start Date',
SUBSTRING(CONVERT(CHAR(10),FOd.RequiredStartDate,120),12,5) as 'Required Start Time',
FOd.RequiredEndDate as 'Required End Date/Time',
CONVERT(CHAR(10),FOd.RequiredEndDate,120) as 'Required End Date',
SUBSTRING(CONVERT(CHAR(10),FOd.RequiredEndDate,120),12,5) as 'Required End Time',
FOd.FirstVisitdateTime as 'First Visit Date/Time',
CONVERT(CHAR(10),FOd.FirstVisitdateTime,120) as 'First Visit Date',
SUBSTRING(CONVERT(CHAR(10),FOd.FirstVisitdateTime,120),12,5) as 'First Visit Time',
'2009-05-18 14:27:10' as 'Start of Travel of First Visit Date/Time',
'2009-05-18' as 'Start of Travel of First Visit Date',
'14:27' as 'Start of Travel of First Visit Time',
'2009-05-18 15:12:26' as 'Time on Site of First Visit Date/Time',
'2009-05-18' as 'Time on Site of First Visit Date',
'15:12' as 'Time on Site of First Visit Time',
'2009-05-18 16:32:18' as 'Time off Site of First Visit Date/Time',
'2009-05-18' as 'Time off Site of First Visit Date',
'16:32' as 'Time off Site of First Visit Time',
'2009-06-01 10:45:39' as 'Last Visit Date/Time',
'2009-06-01' as 'Last Visit Date',
'10:45' as 'Last Visit Time',
FOd.FFPDateTime as 'FFP Date/Time',
CONVERT(CHAR(10),FOd.FFPDateTime,120) as 'FFP Date',
SUBSTRING(CONVERT(CHAR(10),FOd.FFPDateTime,120),12,5) as 'FFP Time',
FOd.FCDateTime as 'FC Date/Time',
CONVERT(CHAR(10),FOd.FCDateTime,120) as 'FC Date',
SUBSTRING(CONVERT(CHAR(10),FOd.FCDateTime,120),12,5) as 'FC Time',
FOd.PFBDateTime as 'PFB Date/Time',
CONVERT(CHAR(10),FOd.PFBDateTime,120) as 'PFB Date',
SUBSTRING(CONVERT(CHAR(10),FOd.PFbDate,120),12,5) as 'PFB Time',
FOd.InvoiceDateTime as 'Invoice Date/Time',
CONVERT(CHAR(10),FOd.InvoiceDateTime,120) as 'Invoice Date',
SUBSTRING(CONVERT(CHAR(10),FOd.InvoiceDate,120),12,5) as 'Invoice Time',
FOd.HeldDateTime as 'Held Date/Time',
CONVERT(CHAR(10),FOd.HeldDateTime,120) as 'Held Date',
SUBSTRING(CONVERT(CHAR(10),FOd.HeldDateTime,120),12,5) as 'Held Time',
FOd.CancelledDateTime as 'Cancelled Date/Time',
CONVERT(CHAR(10),FOd.HeldDateTime,120) as 'Cancelled Date',
SUBSTRING(CONVERT(CHAR(10),FOd.HeldDateTime,120),12,5) as 'Cancelled Time'
FROM
dbo.FactOrders FOd LEFT OUTER JOIN dbo.DimCustomers DCu on FOd.CustomerKey = DCu.CustomerKey
LEFT OUTER JOIN dbo.DimManagementAreas DMa on FOd.MAKey = DMa.MAKey
LEFT OUTER JOIN dbo.DimPropertys DPr on FOd.PropKey = DPr.PropKey
LEFT OUTER JOIN dbo.DimPriorities DPi on FOd.PriorityKey = DPi.PriorityKey
LEFT OUTER JOIN dbo.DimJobs DJo on FOd.JobKey = DJo.JobKey
LEFT OUTER JOIN dbo.DimOrderStatus DOs on FOd.OrderStatusKey = DOs.OrderStatusKey
LEFT OUTER JOIN dbo.DimSkills DSk on FOd.SkillsKey = DSk.SkillsKey
June 8, 2009 at 9:12 am
I have, through trial and error found out hw to do this and the solution works exactly as I want it to to.
I have inserted
+ CHAR(10)
+ CHAR(13)
into the string concatenation and it formats the data just fine.
Thank you for the replies.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply