OneStop Reporting
Multi Company Loading

Contents

1Introduction

2The Multi Company Loading Concept

3Constraints and Limitations

4Installation

© 2010 OneStop Reporting

Ver. 1.1

  1. Introduction

This document describes the installation and use of the OneStop Reporting Multi Company Loading module.

The product comes with a free 30-day trial license included which allows you to evaluate our products before you buy.

All products mentioned in this document can be downloaded from

The example described in this document uses Microsoft Dynamics GP as an example but the concept will work similar for all other Business Systems supported by the OneStop Reporting Multi Company Loading module.

The supported ERP systems for the Multi Company Loading Concept are:

-Microsoft Dynamics GP

-Microsoft Dynamics AX

-Microsoft Dynamics NAV

-Visma Business

Support for other Mamut Enterprise and Microsoft Dynamics SL can be made on request.

  1. The Multi Company Loading Concept
  2. General concept description

The OneStop Reporting Multi Company Loading moduleis a solution to provide for reporting and consolidating across multiple companies using the OneStop Reporting products Report Designer, Report Player and Report Composer.

The OneStop Reporting Multi Company Loading module requires a tool called OneStop Reporting Console and a Multi Company Loading (MCL) definition to work.The MCL definition is contained in an osr-file (.osr extension).

The OneStop Reporting Console application is a tool that executes the MCL and the result is a set of OSR tables created which contains the data from the selected companies.

The actual Load process can be scheduled so that the loading process happens automatically at planned times. The Windows Scheduled Task application is used to execute the planned loading process.

2.2Multi Company Loading definition (MCL) file

The OneStop Reporting Console application allows one or more OneStop Reporting MCL-files to be executed. The MCL file defines a number of processing steps including data Extraction, Transfer and Loading (ETL). The MCL file has parameters defined in the same way as ordinary OSR reports.

The MCL files are unique for the different ERP systems and can be downloaded from:

2.3OneStop Reporting Console

The OneStop Reporting Console is a windows application which is started from the Start->OneStop Reporting Menu folder. The Console executes one or more MCL files and prompts the user for parameters defined in the MCL file, if any.

The parameter selections for an MCL definition are remembered after the first time execution. This way the scheduled execution will work automatically with the same settings each time the Console application is started.

The picture below shows the different panes in the Console application:

The Console application can be downloaded from:

2.4Detailed Description

The OneStop Reporting MCL Concept works in the following way:

  1. The OneStop Reporting Console is started and a MCL file is selected.
  2. You need to be connected to the company you define as the Master company of the MCL process. The Master Company is the company that will contain the OSR MCL tables created during the Load process. These tables will be deleted and created each time you run the load process.

The Master Company will also be the reference for the dimensions like Period, Chart of Accounts, Departments, etc. that will be common for all companies.(See Appendix for a detailed list of which tables are transferred per ERP Business System OSR Integration)
If not already connected to the Master Company select the Report Configuration Options and choose the correct Company.

  1. The user selects the parameter values prompted for. The parameters will be specific for the MCL definition file and may vary between various ERP business systemOSR integrations. Normally, one of the parameters will always be a list of Companies. This list presents the companies in your Business System and they will be the candidates to load data for.
  2. When clicking the ‘Run’ button the process will start and data from the companies selected will be loaded according to the MCL definition and stored into OSR tables created in the Master Database.
    Note: It is important to understand that the Global Company Reporting Module in the OneStop Reporting products are separate data which is not normally linked to any of the ERP System’s other tables and data. One exception from this is the Period Dimension. The Period Dimension used in the Global Company Reporting module is the same Period Dimension which is used for the normal per Company reporting for the master company.
  3. A log will tell you which tables have been created and how many records are loaded from each company.
  4. When the process is complete you can connect to the Master Database with the other OneStop Reporting tools and you will be able to design multi-company reports.
  5. If you want to set up a Scheduled Process you can select the Icon with a watch. This will generate a Windows Scheduled Task.

The following picture illustrates the MCL process.

  1. Installation
  2. OneStop Reporting Console

See the general installation documents for installation and deployment of the OneStop Reporting Console. Download the documents from:

3.2MCL definition file

The MCL files must be deployed somewhere in the file structure. Then you can browse to the file and install the MCL file into the application. You browse to the file by clicking the “Install file” icon:

APPENDIX

This section lists the tables that are being transferred for each supported ERP Business System.

The list specifies the ERP Source table and the OneStop Reporting target table (Source -> Target)

Microsoft Dynamics GP

  • Budget Model:GL00200 -> OSRBgt
  • Budget Transactions:GL00201 -> OSRBgtLn
  • Segment Data:GL40200 -> OSRSegValue
  • GL Account String Data:GL00100 -> OSRAc
  • GL Account Category Data:GL00102 -> OSRAcCat
  • GL Summary Transaction Data:GL10111, GL10110 -> OSRAcTr
  • GL Detail Transaction Data:GL20000, GL30000, GL10001 ->OSRAcTrDetail

Microsoft Dynamics AX

  • Dimension Data:DIMENSION -> OSRDIM
  • GL Account Data:LEDGERTABLE -> OSRAc
  • GL Summary Transaction Data:LEDGERBALANCEDIMTRANS (is used as is since company is key in the table)
  • Budget Transaction Data:LEDGERBUDGET (is used as is since company is key in the table)

Microsoft Dynamics NAV

  • Budget Model:<Company>$G_L Budget Name -> OSRBgt
  • GL Account Data:<Company>$G_L Account -> OSRAc
  • Dimension Data:<Company>$Dimension Value -> OSRDimValue
  • GL Transaction Data:<Company>$G_L Entry -> OSRAcTr
  • Budget Transaction Data:<Company>$G_L Budget Entry -> OSRBgtLn

Visma Business

  • Budget Model:Bgt -> OSRBgt
  • GL Account Data:Ac -> OSRAc
  • R1..R12 Dimension Data:R1..R12 -> OSRR1..OSRR12
  • GL Transaction Data:AcTr -> OSRAcTr
  • Budget Transaction Data:BgtLn -> OSRBgtLn

1