Import of dimension set rules

  • Article

A couple of days ago I had a question. One guy had to generate dimension set rules for ledger accounts, based on information from an Excel file. The file contained 3 columns: the first one for ledger accounts, the second one for values of the first dimension (say, Departments), and the third column for values of the second dimension (say, Cost Centers):

'10510', '49', '0'
'10511', '49', '3'
'10600', '49', '0'
'10600', '49', '6'
'10600', '50', '0'
'10651', '49', '0'
'10651', '50', '0'
'10800', '49', '0'
'10811', '49', '0'
'10900', '49', '0'

and so on. As can be seen from the sample:

  • only department 49 and cost center 0 are valid for the account 10510;
  • only departments 49 and 50 are valid for 10600, while cost centers 0 and 6 are valid for department 49, and cost center 0 is valid for department 50;
  • the accounts 10800, 10811 and 10900 have department 49 and cost center 0 as the only valid values

etc.

I have written a sample job that generates dimension set rules based on this information.

Pre-conditions for the job:

  1. “Dept-cc” dimension set is created, composed of Department and Cost Center dimensions.
  2. The dimension set has neither hierarchies nor rules created.
  3. All mentioned ledger accounts exist in the chart of accounts.

As a result of the job execution:

  1. Several dimensions set hierarchies will be generated. The number of hierarchies will be less than number of accounts, because some accounts have similar validation rules.
  2. The hierarchies will be updated and approved.
  3. There will be a dimension set rule collection created for “Dept-cc” dimension set.
  4. There will be several dimension set rules created – one per ledger account. The rules will be assigned to the rule collection.

The job may be found in the attachment.

job.txt

Comments

  • Anonymous
    May 17, 2007
    PingBack from http://www.axaptasource.com/2007/05/17/import-of-dimension-set-rules/

  • Anonymous
    December 27, 2010
    Hi, you script is awsome, i was just wondering if there is anyway you can do the same for importing dimension set hierarchies based on an excel file , data in the excel file looks like this D1        D2                D3         D4 D5 D6 CEL ARCC406 CEL300 101 NA NA CEL ARCC406 CEL300 102 NA NA CEL ARCC406 CEL300 103 NA NA Your help will be very much apreciated!

  • Anonymous
    December 28, 2010
    Hi Daniel, Actually, I newer tried to manipulate data from Excel sheets with X++. But, you could look at the class named COSExcelImport and figure out how you could convert your file data to something that my script will understand. Thanks, Sasha