Comma delimited list of a given field from records

I have a list that recieves a distinct field from SQL query. I want to
display this list as a comma delimited string of values, i.e. val1, val2,
val3, ...
The query looks something like this "SELECT DISTINCT val FROM TABLE"
However when I create a list associate it with a Dataset that represents the
query and then put that field inside a textbox inside the list region I get
all the values seperated by newlines. Like this,
Val1
Val2
Val3
...
This is clearly not the desired result. Is there an easy way to control the
delimiter between the records?
--
Thank you,
JohnHi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know whether it is
possible to deliver the result with the format of comma delimited in a row.
If I have misunderstood your concern, please feel free to point it out.
You may refer the sample below to build up your queries or stored procedures
use Northwind
GO
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE EmployeeID
= 3
set @.str=left(@.str,len(@.str)-1)
print @.str
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hey that would be great if I could use TSQL but my solution must be database
agnostic. If I could use TSQL I would right a SP and be done with.
What would be nice is a List aggregate function in Reporting Services that
does just what Sybase's SQL Anywhere does, i.e.
SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
So in Reporting Serices it would look like this.
=List(Fields!valueToAggregate.Value)
Can I extend Reporting Services Aggregate functions somehow so that I can
create my own list function?
--
Thank you,
John
"Michael Cheng [MSFT]" wrote:
> Hi,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you would like to know whether it is
> possible to deliver the result with the format of comma delimited in a row.
> If I have misunderstood your concern, please feel free to point it out.
> You may refer the sample below to build up your queries or stored procedures
> use Northwind
> GO
> declare @.str varchar(8000)
> set @.str=''
> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE EmployeeID
> = 3
> set @.str=left(@.str,len(@.str)-1)
> print @.str
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Check out using a matrix and see if that will do what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John A" <i-code4food@.newsgroups.nospam> wrote in message
news:C73D1939-D114-4257-B2C4-F8B57E525A61@.microsoft.com...
> Hey that would be great if I could use TSQL but my solution must be
> database
> agnostic. If I could use TSQL I would right a SP and be done with.
> What would be nice is a List aggregate function in Reporting Services that
> does just what Sybase's SQL Anywhere does, i.e.
> SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
> So in Reporting Serices it would look like this.
> =List(Fields!valueToAggregate.Value)
> Can I extend Reporting Services Aggregate functions somehow so that I can
> create my own list function?
> --
> Thank you,
> John
>
> "Michael Cheng [MSFT]" wrote:
>> Hi,
>> Welcome to use MSDN Managed Newsgroup!
>> From your descriptions, I understood you would like to know whether it is
>> possible to deliver the result with the format of comma delimited in a
>> row.
>> If I have misunderstood your concern, please feel free to point it out.
>> You may refer the sample below to build up your queries or stored
>> procedures
>> use Northwind
>> GO
>> declare @.str varchar(8000)
>> set @.str=''
>> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE
>> EmployeeID
>> = 3
>> set @.str=left(@.str,len(@.str)-1)
>> print @.str
>> Thank you for your patience and cooperation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>|||With 2005 you can:
http://msdn2.microsoft.com/en-us/library/ms165055.aspx
--
William Stacey [MVP]
"John A" <i-code4food@.newsgroups.nospam> wrote in message
news:C73D1939-D114-4257-B2C4-F8B57E525A61@.microsoft.com...
> Hey that would be great if I could use TSQL but my solution must be
> database
> agnostic. If I could use TSQL I would right a SP and be done with.
> What would be nice is a List aggregate function in Reporting Services that
> does just what Sybase's SQL Anywhere does, i.e.
> SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
> So in Reporting Serices it would look like this.
> =List(Fields!valueToAggregate.Value)
> Can I extend Reporting Services Aggregate functions somehow so that I can
> create my own list function?
> --
> Thank you,
> John
>
> "Michael Cheng [MSFT]" wrote:
>> Hi,
>> Welcome to use MSDN Managed Newsgroup!
>> From your descriptions, I understood you would like to know whether it is
>> possible to deliver the result with the format of comma delimited in a
>> row.
>> If I have misunderstood your concern, please feel free to point it out.
>> You may refer the sample below to build up your queries or stored
>> procedures
>> use Northwind
>> GO
>> declare @.str varchar(8000)
>> set @.str=''
>> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE
>> EmployeeID
>> = 3
>> set @.str=left(@.str,len(@.str)-1)
>> print @.str
>> Thank you for your patience and cooperation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>|||Thanks Bruce but I am really at a loss as to how this will help me after
investigating it can you throw me a clue?
--
Thank you,
John
"Bruce L-C [MVP]" wrote:
> Check out using a matrix and see if that will do what you want.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John A" <i-code4food@.newsgroups.nospam> wrote in message
> news:C73D1939-D114-4257-B2C4-F8B57E525A61@.microsoft.com...
> > Hey that would be great if I could use TSQL but my solution must be
> > database
> > agnostic. If I could use TSQL I would right a SP and be done with.
> >
> > What would be nice is a List aggregate function in Reporting Services that
> > does just what Sybase's SQL Anywhere does, i.e.
> > SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
> >
> > So in Reporting Serices it would look like this.
> > =List(Fields!valueToAggregate.Value)
> >
> > Can I extend Reporting Services Aggregate functions somehow so that I can
> > create my own list function?
> >
> > --
> > Thank you,
> > John
> >
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> >> Hi,
> >>
> >> Welcome to use MSDN Managed Newsgroup!
> >>
> >> From your descriptions, I understood you would like to know whether it is
> >> possible to deliver the result with the format of comma delimited in a
> >> row.
> >> If I have misunderstood your concern, please feel free to point it out.
> >>
> >> You may refer the sample below to build up your queries or stored
> >> procedures
> >>
> >> use Northwind
> >> GO
> >> declare @.str varchar(8000)
> >> set @.str=''
> >> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE
> >> EmployeeID
> >> = 3
> >> set @.str=left(@.str,len(@.str)-1)
> >> print @.str
> >>
> >> Thank you for your patience and cooperation. If you have any questions or
> >> concerns, don't hesitate to let me know. We are always here to be of
> >> assistance!
> >>
> >>
> >> Sincerely yours,
> >>
> >> Michael Cheng
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >> =====================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
> >>
>
>|||Hey thanks, that's way cool. It appears that this modifies the actual SQL
Server instance and that would be great if we weren't using Oracle 85% of the
time. Not my choice BTW. I also found a way to create an aggregate function
for Oracle to use and I suppose I could create all kinds of aggregate
functions on all our server for each Database Vendor we support. However, I
don't think I need to tell you what is wrong with this picture.
What I really need the ability to do is create a new aggregate function that
Reporting Services uses. Or am I missing something here. When I create the
new method for SQL 2005 is it available for use as a Reporting Services
aggregate?
--
Thank you,
John
"William Stacey [MVP]" wrote:
> With 2005 you can:
> http://msdn2.microsoft.com/en-us/library/ms165055.aspx
> --
> William Stacey [MVP]
> "John A" <i-code4food@.newsgroups.nospam> wrote in message
> news:C73D1939-D114-4257-B2C4-F8B57E525A61@.microsoft.com...
> > Hey that would be great if I could use TSQL but my solution must be
> > database
> > agnostic. If I could use TSQL I would right a SP and be done with.
> >
> > What would be nice is a List aggregate function in Reporting Services that
> > does just what Sybase's SQL Anywhere does, i.e.
> > SELECT LIST(DISTINCT, valueToAggregate) FROM MyTable
> >
> > So in Reporting Serices it would look like this.
> > =List(Fields!valueToAggregate.Value)
> >
> > Can I extend Reporting Services Aggregate functions somehow so that I can
> > create my own list function?
> >
> > --
> > Thank you,
> > John
> >
> >
> > "Michael Cheng [MSFT]" wrote:
> >
> >> Hi,
> >>
> >> Welcome to use MSDN Managed Newsgroup!
> >>
> >> From your descriptions, I understood you would like to know whether it is
> >> possible to deliver the result with the format of comma delimited in a
> >> row.
> >> If I have misunderstood your concern, please feel free to point it out.
> >>
> >> You may refer the sample below to build up your queries or stored
> >> procedures
> >>
> >> use Northwind
> >> GO
> >> declare @.str varchar(8000)
> >> set @.str=''
> >> select @.str=@.str+convert(varchar,OrderID)+',' FROM Orders WHERE
> >> EmployeeID
> >> = 3
> >> set @.str=left(@.str,len(@.str)-1)
> >> print @.str
> >>
> >> Thank you for your patience and cooperation. If you have any questions or
> >> concerns, don't hesitate to let me know. We are always here to be of
> >> assistance!
> >>
> >>
> >> Sincerely yours,
> >>
> >> Michael Cheng
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >> =====================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
> >>
>
>|||Hi,
It is not possible to use custom function in the Reporting Services
dataset, but you may try data extension.
For SQL Server 2000 Reporting Services
Custom Dataset Data Extension for Microsoft Reporting Services
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=b8468
707-56ef-4864-ac51-d83fc3273fe5
For SQL Server 2005 Reporting Services
Using an External Dataset with Reporting Services
http://msdn2.microsoft.com/en-us/library/ms152917.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Michael, even if this does not help me with this particular problem
and it well may, I it is a great tool to have in my RS bag of tricks.
I went through the sample and am having a problem with the TestDS.rdl report
on my machine. The error I am getting is -
An error has occured during the report processing.
Query execution failed for data set 'DataSet1'.
Request for the permission of type
System.Security.Permissions.FileIOPermission, mscorlib, Version=1.0.5000.0,
Culture=neutral,
PublicKeyToken=b77a5c561934e089 failed.
I gave the folder that contains the .XSD file read and execute permissions
for IWAM, IUSR and local\User account but I still get that error.
What can I do to resolve this problem?
--
Thank you,
John
"Michael Cheng [MSFT]" wrote:
> Hi,
> It is not possible to use custom function in the Reporting Services
> dataset, but you may try data extension.
> For SQL Server 2000 Reporting Services
> Custom Dataset Data Extension for Microsoft Reporting Services
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=b8468
> 707-56ef-4864-ac51-d83fc3273fe5
> For SQL Server 2005 Reporting Services
> Using an External Dataset with Reporting Services
> http://msdn2.microsoft.com/en-us/library/ms152917.aspx
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi John,
I think this KB might help you
How to grant permissions to a custom assembly that is referenced in a
report in Reporting Services
http://support.microsoft.com/kb/842419
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||If you can get the data into a temporary table on a SQL2005 server,
then you could use the new PIVOT function to get the list sideways! Or
just show it in a matrix.
You could certainly get the Oracle data into the SQL Server DB as a
Linked Server.
Whilst it uses T-SQL, your data source can still be any common DB
system.
Alternatively, you could do it in code using ADO.Net to read the data
in and then use your CLR language of choice to concatenate the values
together. Then use the result to populate a textbox if that's what
you're after.
Cheers
Chris
John A wrote:
> I have a list that recieves a distinct field from SQL query. I want
> to display this list as a comma delimited string of values, i.e.
> val1, val2, val3, ...
> The query looks something like this "SELECT DISTINCT val FROM TABLE"
> However when I create a list associate it with a Dataset that
> represents the query and then put that field inside a textbox inside
> the list region I get all the values seperated by newlines. Like this,
> Val1
> Val2
> Val3
> ...
> This is clearly not the desired result. Is there an easy way to
> control the delimiter between the records?