Ggtcf

Why would a flight no longer considered airworthy be redirected like this?

How could a scammer know the apps on my phone / iTunes account?

My adviser wants to be the first author

Identifying the interval from A♭ to D♯

Have researchers managed to "reverse time"? If so, what does that mean for physics?

At what level can a dragon innately cast its spells?

PlotLabels with equations not expressions

Science-fiction short story where space navy wanted hospital ships and settlers had guns mounted everywhere

Happy pi day, everyone!

Meaning of "SEVERA INDEOVI VAS" from 3rd Century slab

Does splitting a potentially monolithic application into several smaller ones help prevent bugs?

What are some nice/clever ways to introduce the tonic's dominant seventh chord?

What options are left, if Britain cannot decide?

Theorems like the Lovász Local Lemma?

Replacing Windows 7 security updates with anti-virus?

Dot in front of file

Can hydraulic brake levers get hot when brakes overheat?

What has been your most complicated TikZ drawing?

When do we add an hyphen (-) to a complex adjective word?

Will a pinhole camera work with instant film?

Did CPM support custom hardware using device drivers?

I need to drive a 7/16" nut but am unsure how to use the socket I bought for my screwdriver

Why doesn't the EU now just force the UK to choose between referendum and no-deal?

How to generate globally unique ids for different tables of the same database?

Report Builder not showing aggregated string

0

in Report Builder I have a datasource derived from a SELECT script containing an aggregated string (rows)

something like this:

Select SUBSTRING((SELECT ',' + field1 AS 'data()' 
 FROM table
 where field2 = code
 FOR XML PATH('')), 2 , 9999)

the SELECT script correctly returns an array of values as a single field,
however when I try to put it in report builder, I can only insert fields with an aggregate function - for Strings the default is First()

for some reason Report builder treats it as an array, and with the first() function only returns the first value

how can I achieve that it will display the full "text array"?
I could not find another aggregate function for strings which would work
and I have no idea how can I force report builder to treat it as a single field not as a text array

sql sql-server reportbuilder

share|improve this question

edited Mar 7 at 12:35

jarlh

29.7k52138

asked Mar 7 at 12:34

balint molnarbalint molnar

82

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sounds like you aren't working at the details level in your dataset, and working at a grouping; impossible difficult to tell without access to the report. Also, I'd suggest using the syntax STUFF((SELECT...FOR XML PATH(''),1,1,'') instead of SUBSTRING. The STUFF will remove the first character only, with those parameters. Although it's unlikely you'll have such a long string, it means that it'll work even within strings of length 10,000+.
  
  – Larnu
  Mar 7 at 12:54
  
  

  
  
  
  

add a comment | 

0

in Report Builder I have a datasource derived from a SELECT script containing an aggregated string (rows)

something like this:

Select SUBSTRING((SELECT ',' + field1 AS 'data()' 
 FROM table
 where field2 = code
 FOR XML PATH('')), 2 , 9999)

the SELECT script correctly returns an array of values as a single field,
however when I try to put it in report builder, I can only insert fields with an aggregate function - for Strings the default is First()

for some reason Report builder treats it as an array, and with the first() function only returns the first value

how can I achieve that it will display the full "text array"?
I could not find another aggregate function for strings which would work
and I have no idea how can I force report builder to treat it as a single field not as a text array

sql sql-server reportbuilder

share|improve this question

edited Mar 7 at 12:35

jarlh

29.7k52138

asked Mar 7 at 12:34

balint molnarbalint molnar

82

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sounds like you aren't working at the details level in your dataset, and working at a grouping; impossible difficult to tell without access to the report. Also, I'd suggest using the syntax STUFF((SELECT...FOR XML PATH(''),1,1,'') instead of SUBSTRING. The STUFF will remove the first character only, with those parameters. Although it's unlikely you'll have such a long string, it means that it'll work even within strings of length 10,000+.
  
  – Larnu
  Mar 7 at 12:54
  
  

  
  
  
  

add a comment | 

in Report Builder I have a datasource derived from a SELECT script containing an aggregated string (rows)

something like this:

Select SUBSTRING((SELECT ',' + field1 AS 'data()' 
 FROM table
 where field2 = code
 FOR XML PATH('')), 2 , 9999)

the SELECT script correctly returns an array of values as a single field,
however when I try to put it in report builder, I can only insert fields with an aggregate function - for Strings the default is First()

for some reason Report builder treats it as an array, and with the first() function only returns the first value

how can I achieve that it will display the full "text array"?
I could not find another aggregate function for strings which would work
and I have no idea how can I force report builder to treat it as a single field not as a text array

sql sql-server reportbuilder

share|improve this question

edited Mar 7 at 12:35

jarlh

29.7k52138

asked Mar 7 at 12:34

balint molnarbalint molnar

82

Select SUBSTRING((SELECT ',' + field1 AS 'data()' 
 FROM table
 where field2 = code
 FOR XML PATH('')), 2 , 9999)

sql sql-server reportbuilder

sql sql-server reportbuilder

share|improve this question

edited Mar 7 at 12:35

jarlh

29.7k52138

asked Mar 7 at 12:34

balint molnarbalint molnar

82

share|improve this question

edited Mar 7 at 12:35

jarlh

29.7k52138

asked Mar 7 at 12:34

balint molnarbalint molnar

82

edited Mar 7 at 12:35

jarlh

29.7k52138

edited Mar 7 at 12:35

jarlh

29.7k52138

asked Mar 7 at 12:34

balint molnarbalint molnar

82

asked Mar 7 at 12:34

balint molnarbalint molnar

82