PDA

View Full Version : Set up Connection for report distribution



NicT
20-Jun-2013, 07:48 PM
Hi all,

We are in the process of rolling out a pre-defined set of read-only reports using Dynamic AI to some of our existing Autosoft (VDF app, SQL Server db) customers. Bob Cergol has been very helpful with this so far. I wanted to swap our discussion over into the forum area so that more people can A) share in the discoveries and B) help guide the discussion. I have two problems - 1 right now and another likely in the near future.

Problem number 1: Distribution of DAI to each new customer site requires modification of the SQL Server instance name and DB name after DAI is installed.
At the moment, we are doing this manually, and that is OK at the moment, but it would be nicer to have this be able to be done programmatically. Bob has sent me a separate email describing an interface to the DAI repository from VDF which can modify the instance and db names, though I haven't tested this out yet. That's all well and good, except for problem number 2.

Problem number 2: Ability to run the same set of reports on different databases on the one client site, ie. different DBs on the one SqlSvr instance.
What I have read so far, including Bob's process, seems to say that a report uses one connection, and one connection has one "value" (ie. points to a single datasource) at a time in the DAI server. And THIS implies that we can NOT have two different users on the one server looking at the same set of reports but pointing to different databases at the one time. This is a problem because some of our customers will not use DAI unless we can allow this to happen.

My question:
-- Is my conclusion for Problem 2 correct?
-- If so, is there any way around this?
-- If not, how does this work?
-- Is it possible to change the DB connection used by a report at runtime?

Thanks in anticipation,

Nic Townsend
Autosoft, Sydney

Bob Cergol
20-Jun-2013, 09:06 PM
Nic,

Re 1) Yes, the repository can be accessed via ODBC so you could programatically set values in connections, etc. Ideally a VDF application would be deployed such that pretty much the only thing that would vary from site-to-site would be the actual server name hosting the database. There is also a simple API for managing users and groups. I made a VDF application that uses this API to manage the Dynamic AI demonstration portal (http://www.dynamicai.com/en/Try-Dynamic-AI/Try-Live-Demo-Portal-608) that creates the new users and emails their credentials to them. I also use it to send follow-up emails.

Re 2) You can definitely configure Dynamic AI to run a single report against different Databases. At run time, a drop-down will present available databases that a given user has rights to, and the report will then use that database. Of course the table names and structure has to be the same. You should be able to specify the database to use in URL with a parameter. I will have to find out what it is as I have never needed to do that.

But here's what it looks like to setup and how the user can choose the connection in the report:
65576558

Regards,
Bob

Bob Cergol
20-Jun-2013, 09:36 PM
P.S. A bit different, but related to this subject: Using what Dynamic AI calls partitioning, you can also point a report to different table names within the same Database. For example you might have a database where separate tables hold similar data for different divisions or years, or whatever, e.g. Invoices_US, Invoices_MX, Invoices_CA, or Invoices_2010, Invoices_2011, etc. In the dyn-view used to build the report a list of partitions is referenced and parameters placed in the view script. At report run time, a drop down is generated from the partition list and the selection value dynamically replaces the table names referenced in the view. This capability supports passing a host of other parameter values into the dynamically generated SQL query as well. I can send a doc on this to anyone interested.

Eddy Kleinjan
21-Jun-2013, 03:04 AM
Nic,

You can also put the name of the database (and/or server) you want to connect to in an alias of the user. For example: Set the Alias10 value of a user to "DatabaseAbc" and set the "Database name" property of the connection to "@ALIAS10". It will then take the value of Alias9 of the logged in user as the database to connect to.

Eddy Kleinjan
Data Access Worldwide