Thursday, October 6, 2011

Easier to build reports on a Globe database with your preferred reporting tools by making use of the Globe Reporting Views.

A lot of data is stored by business solutions in one or more databases. Every business solution will ship a lot of overviews to support your business process. However, a lot of users requires some custom made reports, which are not part of the standard solution. As of today a lot of reporting tools are available to build reports on your database. For instance:
  • SQL Server Reporting Services (SSRS)
  • Crystal Reports (CRW)
  • Excel
  • Power Pivot for Excel
Every reporting tool has it's own advantages and disadvantages. It's up to the report builder to use his favorite reporting tool.
Building reports can be divided into next steps:
  1. Define the goal of your report.
  2. Retreive the data from the data source.
  3. Make calculations with the data you have retrieved.
  4. Format the report according your UX design rules.
The report builder expert will know all in's and out's of the reporting tool. However this expert does not always understand the business logic of the solution. To get a better understanding you need to read and understand the database documentation of the solution. I will explain with one example: How can I get all outstanding invoices of customer X. To answer this question you need to know in which tables this information is stored but also how it is stored. For Exact Globe next tables are used: GBKMUT, BankTransactions, CICMPY. The outstanding items query will contain 140 lines of code. So being a report builder expert you can still not build the requested reports if you do not understand the business logic.
To solve the challenge of the business logic we have developed database views which contains the business logic. The view will present the data in an understandable format which can be used in the report.
The advantages of using views:
  1. Business logic is in the database, not in the report. If business logic changes, the view will be updated with the new logic. No changes are needed in the report.
  2. Data is presented in an understandable way. For instance 'Supplier code' instead of a databasse column name: 'cicmpy.crdcode'
  3. Data sets in the reports are easier to read and maintain. 
  4. Performance can  be better guaranteed.
As of Exact Globe product update 402 the first version of the reporting views are shipped. The Globe Reporting Views start with 'GRV_' in their name.  In my next blog post I will describe how you can use these views with Power Pivot for Excel.

1 comment:

Bas Kroes said...

Exact's best kept secret?

But very handy indeed those GRV_ views...