none
Change SSRS Model's datasource view's connection string

    Dotaz

  • I am publishing a report model, based on a data source view (obviously) to my SSRS 2008 w/SharePoint integrated mode reporting environment.  

    I have 3 data sources to three different production databases, all have exactly the same schema.  I want to at run time, via a parameter or some other mechanism, choose which database to use as the source for the report model. 

    Is there any way to do this for reports that are using report models as a share data source?  I would like to avoid triplicating the data source views and report models (one for each environment).
    4. května 2009 14:44

Všechny reakce

  • frickg,

    Does the one SharePoint site need to access all 3 different data sources when the same user is accessing the application, or is it the same code, but a different datasource depending on which user is logged in?  I would think that you could somehow associate a parameter based on the web audience. I would also think you could do this in a stored procedure providing the stored procedure has simultaneous access to all three databases.
    4. května 2009 18:01
  • The SharePoint side of things is not a concern, a user has access to all the reports or not, and therefore the underlying databases.

    The approach of passing a parameter to the report to drive the connection behind the report model is what I am looking for but cannot find any simple way of doing it.

    The only thing I can think of is to pass a parameter indicating the database to use to the report, somehow trigger a SP call in the report to go out to the model's datasource view to change the datasource in the xml... very messy.  Any other thoughts?
    4. května 2009 18:41
  • Maybe I am oversimplifying this, but how about if the stored procedure gets a parameter from the report and the stored procedure says something like:

    If @pVal = "D1"
    Begin
       select * from D1.MyTable

    End

    If @pVal = "D2"
    Begin
       select * from D2.MyTable

    End

    etc...

    If your network is setup to do so your stored procedure shouldn't care which database it is accessing.

    4. května 2009 22:00
  • The reports I am creating are using the report model as the datasource, so there is no sql or stored proc.  I need to flip the reports' models' datasourceviews' data source at report runtime, fun stuff.

    If there is no other way, the only thing I am left with at this point is writing a script to deploy the entire report model and report solution to the server 3 times, and dynamicall change the <identity> tag in the datasource view creating an entire deployment for all three environements, and let the users just run the one they want.


    5. května 2009 16:08
  • Hi,

     

    If you want use model as datasource, you have to deploy it first. That means you cannot change the database dynamically (the connection string doesn’t contains the database name, only the server URL). Why not create different for different database and then choose the model by parameter.

     

    You can combine connecting string via parameter. For example, create a parameter parameter1 and suppose this is the connection string:

    Server=http://localhost/reportserver; datasource=/Models/model1

    You can change it to expression:

    ="Server=http://localhost/reportserver; "&"datasource=/Models/"&Parameters!paramete1.Value

    After that, you can specify the model at run time.

     

    Note: this is not available for shared data source. You have to use embedded data source.

     

    For more information, see:

    http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/48de91f9-1844-40c1-9614-5ead0b4b69a5#P1Q2

     

    http://msdn.microsoft.com/en-us/library/ms156450.aspx

     

    Hope this helps.

    Raymond

     

    6. května 2009 6:56
  • Raymond,

    Thanks for the advice, however I see two problems here:

    1.  Using this approach, I would have to maintain a copy of the model, datasourceview, datasource, report rds, etc. for every environment I want to connect to.  Any time a new requirement comes up that I would have to update all the models.  Ideally the solution would use one model.

    2.  When I follow the recommendation and attempt to set the data source for the report (embedded) to "http://mysharepointserver/Reports/Models/" & Parameters!EName.Value when I attempt to deploy to the server, it fails with the following error (despite the fact that the parameter exists in the report:

    Error 18 An attempt has been made to use a data extension 'RS' that is either not registered for this report server or is not supported in this edition of Reporting Services. C:\Development\Internal\Reporting\Reports\MyReport.rdl

    This I believe is because there is no such thing as an embedded Report Model data source, correct?  


    Am I missing something?
    6. května 2009 14:50
  • You cannot dynamically change the connection string for a data source in report server and a model can be bound only to a single data source.  A scripted approach (publishing the model three times when changed) is how we would recommend handling this scenario.
    6. května 2009 21:16
  • OK, so here is where I wish to rephrase what I said before, because I was just having a similar discussion with our architect who was asking if he needed to install a report server on every installation of SQL Server.  It simply doesn't matter how many databases or even instances a report needs to access because any stored procedure on any database or instance on the network is capable of gathering data from ALL diverse members.

    The data model here should be rewritten to get it's data from a stored procedure, which is fully capable of deciding which database to retrieve data from through the use of a parameter.

     

    6. května 2009 23:34
  • Jerry,

    I agree and already started doing that, the problem is I am uncertain how the model behaves with a query based entity vs a stored proc based entity.  E.g. if I query the model and apply a filter in my rdl when connecting to the model to bring all records for 2009 (something that filters down the dataset) does that push through to the model query or only filter client side?  If so, would a stored proc based entity be able to do the same?

    Aaron,

    Any example of a script to do such a thing?  Since in integrated mode you cannot use RS.exe atm I am simply using a few xcopy commands to dupe the folder, and once you change the datasource metamapping in SharePoint, it retains it even if you copy the files over again.
    7. května 2009 20:23
  • frickg

    Someone correct me if I am wrong, but I really do believe that at run time the model is not even used.  I create most of my reports in Visual Studio, not report builder and I don't even create models unless I need to use Report Builder for some reason.

    From what I can gather the parameters passed to the report are passed by CDE to the datasource and then the datasource result is used by the report.  The report can then further filter the data based on any filtering/sorting that may be defined there. The model is on vacation at this point.



     
    7. května 2009 21:41
  • frickg

    Someone correct me if I am wrong, but I really do believe that at run time the model is not even used.  I create most of my reports in Visual Studio, not report builder and I don't even create models unless I need to use Report Builder for some reason.

    From what I can gather the parameters passed to the report are passed by CDE to the datasource and then the datasource result is used by the report.  The report can then further filter the data based on any filtering/sorting that may be defined there. The model is on vacation at this point.



     
    I just ran an ssrs report which uses the model as a shared data source, while profile the sql box, and it does indeed push the parameter values setup in the report datasource filters (which inherit the parameter values) all the way to the query used.  It also proves true from a timing perspective, running one of my complex reports with no date range takes 10 seconds, and with a single day, 1 second.

    Is that what you were looking for?
    8. května 2009 18:55
  • Jerry--"at runtime the model is not even used", whether this is true or not depends on your definition of "at runtime".  The model is used when you execute a report: the query definition stored in the RDL is in the SMQL (Semantic Modeling Query Language) XML format and this is translated into SQL during report processing by the report server when the report is executed.  Data is retrieved by executing the SQL and streaming this back to report processing (some metadata such as aggregation info can be added to the dataset in this process).  Therefore under most definitions I would say that the model is used at runtime.

    However, it is true that after the dataset is retrieved, the model has no effect on report processing such as filters/sorting/etc.  This is true of all datasets, regardless of their source.

    Frickg--I am looking into scripting for SharePoint mode servers.  I forgot that you can't use rs.exe in that case.
    14. května 2009 21:36
  • Re: scripting for SharePoint mode servers, you can use the SOAP endpoint to deploy reports and models against SharePoint.  One sample application that demonstrates deployment to SharePoint servers is here: http://ssrsfeaturebuilder.codeplex.com/
    14. května 2009 22:41
  • If anyone finds this thread and is looking for a solution that works the following Code Project that was published in 2012 seems to have solved this problem:

    http://www.codeproject.com/Articles/355461/Dynamically-Pointing-to-Shared-Data-Sources-on-SQL

    13. února 2013 15:29