Concatenating values from rows

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

  • 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 !!!**

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

  • 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

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

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

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

  • 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