One question that is often asked is "Are there ways that I can modify the data coming from the Report Engine Database when creating my Smart Reporting reports?"
In TSOM Smart Reporting I would like to create a report that has a Monitor Instance Full Name detail that is very long… and want to only include a small portion of the name in the report. For example, the Monitored Instance Full Name returned may be a long string like:
I only want a portion of the name “cloudCP1235_AOAG1” to show as the name.
How can I filter (regex?) the names of returned values in any field of Smart Reporting? Or more specifically the Monitored Instance Full Name for instance?
While creating the report if you want to do any changes on any dimension, you can create a calculated field at report level.
There are 3 types of calculated fields.
- Freehand SQL --- In freehand SQL we can use the database function on any column of the database table.
For example here, Monitor Instance name is displayed form the table VW_CONFIGDATA and column INSTNAME.
So to change the value, we can use below database function as freehand SQL dimension.
// Regular expression can be changed as per requirement
// Using oracle database function considering reporting database is Oracle . We can also look for SQL server if it’s SQL Server (see below for more).
Steps to create the calculated field and then use that object in report.
Drag-n-drop this dimension is report.
More examples of regex and use of REGEXP_SUBSTR can be found here:
MS SQL Note in this example we used the REGEXP_SUBSTR call to replace a substring in Freehand SQL because Oracle supports regex out-of-the-box.
MS SQL does not allow regex commands directly but does use Transact-SQL (T-SQL) in a similar way. Please see more here:
More on Smart Reporting Freehand SQL can be found here (note this references Server Automation):