Go Back > Blogs > Hitesh Shah's blog

User login

Frontpage Sponsor


Google search

For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
Installation Wizard into new VRC
Manual into existing VRC
Manual into new VRC
Total votes: 38

Baanboard at LinkedIn

Reference Content

Customer / Supplier financial analysis in pivot table
By Hitesh Shah at 7 Feb 2009 - 13:17

Now it is becoming increasingly clear that users need liveliness and interactivity on static data from OLTP. This is possible through multi-dimensional modeling available in free-form pivot table or cube structures. As there are not many cube applications and not many established routines to populate cubes incrementally periodically automatically, report-directly-in-excel is becoming vogue.  Though most summarized multi-dimensional modeling cab be done easily with old MS Excel 2003, with excellent enhancement in MS Excel 2007, scalability and liveliness has increased manifold in pivoting (multi-dimensional modeling using pivot table).

All that one need is accurate, reliable and relevant figures side-by-side in CSV format for all dimensions / measures to follow report-directly-in-excel-with-data-dict-advantages strategy. For example, here is a baan report which gives all important dimensions/measures related to customer financial behavior.


Here is the list of dimensions:


  1. Customer / Supplier
  2. Company
  3. Country
  4. Year
  5. Half Year
  6. Quarter
  7. Month
  8. Kind of transaction (Opening, Sales , Receipts etc for customer & closing as grand total)
  9. Document Type (sales invoice , currency difference ,Normal receipts , assignments , payment difference etc for customer )
  10. Currency

The measures are:

  1. Amount in HC
  2. Amount in Invoice currency

Grand total for a customer would be the closing balance for it which can be verified with many baan standard reports like tfacr/p2434m000, tfacr/p2433m000 for customer etc.


From this single pivot table users can get following sub-reports:

  1. Customer transaction behavior for a given period (sales , receipts , opening etc )
  2. Change in customer balances  period  wise (Quarter, HY , year  etc  )
  3. Currency gain loss customer wise , period wise , country wise , currency wise
  4. Sales trends for a customer , for country etc
  5. Average currency rate for a particular period for a particular period for particular transaction type.

Such a report could also be useful for incremental update to cube structures using baan job management (if at all data warehousing is deployed for multi-dimensional applications).

Sample report can be viewed from sample cust analysis with functional doc.  One can send PM / email to avail of  such ready reports in their systems  for a consideration.

There is supplier analysis also similar to customer and it is readily available .


Next we'll blog on ERPJewels scheduler advantages for unequal and numeric period for scheduling emails and incremental update of cubes . 

No votes yet

All times are GMT +2. The time now is 00:05.

©2001-2017 - -