Using fields from a dataset as values for another dataset's parameter in SSRS 2010

Question

I have a SSRS report which uses multiple datasets, let's call them D1 and D2.

D2 is a sp with one parameter. The goal is to pass values from a single column from D1 to D2 parameter.

A user-defined split function enabled me to run the SP with multiple values in a single parameter. e.g.

exec MyStoredProc '12345,6789,77891,3423498'

I just need to somehow concatenate the values from D1 with a comma.

using Default Values > Get Values from a query> D1 > Field1 only picks the first value and not all of them.

Any suggestions?

so why not just change D1 sql code to return a concatenated list of values instead of rows. There are lots of examples online how to do that. E.g. https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql — Anton, Aug 20 '19 at 02:39
I thought of that too, it's a bit dirty as D1 might have hundreds of rows...isn't there any other way? — Ariox66, Aug 20 '19 at 02:41
hundreds of rows is not an issue, it's just a few KB string. If it is same sql server for both D1 and D2 and the volume of data is high, you can also pass whatever you want using either physical or temporary tables (insert in D1 and read it in D2). In case of multiple users just pass some unique int value from D1 to D2, so it can distinguish passed data. Or just combine two datasets into one if D2 is completely dependent on D1. — Anton, Aug 20 '19 at 02:57

Shekar Kola · Answer 1 · 2019-08-20T18:09:42.027

I think, making new field in D1 using STUFF would make you achieve what you looking for:

Here is the example:

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)


select * from #YourTable

SELECT 
  [ID],
  STUFF((
            SELECT ', ' + [Name] + ': ' + Cast([Value] as varchar(10)) FROM #YourTable 
            FOR XML PATH('')),1,1,''
        ) AS NameValues
FROM #YourTable Results
GROUP BY ID
go

-- Drop #YourTable

You can also use STRING_AGG i.e.

SELECT 
    STRING_AGG ([Value], ', ')
FROM #YourTable Results

Using fields from a dataset as values for another dataset's parameter in SSRS 2010

1 Answers1