locked
Dynamic Connection string for SSRS Report RRS feed

  • Question

  • Hi,

    My scenario is like this:

    1.  We have a SaaS based application, clients signup on the website.   As soon as they sign up a new database is automatically created for them and their users connect to that database.
    2.  All the reports for all clients are identical except for the fact that their data should come from their respective database.
    3.  I have created reports on SSRS as rdl files and deployed them using a generic (non-client specific) database.
    4.  How can I dynamically pass/modify the connection string to SSRS so that clients can view reports coming out of their respective database.
    5.  I cannot hard-code data source for reports because client databases are dynamically added.

    I guess my requirement cannot be handled by the shared datasources either.  Correct and guide me if I am wrong.

    Any help would be greatly appreciated.

    Thanks.

    AM
    Tuesday, August 11, 2009 12:18 AM

Answers

  • One option to consider is using an expression-based connection string (see http://msdn2.microsoft.com/en-us/library/ms156450.aspx - scroll to the "Data Source expressions" section).  However, note that expression-based connection strings only work with embedded data sources, not shared data sources.

    Another alternative option, if the source databases of your data sources all reside on the same SQL Server instance, is to use just one data source to connect and then use the three-part name (catalog.schema.object_name) to execute queries or invoke stored procedures in different databases. 

    HTH,
    Robert


    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, August 11, 2009 3:32 AM

All replies

  • One option to consider is using an expression-based connection string (see http://msdn2.microsoft.com/en-us/library/ms156450.aspx - scroll to the "Data Source expressions" section).  However, note that expression-based connection strings only work with embedded data sources, not shared data sources.

    Another alternative option, if the source databases of your data sources all reside on the same SQL Server instance, is to use just one data source to connect and then use the three-part name (catalog.schema.object_name) to execute queries or invoke stored procedures in different databases. 

    HTH,
    Robert


    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, August 11, 2009 3:32 AM
  • Thanks a lot Robert.  It was really helpful.

    However it took a while to find out that the "Server" and "Catalog" parameters that I defined for expression must be on top of the parameters list.

    Thanks again.
    Tuesday, August 11, 2009 3:34 PM
  • Hi,

    I am using the connection string like this:

    ="" & Parameters!ConnectionString.Value

    It works for the report.


    But I got into another interesting issue.

    I have a properties dropdown that is filled through a query. 
    The query looks like this:
    "select DepartmentName from Department"

    The report was giving me error that the dataset could not be loaded for that dropdown.  I had all the access rights set up correctly.
    To find out what is happening I changed my query to:
    "select db_name() as DepartmentName "

    And guess what, I found out that the database was "master".

    If I remove this parameter from my report definition everything works fine.  This is my only parameter which is coming from a query.

    I don't understand why the database is "master" even though I have defined the Initial Catalog in my connection string, and ALSO the report runs fine (after removing this parameter) using the correct database defined in connectionstring.

    If I change my query to:
    "select myDbName.dbo.DepartmentName from Department"
    every thing works just fine.

    But that's not I want, my database names cannot be hard coded as I mentioned in my original post.

    Any ideas?.
    Tuesday, August 11, 2009 6:12 PM