• 0
Login to follow this  
Heineperson

Concatenate column values as string in calculated field

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?

 

Share this post


Link to post
Share on other sites

2 answers to this question

  • 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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Login to follow this