Creating a report model that can be used against multiple databases

Sometimes it is useful to create a report model that can be used against multiple databases that have the same structure, but reside on different servers and/or have different schema qualifiers.

Uploading a second copy of a report model and pointing it at a different database is certainly easy enough, but you can run into issues if the second database has different schema qualifiers. The problem is that, by default, the Data Source View wizard creates a DSV that includes schema qualifiers in the bindings. If those qualifiers are not necessary (i.e. the bindings can be fully resolved if a default database is specified in your data source connection string), you can make your report model work for the second database by opening the .dsv file in a text editor and use Find/Replace to simply remove all the schema qualifiers.

Test it out against the old and new databases, and you should be good to go.

Comments

• Anonymous
  October 26, 2006
  Hi,As as SaaS provider we use a DPE to dynamically change reporting datasources at runtime so we can have a common report server that works across all our customers database catalogs. Most are of exactly the same schema structure & version.What would be most useful is if the DataSource (Not DSV) could utilise the DPE connection. No other providers show up when create a DataSource. Beacuse of this we have to roll out a new model each time we give report builder access to our customers.Is there a manual way of doing this or is this feature scheduled for a future update.RegardsTobi
• Anonymous
  October 27, 2006
  So your back-end databases are really SQL Server, but the report server doesn't know it because you've inserted your custom DPE in the middle. I'm not sure how we could support this unless we allowed you to "claim" when registering your data extension that it supports SQL Server syntax, and therefore should be a valid target for SQL-flavored semantic query translation. No plans for adding this right now, but I'll definitely forward your suggestion.
• Anonymous
  October 31, 2006
  Hey Bob,  on the topic of running a report against multiple databases, is it possible to use the database name as a parameter?  That is, the user would select which database they wish to run the report against?  
• Anonymous
  October 16, 2007
  hi,are there code samples available for DPE, we also are a SaaS provider and need to change the database namedynamically underneath a report modelThanksAjay
• Anonymous
  October 24, 2007
  HelloI hope this topic is still active!!I too have a report model that can be used against multiple databases ( all identical) I need to be able to dynamically change which database it points to when my report runs. Is it possible to do this??Thanks,Kelly