Baanboard.com

Go Back   Baanboard.com > Blogs > Hitesh Shah's blog

User login

Frontpage Sponsor

Main

Google search


Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
36%
Installation Wizard into new VRC
36%
Manual into existing VRC
7%
Manual into new VRC
20%
Total votes: 44

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 . 

0
No votes yet


All times are GMT +2. The time now is 08:57.


©2001-2017 - Baanboard.com - Baanforums.com