SSRS Shared Dataset - a dynamic, nested EXEC

We have a number of "GetData" SP's to fetch analytical data, as well as a new "CreateHeatMap" SP (CLR assembly, actually) which is used to create a spatial dataset in order to turn a Map into a TreeMap.

It took some work, but I was able to form a nested EXEC text query for a shared dataset in SSRS 2008 R2:

Shared Dataset Text Query
My only challenge now is how to pass in Parameters in place of the '3', '2', '2', etc. CreateHeatMap expects 3 inputs, the 3rd being text to fetch the recordset which defines the polygons in the TreeMap. It expects one string, and I was having trouble with both "&" or "+" operators in trying to form this essentially dynamic, nested EXEC statement.

Any help on substituting in parameters would be appreciated, if it's possible in this context.
Who is Participating?
Nico BontenbalCommented:
Does something like this work:
declare @exec varchar(8000)
set @exec = 'EXEC spLocal_com__GetDataByDayOfWeek_Spatial ''' + @param1 + ''',''2'',''2'''
exec CreateHeatMap 20,25, @exec

Open in new window

It's only a part of the actual code you need, but I suppose you get the idea. When you have trouble with it you could use this code:
declare @exec varchar(8000)
set @exec = 'EXEC spLocal_com__GetDataByDayOfWeek_Spatial ''' + @param1 + ''',''2'',''2'''
select  @exec as [exec]

Open in new window

And create a report that shows the output. This way you can make sure the @exec parameter has the correct value. Then you change it back again.
ValentinoVBI ConsultantCommented:
You should be able to build your statement dynamically using the "fx" button to the right of the query box.  Here's an example of a dynamic select statement built that way:

="select '" & Globals!ExecutionTime & "' as ExecutionTime, '" & User!UserID & "' as UserID"

Once your statement is complete, you need to manually add the fields to the dataset.  Just switch to the Fields page, click the Add button as many times as there are fields returned from the SP and give them correct names.  That should do it.
jdallen75Author Commented:
Thanks, @Nicobo... I had already started down that path and got stuck on the apostrophies, but your suggestion to simply select the @exec helped debug this.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.