BI functions

Introduction

Within the views these bi related functions are used to transform data for specific purposes

applfn_ConvertToBITimeZone

Purpose

Support time zones for date fields

Settings

The date will be converted to the timezone set in the application setting “BI Time Zone” (UmmBusinessIntelligenceTimeZoneMapping). When empty date is changed to value without offset.

Arguments

Parameter	Description
@cDate	DateTimeOffSet that needs to be converted

Syntaxis

Select dba.applfn_ ConvertToBITimeZone(@cDate)

Example

Select dba.applfn_ConvertToBITimeZone(JobSchStartDate) from dba.job

Example result

Date	Result
2022-08-08 12:05:00.0000000 +01:00	2022-08-08 11:05:00.0000000 +00:00

Remarks

• Before RR (2023.05.05) known as applfn_ConvertToPowerBITimeZone

Status

Available since 2020.R1

applfn_GetBIColumnTranslation

Purpose

Support multilingual fields.

Settings

The description (when multilingual) will be in the language set in the application setting “BI Language” (UmmBusinessIntelligenceLanguage). When empty the application configuration language is used.

Arguments

Parameter	Description
@TableName	Table with te possibly multilingual field
@ColumnName	Column with te possibly multilingual field
@ColumnValue	Value that is the result for select column from table

Syntaxis

Select dba.applfn_GetBIDescriptionWithId(@TableName, @ColumnName, @ColumnValue)

Example

Select dba.applfn_GetBIDescriptionWithId(N'Equipment', N'EqmDescr', EqmDescr) from dba.equipment

Remarks

• Before RR (2023.05.05) known as applfn_GetPowerBIColumnTranslation

Status

Available since 2020.R1

applfn_GetBIDescriptionWithId

Purpose

Show the description with additional information to improve filtering and grouping.

• Filtering, make sure it can be filtered on id and description within the same field.

• Grouping, prevent grouping based on description, which some BI tools do automatically.

Arguments

Parameter	Description
@TableName	Table with te possibly multilingual field
@ColumnName	Column with te possibly multilingual field
@ColumnValue	Value that is the result for select column from table
@Id	Record id
@nStatus	Record status

Syntaxis

Select dba.applfn_ GetBIDescriptionWithId(@TableName, @ColumnName, @ColumnValue, @Id, @nStatus)

Example

Select dba.applfn_GetBIDescriptionWithId(N'Site', N'SitDescr', SitDescr, SitId, SitRecStatus) from dba.Site

Example results

Description	Id	Trashed	Result
Office	01	0	Office (01)
Factory	02	1	Factory (*)

Remarks

• Uses applfn_GetBIColumnTranslation for the description in order to support mulitilingual.

• When record is trashed an asterisk * will be used i.o. the id.

• Before RR (2023.05.05) known as applfn_GetPowerBIDescriptionWithId

Status

Available since 2020.R2

applfn_GetBIFilterId

Purpose

Combine all trashed items to one item (per company)

Arguments

Parameter	Description
@nStatus	Record status
@Id	Record id
@CompanyId	Company

Syntaxis

Select dba.applfn_GetBIFilterId(@nStatus, @Id, @CompanyId, @Id, @nStatus)

Example

Select dba.applfn_GetBIFilterId(PrfRecStatus, PrfId, PrfCompId) from dba.ProcessFunction

Example results

Id	Company	Trashed	Result
001	000001	0	001
001	NULL	0	001
002	000002	1	BIRWC-Trashed-000002
002	NULL	1	BIR-Trashed

Remarks

• Uses applfn_GetBIIdWithCompany to get an company specific id when not trashed

• Explanation for prefix trashed items

  • BIRWC -> BusinessIntelligence-Replacement-WithCompany

  • BIR -> BusinessIntelligence-Replacement

• Before RR (2023.05.05) known as applfn_GetPowerBIFilterId

  • Also working was different

Status

Available since 2020.R2

applfn_GetBIIdWithCompany

Purpose

Handle empty foreign keys with multi company filtering

Arguments

Parameter	Description
@Id	Record id
@CompanyId	Company

Syntaxis

Select dba.applfn_ GetBIIdWithCompany(@Id, @CompanyId, @Id)

Example

Select dba.applfn_GetBIIdWithCompany(PmwDepId, PmwCompId) from dba.PmWorkorder

Example results

Id	Company	Result
001	000001	001
001	NULL	001
NULL	000002	BIRWC-Blank-000002

Remarks

• Explanation for prefix blank items

  • BIRWC -> BusinessIntelligence-Replacement-WithCompany

• Before RR (2023.05.05) known as applfn_GetPowerBIIdWithCompany

  • Also working was different

Status

Available since 2020.R2