Im not 100% sure this would work when querying in SSRS as I havn't wrote any reports for a long time but, using sp_executesql may give you the desired results but the syntax is a little more complex.

Below is a script i put together to call a replication validation stored procedure for every publication on a server which would give you some ideas on how to utilise it:

SET NOCOUNT ON

GO

-- Check if the temp table already exists, if it does drop it.

IF OBJECT_ID('tempdb..#MyPubs') IS NOT NULL

DROP TABLE #MyPubs

--Populate the temp table with Publications and Articles

SELECTDISTINCT p.Publication,

a.article AS Article,

a.publisher_db AS Publisher

INTO #MyPubs

FROM Distribution..msarticles a (NOLOCK)

JOIN Distribution..mssubscriptions s (NOLOCK) ON a.publication_ID = s.publication_ID

JOIN Distribution..mspublications p (NOLOCK) ON s.publication_ID = p.publication_ID

where s.subscriber_db<>'virtual'

GROUP BY a.article, a.publisher_db, s.subscriber_db, p.Publication, a.source_object, a.destination_object

ORDER BY a.article

-- Create variables

DECLARE @pub_db sysname,

@pub varchar(255),

@art varchar(max),

@cmd nvarchar(4000),

@procname varchar(255),

@Parameters nvarchar(1000),

@ReturnResult bit

-- Check if the temp table already exists, if it does drop it.

IF OBJECT_ID('tempdb..#ValidationResults') IS NOT NULL

DROP TABLE #ValidationResults

CREATE TABLE #ValidationResults (

Publisher varchar(255),

Publicationvarchar(255),

articlevarchar(255),

ValidationSuccessfull bit

)

--Declare the cursor using the temp table as an input

--!!!!not all cursors are bad!!!!, calling a procedure cannot be done

--in a set based manner, hence an iterative loop is required.

DECLARE cur_published CURSOR FOR

SELECT Publisher, Publication, Article

FROM #MyPubs

--Open the cursor to begin processing the rows added to the cursor in the code above.

OPEN cur_published

FETCH NEXT FROM cur_published INTO @pub_db, @pub, @art

WHILE @@FETCH_STATUS = 0

BEGIN

--For the current publication \ article generate the dynamic sql command for executing

-- sp_article_validation, its sp_execute SQL so the result can be returned to variable for adding to the

-- results table, the result message cannot be captured to the same table due to the design of the proc

-- by Microsoft themselves.

SELECT @procname = @pub_db + '..sp_article_validation'

-- !!!!!! IMPORTANT !!!! - In this line change the 3rd and 4th Parameters from 2,2 to 1,1

-- If performance is poor, these are the parameter values for @rowcount_only and @full_or_fast respectively.

SELECT @cmd = 'exec @ReturnResultOUT = ' + @procname + ' @pubIn, @artIn, 2,2,0,0'

SELECT @Parameters = '@pubIn varchar(255), @artIn varchar(255), @ReturnResultOUT bit OUTPUT'

EXECUTE sp_executesql @cmd, @Parameters, @pubIn = @pub, @artIn = @art, @ReturnResultOut = @ReturnResult OUTPUT

--Lastly load the entire result set to #ValidationResults for the procedure call

--Include the publisher \ publication \ article and Message so you have all the necessary info.

--AS sp_article_validation returns 0 for OK and 1 for their being issues I have flipped these for reporting in the log table.

INSERT INTO #ValidationResults

SELECT @pub_db, @pub, @art, CASE WHEN @ReturnResult = 0 THEN 1 ELSE 0 END

--Fetch the next article \ publication from the list to validate.

FETCH NEXT FROM cur_published INTO @pub_db, @pub, @art

END

CLOSE cur_published

DEALLOCATE cur_published

GO

-- Check how validation went, for failures only add WHERE ValidationSuccessFull = 0

SELECT * FROM #ValidationResults

--WHERE ValidationSuccessFull = 0