|
|
You're in the
sql server reporting services
group:Comma delimited list of a given field from records
sql server reporting services:
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,
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 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 [quoted text, click to view] "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 [quoted text, click to view] "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] [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] 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? --
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |