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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
2. Bulk insert static rows
You can bulk-insert leaf-level rows or hierarchies using the 'Insert rows' option from Insert Rows > Data Input.
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.
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.
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.
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.
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.
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.
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.
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.
In the Insert Row Configuration, select Options list from CSV from the dropdown.
STEP 2: The Add Options from CSV window opens. Click the 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.
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:
Brand
brand
Category
category
Variant
variant
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.
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.
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.
In the image below, we see that the brand row dimension has the Option from Lookup model selected.
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.
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.
The image below shows the Distinct values of the variant in the dropdown that can be selected and inserted.
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.
By default, this toggle is disabled. The blank categories are highlighted in a red error box, and Lumel EPM prevents you from inserting rows.
By enabling this option, you can insert row hierarchies that contain blank leaf categories.
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.
After creating a custom hierarchy level, you can insert static rows, calculated rows, aggregated rows, etc.
Last updated