Analyse and report on Advanced Exchequer data simply and easily with ExReport. Produce multi-dimensional reports, instantly organise related data and spot trends in the data shown.
ExReport Visual Query and Data Mining
ExReport takes Advanced Exchequer reporting to the next level with simple drag and drop Pivot data mining and multi-dimensional views of the data.
ExReport has been designed to integrate with ExSync to provide rapid reporting and broad analysis of data. ExReport does not necessarily have to run in conjunction with ExSync and can simply be used for the visual querying that it displays.
The ExReport software does not have any dependencies other than the requirement of either MS SQL Server or Oracle MySQL to read data from, multiple instances of the application can be run across a network.
There are two versions of ExReport, the standard edition and the unique ‘Pivot’ edition.
ExReport is the client query/reporting engine that connects to the data synchronised by ExSync the multi-threaded application that connects to the Pervasive SQL server via ODBC and synchronizes the data to the Microsoft SQL Server or Oracle MySQL (version dependant).
A number of useful features have been built in to allow visual query building as well as export to Excel and PDF reporting, as a result, complex queries can be constructed and the data output in a fraction of the time it takes Advanced Exchequer to process.
The number of Advanced Exchequer companies is unlimited and the MS SQL/MySQL server schema can either be an independent database or can form part of an existing database so that queries can be built across applications, the MS SQL/MySQL configuration within ExReport allows for multiple SQL databases. We have designed this application with power, flexibility and ease of use in mind
The whole ethos of the development has been to produce a suite of tools that allow for rapid reporting and analysis of the data.
ExReport has a built-in visual query builder that will query the connected SQL server database. Queries can be saved as .sql files and loaded or loaded directly into the SQL server management console.
All standard SQL statements and MS SQL/MySQL server-specific SQL statements can be used.
Queries can be built from typing the statements in or using the visual drag and drop and then modifying the automatically generated SQL code.
The standard edition features a number of tools for creating reports:
- Visual query builder with the ability to save scripts
- Export to Excel, CSV
- Customisable PDF Reporter, this can be edited to include company templates
- Different colour schemes or ‘skins’ for the application itself
ExReport Pivot Edition
The Pivot view is only available in the Pivot version of ExReport. This feature enables you to summarise and analyse data in lists and tables. Pivot tables are called such because you can quickly rearrange the position of pivot table fields to give you a different view of the table.
Because ExReport allows you to query the database any way you like with SQL, a result set is output to the Pivot and you can arrange the data in many different ways to sum the totals and create elaborate views of the data not possible in Advanced Exchequer.
A simple double click on a value in the Pivot drills down to the actual query records that make up the summary.
With the drag-and-drop functionality, it allows you to rearrange fields in the view dynamically, without having to change the structure of the data store. They can interchange columns and rows on the fly, filter and sort items in different ways, and also collapse and expand data at different levels, thus the desired report can be obtained immediately. A side benefit is that no code is required for generating reports of different types, data is analysed from a user’s perspective.
You may find it handy if you need to:
- Produce multi-dimensional reports on a set of ordinary data
- Analyse the data that is involved in a multi-dimensional relationship
- Instantly organise related data together
- Spot trends of the data in the fastest way
Along with the solutions to the above tasks, the Pivot table offers essential functionality to succeed in business reporting. The Pivot Edition of ExReport also contains an integrated charting module, allowing quick and easy generation of charts for reporting.
To efficiently perform an analysis, the pivot mechanism requires that:
- Automatic summary calculation for each cell – There are 9 summary functions available: Sum, Count, Min, Max, Average, Standard Deviation based upon a sample, Standard Deviation based upon the entire population, Variation based upon a sample and Variation based upon the entire population.
- Any number of data fields – This feature allows you to calculate and display multiple summaries at the intersection of each column and row.
- Any number of column and row fields – The values of column and row fields are listed along the top and right edges of the control and they identify column and row headers, the values of multiple column (row) fields are arranged into a hierarchy and can be expanded.
- Automatic Total calculation – Totals (which are inserted as additional rows or columns) display total summaries calculated for outer column and row fields.
- Automatic Grand Total calculation – Grand Totals display overall summary totals calculated against all the columns and rows, they are displayed as the bottommost rows or rightmost (leftmost) columns.
- Multiple Totals – It’s possible to calculate multiple Totals for specific column and row fields.
- Filtering data – This feature enables you to limit which records are processed by the Pivot, you can easily change the filter via the pre-filter panel or filter dropdown.
- Automatic sorting of column and row field values in alphabetical order (for text data) and in order of magnitude (for numeric and date/time data) change a field’s sort order (from ascending to descending and vice versa) by clicking the field header.
- Export – Excel.
- Print support – The contents of the Pivot can be printed out via the PDF Reporter.
- Cell selection and copying to clipboard – Select a range of cells with the mouse and then copy their contents by pressing the CTRL+C shortcut, the selected data is copied to the clipboard in the tab-separated format that is natively supported by Microsoft Excel, for instance.
- Dragging fields between different areas – You can freely drag field headers between different control areas, this gives you full control when analysing the data.
- Built-in Customization Form – Allows you to temporarily hide fields and make them visible again.
- Built-in context menus – Provide access to the most commonly used features, using the Pivot, you can turn a large amount of data into a compact report with easily manageable results.
What makes ExReport such a powerful tool is that you can perform visual modelling of the report’s structure by manually rearranging fields across different areas or within a particular zone or by hiding them in the customization form, dragging a field or a group of fields around in the view will result in changing a report on the fly.
You can quickly change the orientation of the field from column to row, line up fields in a particular header area, so the values of those fields are arranged in the related area into a hierarchical tree, thus the logically related data can be organized by categories. Having that tree in your report, you can drill down and up, data at different levels, as a result, you perform a deeper data analysis or avoid unnecessary details.
Subtotalling is another way of arranging and summarizing data.
To narrow down the report’s results, you can use filters, filters can be applied at three levels, first, you can use the pre-filter panel, this will affect the entire report, second, you can drop a field onto the filter header area and uncheck values in the field’s drop-down list to be filtered out, this will affect the entire report too, third, you can filter values of the fields located in areas other than the filter header area areas. Only the unselected items of those fields will be hidden in the view but not involved in the calculations.