Reporting Services 2000 and Dynamic SQL
I hit a snag with SSRS 2000. I have data for my report which can be filtered by location or groups of locations. The problem is that this filter requires some dynamic SQL on my part:
CREATE PROCEDURE vm_RPT_GetSalesMixData
@pInqType integer
AS
-- set our where clause
Declare @LocNoFilter NVARCHAR(100)
if @pInqType = 0
select @LocNoFilter = ''
if @pInqType = 1 or @pInqType = 2 or @pInqType = 3
select @LocNoFilter = 'where LocNo in (SELECT LocMstr.locno FROM LocMstr WHERE LocMstr.LocType=1 AND LocMstr.zone = ' + cast(@pInqType as nvarchar(3)) + ')'
else
select @LocNoFilter = 'where LocNo = ' + cast(@pInqType as nvarchar(3)) + ' and budgetcat = ''Budget'''
Declare @SQL NVARCHAR(600)
SELECT @SQL = 'SELECT AlphCfg, AlphCfgDesc, Cat, CatDesc, Year, Period, Periodmnth, Sum(LocSales) as SalesTotal FROM tSalesMix '
SELECT @SQL = @SQL + @LocNoFilter
SELECT @SQL = @SQL + ' GROUP BY AlphCfg, AlphCfgDesc, Cat, CatDesc, Year, Period, Periodmnth
ORDER BY AlphCfg, AlphCfgDesc, Cat, CatDesc, Year, Period'
EXEC sp_executesql @SQL
The problem is that when I’m looking at the report’s Fields pane; my stored proc didn’t seem to be returning the fields in such a way that my Reporting Services designer understood.
I wanted something like this: 
but ended up with this: 
.
My stored proc was definitely returning data, even inside the designer’s Generic Query Designer.
I tried entering the fields manually per this recommendation
This got me some fields in my list, but when I tried to run the report I got errors that the data wasn’t present. So I was about to try out a new approach – generate the data into a temp table and select out of the temp table.
Of course, in the process of building this query I found out that my @LocNoFilter was insufficient so I could change the proc to something that didn’t require dynamic SQL. Don’t you hate it when that happens?
However, had I been forced to continue down this path – the next step would have been to put the results of the query into a temp table and select from the temp table in my stored proc. That would have gotten me fields I could use. The procedure would probably look like this:
CREATE PROCEDURE vm_RPT_GetSalesMixData
@pInqType integer
AS
-- set our where clause
Declare @LocNoFilter NVARCHAR(100)
if @pInqType = 0
select @LocNoFilter = ''
if @pInqType = 1 or @pInqType = 2 or @pInqType = 3
select @LocNoFilter = 'where LocNo in (SELECT LocMstr.locno FROM LocMstr WHERE LocMstr.LocType=1 AND LocMstr.zone = ' + cast(@pInqType as nvarchar(3)) + ')'
else
select @LocNoFilter = 'where LocNo = ' + cast(@pInqType as nvarchar(3)) + ' and budgetcat = ''Budget'''
Declare @SQL NVARCHAR(600)
SELECT @SQL = 'SELECT AlphCfg, AlphCfgDesc, Cat, CatDesc, Year, Period, Periodmnth, Sum(LocSales) as SalesTotal
INTO @TempTable FROM tSalesMix '
SELECT @SQL = @SQL + @LocNoFilter
SELECT @SQL = @SQL + ' GROUP BY AlphCfg, AlphCfgDesc, Cat, CatDesc, Year, Period, Periodmnth
ORDER BY AlphCfg, AlphCfgDesc, Cat, CatDesc, Year, Period'
EXEC sp_executesql @SQL
SELECT * FROM @TempTable
I know that there are flaws in the above query - I never tried to use it. This is only a sample indicating the direction I would have gone.