Preparing Spreadsheet for Export to Dataset

Before you start

Before preparing a spreadsheet for export to dataset, these prerequisites must be met:

• The spreadsheet to be imported contains the exact amount of balance keys as the dataset, either in the form of columns, rows, or a combination of the two.
• All types of values in the spreadsheet are defined as Accumulator Field in the dataset, either as columns, rows, or a combination of the two.

Follow these steps

The scope of the supporting function is described below:

1. Inserting control column

   A column must be entered on the sheet. The column is used to specify the field name of the dataset for the balance key or the accumulator field that are represented by the rows on the sheet.

2. Inserting control row

   A row must be entered on the sheet. The row is used to specify the field name of the dataset for the balance key or the accumulator field that are represented by the columns on the sheet.

3. Marking *FRF-cell

   Enter *frf in the first column of the control row. This selection is used to identify the control column and control row.

4. Marking off non-current rows and columns

   Rows and columns that contain information that will not, or cannot, be transferred to M3 are marked off. A column is marked off by typing *txt in the column in the control row. Rows are marked off by typing *txt in the row in the control column.

5. Connecting cells to database field

   Columns that contain balance keys or accumulator values must be marked with the field name the information corresponds to in the dataset. This is done by typing the field name in the control row for each column.

   Rows that contain balance keys or accumulator values are also marked with a field name. The field name is printed in the control column for each row.

6. Adding period number

   A period number must be entered for every row or column that contains an accumulator field. This information is necessary because period numbers are retrieved from the spreadsheet when exported.

   The period number can easily be added to the spreadsheet manually, either by copying it or by using the sheet's arithmetic functions.

7. Marking repetitive key values

   If a column contains repetitive key values, that is if various rows in a column belong to the same key value, every row must be marked. In other words, it is not enough just to mark the first row.

Examples

These examples illustrate how a spreadsheet must be changed to enable the export of information to a dataset in M3.

• Example 1 – Spreadsheet with Two Dimensions

  This example illustrates a spreadsheet with two dimensions. This means that two terms are used to identify an amount.

  This table shows how the spreadsheet looks before being edited:

  *FRF	UCONNO	*TXT	OSPERI	UCSAAM
  *TXT	Customer	Name	Period	Amount
  	A1	Corporate A	1	450
  	A1		2	600
  	A1		3	750
  	A1		4	550
  *TXT				Total 2350
  	A2	Corporate B	1	1200
  	A2		2	1400
  	A2		3
  	A2		4	3000
  *TXT				Total 5600
  *TXT				Sum of Totals 7950

  After editing the spreadsheet it looks as follows:

  Quantity sold per item and customer - Period 1

  Customer	Item	Item	Item
  	A100	A200	A300
  A1	144	3221	1233
  A1	433	33	3212
  A1	312	9874	423
  A1	322	2388	388

  Quantity sold per item and customer - Period 2

  Customer	Item	Item	Item
  	A100	A200	A300
  A1	322	322	32
  A1	3	32
  A1	32	2333	3455
  A1	9333	833	732

  The following changes have been made:

  • A control row and a control column have been added.
  • Cell A1 has been given the value *FRF.
  • Columns that contain text and totals have been marked off with *TXT so the information is not available for export.
  • The repetitive customer number key has been copied so that every customer number row is marked with A1 or A2.

  Rows and columns that contain balance keys and accumulators are marked according to the following:

  UCCUNO	Customer Number
  OSPERI	Period Number
  UCSAAM	Invoiced amount in the local currency

• Example 2 – Spreadsheet with Three Dimensions

  The following example illustrates a spreadsheet with three dimensions. This means that three terms are used to identify an amount.

  The following table illustrates how the spreadsheet looks editing:

  Quantity sold per item and customer

  *FRF	UCCUNNO	UCIVQT	UCIVQT	UCIVQT
  *TXT	Period	1
  *TXT	Customer	Item	Item	Item
  OSPERI	1	1	1	1
  UCITNO		A100	A200	A300
  	A1	144	3221	1233
  	A1	433	33	3212
  *TXT	A1	312	9874	423
  	A1	322	2388	388
  *TXT	Period	2
  *TXT	Customer	Item	Item	Item
  OSPERI		A100	A200	A300
  UCITNO	2	2	2	2
  	A1	322	322	32
  	A1	3	32
  	A1	32	2333	3455
  	A1	9333	833	732

  After editing the spreadsheet looks as follows:

  Customer	Name	Period	Amount
  A1	Corporate A	1	450
  		2	600
  		3	750
  		4	550
  			Total 2350
  A2	Corporate B	1	1200
  		2	1400
  		3
  		4	3000
  			Total 5600
  			Sum of Totals 7950

  The following changes have been made:

  • A control row and a control column have been added.
  • Cell A1 has been given the value *FRF.
  • Columns that contain text and totals have been marked off with *TXT, so the information is not available to export.
  • The repetitive period number key has been copied so that every period number column is marked with 1 or 2.

  Rows and columns that contain balance keys and accumulators are marked according to the following:

  UCCUNO	Customer Number
  OSPERI	Period Number
  UCITNO	Item Number
  UCIVQT	Invoiced number