Data source: sample data
Data set: stored procedure
Create PROC dbo.uspSales
@Region nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @pRegion nvarchar(max)
SET @pRegion = REPLACE(@Region,',',''',''')
DECLARE @SQLCode varchar(max)
--place result set into a temporary table to display the fields
SET @SQLCode='
SELECT Year([OrderDate])[OrderDate]
,[Region]
,[Representant]
,[Category]
,[Item]
,[Units]
,[UnitCost]
,[Total]
INTO ##TempResult
FROM [dbo].[salesCat]
where Region in ('''+@pRegion+''')'
EXECUTE (@SQLcode)
SELECT * FROM ##TempResult
DROP TABLE ##TempResult
END
--------------------------------------------
Exec dbo.uspSales 'Europe,America'
Using a dynamic query and global temporally table. If I would use just the dynamic SQL query, SSRS won't display the fields at all.
Step 1
Add dataset, click on Stored Procedure and find it on the list. Click Refresh Fields button, provide a value for the parameter to display the fields.
Step 2
Add second dataset. This will be the input for the parameter.
Step 3
Adding second dataset
Step 4
Parameter properties
GENERAL: Allow multiple values
AVAILABLE VALUES: Get values from a query
Test the report
댓글