Report scripts

Report scripts are to further enhance the user experience. We can divide the scripting options into 2 areas, the first area is to manipulate the behavior of the filters, drill etc. The other area is to add graphical elements such as graphs and KPI’s. The second part will be described in the following chapters.

$olap.setFontSize()

Override the default report font size, options: xx-small, x-small, small, medium, large, x-large, xx-large.

$olap.setFontSize("x-small")

$olap.setLastUpdatedField()

Shows when the data is last updated. The field created must exist in the data source.

$olap.setLastUpdatedField("[_created_]")

$olap.setShowLastUpdated()

1: show last updated, 0: hide last updated, field to use.

$olap.setShowLastUpdated(0,"[_created_]")

$olap.setLinkScript()

• [dimension_key] : The dimension to add the link to, use key.
• position : The sequence of the link if more than one on the specified key.
• document=XXX;key={0} ;tab=opportunity : The document to link to, example:
• document=Account;key={0} ;tab=opportunity
• report=closedopportunity ;filter=[owner_key]=‘{0}’
• Link Title: the title to show in the dimension menu.
• Target: which field to use in link (0 = key, 1 = name).

$olap.setLinkScript("[dimension_key]",position,"document=XXX;key={0};tab=opportunity","Link title","target")

Examples:

$olap.setLinkScript("[payer_number]",1,"document=Account;key={0};tab=report.payer_info","-> Customer tab ","1")
$olap.setLinkScript("[owner_key]",3,"https://www.google.no/q={0}","-> Google search")
$olap.setLinkScript("[owner_key]",1,"node=53:18;filter=[owner_key]='{0}'","-> Open opportunities report")
$olap.setLinkScript("[owner_key]",2,"report=closedopportunity;filter=[owner_key]='{0}'","-> Closed opportunities report")
$olap.setLinkScript('[account_key]',1,'document=Account;key={0};tab=opportunity','-> Account')

$olap.setMaxRows()

Override the default number (5000) of rows to be returned.

$olap.setMaxRows(1000)

$olap.setNumberOfKpiRows()

Override the default number (1) of KPI rows to be displayed.

$olap.setNumberOfKpiRows(int)

$olap.setOpenLevelY()

Override the report setting of rows to be opened. This can also be done by setting the number of levels in the field open level y in the report tab.

$olap.setOpenLevelY(2)

$olap.setShowGraph()

1: show graph, 0: hide graph.

$olap.setShowGraph(0)

$olap.setShowLevels()

Select how to show levels in report:

• 0: Show the lowest level and history.
• n: (1..98) = number of levels.
• 99: show all.

$olap.setShowLevels(0)

$olap.setShowSelectX()

1: show x-dim, 0: hide x-dim.

$olap.setShowSelectX(0)

$olap.setShowSelectY()

1: show y-dim, 0: hide y-dim.

$olap.setShowSelectY(0)

$olap.setShowTable()

1: show table, 0: hide table.

$olap.setShowTable(0)

$olap.setShowToolbar()

1: show toolbar, 0: hide toolbar.

$olap.setShowToolbar(0)

$olap.setShowTotalX()

1: show x-axis, 0: hide x-axis.

$olap.setShowTotalX(0)

$olap.setShowVisualGroup()

1: show visual group, 0: hide visual group.

$olap.setShowVisualGroup(0)

$olap.setSkipZeros()

Remove rows with only 0/null values: 1: show / 0: hide.

$olap.setSkipZeros(0)

$olap.sortMeasure()

Set sort measure, DESC on first measure.

$olap.sortMeasure(0)

$olap.setFixedColumnWidthLimit()

Removes the standard auto adjustments of column width. Deem Insight has an auto adjust of columns set to hit when you reach 15 columns in the report. To overwrite this set a higher number of columns in the function script.

$olap.setFixedColumnWidthLimit(15)

$period.setMode(‘PeriodsInYear’, ‘Start’, ‘NoPeriods’, ‘CurrentPeriod’)

• PeriodsInYear: Terial (2), Quarter (4), Month (12), Week (52).
• Start: Number of periods awailable back in time.
• NoPeriods: Total number of periods awailable.
• CurrentPeriod: Usually 01 or blank.

$olap.setFixedColumnWidthLimit(15)

$period.setCP()

Change the default selected period from current period to e.g current period -1.

$period.setCP(-1)

$period.setYMD8()

This function set current period in $period based on a YMD8 formated date. If $period is set to 12 periods, then $period.setYMD8('20190325') will set $period=201903.

$period.setYMD8('20190325')

$report.setCP()

Set current period +/- relative to current period, 6 digits (‘201812’)

$report.setCP(-1)

$report.setMode()

Set XML report file.

$report.setMode("finance_result")

Additional scripts

There are several scripts which are related to the current user ($user), which you can use to filter data. In addition, there are also script related to account ($account), contact ($contact), business unit ($businessUnit), business area ($businessArea), which can help you to filter data. All these can be combined with .getKey, .getName, .getExternalId.

Examples on how to filter reports used in CLM:

[payer_number] = '$account_key'
[project_number] ='$project_key'
[manufacturing_order_number] ='$order_key'

Examples on how to filter reports on user, this includes both the main business unit and also related business units:

$user.getBusinessUnit('division', 'key', '[division]')

$olap.setLinkScript() may also be used to link drill reports, graphs and addon tables to a URL in your ERP system. For example, you can use this to start a program in M3. The selected dimension in Deem Insight are what you will jump to in M3.

Deem Insight supports embedded content which can be set up as a URL. As an example, we can embed M3 programs.

Please contact your consultant to discuss the possibilities for your company.

Function and script	Description
$user.getAccount()	Returns current users Account object.
$user.getAccountId()	Returns current users Account Id.
$user.getBusinessArea()	Returns current users Business Area.
$user.getBusinessUnit()	Returns current users Business Unit.
$user.getContactId()	Returns current users Contact Id.
$user.getEmployeeNo()	Returns current users Employee Number.
$user.getExternalId()	Returns current users External Id.
$user.getId()	Returns current users Id.
$user.getIdAsString()	Returns current users Id as string.
$user.getKey()	Returns current users Key (email).
$user.getName()	Returns current users Name.

#if

Scripts to exclude data on sum levels in the report, to control when data is shown. Please note, "#if" must be included in the script. These scripts can replace intricate case when with count(distinct[])=1 scripts.

$olap.isCurrentDim()

Can be used in the data field on the measure. Will only display data on the dimension set in the script.

#if ($olap.isCurrentDim('[sales_id]')) max([sales_id]) #else '-' #end

$olap.isDimUsed()

Can be used in the data field on the measure. Will display data on the dimension set in the script and also on lower levels of the data.

#if ($olap.isDimUsed('[sales_id]')) max([sales_id]) #else '-' #end

#if ($olap.isDimUsed('[dim_value_4]')) max([status_descr]) #elseif ($olap.isDimUsed('[project]')) max([project_status_descr]) #else '-' #end

Supports multiple fields:

$olap.isDimUsed('[sales_id],[order_id]')