SQL Server report based on stored procedure not showing columns I'm working in SQL Server 2008 R2. Created a stored procedure (EXEC dynamic SQL with CASE THEN ELSE ) on server with parameters works ok. But Report Builder on insert table is not showing columns for arrange!

I found WORKAROUND http://forums.yessoftware.com/posts.php?post_id=115829:

But there is a easy way?

simpified code

ALTER PROCEDURE [dbo].[VetCertTest] @Store char(5), @Date char(8)
AS
BEGIN
DECLARE @Log char(7) = 'MOS_DIS'
DECLARE @sqlQuery VARCHAR(8000) = 'SELECT 
  rtiersS.alias store
, rfampr.lb as type
FROM 
 presta_ru01.rul
, presta_ru01.rtiers rtiersS
, presta_ru01.pporte
, presta_ru01.rub
, presta_ru01.rfampr 
WHERE 
    rfampr.rdoss_code = ''''' + @Log + ''''' 
AND rfampr.code = rub.rfampr_code 
AND rub.code = rul.rub_code 
AND rtiersS.code = pporte.rtiers_code_dest 
AND dhr_mvt &gt;= to_date(''''' + @Date + ''''',''''DDMMYYYY'''') 
AND dhr_mvt &lt; to_date(''''' + @Date + ''''',''''DDMMYYYY'''')+1  
' + CASE WHEN @Store = 'MA000' THEN '' ELSE 'AND rtiersS.alias = ''''' + @Store + '''''' END + ' 
ORDER BY rtiersS.alias, rtiersS.nom1, rfampr.lb'
DECLARE @finalQuery VARCHAR(8000) = 'SELECT * FROM OPENQUERY(WMSFELRU,' + '''' + @sqlQuery + '''' + ')'
EXEC(@finalQuery)
END


solved - add defaults to parameters and Report Builder can detect columns

Its a Report Builder bug - it not ask for parameters when refresh fields

IF @Store IS NULL SET @Store = 'MA000'
IF @Date IS NULL SET @Date = '19092014'
Add Fields manually which not refreshed under Dataset properties.

Datasets --> Right Click --> Properties --> Fields --> Add.

mentioned column name and parameter name as per query.