Level Seven: Conclusion and Q&A

In the final level of the EXO reporting series we will examine different types of reports.

Looking back at the reports written and modified for clients over the past few years, I started noticing a pattern of how these reports are created and used.  There seem to be three types of reports and best tools to deliver them.  By classifying the task at hand as one of the below, report writers may narrow their choice of technology and deliver a better outcome for the users.

 Operational reports

  • Such reports are generated and used by company staff with immediate access to MYOB EXO system (logged in users).
  • Purpose of those reports is very specific and closely linked to user interactions with the system.  Customer lists, order lists, aged balances, stock on hand, etc.
  • Reports are created from the system and used to drive the system or daily work.

The best tool for such reports is Clarity.  Clarity has the advantage of being the native report writer of MYOB EXO.  It delivers reports to paper or “paper-like” formats (PDF) like a charm.

Clarity is relatively easy to learn and use and does not require programming background.  MYOB EXO comes with a large number of sample reports, which are fully working prototypes.  Learning from or re-using sample reports is a very cost effective exercise.

Refer to Level 2: Clarity Report Writer for more details.

Finance teams may also be interested in building custom GL reports.  Level 3: GL Report Writer walks through the key features of this tool.

Stakeholder reports

  • These reports target audiences much broader than common users of MYOB EXO.  Think management and directors, all staff, external service providers or key customers.
  • Purpose of those reports is to provide information “self-service” to key users.  They may have access to information, but not necessarily create it (i.e. they may never log into MYOB EXO).  The ideal way of providing such access is intranet or web.
  • Examples may include departmental profit and loss statements for top management,  summary sales reports for sales representatives on the road, KPI reports for staff members, selective product availability or transaction history for key customers.

In MYOB EXO world, I would nominate SQL Server Reporting Services as the best tool to deliver stakeholder reports.  By its nature SSRS allows intranet access to reports and email subscriptions.  Reports are likely to be summarised and heavily customised for each target application.  High availability and enterprise level security is ensured through the proven Microsoft platform.

We have discussed SSRS in Level 5: SSRS and 3rd party report writers

 Analytical reports

Unlike previous categories, analytical reports require intense user interaction with data.  Users of such reports are often subject matter experts:  CFOs, managers or skilled analysts.

The problem they try to solve is hard to define and they tend to explore and analyse data step by step.  Such problem is not defined upfront (otherwise they would simply run a summary report and live happily ever after), so they will find the question and the answer at the end of the journey.  A classic examples is investigation of profitability: what products / sales people / territories / customers / days of the month bring the company most profits.

Since user needs interaction with data I would nominate Microsoft Excel and its Pivot tables as the tool of choice.  Power Pivot may assist with large data crunching.  However, if your organisation is data driven and you have a dedicated analyst, you may consider creating a business intelligence solution.

Level 4: ODBC Reports (live feed to Excel) and Level 6: OLAP and BI (PowerPivot and SSAS) provide some insight into how these technologies work.

I hope you enjoyed reading this Reporting series. I would love to hear your feedback. Please email andreym@k-c.com.au to let me know your thoughts.

Good luck with your reporting!

Level Six: OLAP and BI

Introduction

In previous “Levels” we explored at a variety of tools that report directly off MYOB EXO ERP system.  This Level is about a more general information concept for a medium to large enterprise – Business Intelligence (BI).

Previously known as Decision Support Systems (1960s-1980s) or Executive Information Systems (1980s-1990s), Business Intelligence is a broad discipline that combines tools and techniques for using information to the advantage of an enterprise.  Sounds great, doesn’t it?  Just roll up the sleeves and take over the world with our reporting.  But on enterprise level there’s bound to be issues…

  • What if data resides in separate systems?

Only rare companies develop software systems in-house, most use existing products.  However, a combination of software products inside an organisation is quite unique.  So are business structure and rules.  Drawing data from multiple systems for a report may be a challenge.

  • Will transactional databases “like” analytical reporting?

Analytical reports tend to read orders across thousands of customers, months, products, etc.  They generate spikes of database activity as large amounts of data are summarised.  Running “heavy” reports during business hours may slow systems down.

The answer seems to be “yet another system” – an independent data store that combines all systems and can be used for reporting.…  Business intelligence practice suggests that “preparing” and “using” data should be done outside of business systems using an “architected environment”.  Such architected environment can range from a simple Excel based solution to multi-server platform combining data warehouse, data mart, OLAP cubes and some data mining.

Microsoft PowerPivot for Excel

Most data-savvy users “live” in Excel… and Pivot tables are their tool of choice.

However, Excel Pivot tables have limited processing capacity (a few million rows).  Since pivot tables do not reference data from more than one set, data from several systems has to be “staged” as a table and… VLOOKUP’ed.  Creating functions on large sets of data is also a challenge despite the ever growing capacity of our computers.

So the stage is set to introduce PowerPivot – a free add-on to Microsoft Excel 2010 and 2013.  It uses Excel as the interface and a special memory based engine to store, compress and crunch data.

Power Pivot

 

There are many advantages of PowerPivot over standard Excel Pivot tables.  Some of them are listed below:

  • PowerPivot can draw on massive data sets (as large as 100,000,000 rows) capped by 2Gb of compressed storage space in an Excel file.
  • PowerPivot allows “mashing up” data from various sources:  Excel tables, MS Access, SQL databases, SSRS Reports, other PowerPivot documents, ODBC, etc.
  • PowerPivot allows users create relationships between independent datasets and returns resulting data to a pivot table.
  • Formula engine is using DAX language that is similar to standard Excel formulas, but a lot more powerful.  There are functions like “year on year change as %”.
  • Solutions can be published on the corporate intranet website (Microsoft Sharepoint 2010 onwards) and upgraded to server based BI solutions (SSAS Tabular model).

The interface of PowerPivot is quite intuitive to work with and resembles Excel.  Each query with external data is loaded in a separate tab (Query1, Query2 in my example).  Tabs can be related to one another using “key” relationships (concept similar to joins).

Power Pivot Table

Power Pivot Table - Create Relationship

Once the solution is created, there are a few visualisation options to choose from.

Power Pivot Table Visualisation

End user experience is very similar to that of a pivot table, but multiple queries available for pivoting and extra features (like slicers) give it the distinct PowerPivot “flavour”.

Power Pivot Table - End User Experience

Up-market BI

A more comprehensive server based solution can be created using SQL server components of Microsoft SQL Standard edition and above: SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS).

First, a data warehouse – a separate database that contains amalgamated data from business systems, the architected environment.

Data warehouse is loaded from source systems using an Extract-Transform-Load process (ETL).  ETL is usually done via Integration Services:  data is extracted from source systems, staged, cleansed, normalised (sometimes lightly aggregated), audited and loaded into the warehouse.

Then the actual crunching…

There are two main vectors of BI reporting: analytical (i.e. investigation of existing data) and predictive (i.e. suggesting future trends based on past “experience”).

Analytical reporting is presented by OLAP cubes (SSAS 2005, 2008, 2012) and Tabular model (SSAS 2012).  In a nutshell, OLAP cube is a “pre-calculated” pivot table, while tabular model is server based PowerPivot.

Predictive reporting is using Data mining algorithms to find patterns and dependencies in OLAP cubes (SSAS 2005, 2008, 2012).  There is no predictive reporting out of tabular data… yet.

Both types can eventually be displayed through Microsoft Excel or Microsoft SQL Server Reporting Services.

Below a screenshot of my demo SSAS OLAP Cube for MYOB EXO Motopartz database.

SSAS OLAP Cube

 

And since it looks very much like a pivot table, same data can be provided to user via Microsoft Excel:

 

Adding Data From Other Sources

OLAP Line Total

Conclusion

Despite the overwhelming number of acronyms above, we barely scratched the surface of what business intelligence and Microsoft BI suite has to offer.

The architected solutions are custom built for client needs, size and systems in place.  Some are easy to build by an average super-user.  Some are an ongoing project to support and empower business users with insightful information.

Feel free to enquire with Kilimanjaro team or email myself andreym@k-c.com.au if you have questions on anything relating to this post or would like to discuss how Kilimanjaro can assist with your Business Intelligence ideas.

References

Go back to the beginning of the Reporting Series.

Level Five: SQL Server Reporting Services (SRSS)

In Level Four we looked at ODBC connections as a way of connecting Excel directly to EXO database.  You must have guessed that not only Excel can connect to SQL.  There is a large number of third party reporting tools that can do so.  If you know one – chances are it can connect to Microsoft SQL Server.

In broad terms, I would classify report writers as follows:

  • Full reporting suites:  SQL Server Reporting Services, Crystal Reports and others.

Such suites include a proprietary reporting engine and a client interface (a program or a web page).  There is usually a report writer program that comes as a developer tool. For a company using EXO that comes with Clarity report writer, a full reporting suite can be a consideration only if it addresses limitations of Clarity reports (which are few and far between).

  •  Excel based solutions:  Alchemex is one example of such tools.

These solutions use Excel as an engine (ODBC connections) as well as client interface.  The report writer comes as an add-on to Excel.  Developer tools can be quite sophisticated, but usually lack the scale and robustness of full reporting suites.

  • Cloud based solutions.  A quick Google search will yield the reader a stack of those.

While there is a great variety of such tools, some of them provide not more than a slick client interface.  The “guts” of report building should be done prior to loading clean data into the tool.  Common considerations in using such tools are data security and cost benefit analysis.

In my opinion, SQL Server Reporting Services (SSRS) deserves our focus for a number of reasons.

Availability

  • SSRS is part of Microsoft SQL Server.  Even the free Express Edition of SQL server has SSRS with minor limitations to features.
  • SSRS is most likely already available on an existing SQL server installation.  If installed correctly, it only takes an hour to configure the back-end and get it up and running.
  • Choice of built-in reporting website or native integration with SharePoint portal.
  • Native integration with SQL Server Analysis Services for Business Intelligence and Data Mining applications.

Web (Intranet) Access

  • Can be integrated into EXO URL Dashboard Widget or any other application that can load a web page (including web-shops).
  • Standalone use: no EXO user licence required.
  • Out of office access (subject to network configuration:  VPN / part of company website).
  • Windows login based security is used to access reports (seamless single sign-on is available when using Internet Explorer).

Self-Service

  • Reports are published on an intranet website, which makes their use rather intuitive.
  • User can set a “subscription”, so their reports are delivered via email or saved to network folder.
  • Subject to permissions, users can create “their” versions of report by changing parameters and even editing reports (subject to permissions).
  • Reports can be exported to variety of formats when viewed or delivered as subscription.  As a report writer I like native Excel / Word / HTML export that SSRS offers.

SSRS is a very powerful tool that helps deliver information to users in a medium to large enterprise environment.  SSRS is not a replacement for Clarity reports, rather a supplement.  It adds “web” and “self-service” elements to reporting and allows a broader user base get access to enterprise information.

It is important to note that with power comes complexity.  Unlike Clarity, which is a super-user friendly package, SSRS reports are built using Microsoft Visual Studio.  Creation of reports will require functional knowledge of SQL queries and language, and some basic development techniques.

I hope above won’t turn the reader off the tracks of exploring the rich SSRS offering.  Below are screenshots that illustrate user-facing side of SSRS.  There’s also examples of SSRS GL reports Kilimanjaro team has built recently and a full P&L report exported to Excel.

Internet Explorer showing a report folder with two reports

Screenshot 1: Internet Explorer showing a report folder with two reports.

 

A generic P&L Report for Motopartz.com.au demo database

Screenshot 2: A generic P&L Report for Motopartz.com.au demo database

References

Reporting Services (SSRS)

Stairway to Reporting Services (SQL Server Central)

 

Go back to the beginning of the Reporting Series.

Level Four: ODBC Reports

In previous levels we looked at structured reporting where a report writer (consultant or super-user) defines the look and feel of a report.  These reports have consistent or “fixed” logic of what they deliver.  There are, however, situations where users cannot give a precise instruction on how to write a report and need to explore “raw” data.  The reasons may include finding patterns, exceptions or calculating custom totals.

Why Excel?

Being part of Microsoft suite, Excel can natively connect to an SQL database using ODBC driver (ODBC stands for Open Database Connectivity).

As data in a database table is arranged as columns and rows Excel is a great fit.  One would hardly find a better software to manipulate table data by hand.

In Excel an ODBC link is created via a Data Connection Wizard:

Excel ODBC Link

 

Wizard will then ask for database server name, authentication details, database you want to connect to and will offer a list of tables so you can select one.  As this series is not meant to be very technical, details for each step can be looked up on the web or in Microsoft Help files.  Kilimanjaro Support technician can assist with creating and troubleshooting an ODBC connection for you as well.

Presenting data in tables

The simplest way is to present data as it is kept in the database – a table.  Once data is in Excel, it can be filtered and sorted.  User can create additional calculated columns.  All the work is saved with the file.

Data can be saved in the file too or loaded every time the file is opened.

Table data is useful when we need a list of records from EXO.  Below is a listing of Debtor accounts data straight from DR_ACCS table.

Importing Data To EXO

Clicking on the drop-down button in the NAME field will produce a sort/filter dialogue like below:

Debtors Account Data Filtered

Presenting data in PivotTable

PivotTables are a fantastic way to explore the data and calculate totals.  I hope the reader had a chance to get familiar with these.  If not – there’s plenty of information provided by Microsoft as well as third parties to facilitate your learning (see references section below).

Once data is pulled in Excel it will be used in the pivot table as usual.  The key benefit of the ODBC pivot approach is that pivot table definition and layout stays saved in the file, while data refreshes as requested.

Pivot tables offer sort and filter options.  They allow custom formatting and layout settings.  Calculations can be done as straight aggregate functions (sum, average, min/max, count) or as percentage of parent total / grand total or can be a running total.  Field names can be changed to be more human-readable.

Below is a PivotTable of Debtor invoices data straight from DR_TRANS table.

EXO Data in Pivot Table

 

Limitations

  • It is possible to combine multiple tables without SQL – just by using VLOOKUP function in Excel.  However, VLOOKUP over large tables is slow.  It is against best practice to pull raw data and recombine it in Excel.  SQL engine is way more efficient in the task.
  • Sometimes technical references / lookups are not human-readable.  E.g. transaction types in debtors ledger are 1, 4 and 5.  Unless user had prior experience with EXO reporting, they may need have numbers translated into 1-Invoice, 4-Payment, 5-Adjustment.
  • Pivot Table may become slow as data volume increases.  There are also limits to how many records can be pivoted (this is dependent on Excel version in use).

One of the common solutions to those issues is to prepare data on the SQL server side and return it in a user-friendly fashion.  This can be done via SQL Views – virtual tables that can join tables, perform scalar or aggregate calculations, enforce security and many more behind the scenes.  One would need a good understanding of SQL language to be able to create an SQL view, so it may be best to ask for assistance from Kilimanjaro team.

The solution to reporting on very large data volumes that cannot be pre-crunched is using a special tool – SQL Analysis Services (OLAP concept, which will be covered in a future post).

References

Go back to the beginning of the Reporting Series.

Level Three: General Ledger Reports

In previous levels of this series we explored ways to report using ExoGrids and Clarity Report builder.  This time we look at GL Report Writer – a built-in utility in MYOB EXO Business.

General Ledger reports are fairly difficult to write compared to reports for other areas of ERP database.  This is because of accounting conventions with debits and credits, differences in calculations between Balance sheet and Profit and Loss accounts, current year earnings calculations and a few other technical reasons outside of the scope of this digital paper.

MYOB EXO Business offers a neat solution for the hard task – a GL Report Writer utility.  This utility is a mature tool, which dates back to early versions of EXO (then Exonet) software.  As result it lacks some of the glamour of more recently added Clarity reporting, but it does its job well and is easy to use.

GL Report writer is quick to learn even for non-technical users.  A report is built by specifying row and column groups.  Each row group is defined as either a text line, a range of GL account or GL account groups, a total or custom function (e.g. current year earnings).  Each column group is defined as an account name, number or balance for the given row group in a single or multiple months.

Effectively, the user selects what they want to see and hit run.  GL Report will automatically work out the correct Month and Year to date calculations for Balance Sheet and P&L accounts, Opening / Closing stock position for periodic stock businesses, dollar variance against selected budget.  Simple as that.

EXO Business help file covers GL Report writer functionality in great detail, which makes “playing” with GL Report writer a nice experience.

Below screenshots show how a standard P&L report is set up in GL Report Writer.  The reader is welcome to make up their mind as to how difficult the setup is.

Setup GL Reports in EXO

 

P & L Reports - All Branches

It is possible to print GL Reports individually or create a GL Report Batch and define parameters for each report in the batch.  When a full report pack is required, the user then runs a batch.

And, as with most reports in EXO, we can export GL Reports to Excel via Clarity format template.

As with any technology, there are limits to what GL Report writer can do.  However, my experience shows that most finance and accounting users find it a good fit for GL reports of medium complexity.

For complex GL reports Kilimanjaro team tends to recommend SQL Server Reporting Services platform (SSRS), which I will cover in level 5 of the series.  Next time (Level 4) we will be looking at ODBC reports that allow pulling data from database into Excel.

Go back to the beginning of the Reporting Series.

Level Two: Clarity Reports

In Level One of this series we explored ways to report using ExoGrids.  This time we look at Clarity – an integrated report writer for MYOB EXO.

 A bit of trivia

Most financial software packages use reporting software of some description to produce business forms (e.g. Debtors Invoice). MYOB EXO has a customised version of a Report Builder application by Digital Metaphors, which is integrated in the product and branded “Clarity” report designer.

We will look at key features of Clarity reports being:

– Accessible;

– User friendly (to normal users, as well as geeks); and

– Integrated.

 Accessible

Standard forms and reports in MYOB EXO Business are built in built in Clarity.  There is over 400 standard forms and reports in recent versions.  Technically both are reports with exception that forms are called from the screens and “know” which transaction to print.  I will use the word “report” in the text below, but everything equally applies to forms.

Reports are stored as files with instructions on how to collect and display information.  These instructions are stored mostly as text, but we never look the raw text as such.  Clarity report writer is used to create and modify reports.

MYOB EXO reporting is structured in such a way that the system will look for a file in “Custom” folder before going to “Standard” one.  This means a custom report will be given priority over a standard report.  Any standard report can be altered to contain company logo, colours, fonts and distinct graphics design elements and then saved to “Custom” folder.  This allows super-users and consultants make customer-facing business forms with consistent corporate identity and keep less used or internal forms default.

When in EXO go to Help drop-down menu > About.  The screen will display your Custom and Master folders.

About EXO Business

 

User friendly

Clarity report writing does not require much technical knowledge.  User can create a basic report by choosing an EXO table and dragging and dropping data into a page template.  A report is readily available to print or you may choose to copy to Excel via a grid.

It is possible to change report presentation using controls similar to Power Point (see screenshot for “look and feel”).  Report preview function allows a report writer to view and correct changes before the report is deployed or even saved.

Clarity Reports have many exciting features, some of which include sub-reports, charts and graphs, easy data and run-time calculations.

For those with technical aptitude, Clarity allows detailed event control that can change the whole behaviour of the report.  It is even possible to create interactive reports and dashboards with Clarity.  This all can be achieved if we delve into custom scripting (SQL and Delphi).

EXO Business Clarity Designer

 

EXO Business Clarity Designer2

 

Integrated

Clarity program code is embedded in every user-facing module in EXO.  This means report writer launches instantly without any delays.

It also helps report writers avoid complexities of connecting to right database.  Clarity is part of EXO so it runs in the same context.

All standard forms and reports are built in Clarity and so are Clarity dashboards.  EXO has many ways to link Clarity reports to system: drop-down and business flow menu; drop-down selections at runtime; standard and custom buttons.

In return, Clarity “knows” a lot about its host – EXO system.

Reports can use staff ID of the currently logged in user and any of user profile values (e.g. default branch) to limit data visible to users for security purpose.  All filters would apply dynamically as user runs a report.  As example a report can allow a sales rep see data for his / her accounts only, but sales manager will be able to see that for every account.

There’s more to Clarity: If you used EXO for a while you may have noticed that some of the report parameters in Clarity are inherited from EXO – e.g. Debtor Account search screen.  For most users that comes as granted, however, very few reporting tools in mid-market systems allow such user experience.  MYOB has promised there’s more of those “native” search parameters to come in future versions.

Not only we have native search, Clarity can also open an EXO Debtor Account / Creditor Account / Stock Item or Job Costing screen in EXO versions 7.0 – 8.4.  In EXO v8.5 MYOB will introduce a hyperlink technology that will allow Clarity to pop open any transactional screen (e.g. a Sales Order).

 

Clarity Report Parameters

 

References

MYOB EXO Business help file > Reporting

MYOB EXO Business help file > Additional Modules > Clarity Report Designers

MYOB EXO Business help file > Additional Modules > Clarity Dashboard Reporting Tools

Training

Clarity Beginners course at Kilimanjaro Consulting (learning now to create a basic report and how Clarity works with data)

Clarity Intermediate course at Kilimanjaro Consulting (learning now to create a fully functional report with parameters and deploy to user menu)

Clarity Advanced course at Kilimanjaro Consulting (charts and graphs; building a dashboard; Delphi event control)

 

Go back to the beginning of the Reporting Series.

Stairway To EXO Reporting : Level One

Introduction

In my role as an implementer I ask managers and key users of many organisations why they switch to a new ERP system.  No two answers are the same, but almost every time I hear that the client wants better reporting.  MYOB EXO Business has a multitude of options to deliver exactly that – “better reporting”.

I will run a series of posts on Kilimanjaro Consulting blog and share some of the “sherpa secrets” on reporting methods.  We will not go into technical detail but rather cover what is available in the system.  I hope this will be helpful to both new and existing users of MYOB EXO who look for ideas on structuring existing or creating new reports.  For readers with technical background I will provide references to available documentation and further training options at the end of posts.

Posts will be organised as stairway levels taking us from basic and readily available methods to very complex ones.  We will discuss the following:

ExoGrids

Let’s start by looking at reporting from EXO screens.  You may have noticed that most EXO screens have a similar look, with light blue headings and white grids underneath.  This is called ExoGrid.  Search screens (e.g. Debtor Accounts and Transactions) show data in ExoGrids.

Why we call it reporting – because we can see and use system data.  In Excel, in emails, in PowerPoint presentations.  Anywhere really…  System data is just two clicks away:  Right Click > Copy to Clipboard.

MYOB EXO Debtor Account Details

MYOB EXO Debtor Account Details II

 

But there’s more to ExoGrids…

More Visible Columns

System “super-user” or EXO consultant can add more columns to most search grids.  These can be data fields not displayed on grid by default or even dynamic calculations.

For example, a customer search screen can show aged balances breakdown (Current / 30 days / 60 days) and this year turnover (neither available in visible columns by default); also it can show a number of outstanding invoices for every customer (custom dynamic calculation).

MYOB EXO Customer Search Screen

Clarity Report Grid

Every Clarity Report has a grid where it displays technical data behind the report.  In addition to a printer-friendly “Preview” tab we can explore a “Grid” tab.  (NB: for complex reports we may not see all data, just the core data.)

MYOB EXO Print Preview

 

MYOB EXO Print Preview II

 

Grid Widget (Dashboard)

In MYOB EXO Business v8.2 a new type of dashboard widgets was introduced – a Grid Widget.  All this widget does is show an ExoGrid.  It’s that simple.  Grid widgets can query and display any data from MYOB EXO Business database.  They are relatively quick to build as the only input they require is an SQL query.  Below is a screenshot of a sample Grid Widget built in Clarity Advanced training course that shows customers who either exceeded their credit limit or are on stop credit.

MYOB EXO New Grid Widget

MYOB EXO New Grid Widget II

 

In summary, EXO exposes data in ExoGrids on most search screens, Clarity reports and grid widgets in dashboards.  Data from grids can be copied into Excel or any other program; wherever the user may need it.  Grids are simple to use, can be customised and provide ad-hoc “screen based” reporting.

Next time we will look at Clarity report writer, an integrated tool in which most standard and custom reports are built.

If you would like to submit any questions for our Q & A segment or have any other feedback about the series, please feel free to email me.

Till then.

 

References

MYOB EXO Business help file > Getting Started > Grids

MYOB EXO Business help file > Reporting > Dashboards

 

Training

Business Tools course at Kilimanjaro Consulting (Extra search fields setup; EXO Dashboards)

Clarity Beginners course at Kilimanjaro Consulting (Report Grid)

Clarity Advanced course at Kilimanjaro Consulting (EXO Dashboards and Grid Widget setup)