Can a report have a dynamic DSN?
-
We have an application with three separate instances. One is for development. A second is currently for "demo" and will probably be for "beta" testing in the future. And the third is the main "production" instance. The same reports need to exist in all three places, except that they need to pull from different databases.
Is there any way to use a variable for the DSN so it could be passed in at run-time? Our developers sometimes forget to adjust the DSN before they deploy to each location, which then means the reports pull from the wrong data-- not a good thing.
Just want to know if this can be avoided somehow.
-
What is the report engine? Crystal.
Are you using a web UI or application UI?
-
Without knowing the answers to the last post yet, I can still tell your going to need a front end form which will ask the developer for the database required before doing anything else... which can be used by users, & testers as needed. This should set all environment settings as well as the database in use.
This should be created as a portal using your current development product (GUI or otherwise)
PS: Your developers need to keep all new development as non-specific between databases, and the databases should be kept as equal as possible, such as schema changes & new data...
Good luck!
Regards,Coach James
-
If your reporting product is Crystal, you can reset the data source at runtime from the front end. If all you want to do is change the DSN, then you have to make sure that every user has all of the possible DSNs on their computers (unless this is a Web app).
If you don't want to deal with DSNs at all (we don't), you can use an OLE DB instead of the DSN-based ODBC. I have VB6 code to do this with Crystal 9; let me know if you want a snippet. Also make sure that all Crystal service packs and the latest MDAC versions are applied.
We just dealt with this very issue last week, as we're migrating to a new database server and faced the prospect of having to modify over 400 reports, but OLE DB saved us from having to do that.
Dana
Connecticut, USA
Dana
Connecticut, USA
Dana -
Could you post some of your code that uses OLE DB instead of using a DSN. We're in the middle of migrating everything to a new server, and all of our users use DSNs to connect to the database. We're looking at writing an app to enumerate through each users registry to find DSNs pointing to the old server and update them to the new server. If this can be avoided, it would be a huge win.
Thanks,
Brian
-
We create a business object on each server (dev, test, train, and production) that provides the connection information. That component is accessed by each object, so as each object is moved from server to server, it always picks up the correct connection string.
This works well with our crystal reports because we use unbound reports, and the objects send the recordset to the report.
-
Wow... so much about Crystal. Actually, we're moving away from Crystal to SQL Reporting Servinces (hence the post in this forum).
The application is written mostly in Cold Fusion (iMIS by Advanced Solutions) with some custom code in ASP/VBScript (by me). The reports are all currently Crystal, deployed within each application directory and served off the 5-user "enterprise" license that comes with Crystal Pro. We're afraid that the 5-user license is not adequate to cover the probable usage and, being non-profit, don't want to spend the money to upgrade to Crystal Enterprise. So we got SQL Reporting Services for almost nothing. Since SQL Reporting Services is licensed the same as SQL Server (per processor), I now have no limit of concurrent usage.
Transferring the reports won't be an issue since the report developer currently updates the report in the test site, then changes the dsn, deploys to the demo site, changes the dsn again and deploys to the live site. The problem is that he's forgotten to change the dsn a couple times on the deploys. So he'd like to see if we can simply deploy the same report to the other "sites" in this case probably separate SQL Reporting Server projects and avoid the need to change the DSN each time by making the DSN (or connection string) a parameter. Using a DSN or connection string is really immaterial since the report actually runs on the server so all users would use the same set of DSNs.
That's the background... no we're NOT going to use Crystal Reports. Yes, we ARE going to use Microsoft SQL Reporting Services.
-
In re-reading that last post, I think I need to state clearly-- this is a WEB application.
-
Not sure to which post you are referring because the time stamps are close, but if it's mine above, I am talking about a web application. The reports are on the IIS box. The client makes a request through an MTS bus obj that returns a recordset from the db and is joined to the unbound report.
This frees us from having to specify connection information within the report itself. That is handled as described above by the bus obj.
-
Actually, I was referring to my last post... in answering the question posted right after my original post, Andrew asked what report engine and what type of application. I answered the report engine quite clealy and the reason we're using it, but hadn't answered the application type as clearly.
So... the question is, is it possible to create an "unbound" report in SQL Reporting Services?
Or, for that matter, could a report inherit its database connection from the project it's part of.
Hmmm... I just noticed that there's a "shared data sources" as part of the project. I wonder how "shared" they are. If each project has it's own set of "shared" sources, then simply using a shared source and re-deploying the report might do it.
-
Great! You can do this:
Create a deployment.asp page for your developer, where he maintains his dsn on test for creating and maintaining your reports, then runs the deployment.asp from your web application when he's done.
Since his reports are files, use SaFileup or other "File Manager Object" to list his reports on test. He selects the report via selection box (multiple is preferred), use javascript to get the file(s) name(s) and use "File Manager Object" to FTP the report(s) from test to each new location.
Now he has automated report redistribution!
Regards,Coach James
-
It took me a couple days to get a report converted from Crystal to SQL RS. I set up this report using a shared data source on my "test" database. Once I was happy with the report, I copied the .rdl file from my test project to my production project, where there is a shared data source with the same name pointing to my production data source. Deployed the report without making a change and it happily used the production data source.
So... If you need the same report in multiple contexts, you can use shared data sources and simply copy and deploy the same report as part of different "solutions" and know that it will use the shared data source from the solution it resides in.
That makes me happy.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply