SSRS Shared Dataset - a dynamic, nested EXEC

Posted on 2012-09-17
Last Modified: 2012-09-18
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.
Question by:jdallen75
    LVL 22

    Accepted Solution

    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.
    LVL 37

    Expert Comment

    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.

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now