Go Back > Blogs > Hitesh Shah's blog

User login

Frontpage Sponsor


How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
200 - 500 GB
500 - 800 GB
800 - 1200 GB
1200 - 1500 GB
1500 - 2000 GB
> 2000 GB
Total votes: 69

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 07:49.

©2001-2018 - -