March 30, 2006 at 5:50 pm
Let's say I have a SP that returns a bunch of data along with e-mail addresses and a report that displays this data.
At the end of the report, I'd like to create a hyperlink to send an email to all records listed (mailto:xx@yy.com, yy@zz.com...) (Making sure to list each e-mail address only once) Is there any way to have a textbox and concat all values from all the rows into it?
(this can be done in crystal, and I need to prove to my peers that sql server reporting is as powerful)
March 30, 2006 at 9:32 pm
Don't know anything about reporting services but if you're looking for a concatenating example, here's what you'd do...
DECLARE @emailAddresses VarChar(255) SELECT @emailAddresses = COALESCE(@emailAddresses + ', ', '') + emailCol FROM myTable PRINT @emailAddresses
**ASCII stupid question, get a stupid ANSI !!!**
March 30, 2006 at 9:35 pm
Yes, I know how do do this in SQL, but like I said, the data comes from a stored procedure so I need to do the manipulation in the report itself.
March 31, 2006 at 4:33 am
I would suggest you to write a subreport ,such that when you click on the textbox,it jumps to another report where you will try to concatenate one by one in a using stored procedure
in order to make sure that you are concatenating the email address which you filtered out in a first report.use the same query for the cursor in the stored procedure
your subreport will be using a sstored procedure. try to work out
it works
Ok
Regards
Raj Deep.A
March 31, 2006 at 5:14 am
That would require writing sql code as well. Can anyone think of a way to to it all in RS, without writing any other queries or stored procedures? (Like Crystal can)
March 31, 2006 at 10:36 am
In the report layout you can click on the box in the upper left corner, go to properties, click on the CODE tab, and you can add code to do it.
March 31, 2006 at 12:39 pm
I know how to add code to a report. However I cannot find any documented way to access the rows of a DataSet and loop over them. Anyone?
March 31, 2006 at 2:43 pm
Set a column in the report that will be blank but will start to accumulate the email addresses, wherever you want the list of email addresses add whatever is needed and set teh value of it to call the string you're building of the email address.
Here is how I did it with order numbers
[Code]
PUBLIC DIM fldLst as String
PUBLIC FUNCTION GetFld(orderID as String) as String
fldLst = orderID & vbnewline & fldLst
GetFld = ""
END FUNCTION
PUBLIC FUNCTION getFldLst() as String
getFldLst = fldLst
END FUNCTION
[/CODE]
If you need to modify each thing as you're grabbing it, so be it, but that should give you an idea and hopefully it'll help you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply