Doing More in Excel with your Kronos Data

In this last article, we reviewed how Kronos Workforce Central provides the ability to export data to Excel format. Now I am going to give you a good reason to stop! This article will talk about a powerful tool that allows seamless integration between Workforce Central and Microsoft Excel, automating spreadsheets. The tool is called Spreadsheet Server™ and was created by one of our partners, Global Software Inc. to address Excel automation. With it you can integrate or completely transform your reporting environment to Microsoft Excel (the 32-bit version only at this time). It can also be used with any relational database and has built-in support for many popular ERP systems. It can be used to create and distribute dynamic spreadsheets and also has drill-down capabilities.

Imagine, Kronos labor data alongside your existing ERP, GL, or AP data… all on one Excel spreadsheet!

Excel as a reporting tool.

The first question typically asked is: Why another reporting tool? Don’t we already have that capability with Kronos to customize existing reports? Yes and no. If your staff consists of those technical people that already can write sophisticated Microsoft SQL Server 2008 reports, then stop reading here. This article is geared towards Microsoft Excel users (those using Excel on a daily basis). One of the advantages of using Microsoft Excel versus the Microsoft SQL Server reporting tool is familiarity. Excel is used everywhere and by everybody, it seems. Per the Global Software home page: “Global's products are used in 40+ countries, by over 4,000 customers worldwide who are supported by Global's 24/7/365 services and support infrastructure.” Also, the chance of having Microsoft Excel available on a PC is more likely than a PC having the Microsoft SQL Server report writing tool. One other advantage is that the software Global Software provides is incredibly easy to use!

The first incredibly easy to use tool I’d like to talk about is Query Designer™. First, what is it? Query Designer can create 4 separate kinds of queries: Summary, Detail, Selective (uses Smart Parameters), or Interactive (On Demand). After creating a query, it can be used inside Excel. The main advantage using Spreadsheet Server’s Query Designer is?

There is no requirement to write complex SQL!

Using the mouse to point & click, the tool will auto-generate the SQL for you (no typing necessary!). Simply choose the table(s) and column(s) you want from the database and right click on a field. It is that easy! If however, you do have complex queries already in-use, those can be ported over to Query Designer. After a query is created, you can choose how you would like to group the data or get even more sophisticated by using parameters for filtering. Let’s take a simple scenario by creating an hours report. I choose the timeframe of the data I would like to group on. Then I can use parameters (Global Software refers to these as Smart Parameters) to provide selection by person number, labor account, or pay code. If necessary, I can use calculated fields (these are similar to Excel functions). Save the query, place it on the Excel sheet, and now a dynamic report is ready for re-use. You can refresh the data at any time inside of Microsoft Excel simply using the Spreadsheet Server plug-in.

So how does Global Software’s Query Designer hold up to Kronos Workforce Genies™? With Query Designer you are no longer limited to the fields (column sets) that Kronos provides in Workforce Timekeeper. Any field in the Kronos database can be displayed in Microsoft Excel using Spreadsheet Server. There are also drill-down capabilities. Using the simple hours report scenario, I can select a worked account and see all of those employees that worked in the account. Any Excel feature can be used to create spectacular looking reports (pivot tables, charts, graphs and named ranges for example). Using Smart Parameters, I can also wildcard any field. This allows for very fast, precise ad-hoc reporting.

Spreadsheet Server also comes with a product known as Query Exchange™ (a cloud-based repository). This product allows queries written with Query Designer to be shared within the organization. You can organize queries centrally or farm out queries to other users. Users can then post, evaluate, comment and tag queries online. Rather than filling out a report request, you have the ability just to note a field needs to be added to a particular query (right on the query itself).

 So in summary, here are the key benefits of using Spreadsheet Server:

• Expand existing Kronos reporting capabilities and combine other system data (e.g. AP, ERP, PR, or GL)
• Very intuitive report writer (point & click as opposed to hand writing SQL)
• Built-in support for many existing ERP systems and use with any relational database (like Kronos)
• All typical standard report writing features included along with drill-down capabilities
• Ad-hoc reporting capabilities via Smart Parameters
• Easily access Corporate data and share with executives
• Save time with less steps (no import into Excel): seamless integration with Microsoft Excel
• Larger reports may display faster in Excel (as opposed to using a web browser for a large dataset)