External Reports

Overview

In addition to the "internal" reports available from KeyConfigure, you can also query K2 data tables using your favorite "external" SQL reporting tool. This capability lets you build your own reports with customized formatting and data summaries. Since most SQL reporting tools can query data from multiple data sources, this feature might also be used to build a custom report that joins any of K2's databases to other corporate databases.

Regardless of what platform is hosting the K2 server process, there is no need to export K2 data in order to query the data using an external reporting tool. You can use any external reporting tool that can be configured to send SQL queries through ODBC - you must have set up a DSN (Data Source Name) that is configured to use the KeyServer Driver, ksODBC. Queries will be translated and conveyed to the K2 server through the ODBC driver. K2Admin.exe for Windows installs the KeyServer Driver by default - this installer can also be used to install just the ksODBC driver and external report examples without installing KeyConfigure.

External Reports Folder

The K2Admin.exe installer creates an External Reports sub-folder within the Admin folder (available from the Start menu). Note: all of our external report examples are available only for reporting tools run on a Windows computer. Similar reports can be constructed using FileMaker on Macintosh (or Windows). The example reports are designed to demonstrate how to setup database joins using the various KeyServer data tables, and to show example summary calculations that may be useful.

In general, there will be four files illustrating each example report:

• a Crystal Reports file
• a Microsoft Access File
• a pdf showing example output (generated from the Crystal report)
• a text file describing what the report shows, and anything of interest about how the report files are set up

These files are organized into four main folders (Documentation, Sample PDFs, MS Access Reports, Crystal Reports), and then subdirectories as follows:

• The “Audits” directory contains reports which give information about what programs are installed on client computers.
• The “Usage” directory contains reports which give information about when programs and licenses were used on each computer.
• The “Node Lock” directory contains reports which deal with licenses dedicated for use only on particular computer nodes.
• The “Unfinished” directory contains reports which are not well formatted or ready for immediate use, but may be useful as examples of how certain things can be done with the data which KeyServer provides. Note that currently the MS Access Reports directory does not contain this subdirectory. This is because we have found that it is much faster to prototype and develop reports in Crystal Reports than it is in Microsoft Access.

The example reports are similar to some of the internal reports so unless you modify them, they are redundant and of little interest. However, they may serve as a valuable starting point for customization to suit your special reporting needs. In many of the example reports, there are very simple ways to change how the data is organized, what data is selected to be shown, etc. With Crystal Reports, this is accomplished through formula fields. In Microsoft Access, this is accomplished either with column aliases in the main query, or by field names in the report. In either type of file, the objects which are easy to change, and which will result in useful variations, have names which begin with underscores (e.g. “_threshhold”). This is to make it easy for you to find specific parts of the reports which you might want to change, while ignoring the parts which will probably remain the same.

Assign a password to the External Report Account

By default, the installer K2Admin.exe creates a DSN called “KeyServer Datasource”. It points to the local machine at address 127.0.0.1 and is configured with the default user name "External Report Account" and password "Sassafras". This DSN is setup to use the External Report Account that is automatically created by the KeyServer installation. However, before this account can be used, it must be given a matching password!

Use the KeyConfigure Admin menu item "KeyConfigure Accounts..." to select the account "External Report Account" from the drop down menu and then set the password to "Sassafras" so it matches the DSN setup. For data privacy it is actually preferable to configure a custom password (instead of "Sassafras") for both the External Report Account and for the matching KeyServer Datasource..

When running external reports on a separate computer (e.g. not the KeyServer host), you will have to configure a DSN with the correct address of your KeyServer (instead of 127.0.0.1 created by the default install). After using k2admin.exe to install the ksODBC drivier, use the Windows administrative control panel "Data Sources (ODBC)" to modify the “KeyServer Datasource” DSN. Alternatively, you can create and configure a new DSN (with a different name but likewise based on the KeyServer Driver). Note: you will then also have to specify this new DSN name in the example reports.

Changing reports to use a different DSN

All the example reports are setup to use the default DSN called “KeyServer Datasource”. You can create your own DSN using the Windows administrative control panel "Data Sources (ODBC)". Point it to the correct KeyServer address and use the "External Report Account" with the correct password (as configured in the "KeyConfigure Accounts..." dialog).

To change the reports to use a different DSN:

• For a Crystal Report, choose “Set Location” from the Database Menu. Select any of the databases in the list, and click the “Set Location...” button. Find your DSN, and choose the corresponding table. Click “Set”. If you get a dialog that says “Propagate server and database changes across tables with the same original information?”, click Yes. Then click Done in the dialog box.
• For a Microsoft Access report, go to the tables window, right-click on one, and select “Linked Table Manager”, at the bottom of the menu. In the dialog that comes up, make sure that the “Always prompt for new location” checkbox at the bottom of the window is checked (on). Then click the “Select All” button, then the “OK” button. Then find the correct DSN, select it, and click OK. (You may have to select the DSN once for every table, or you may only have to do it one time).

If the K2 data tables have been exported to a dedicated database server (see exporting), then you can create a DSN that uses the corresponding ODBC driver, database server address, account name, and password - these same external reports will then use the dedicated database server as data source.

Note: in Crystal Reports, when you change between a DSN using the KeyServer Driver and a DSN using some other ODBC driver, the reports may say that tables have changed. This is because many fields have different lengths if they have been exported. The reports will still work, so simply ignore the warning message.

Performance Notes

The KeyServer ODBC driver has various optimizations to help reports run quickly. The most important optimization reformats original SQL queries, parsing out many common joins so they will be performed on the K2 server. Most reports take advantage of this optimization heavily. However, it is possible by adding more tables or changing how the SQL query is arranged, to prevent this optimization from occurring. This will result in a drastic slow down.

You are more likely to end up with a slow report in Microsoft Access than you are in Crystal Reports. This is for a few reasons:

• For certain queries, Access will decide not to send the query to the driver as a single SQL query, but rather to send part of it, and then for each row in the result set, send a second SQL query. This happens in particular when you have a mix of inner joins and left outer joins. Because the driver is not given the entire original query, it cannot even attempt to optimize the query.
• Microsoft Access may use a single query, but within the query, decide to use a mix of implicit joins (two tables separated by a comma, with a where clause specifying the fields to link) and explicit joins ( {oj ta INNER JOIN tb ON ta.x=tb.x} ). When these two styles of joins are combined in a single query, sometimes they can be optimized, but in certain cases, they cannot be optimized as much as if the query were to use only implicit joins, or only explicit joins.
• Some reports which are done as a single query in Crystal Reports require multiple queries in Access. For example, all of the reports that have a distinct count in the report header (of computers, licenses, or programs) require separate queries in Access to compute these counts. So a report with two distinct counts is roughly three times slower in Access than it is in Crystal.

If your K2 server is tracking usage for hundreds of programs on many thousands of clients for dozens of months, the usage database will become very large and reports will slow in response. This is a situation that may benefit from export to a high performance, dedicated, database server. The DSN configuration details documented above explain how to continue using your custom external reports. You can also continue using internal reports (Windows KeyConfigure only) - use the Option button from the internal report interface to re-direct the data source using a properly configured DSN that points to the dedicated database server (instead of the default internal target, KeyServer).

Display of ID fields

All of the ID fields in the KeyServer tables are of type SQL_C_BINARY. This type is used to store arbitrary data, which is not necessarily a string or a number. When Crystal Reports sees a column with this data type, it displays a string representation of the hex values contained in the field. This allows you to see the data in a readable format, and compare data from one field to another field. Microsoft Access, on the other hand, treats binary data as unicode, and simply displays whatever characters the data happens to encode. As a result, what you will see may be garbage characters (letters with accents, mixed with characters showing a square box), or it may be non-roman characters (Japanese, Russian, Arabic, etc).

If you are using Access and want to be able to read the values in the ID fields, create a macro containing the function below:

   Public Function DataToHex(udata As String) As String

   Dim ByteData() As Byte
   Dim i As Long
   Dim ret As String
   Dim htxt As String

   ByteData = udata
   ret = ""
   For i = 0 to UBound(ByteData)
        htxt = Hex(ByteData(i))
        If Len(htxt) < 2 Then htxt = "0" & htx
        ret = ret & htxt
   Next
   DataToHex = ret
   End Function

Then, when you design your query, instead of including an ID field directly (e.g. “licenseID”), use an expression like “DataToHex(licenseID)”.

Printing reports to A4

Neither Crystal nor Access seem to have an easy, automatic way to convert reports from Letter size to A4 size. Here are instructions to make the borders slightly smaller, so the same content can fit on an A4 page without clipping.

Microsoft Access:

• Go to “Page Setup”, in the File menu.
• Change Left and Right margins from .5 inches to .35 inches.
• Click on the Page tab and choose A4 from the dropdown menu.
• Click OK.

Crystal Reports:

• Go to “Printer Setup” in the File menu.
• Choose A4 paper.
• Under the printer name, make sure “Default Properties” is NOT checked.
• Click OK.
• Go to “Page Setup”, in the File menu.
• Change Left and Right margins from .5 inches to .35 inches.
• Click OK.

Example SQL

In case you are writing direct SQL queries, instead of using a “Builder” interface, here is an example of an SQL query. In this case, it is selecting quit events for logged and controlled programs within a certain time range, and selecting relevant fields that might be useful in a report:

SELECT programVariantName,programVariant,programID,programPlatform,programVariantVersion,programFlags,programAction,usageUser,usageTime,usageWhen,usageOtherTime
            FROM {oj KSUsage INNER JOIN KSPrograms ON KSUsage.usageProgramID=KSPrograms.programID AND KSUsage.usageServerID=KSPrograms.programServerID }
            WHERE (usageEvent=4 OR usageEvent=9 OR usageEvent=11 OR usageEvent=13) AND programAction>1
            AND usageWhen>={ts '2008-12-01 00:00:00'} AND usageOtherTime<={ts '2008-12-02 00:00:00'}
            ORDER BY programVariantName,programVariant,usageUser

Note that this example is selecting any  quits where the use of the program overlapped a certain day whatsoever. If you are dealing with the results in some sort of programming interface, such as php, then the code dealing with the results can adjust to only count the overlap, instead of the entire duration. Also, since the SQL is not doing grouping and summarization, the results may not be terribly interesting unless some summarization is done on them after the fact. You could use SQL's GROUP BY syntax to do some summarization, but you would be limited to a single level of summarization - unlike the internal reports which often have two levels of detail.

Related Topics