SAP Knowledge Base Article - Public

2718538 - Substring Function for Calculated Columns - Online Report Designer

Symptom

In ORD Calculated Columns, how can we remove some characters from a string? How can we apply the function substring?

For example, the first three digits must be removed from the User System ID field.

Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental



Environment

SAP SuccessFactors Reporting & Analytics

Resolution

This can be done from the Function Selector screen of the Calculated Column Designer when the Data Type is Text.

  1. Open the Calculated Column Designer in ORD by clicking on the icon in the toolbar.
  2. Enter a Name for your calculated column and then select the Data Type Text.
  3. Select the field or column name which you need to remove characters from under the Text grouping.  For the give example above, drag the User Sys ID field from Global Job Information into the Value box.

Text field.jpg

4. Double-click on the field you just added.   The Token Editor window will display.

5. Click on Edit beside Function to open Function Selector.  Here you can specify the function and the number of characters to get from the given string.

Left - get the first x number of characters from the string. E.g for the screenshot below, if the string is '123456' the calculated column will return '123'.

Function Selector.jpg

Right - get last x number of characters from the string. E.g. '012345', will return '2345'.  This can apply when you want to remove leading characters from the string.

Right Substring.jpg

Middle - get x number of characters starting from position x#. E.g. '123456', below will return  '234'

 middle substring.jpg

 

Result:

Substring result.jpg

 

Keywords

ord calculated columns, substring function, text substring, cut characters from text, remove leading zeroes, get a portion of a text through calculation , KBA , LOD-SF-ANA , Analytics & Reporting (Ad Hoc, ORD) , Problem

Product

SAP SuccessFactors HCM Core 1808