Multi-Valued Parameters in SSRS2008

From small-mid size company point of view, MS SSRS 2008 does provide an excellent solution, which doesn’t need a professional programmer involved for developing business report.

However, as some special business or client’s requirement, multi-valued parameters need to be added in the report. We know that SSRS have this option in report designer, to enable multi-value parameters, but that doesn’t appear a final solution for some value set which may change in the parameter level.

For instance, one of my clients (farm grower) needs a report which can pull out all the information according to Pivots, while the pivot table sits in a separate database. This is easy I can create a separate dataset to hold the values. However the main data show on the report will relay on the Pivot parameters. The end user will be able to pick maybe 1 Pivot parameter, maybe 2, maybe n or may want to see all of them.

So if you create a SQL stored procedures, how could you determine how many parameters the client wants? It’s variable. The SQL Stored procedure worked for a single selected value only.

After thinking for a while, I found a way to achieve this requirement.

I decide to use dynamic SQL and put varchar(max) as one of the parameters’ type. This will allow me to put a text string in to the query and max size of it is 2^30-1 bytes. Normally, as a parameter won’t be longer than this value.

@param varchar(max)

In the script I declare a sql command as a variable:

declare @Sqlcmd varchar(max)

Next step is creating a temp table which can holds all the value, that parameter contains. Now a new problem raised, SSRS will generate a multi-selected parameter and put comma between the values. How to turn this special parameter into a table?

Here is my solution:

set @Sqlcmd = 'insert #J ([Pivot]) '

set @Sqlcmd = @Sqlcmd + 'select ''' + RTRIM(REPLACE(CONVERT(NVARCHAR(max), @Pivot), ',', ''' union select ''')) + ''''

exec (@sqlcmd)

Now a temp table #J has been created and contains only one field called [Pivot]. The rest is finishing all the logical part of code and just need to put some simple filer in the where clause:

where 1=1

and [Pivot] in (select [Pivot] from #J)

Now all problems solved and the client will be happy to have their new report with this multi-parameters functions.

由于工作原因我的大部分文章主要为E文

下一篇关于SSRS,我将会讲一下如何在SSRS中嵌入 function code和 hovever over show value.

 

以上文章属原创版权为Jack Ni所有.