Data extract for full Questions (Export)

For full questions, the three Download types have different purposes. Here’s some more things that you need to know about outputting to Excel:

Export (Excel spreadsheet): This output is specifically formatted for Excel, and automatically downloads for opening with Excel.
Dates and times are in the time of the logged on user and appear in a human readable format that is compatible with Excel e.g. 1/01/2016 11:13 AM.
Excel cannot support HTML formatting so some HTML formatting is removed for display in Excel – for example hyperlinks in this Excel display can be copied and pasted into a browser to view but cannot be clicked directly in Excel (to see the formatted data and use hyperlinks in the data view the Display output option).
A clickable hyperlink back to the question in RefTracker is provided.

– Don’t forget to choose whether you want HTML or text formatting. Excel handles HTML well by formatting it, so you may well want to include HTML when using Excel, but not other formats.
Text fields are provided in full in all the Export output formats as the [more] link would not be able to be clicked in these formats.

– You can also choose to have the output formatted (code tables appear with their descriptive text values) or unformatted (code tables appear as their numeric codes)

Excel formats data according to how it thinks it should be best formatted. For example, if it finds a column that appears to only contain numeric data it may choose to format that data as numbers. A common example of this is presentation of numeric only Borrower numbers. If you have a borrower number that contains 12 or more numerals and only numerals, Excel will reformat it using scientific notation e.g. 1.23456E+11. RefTracker has code to get around most of this unexpected Excel reformatting, but should you find any you should use Excel’s column formatting tools to change the column formatting back to Numeric with zero decimal points, or to General.

– Date fields in the Data Extract Excel output show date and time and are provided as General format, which means that customers who want to represent dates in different ways can easily do so by selecting the column and choosing Format Cells. Then select the date category and the type of formatting that you want to use. In this example we have used this method to present the date without the time.
Note that Excel’s date formatting refers to your computer’s specified locale to ensure that the date format, presents by default using the standard date presentation for your Country/Language (e.g. mm/dd/yyyy, dd/mm/yyyy, etc.).

– The Time used column reports the total time used by staff in responding to this question to date, in minutes. It is not the time spent during the selected period unless you select the Journal activity option at Date type! In the Excel output this and another numeric only fields are totaled at the end of the data.

– The Cost incurred column reports the total costs attributed to this question to date. Costs are accumulated by multiplying time used by the billing rate of the staff member performing the action, or by adding specifically incurred costs using Records costs . Costs reported are the total costs for responding to the question to date, not the costs incurred during the selected period. In the Excel output this and another numeric only fields are totaled at the end of the data.

– The Minutes to close column shows the number of minutes from arrival of the question to when it was closed. A zero in this column means the question was closed on the spot, or is not yet closed, or has been reopened and is not yet re-closed. Other numbers are calculated as minutes from Received date to date closed, or, if the request has been reopened, minutes from Reopened date to Data closed. Divide by 60 to get hours. Divide by 1440 to get days.
Note that the value in this column is time from arrival to time closed “as seen by the client”. It is not adjusted for public holidays or for any times that your organisation was closed. You can report on this figure using the “Specify time to close” parameter that appears when you Choose questions with Closed status, in the Statistical reporting screen. You can even use this parameter option to limit the question included in this Data extract report to just those closed before or after a certain amount of time from their receipt.

Where a question has been reopened, this Data extract report is based on the date it was reopened to the final close date because this is the time that the client sees as your responsiveness, but it does mean that if the client reopens the question with a “thank you”, this will decrease the total minutes to close. For this reason we recommend that you review the Edit options setup of your Request forms and implement the client evaluation function if it is not already in use (as when clients use the evaluation function, the question is not reopened so better statistics are provided), and consider preventing clients from reopening questions where appropriate. In the Excel output this and another numeric only fields are totaled at the end of the data.

Export (CSV): This output provides International standard CSV format – for use by other applications. Dates and times are in ISO standard which means that the dates are in the date and time of the logged on user, but also show the UTC offset (in the format YYYY-MM-DDTHH:MM:SS+hh:mm where hh:mm is the offset) e.g. 2016-01-01T22:13:37+11:00. HTML coding used in data fields is included as encoded data as it is generally used for computer to computer transfer of data.

– Don’t forget to choose whether you want HTML or text. CSV does not format HTML, so if you want a human readable output, tick this option so that you just see the text.

IMPORTANT NOTE: To view a CSV file in Excel, double click on the file. Do not open Excel, and then select the CSV file, as Excel will not display CSV files correctly when opened in that way.

Export (XML): This output provides International standard XML format – for use by other applications. Dates and times are in ISO standard which means that the dates are in the date and time of the logged on user, but also show the UTC offset (in the format YYYY-MM-DDTHH:MM:SS+hh:mm where hh:mm is the offset) e.g. 2016-01-01T22:13:37+11:00. HTML coding used in data fields is included as encoded data as it is generally used for computer to computer transfer of data.

– Whether you choose HTML or text. will depend on the use that this HTML file is being put to. If the data is going to an HTML compatible application, include the HTML, but if not be sure to tick the text option.

Note that it is good practice to take an extract using this report for each statistical reporting period, and to store it in a safe place so that statistics can be obtained for this time period into the future, without them having been diluted through the deletion of questions, or information within questions, that can occur in the main database!

Data extract Export (Excel spreadsheet)

Data extract (Excel spreadsheet) presents the selected data in a form suitable for use in Excel. Any HTML is removed. Hyperlinks and images display the URL they link to. This report also handles fields of data longer than the 32K characters per field that Excel can handle. It displays only a manageable subset of very long data fields (30 lines) until you double click in the cell. It also provides a question number hyperlink back to RefTracker (for making easy adjustments).

Eith this file you can now use normal Excel techniques to analyse the data in more ways than are provided by the RefTracker Statistical reporting screen, for example, “during the selected time period, how much librarian time was used on successfully provided ILL’s for clients from the Marketing department”.

You can even use it to export data so that it can be transferred to a billing system.

Below is an example of a export showing how images and attachments are included as hyperlinks that can be copied and pasted to a browser for viewing.

It also provides an example of how Excel addresses VERY large amounts of text. It will display what it can fit in 32 lines of text. To see more, double click in the cell and it will expand to show the rest of the text. Making the cell wider will also help to see more of the text.

Data extract Export (CSV or XML)

Data extract Export for CSV or XML creates an industry standard file, of the selected format, that is suitable for use with computer based products accepting these formats.

When you change the parameters to Output type “Export (CSV)” or “(XML)”, and click on Submit, you will be presented with the same browser based download tools you saw when exporting the quantitative data (if you are dealing with a lot of data, it can take a while to appear in Excel!).

Data extract Export (Exchange)

Data extract Export with the Exchange option creates a file of data suitable for importing into another RefTracker system with different field mapping (using the System>Batch process menu>Data import/export>Data import function).
Create a saved Field set to achieve the export so that you can test the export, and amend it as required.
Note that it only creates the Source data in a suitable format. You will still need to create field mappings and code table mappings as described in the Data import function help.
Also note that you may need to adjust the SystemSendFilter.xml file at config/exchange/reftracker/settings to stop some fields from being stripped as the data is exported. If this is necessary, create a SystemSendFilterExchange.xml version of this file that can renamed to SystemSendFilter, just for the period that you are doing the export (for example, if you need to export Allocated staff member). Please ask your Altarama support representative for help with this, though it might be part of a chargeable data export/import service.

To allow Altarama to convert this Exchange formatted data into data suitable for the RefTracker import process you will need a map in the following format that explains the Source field that is to be used to fill the relevant Target field, and if a code table is involved, the Target field code that should be set for each Source field code that might be found.

Format is : <target>|<source>|<description>|<note>|<codemap>
Where
Target =the RefTracker data dictionary field name in the system that will receive the data
Source = the RefTracker data dictionary field name in the system from which the data has been exported using the Exchange format described above
Description = Usually the file label in the Receiving system, but perhaps the Target system field name followed by the Source system field name. Only mandatory if a note or codemap needs to be provided.
Note = Any note that help explain this mapping. Only mandatory if a codemap needs to be provided.
Codemap = If the fields to be imported on this line are code tables, then for each possible Source code value
<code from the Source system>:<code that should be used for it in the Target system> comma separated.

Example format of map txt file