Jump to content
  • 0

Concatenate column values as string in calculated field


Heineperson

Question

Hello,

I would like to create a calculated field in tabular report datapage that displays a column in related table as a concatenated string.

For Example, many of my apps use the tblPlants as a data source. This is a table of plant species that has a one-to-many relationship with many other tables. I would like to present a field in the report that lists the the botanical gardens for each plant is found in from the tblBG as a string. 

SELECT STRING_AGG(tblBG.Garden,",")

FROM tblBG

WHERE tblBG.PlantID = [@field:PlantID];

I get an error that say there is a syntax error near "(", which I take to meant hat the STRING_AGG function does not exist. Is there another function I could use to accomplish this?

 

Link to comment
Share on other sites

6 answers to this question

Recommended Posts

  • 0
On 9/12/2017 at 7:55 PM, Heineperson said:

Hello,

I would like to create a calculated field in tabular report datapage that displays a column in related table as a concatenated string.

For Example, many of my apps use the tblPlants as a data source. This is a table of plant species that has a one-to-many relationship with many other tables. I would like to present a field in the report that lists the the botanical gardens for each plant is found in from the tblBG as a string. 

SELECT STRING_AGG(tblBG.Garden,",")

FROM tblBG

WHERE tblBG.PlantID = [@field:PlantID];

I get an error that say there is a syntax error near "(", which I take to meant hat the STRING_AGG function does not exist. Is there another function I could use to accomplish this?

 

Calculated field can return only one value. Perhaps, you may create a view, join tables and then use that view as a data source for report with grouping.

Hope that helps.

Link to comment
Share on other sites

  • 0

Hi - Just an addition to this, if you would like to display multiple values (comma and quotation marks separated), you can use this formula:

STUFF((SELECT ', ' +'"'+ (FIELDNAME) +'"' FROM TABLE_NAME FOR XML PATH ('')), 1, 1, '')

Result:
"test1.com", "test2.com", "test3.com", "test4.com"

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...