Getting started with using Q&A / Vision 6 to report on other databases.

Most users of SunSystems are familiar with using Q&A / Vision to report on their SunSystems data, but many are not aware you can use it to report on pretty much any type of database.  This makes your Vision Q&A skills even more useful, and opens up the possibility of creating reports that combine Sun data with information from other sources.

You may be aware that Vision Q&A connects to the underlying database using a 'datalink' (formerly known as a 'serduct').  When you buy it with SunSystems the datalink to connect to the Sun database will be present as part of the standard install process.  It is possible to purchase datalinks for other well-known applications (e.g SAP, Earnie Payroll etc.), these will have been created using an additional module called 'EIF studio' (formerly known as 'Serduct studio'), which you can also purchase to design your own datalinks.

However, if you don't want to pay any more there is a method of creating your own datalinks that comes with a standard Vision Q&A installation.  It is to be found within the 'Maintenance' option within the 'Query and Analysis' or 'Vision' set of options from the Windows 'Start' button.  The exact path may vary depending on the version of Vision Q&A you have and how it was installed.  You will be asked to provide a password - this is the system administrator password, not your regular Vision Q&A log-on.  By default this is blank, but obviously your system admin may well have changed this.

Once logged in, you should see the standard maintenance screen.



Click the 'Client Setup' option and you will see a list of the connections you currently have.  You will have at least one connection to SunSystems and one to either 'Query and Analysis' or 'Vision'.



On the right hand side, click the 'Add' button, select 'database' and then select the database type you want to connect to.  In this example I am going to connect to a Microsoft Access database but other choices include SQL Server, Oracle, Excel or an 'other' option which allows you to specify a connection string to any ODBC compliant database.

Depending on the type of database you select you will have some further parameters to supply.  For an access database you must supply the path to the database file.  Depending on security setup you may or may not need to supply a system database (which is used to hold user security information) and a log-in ID and password.



In my example I am connecting to a database which holds details of conversations I have had with customers.  Clicking 'Open' will take me to a set of general fields that can be edited if necessary.



The important bits however are on the 'Tables' tab.  Here we have a list of the tables (blue background) and the individual fields contained in those tables.



Tick those that you want accessible via Vision Q&A and then click the 'Execute' button.  You should get a message to let you know that the process completed successfully.  You can now exit the database information window and in the client setup window select your new datalink, 'Test' the connection and then 'Save'.  The new datalink is now ready to go!  Log into Vision Q&A XL in the normal way.

After the log-in process has completed, you should be able to select your new database as an additional product from any of the extraction functions (e.g. summary link, detail report etc.) and use it as you would a normal SunSystems formula.



There are some restrictions with this type of datalink - the most notable of which is that you can only have a maximum of 5 tables from the source database included in your datalink, and there is no facility to link one table to another in the way that you often see in the standard Sun datalink where, for example, the ledger account code links to the chart of accounts table.  There is no limitation on using two or more products in the same report, so in this example as long as my customer code is the same in Sun as it is in my customer contacts database I could get the balance on their account from a summary link formula looking at Sun data, and a list of the contact notes from my contacts database all on the same page of an Excel workbook using the same cell as the source for the customer code.

If you need the extended functionality described above you will have to buy the EIF studio.  But if you can work within these limitations, then you already have all you need to get cracking, so I hope this starter guide helps you along the way.  Do email me at andrew@zoanda.com with any questions or comments.

Back to home page