Post reply

Using dynamic datasets/Pass in table name to use as dataset

  • October 11, 2011 at 3:39 am

    #245179

    I want the source for my reports to be temporary tables created before the report is called

    In other words the report itself does not do the data crunching merely picks up the data in a temp table

    The reason for doing this is because the application generates some very complex SQL with security controlled parameters enforced at the application level.

    To achieve this I have to pass in the name of the temp table as it will change name constanty, in fact, each time the report is run it will have a different name

    I expect this has been done before or maybe there's a better way but I couldn't see much out there about how to do this

    The report parameter 'SpoolID' contains the unique number of the report which equates to the name of the temp table

    I couldn't find an easy way to specify a table name based on a parameter, but this worked:

    EXEC ('SELECT * FROM OpenQuery([.], ''select * FROM mydb.db_owner.Report' +@SpoolID + ''') query')

    - This makes use of a linked server [.] which is actually the server linked to itself, eg:

    EXEC sp_addlinkedserver @server='.', @srvproduct='', @provider='SQLOLEDB', @datasrc='SERVERNAME\.'

    The only reason to use linked servers is that OpenQuery permits a string to be passed which can then be modified with the parameter. Maybe a BaseTable query would work as well

    Now when the report is run I can pass in the number of the report and it will pick up the correct source table

    Now SSRS behaves a lot more like report writers I've used before :blush:

  • October 11, 2011 at 8:10 pm

    #1393210

    You can retrieve data from a stored procedures with multiple input parameters

  • January 16, 2015 at 1:20 pm

    #1770885

    hello martin chantler,

    I know its a old form from 2011 but I am actually facing the same problem now that you explained before so I am sending this message in hope that you found a solution to this problem.plese please let me know if you found any solution for this or atleast i was hoping you will give me an idea of how to do this. thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply