sql - Report Builder not listing any fields from query using exec(@customquery) to output

Question

Using SSMS, the code below works perfectly fine. When I input the code into a Dataset in SSRS, the report data view doesn't show any of the fields the exec() function outputs. I use these fields in other areas of the report to filter and sort by, so the report is currently broken. I have looked into other questions on here, and they don't address this situation. If there is a way to store this query as a stored procedure and then call it in SSRS, that will work for me. I just don't know how to do that.

Declare @YR2 VARCHAR(MAX), @YR1 VARCHAR(MAX)
Declare @MT2 VARCHAR(MAX), @MT1 VARCHAR(MAX)
Declare @Query VARCHAR(MAX)
SET @YR2 = YEAR(dateadd(month, datediff(month, 0, getdate())-2, 0))
SET @YR1 = YEAR(dateadd(month, datediff(month, 0, getdate())-1, 0))
SET @MT2 = FORMAT(MONTH(dateadd(month, datediff(month, 0, getdate())-2, 0)),'00')
SET @MT1 = FORMAT(MONTH(dateadd(month, datediff(month, 0, getdate())-1, 0)),'00')

Set @Query = 'Select * FROM 
             (Select '+@MT2+'.'+@YR2+' as [Starting Point],ACCT_NBR as [Account],NBR_DUE From dbo.EOM_DB99_CalculatedValues_' 
             + @YR2 + @MT2 + ' UNION ALL
              Select '+@MT1+'.'+@YR1+' as [Starting Point],ACCT_NBR as [Account],NBR_DUE From dbo.EOM_DB99_CalculatedValues_' 
             + @YR1 + @MT1 + ') a Where a.Account IN (Accounts)'

Exec(@Query)

I was needing to automate a report that always uses the previous 2 EOM tables from the database and this was the only way I could get it to work in SSMS. Now I just need it to work in Report Builder 2014. I am open to ideas!

output

question from:https://stackoverflow.com/questions/65649598/report-builder-not-listing-any-fields-from-query-using-execcustomquery-to-out

Answer 1

SSRS may not be able to determine the fields without executing the query you you could do two things..

NOTE: Both of these assume that the field names and datatype will always be the same. SSRS expects the same result structure each time.

Options 1:

Create a temp table with the desired structure and stick the results in there like this (swap column names and datatypes out as required)

...
Set @Query = 'Select * FROM 
             (Select '+@MT2+'.'+@YR2+' as [Starting Point],ACCT_NBR as [Account],NBR_DUE From dbo.EOM_DB99_CalculatedValues_' 
             + @YR2 + @MT2 + ' UNION ALL
              Select '+@MT1+'.'+@YR1+' as [Starting Point],ACCT_NBR as [Account],NBR_DUE From dbo.EOM_DB99_CalculatedValues_' 
             + @YR1 + @MT1 + ')'
...
CREATE TABLE #t (StartingPoint varchar(10), Account varchar(20), NBR_DUE INT)

INSERT INTO #t
    EXEC(@Query)

SELECT * FROM #t WHERE Account IN (@Accounts)

NOTE: @Accounts is a multi-value parameter p[assed in from the report.

As the structure of #t is defined in the query SSRS will accept this without any problems. As I said, you will have to change datatype and names to suit but hopefully that will give you enough to solve the problem.

Option 2:

Add all the fields manually to the dataset - I 'think' this will work but I have not tried it.