Overview

KxPlorer

KxPlorer is a product that is part of the Kx Core suite and permits the creation and execution of any number of customised reports, known as Extracts.  Extracts may be grouped, so they can be found easily, and they can be enabled in other applications such as KxStudent or KxConferencing, so they can be accessed and run without needing to open the KxPlorer module.

Extracts

Extracts are the reason KxPlorer exists.  They return a set of data directly from one or more databases in a specific format, to allow business users to access the information they need more quickly and in a format they can easily understand.  Usually the extract is output as a table containing the required data, but in some cases there may be a formatted report output which can be printed and issued to third parties.  Extracts can also be exported to Excel as standard.

Just as with the standard reports available in other Kx applications, extracts almost always have a set of parameters that can be altered at runtime to allow users to run the extracts for only the results they are interested in, such as those for a specific Academic Year if looking at student data, or a specific Event Type if looking at Events.

The Summary tab provides an overview of the extract, its parameters and its output, along with details of any calculations or other extract-specific information.  This is a very valuable tool for understanding an individual extract and it should be consulted whenever an extract’s behaviour or data is in question.

Extracts can be restricted by user or user group; so only specific users have access to them.  This can be useful if you operate over several sites, or have Kx users in several business areas, and you wish to restrict access to your extracts to only those users that need to run them.  Extracts are unrestricted by default, but restrictions can be applied from the Security tab for each individual extract.

The SQL tab within each extract shows the SQL used to produce the output, which can be edited if the user chooses to do so.  Changing the SQL in this tab is not recommended for any extracts developed by Kinetic Solutions, as it will affect how the extract works and can stop it from working altogether.  New extracts can be created in KxPlorer; however, these will not be supported by Kinetic Solutions.

Please contact Kx Support for a report request form if you require a new extract or changes to an existing one.

Testing Checklist

New extracts should be thoroughly tested before accepting into everyday use, to make sure the parameters are flexible enough, the layout is easily understood and the data is correct.  If the data is intended to be exported this should also be checked to make sure no data is lost in the process.  If the extract is to be tested using a Test or Training database, Kinetic Solutions recommends ensuring the data and configuration on that database is as up-to-date as possible to ensure enough data is available to report on and the minimum amount of new data needs to be added for testing purposes.

Kinetic Solutions recommends using the following checklist to test each aspect of the extract:

1. The Summary Page (is as per the spec and contains all the parameters and columns in the extract).
2. Parameters and Parameter Options (are present on the Run page).
3. Extract Runs Successfully (with the default parameters).
4. Columns and Fields (appear correctly and the layout is correct).
5. Data (in the extract is correct).
6. Data is Filtered Correctly (by each of the parameters).
7. Export to Excel (with no loss of data).
8. Formatted Output (is laid out correctly and contains the correct data).
9. Run from KxStudent/KxResidential/KxMarketing, if requested.
10. Report Scheduler, if requested and enabled

The sections below explain a little more about each of the checklist items, and all assume that the extract being tested has been created by Kinetic Solutions.  For extracts not written by Kinetic Solutions the same checklist may not apply and any corrections required will not be made by Kinetic Solutions.

1. The Summary Page

The Summary page is the one explaining the extract’s purpose, structure and contents.  It should match the specification excluding any screen-prints and drawings, as these cannot be displayed in KxPlorer.  Not all extracts have a Summary page and, depending on their age, they may be structured differently, but extracts created after 2014 should conform to this layout:

1. Extract Title – appears at the top and is the name of the extract.
2. Kx Reference Number – appears below the Extract Title.  This is the reference number used by Kinetic Solutions to identify the original work for the extract.
3. Blurb/explanation – this is the section of the Summary page that explains what the extract is for and how it works.  This may contain important information such as calculations or exceptions to the logic in the extract and should be read carefully, as details on complicated logic or restrictions on what the extract will show will usually be described here.
4. Parameters – this is normally a table, detailing all of the parameters and options available for the users to use when running the extract.
5. Columns – this is also a table, this time listing each of the columns in the extract output in order, with information about the contents of each column including calculations and any hard-coded values.  The data source may also be explained for some columns if relevant.
6. Ordering – sometimes there is additional text below the Columns table, detailing any Sub Totals and Totals, and which columns the extract is ordered by.  Sometimes it may include other information as well if it is relevant to the extract.
7. Output – this section describes how the results of the extract can be produced.  All extracts will produce a grid output in KxPlorer and can be exported to Excel or a file, however some can also produce a formatted output for printing.

2. Parameters and Parameter Options

Most extracts have a set of parameters, just as with the standard reports available in other Kx applications, that allow users to run the reports and only return the data they are interested in.  The parameters for each extract is different, just as with the standard reports, since it is only practical to include parameters which are relevant.

Parameters are set up so that they allow selection from a list of values found in the database, or from a list of hard-coded values.  This is not something that can be changed by a user, so if the options in a hard-coded list need to be changed this would require development from Kinetic Solutions.  The extract summary should detail where the data for each parameter comes from, and it should be obvious if these are hard-coded or not.

Some extracts will have Site, Area and Block parameters; some will also have a Sub Block.  These may work differently depending on which version of Kx is running; in versions prior to Kx2015 these parameters worked independently of each other so the option selected for one of these parameters had no effect on the options available in the other.  For example, the list of Blocks available to select was unaffected by which Area had already been selected and contained every Block configured in the database.  From Kx2015 onwards an upgraded component was introduced so that these were now dependent on each other, for example the selections available from the Block parameter were dependent on the selection made in the Area parameter.

The following is an example of the parameters that may appear on an extract:

3. Run the Extract Successfully

Running the extract with no specific parameters allows a user to check that there aren’t any environmental issues that would cause it to fail.  All extracts will have default options pre-selected for each parameter, so these do not need to be set before running the extract.

If an error is returned when the extract is run, this will indicate where the problem exists and helps to identify what needs to be corrected.  For example, an ‘Access Denied’ error indicates a problem fetching data from a linked database e.g. a database for one of our web products.  This will need to be corrected by a SQL database administrator, as it is likely that there is a permissions issue preventing the extract from fetching data.

4. Columns and Fields

If the extract runs successfully it produces a grid containing the data requested by the user running the extract.  The columns in the output should match those specified in the summary, in the correct order and spelled correctly.

Data produced by extracts are often ordered before displayed to save users doing this themselves.  This pre-ordering is mentioned in the Summary tab if it exists for that extract, however once the data has been produced it can be re-ordered as desired by clicking the column headers.  The data can also be filtered by hovering over the right-hand side of the column header and clicking the arrow, then selecting the values of interest. 

A word of warning for re-sorting or filtering the output: if the extract data is produced in a specific format, then re-sorting or filtering the output may mean the original layout cannot be recovered without re-running the extract.  For extracts where the data can change over time it is particularly dangerous to re-sort or filter the data, so this should only be done when necessary.  Sorting and filtering can be achieved in Excel, so it is safest not to do this until the data has been exported (more on this later).

5. Data

Extracts exist to pull data from the database that is useful and in a format that can be used to assist a business in its day-to-day operations, therefore it is imperative that the data is valid and correct.  The extract Summary should always be used as a starting point to understand what the data in the extract should be and, if relevant, where it comes from.  Depending on the complexity of the extract itself this can be a simple task or a very complicated one, requiring calculations on data or investigations across many different parts of the Kx products suite.  Kinetic Solutions makes every effort to produce working and correct extracts and will fix all issues identified prior to delivery.   These issues broadly fall into the following categories:

Duplicated data: Duplicate data is usually easy to identify, but it can cloud useful data and lead to complications when working with the data to perform other processes, such as sending mailshots or totalling financial data. Duplicated data becomes more difficult to spot in more complex extracts, as what appear to be duplicates can turn out to be unique data with all values in the grid being the same except in one column.  If duplicate data is found, it should be double-checked to ensure it is in fact a duplicate and then reported to Kinetic Solutions for diagnosis and correction.

Missing data: This is more difficult to recognise, as it cannot be identified unless the user is expecting data that is not returned.  If, after checking the Summary to ensure the data in question is not intentionally excluded, it is apparent that data is missing from the extract, the data itself should be checked to make sure it is valid.  For example, for financial data it may be necessary to invoice any charges before they appear in an extract, or for student data it may be necessary for the student to be checked-in before they appear in the extract.  For very complex extracts there may be many criteria for data to appear in the extract, so due care and attention should be taken.  Genuine missing data should always be reported to Kinetic Solutions for diagnosis and correction with reproducible examples.

Incorrect data: Data in any given extract can either be pulled directly from the database, be hard-coded or can be calculated.  Hard-coded data is usually not an issue, but data pulled directly from the database can be erroneous if similar data is held in more than one place and the wrong one has been retrieved.  To avoid this the data source is usually specified in advance of creating the extract, and this will be detailed in the extract Summary.  The most common reason for incorrect data is an erroneous calculation, which can be a particular problem for sensitive financial data or if the calculated value is then used for other calculations in the extract.  For example, some Total values may not match with other Totals in the same extract.  In such a scenario there may be an issue with some data that is only included in one total but not another, and it can be difficult to identify where that data is. All calculated data should be verified using the calculations detailed in the extract Summary.  If there are any issues found with the data causing it to be incorrect, this should be reported to Kinetic Solutions for diagnosis and correction.

It is important to note that the data returned in the extract will reside in the same database as where the extract is being run, e.g. if the extract is run on a Kx testing database then the data in the extract will also be in the Kx testing database.  As KxPlorer is part of the core application suite, there are exceptions to this rule where linked databases are involved.  An extract that returns data from a web product, for example, will link to the database for that web product and return data from there in addition to data from the database KxPlorer is being run on.

Another point of note is that extract data can sometimes be checked against standard reports.  However, care must be taken with this approach as the customised nature of extracts can mean that the approach taken to produce the data in an extract can be very different to that taken to produce a standard report.  For example, a standard report may return bedroom occupancy based on the dates of the booking, whereas an extract may return data based on the dates the bedrooms are actually occupied.  These differences may be small but can produce very different results, so should be remembered when comparing the data in extracts to the data in standard reports.  

6. Data is Filtered Correctly

If the data in the extract is found to be correct, the next step is to check that each of the parameters on the Run tab filters the data correctly.  This is simple to do but it is important to go through all the parameters and make sure they all do their job as expected, for example date parameters do restrict the extract to returning only the data that fits between the dates given.  As mentioned in the Parameters and Parameter Options section above, some parameters have Some or All options.  These add complexity to the SQL code that produces the output, so should be checked to ensure that the data returned is only for the selected options.

7. Export to Excel

A standard piece of functionality for extracts in KxPlorer is the ability to export the output to Excel.  This will allow easy manipulation of the data for any purpose as required, and is very simple to do: just click Export on the extract results tab in KxPlorer and choose the Excel option.  After giving it the file a name and saving it to the desired location it will be available to work with.  The data in the Excel file should be exactly the same as in the extract, but it should be checked just in case as there can be anomalies arising from data in a specific format, for example date/times may not be exported or displayed as expected.

8. Formatted Output

Some extracts include a formatted report output, which is displayed as a PDF in Kx.  Although the option is available for any extract to produce a formatted report (just click the ‘Print’ button on the extract Results tab), a formatted report needs to be written specifically for an individual extract where it has been requested in order for this option be useful.  The Summary page will specify if a formatted report should be available with the extract.

Many of the checks above also apply to formatted reports; the columns, layout and data itself should all be verified as correct.  However, a formatted report will have its own design and may not display all the data available in the grid output.  Since mock-ups cannot be displayed in the Summary tab there may be details specific to the formatted report of what it should look like and what data should be in it, but if this is not available the extract specification document should be consulted in order to perform the necessary tests.  Any errors should be reported to Kinetic Solutions for diagnosis and correction.

9. Run from KxStudent/KxResidential/KxConferencing

Standard extract functionality includes the ability to run an extract from within the KxStudent, KxResidential and KxConferencing applications.  This does not change the data returned in an extract, however it may make it easier to work with it and reduces the time to access them by avoiding the need to open KxPlorer.  Testing the extract in each of these applications is usually only necessary if Kx functionality will be used to manipulate or work with the extract results, such as mailshots or navigating directly from a record in the results to the source record in the relevant application e.g. clicking a student’s ID to get to their student record in KxStudent.

In KxConferencing extracts can be run from the list of Custom Reports in the Reports tab in the ribbon.  The Summary is not available when the extract is run from KxConferencing, but the parameters are displayed immediately upon accessing the screen and the extract can be run using the Print button in the bottom-right of the screen.  Use the Print to Screen option to produce a formatted report, the Export to Excel option to save and view the data in an Excel file, and the View Data in Grid option to view the results in a grid in KxConferencing.  The parameters remain visible when the extract is run, and after changing them either the Refresh button or the View Data in Grid option can be used to re-run the extract with the new parameter options.

In KxResidential extracts can be run by clicking the Custom Reports button under the Reports tab in the ribbon.  The extract can be easily run by clicking the Run button, which then produces the results in a grid in a new tab on the screen.  These can also be exported to Excel by clicking the ‘Export’ button and then the Excel option.

In KxStudent extracts can be run from the Custom Searches button on the ribbon, found under the Students tab.  Extracts run in KxStudent do not return the KxStudentID or ApplicationID columns even if they appear when running the same extract in KxPlorer.  This is because the ApplicationID column does not otherwise appear anywhere in the KxStudent application, and the KxStudentID is used to form a hyperlink from the StudentID column where it is included in an extract, allowing users to click straight through from the extract to a student’s record.  It is also worth mentioning that mailshots can be sent from an extract in KxStudent if this has been requested.  The extract results can be exported to Excel by clicking the ‘Export’ button and clicking the Excel option.

10. Report Scheduler

Extracts can be scheduled to run with specific parameters on a defined schedule, the same way some standard reports can be.  The parameters must be saved as normal using the ‘Options’ button on the Run tab (KxPlorer), then a schedule can be created using the ‘Schedule’ button and all the same options that are available for the standard reports.

It is important to note that Report Scheduler may not be available during the test phase of an extract implementation project.  If this is the case it is advisable to put in place measures that allow the extract to be tested with the Report Scheduler on the database where the Report Scheduler is running, with contingencies in case the extract is incompatible and does not run correctly.  If an issue is identified, it should be reported to Kinetic Solutions.

Reporting an issue

Any issues identified with an extract created by Kinetic Solutions should be double-checked internally first to verify it is in fact an issue, then reported to Kinetic Solutions.  If the issue identified is with the data, in order to help diagnose and fix the issue we strongly advise that all the parameters used to run the extract are provided, along with a screenshot of the data itself and examples of the missing/incorrect records.  The expected data should also be provided and, if it is derived from a calculation, the steps in the calculation that show what the data should be.  Any other relevant information that can be provided will also be helpful to investigate the issue.

Please remember that extracts can stop working if the way is stored data changes or the data is moved.  When this happens the extract needs to be updated to ensure it uses the correct logic to retrieve and process the data in the right way.  if you require a change to an extract please contact Kinetic Solutions in the first instance.