CODEWITHSUNDEEP
sql-serverreporting-servicesparameterssql-server-data-tools
Ariox66
Ariox66

Reputation: 660

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

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?

Upvotes: 0

Views: 700

Answers (1)

Shekar Kola
Shekar Kola

Reputation: 1297

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

Upvotes: 1

Related Questions