Insert Data Input rows

Lumel EPM lets users insert rows in table/matrix reports and enter their own data. For example, a financial statement report connecting to a database query may be able to fetch revenue & expense metrics, but it might not retrieve the number of shares outstanding. Similarly, a sales report can leave out the sales data for a newly launched product category.

To address these scenarios, Lumel EPM lets you insert static rows in matrix-style reports where you can enter the data.

1. Insert data input row

You can insert data input rows from the 'Home' tab > 'Insert Row' menu. Please bear in mind that the Insert Row button will be disabled until you select a row in your report. Select the Number option from the Data Input sub-menu.

Insert Data input - Number row from toolbar

Another way to insert static rows is by using the row gripper. The gripper icon is highlighted when you hover over a row. Click on it, then select the 'Insert' > 'Data Input' option.

Inserting data input rows from the gripper

1.1. Data input row properties

A side panel opens when creating a data input row. Type in the name of the row in the 'Title' textbox and click Create. You will see an empty record created at the end of the level of the selected row. You have successfully inserted a data input row. We will now discuss the properties of the Static Row panel below.

Inserted Data input row and Static Row panel

i. Row type

The row type dropdown helps you to create

• Calculated row using Formula

• Data input row using the Number

• Aggregated row using the Aggregation option, selecting the appropriate option. Know more about aggregation here.

You have the flexibility to switch between data input, calculated, and aggregated rows at any time with the Row Type dropdown.

For instance, you may have used a formula and referred to the values of an existing row dimension while creating the row. You can easily change it to data input and enter values without having to create a new data input row.

Row type

ii. Insert As

• Single Row - Select the Single Row option to insert a single data input row.

• Templated Row - Select the Templated option to insert the row across all levels of the hierarchy.

For instance, you may create a product line specific to a particular region. You can use the Templated option to replicate the same product line across all regions. To know more about Templated rows, click here.

The image below shows how template rows are rendered.

A templated row is inserted.

iii. Scaling factor

You can set the scaling factor for the static row to thousands, billions, trillions, etc, depending on the data that is expected to be entered. It is set to Auto by default.

Scaling Factor

v. Include in total

When the 'Include in total' option is enabled, values entered for the new row are added to the total of the parent row.

'Include in total' is enabled by default.

Include in total option is enabled

iii. Distribute parent value to children

The 'Distribute parent value to children' option ensures that if a value is entered at a total level, it gets distributed to the levels below it.

Distribute parent value to children is enabled

iv. Bind for cross filter/RLS

You can enable the Bind for Cross filter/RLS option – this ensures that cross-filter selections and RLS settings apply to calculated/manual input rows that reference other rows.

For example, if this option is unchecked, a manager handling Canada accounts can view a manually inserted row that references US data or an inserted region that is manually created at the visual level. Learn more about binding rows.

v. Default value

When a new row is created, you can create it with a static value or source default values from another row.

Static default value

Default values from Selected row

1.2. Delete a static row

To delete a static row, hover over the row and click on the row gripper. Select the 'Delete Row' option. You can also delete rows from the Manage Rows interface that will be covered in subsequent sections.

Deleting a static row

2. Bulk insert static rows

You can bulk-insert leaf-level rows or hierarchies using the 'Insert rows' option from Insert Rows > Data Input.

Insert Row option

2.1. Inserting child rows

To insert a leaf-level row, click on any child row or the parent row under which you want to create rows. From the row gripper, select the Insert Rows option. Notice that the parent category is already populated in the Insert row pop-up.

To insert a new row, click the icon that appears on hovering near the parent category or click Add New. Key in the variant and click save.

Insert Row pop-up

To insert 5 or 10 rows, click the drop-down button next to Add New, then choose to insert a single row or bulk insert 5/10 rows.

Bulk Insert Rows

The image below shows a bulk insert of 5 rows. You can enter the variant name and click save to bulk insert all 5 rows.

Bulk Insertion of Brown Ale Beer variants

Bulk inserted rows

You can also choose the dimensions where the new rows are to be added from the icon shown.

2.2. Inserting a hierarchy

To insert a new hierarchy, go to 'Insert Rows' > 'Add New' from the Home tab. In the Insert Rows window, you can overwrite the existing parent and its child categories and click Save. The new hierarchy is then inserted.

Only when the Text option is chosen, Manage Rows > Row Settings > Insert Row Configuration > Type > Text, Overwrite is allowed. This is set by default.

In the image below, a new brand, Cream Ale, with Beer as a category and Light variant, has been inserted.

Cream Ale, a new hierarchy is inserted

Inserted Hierarchy

2.3 Disabling row insertion

You can restrict users from creating new categories for a particular level. Navigate to Insert >Manage Rows > Row Settings> Insert Row Configuration.

In the image below, the creation of new regions is disabled. However, new subregions can still be inserted.

Insert Row Configuration pop-up

Notice how the Region textbox is disabled but we are able to enter values in the Sub Region and Category textbox in the image below.

Row insertion is disabled at a specific level.

If you have hierarchical row dimensions, when you disable row insertion for a specific dimension, this will automatically disable row insertion for all levels above it.

Automatic disablement of rows at higher levels.

2.4 Uploading from an Excel/CSV sheet

You can upload the row categories from an Excel/CSV sheet. In the Insert Rows window, you will be able to see the data from your Excel as dropdown options. Let's see how.

STEP 1: Navigate to Home > Manage Rows > Insert Row Settings> Insert Row Configuration.

Insert Row Configuration.

In the Insert Row Configuration, select Options list from CSV from the dropdown.

Option to source data from CSV.

STEP 2: The Add Options from CSV window opens. Click the Upload.

CSV upload

Browse and upload the source file from the CSV Upload tab. Lumel EPM displays a preview of the contents of the CSV file. Click Add.

CSV file preview

STEP 3: In the Option Configuration tab, map the source field from the file, which will be used as row categories. Choose the field from the CSV file from the Label Column dropdown. In this case, the mapping is as shown below:

Excel Column	Visual Row Category
Brand	brand
Category	category
Variant	variant

Dimension Mapping of Label column and Visual Column name

STEP 4: Post-mapping the dimensions in the file with the visual column names, click Save. You will need to set up filters if you want to map subcategories under the respective parent category.

Once the steps discussed above are completed, you will be able to see the data from your CSV in the Insert Row modal. Follow the steps discussed in sections 2.1/2.2 to insert the configured row categories into your visual.

Rows inserted from excel source

2.5 Insert rows from Lookupmodels

You can also insert rows and their leaf categories from Lookup models.

STEP 1: To pick and upload row categories from a semantic model, navigate to Insert > Manage Rows > Insert Row Settings > Insert Row Configuration > Manage.

Select Options list from Lookup Model from the 'Type' dropdown.

Options list from Lookup Model

STEP 2: The Add options from Lookup model window opens. In the Table tab, select the table you need to connect to. Under Column Label and Column ID, select the Column of the table and its ID. Use the Filter options to filter out based on a certain condition. Click Add.

Inserting Rows using Lookup Tables

In the image below, we see that the brand row dimension has the Option from Lookup model selected.

Insert Row Config model

Once the configuration is completed as shown in the image above, you can follow the steps in sections 2.1/2.2 to insert the configured row categories into your visual.

You will see the data from the semantic model in the Brand dropdowns.

Lookup table values are reflected under the Brand

2.6 Distinct Values

There is an option under the Insert Row Configuration Type called Distinct Values. Selecting this option lists all the distinct values that can be inserted.

Distinct Values

The image below shows the Distinct values of the variant in the dropdown that can be selected and inserted.

Distinct Values of Variant

2.6. Allow blank values in categories

While inserting row hierarchies manually, you can enable the Allow Blank Values toggle if you expect blank row categories in the leaf nodes. This option is available at Home tab > Manage Rows > Row Settings > Insert Row Configuration > Allow Blank Values.

Enable 'Allow Blank Values'

By default, this toggle is disabled. The blank categories are highlighted in a red error box, and Lumel EPM prevents you from inserting rows.

When Allow Blank Values toggle is disabled

By enabling this option, you can insert row hierarchies that contain blank leaf categories.

Row hierarchy with blank categories

Please note that you cannot create blank parent nodes for child nodes that are not blank.

3. Row hierarchy

This option can be used to insert a single user-defined level for hierarchical data. Only one row is created while using this option, unlike the Insert Rows(s) option, where you can define the hierarchy structure with multiple nested levels and rows.

Add a new hierarchy level

After creating a custom hierarchy level, you can insert static rows, calculated rows, aggregated rows, etc.

New Hierarchy is inserted.

Aggregated, calculated, and Static rows are inserted under the New hierarchy