Baanboard.com

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

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

 
Excel Assisted reporting strategy for Baan customers
By Hitesh Shah at 1 Mar 2009 - 10:25

Here is a blog on a reporting strategy for baan complemented with Excel load clients. This blog discusses various approaches to reporting in Excel and ingredients of successful Excel assisted baan reporting.

 

WHO SHOULD READ THIS  

One should read this blog if:

  1. one is very much comfortable with existing authorizations and semantic layer in Baan (Read baan data dictionary ) technically ( table/domain /field codes ) and functionally (descriptions )  and
  2. one wishes to capitalize on  existing learning and knowledge of existing semantic layer in Baan (technically as well as functionally )  and do not wish to invest (or waste??) in learning / migrating /adopting / translating / maintaining new semantic layer for reporting
  3. one is very much adept at using Baan reporting and programming for routine documents  and
  4. one’s users enjoy the freedom, flexibility and functionality of spreadsheets (which may be freeware also) for analytical purposes.
  5. there are sufficient Office licenses already with company on PCs with high CPU and memory ( far exceeding basic Excel requirements and which have become norm rather than exceptions ) in one’s place and there is high need to optimally utilize  these resources  and to reduce the load on servers .

WHO SHOULD NOT READ THIS  

One whose objectives are different OR who don’t have any objectives only OR who is extremely satisfied with their current reporting already.

 

APPROACHES TO EXCEL ASSISTED BAAN REPORTING

People can have one or more of following approaches. One may not adopt an approach if one is able to accomplish the results with fewer approaches e.g. we do not use output conversion approach as our other approaches give adequate results.  

  1. Third party tool for  dynamic data extraction from any table and / or  report -  Open source free tools like ERPJewels  enable users to extract / transform data directly in Excel dynamic query. Even Baan’s own EasySQL is such tool loading the results on Baan report or a chart . While such tools are free, it kickstarts one’s efforts in the direction of Excel-assisted-baan-reporting.  Key features of this approach are
    1. Dynamic query
    2. Calculated items (massaging data without changing the original data ie dynamic expressions using expr.compile or dynamic query on lookup tables )
    3. Data formatting (Dates , numbers etc)
    4. Data transformation like sum / min / max etc (disk based or In-memory )
    5. Ad hoc UDF (user defined functions ) as fields and / or conditions.
    6. Hierarchies construction and usage .(including time hierarchies)
    7. Multi-company support (_compnr specification)
    8. Baan specific IN/ LIKE clauses
    9. Native use of data dictionary leading to easy data entry , alignment , conversion and identification (without worrying for VRC’s , labels ,table sharing etc)
    10. Data extraction  embedded in  Baan report object itself  and done simultaneously .
    11. Load client in Excel
  2. Output conversion approach to convert Baan report to excel . This approach is useful particularly when one wants to build solution on its own and does not want to use a third party tool . In this approach, the xl output comes out of  Baan as an output file as an output . Whereas in 3rd party extract kind tool (like  ERPJewels ) following ETL discipline in addition to original output . The key features of this approach are
    1. Connector at Baan level – Typically this is 4GL program at device level . There are nice code samples available as back as 2001 for this. Typically this involves
      1. creation of device specifically for xl as ‘Rewrite file’.
      2. 4gl program is customized program which checks all user /report / LAN settings , presence of .xla file at client level , converts and transfer report file using server2client function etc
      3. Argument can be like ‘ascii:”{BSE}\xladdin.xla” %s’   OR  ‘ascii:excel.exe %s’ OR ‘ascii:third_party_exe  %s’ . XLA or exe files can be at central mapped network location also if convenient.
      4. Only if %s is specified , the report file will be transferred to client in {BSE}\tmp folder
    2. Report layout conditions changes – Baan report needs to be modified to make certain print condition change for report layouts. Layout conditions can be like   strip$(spool.device) <> "BXL" or lattr.pageno=1  where “BXL” is the device name . This strategy is inconvenient for complex layouts due to complexity of formats.
    3. Sample macro or VBA or XL Addin xla etc are VB like Excel embedded  programs for which one does not need to have VB separately . The code needs to be wriiten in workbook / auto_open section . The VBA code can optionally be password protected .Usually coding in macro is better done first doing recording macro and then recoded to make it general. Such macro typically can do following;
      1. Reads the baan report output from {BSE}\tmp  and save it as  per agreed  convention with/without date / time notations to keep history.
      2. Remove blank lines from output.
      3. Text2columns for data
      4. Plot the data in an XL template and save the results at a pre-determined location 
      5. Save in html format for viewing at a central LAN location.
      6. Bring images / create hyper links 
      7. Send emails to recipients 
      8. Run any other routines for charting / multi-dmensional modeling 
      9. Consolidate and pivot data from multiple  sources like MS SQL, DB2,Oracle , prostgre sql ,mysql ,text files , dbf files , csv files
      10. Format cells , numbers, borders etc 
      11. Make workbook readonly(application.ActiveWorkbook.ReadOnly set to true) or password protected.
      12. Execute any commands at various events of workbook , sheet like open / save / close .
      13. Create a form with various control and good user interface.
  1. Customization approach. Usually this approach is adopted for non-standard ad-hoc cases where data processing / formatting is complex .Complex data formats may well be available with users in and can be re-used generate output repeatedly in that format. E.g. Ledger balance trends , customer / supplier financial analysisf, unds flow statement are these approach. Other examples could be image reports or complex format XL output. Normally  this involves
    1. Customization in Baan to generate the file in manner previously agreed upon .
    2. File transfer (server2client) for output file.
    3. VB 6 , VB.net , java ,VFP  or excel addin (XLA file)  (app_start  / start.application.local)  to process the output from Baan and generate the output in XLS in pre-determined format.
  2. Data warehousing  & ECM – At a certain stage (not from day one ) when data cube size is extremely big , Cube build, cube refreshing, cube browsing, cube partitioning, cube authorizations etc may be required. Databases like SQL / Oracle have this as a native feature. Some routines are required to update the cube incrementally periodically. Here too ERPJewels can help in incremental refresh of cube due to its integration with Baan scheduling.Further some sort of ECM  (enterprise content management ) functionality  may be required to make such analytical results and dashboard , shared to  group in an authorized manner.

 

INGREDIENTS OF SUCCESSFUL EXCEL AASSTED BAAN REPORTING

  1. Good learning / training on Excel / VBA / Addin / templates / multi-dimensional modeling

  1. Good knowledge of baan data dict , Baan infrastructures , baan reporting , in-memory  data transformations . This can ensure there is no additional need for a semantic layer and u can combine meaningful and relevant KPI’s together easily without additional structures / semantic layers.

  1. Excellent knowledge of company’s users’ requirements, security concerns, scheduling needs, freedom in excel / html outputs, kpis and measures etc.


AttachmentSize
excel assisted baan reporting.doc46.5 KB
0
No votes yet


All times are GMT +2. The time now is 20:38.


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