Extracting Data from Vista

Using Vista Tools to Gather the Data You Need

Extracting data is one of the key reasons you’ve implemented an HR, Benefits, Payroll product. You spend lots of time collecting data just so you can access it easily and Vista has made it easy for you to get to your data!

Of course, Vista uses an open database that allows access from other products (such as Microsoft Excel) and by using our secured database views, Vista Security can be easily enforced.

This article will explore the various tools within Vista that are available for pulling data out of your database(s). You may be surprised to learn that you’ve forgotten one or two ways!

• Crystal Reports®
• Vista EasyAsk®
• Table/View Export Function
• Vista Analytics (optional add-on component)
• Vista External Interfaces

Finding the Data

Don’t know where the data is stored? Some of the Vista tools described below can help you gather SQL to extract data. However, the power of Vista is understanding the data model so you may want to consider additional PDS training in this area.

To aid in finding Vista data within the database we have provided a report that is available within the Vista Administration Reporting application.

By executing the “Vista Database Views” report you will get a listing of all PDS delivered database views and columns. This may be very helpful in locating the data you want.

Not an expert in SQL? We understand that this may not be your area of expertise. To learn more about SQL, keep in mind that PDS has developed a series of SQL Workshops for our User Conference events that could help you to learn more. These workshops are an excellent way to learn the basic building blocks of using SQL.

Below is a list of the various Vista tools along with a high-level review of functionality.

Now let’s review the various Vista tools and discuss their strengths and weaknesses.

Crystal Reports

Vista provides the ability to execute any Crystal reports and easily select one of the Excel “Output Types” to extract any type of Vista data into a spreadsheet.

A few different options are available depending upon the data content desired. Each of the options are described below:

• “MS Excel” creates a page-based format that converts your report contents into Excel cells and retains most report formatting but does not export line or box objects from your report.
• “MS Excel Record” Data only is a record-based format that is useful for data transfer but retains less formatting information than the Microsoft Excel format does.  (This format is the easiest to utilize for sorting, etc.)
• “MS Excel Workbook” is similar to the above, “MS Excel Record”.

One other very valuable benefit from using Crystal Reports, if you already have a report that contains the data you’re interested in extracting, you can easily copy the SQL used to get you started on an extract, analytic, or interface. Please note that Crystal does not include all SQL in their copy function, e.g., selection criteria from the report is not included.

Vista EasyAsk

Our English-language query toolset provides a simple means of extracting data from Vista. Your data request (easily entered in English) is translated into SQL, and then generates a listing of data requested. Users can then easily display the results as a spreadsheet.

One other valuable benefit from using Vista EasyAsk, is that the user can display the SQL used in the query and they can then copy the SQL, containing the views and columns that can be pasted in an extract, analytic, or interface. One negative of this tool is that not all Vista tables are accessible within EasyAsk, as we have coded only user reporting data.

Table/View Export Function

This powerful tool provides the means to easily extract one database table or database view and create a single CSV per data set. The tool provides a simple, secured method of extracting unlimited data, and even the means to qualifying the selection of data by entering code to define filtered data. A simple link to extract the data in a CSV is presented to the User. This function is typically locked down and limited access is available.

Vista Analytics

This extremely powerful tool provides the means to configure any type of data presentations within dashboards that can be defined by user. Analytics are configured via the entry of simple or complex SQL that generates the data results which can then be formatted into grids or graphs for presentation purposes.

The Vista External Interface tools provides users with the means to develop exports of any Vista data using SQL that they input. Interfaces can be secured, scheduled, and can automatically deliver results via email, etc.

Please note that in addition to the above tools, PDS provides users with the means to develop custom solutions using stored procedures which can be called from a Vista Enterprise Server job. Past User Conference sessions and workshops have illustrated many examples of these.

Hopefully you’ve found this helpful. Earlier I had said that ‘You may be surprised to learn that you’ve forgotten one or two ways!‘ But then again, I may learn of creative ways that you have found! If you have, I’d love to hear them. Please consider sharing your creative solution, by sending me an email and I’ll be happy to promote your ideas with others!