| Author |
Topic |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
 Posted - 2005-08-09 : 11:54:20
|
| Any ideas on how I can dynamically select the Data Source for a report?Here's my story: In a decision that was made before I got here, and will not be modified any time in the near future, we have a set of production databases identical in structure, but different in content. Think of it like an application service provider who created a new database for each client rather than just segmenting the data within one database. Or, think of it like Dev / Test / Production systems. Here's the issue. I'd like to define the report only once, and then upon calling it from a web page, direct it to use the appropriate database.The dataset definition does not need to change other than its Data Source property. Any ideas? And in case it matters, the report uses 6 Data Sets but they all reference the same Data Source.---------------------------EmeraldCityDomains.com |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-09 : 13:31:36
|
quote:
Originally posted by X002548
Can reports be based on a sproc?
Yes, and all of mine are. Except a side glitch is that your sproc can only return one resultset. That's why I have 6 datasets defined.Are you thinking I should use a primary DB that contains my sproc and have it retrieve the data from the appropriate user database? That might work.---------------------------EmeraldCityDomains.com |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-10 : 12:34:13
|
| Appropriate user for SQL permissions? Yeah, probably that or SUSER_SNAME() or one of those, I guess. That's a good question. Not sure that it will be an issue for my scenario, but I'll have to give it more thought to be sure.---------------------------EmeraldCityDomains.com |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-18 : 14:41:03
|
| Well, just to cap off this topic, what we ended up doing was structuring our Report Server so that we have multiple copies of the reports in different folders and each folder has its own shared datasource that specifies the appropriate database. I'm not a fan of multiple copies like this, but we've already gone down the path of having multiple copies of all the other parts of the system, so this is consistent, if not my ideal.---------------------------EmeraldCityDomains.com |
|
|
|
gcowhsu
Starting Member
38 Posts |
Posted - 2005-08-18 : 21:21:50
|
| Your example looks like you only have one report manager, with different copies. That is not good and you may pay for it later. I suggest that you have 2 different report managers. One for test and one for production and then make all the datasources the same for the reports in the same manager.Another way is to have 2 different datasources and make 2 tables. In the visibility put an if statement there that will make the correct table based on the server. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-19 : 13:33:11
|
| We do have two different report managers/report servers, one for production and one for development/testing. That was just an analogy I was using. The real situation is more like an Application Service Provider who has separated data and web pages into multiple databases and sites rather than just partitioning the data in a single database.Your idea to do multiple identical tables is a creative approach and certainly one I had not thought of. However, I would be nervous with having to keep those two in sync with every change from one having to match the other. That sounds like a lot more work than just managing deployment of a single RDL file to multiple locations, especially given that we will have more than just two sources. As the number of sources/systems increased, I would have to add yet another table/datasource to the report and re-deploy it rather than just deploying the latest production version into a new folder.---------------------------EmeraldCityDomains.com |
|
|
|
gcowhsu
Starting Member
38 Posts |
Posted - 2005-08-19 : 18:12:50
|
| You can just make changes to one table and then copy and paste it, change the datasource. I don't know if you can actually make it see which server it is on unless you check the URL. But I know you can do this with the Custom code as a parameter that has only one option, it would return server1, server2... THen you can write an iif statement in the tables visibility based on that parameter.*I think this is what you are doing*What we do is have one report project and we change the target server URL when we want to deploy to differenct places. Then we change the datasource based on which database we want to pull from. |
|
|
|
|
Dynamic Data Sourcing