# BI functions

## Introduction <a href="#toc132789781" id="toc132789781"></a>

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

## applfn\_ConvertToBITimeZone <a href="#toc132789782" id="toc132789782"></a>

Purpose

Support time zones for date fields

#### Settings <a href="#toc132789784" id="toc132789784"></a>

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 <a href="#toc135647578" id="toc135647578"></a>

| **Parameter** | **Description**                           |
| ------------- | ----------------------------------------- |
| @cDate        | DateTimeOffSet that needs to be converted |

#### Syntaxis <a href="#toc135647579" id="toc135647579"></a>

Select dba.applfn\_ ConvertToBITimeZone(@cDate)

#### **Example**

Select dba.applfn\_ConvertToBITimeZone(JobSchStartDate) from dba.job

#### Example result <a href="#toc132789785" id="toc132789785"></a>

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

#### Remarks <a href="#toc135647581" id="toc135647581"></a>

* Before RR (2023.05.05) known as applfn\_ConvertToPowerBITimeZone

#### Status <a href="#toc132789786" id="toc132789786"></a>

Available since 2020.R1

## applfn\_GetBIColumnTranslation <a href="#toc135647583" id="toc135647583"></a>

Purpose

Support multilingual fields.

#### Settings <a href="#toc132789790" id="toc132789790"></a>

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 <a href="#toc135647586" id="toc135647586"></a>

| **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 <a href="#toc135647587" id="toc135647587"></a>

Select dba.applfn\_GetBIDescriptionWithId(@TableName, @ColumnName, @ColumnValue)

**Example**

Select dba.applfn\_GetBIDescriptionWithId(N'Equipment', N'EqmDescr', EqmDescr) from dba.equipment

#### Remarks <a href="#toc135647588" id="toc135647588"></a>

* Before RR (2023.05.05) known as applfn\_GetPowerBIColumnTranslation

#### Status <a href="#toc132789791" id="toc132789791"></a>

Available since 2020.R1

## applfn\_GetBIDescriptionWithId <a href="#toc132789792" id="toc132789792"></a>

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 <a href="#toc135647592" id="toc135647592"></a>

| **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 <a href="#toc135647593" id="toc135647593"></a>

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 <a href="#toc135647594" id="toc135647594"></a>

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

#### Remarks <a href="#toc132789795" id="toc132789795"></a>

* Uses [applfn\_GetBIColumnTranslation](#_toc135647583) 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 <a href="#toc132789796" id="toc132789796"></a>

Available since 2020.R2

## applfn\_GetBIFilterId <a href="#toc132789797" id="toc132789797"></a>

Purpose

Combine all trashed items to one item (per company)

#### Arguments <a href="#toc135647599" id="toc135647599"></a>

| **Parameter** | **Description** |
| ------------- | --------------- |
| @nStatus      | Record status   |
| @Id           | Record id       |
| @CompanyId    | Company         |

#### Syntaxis <a href="#toc135647600" id="toc135647600"></a>

Select dba.applfn\_GetBIFilterId(@nStatus, @Id, @CompanyId, @Id, @nStatus)

**Example**

Select dba.applfn\_GetBIFilterId(PrfRecStatus, PrfId, PrfCompId) from dba.ProcessFunction

#### Example results <a href="#toc135647601" id="toc135647601"></a>

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

#### Remarks <a href="#toc132789800" id="toc132789800"></a>

* Uses [applfn\_GetBIIdWithCompany](#_toc132789802) 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 <a href="#toc132789801" id="toc132789801"></a>

Available since 2020.R2

## applfn\_GetBIIdWithCompany <a href="#toc132789802" id="toc132789802"></a>

Purpose

Handle empty foreign keys with multi company filtering

#### Arguments <a href="#toc135647606" id="toc135647606"></a>

| **Parameter** | **Description** |
| ------------- | --------------- |
| @Id           | Record id       |
| @CompanyId    | Company         |

#### Syntaxis <a href="#toc135647607" id="toc135647607"></a>

Select dba.applfn\_ GetBIIdWithCompany(@Id, @CompanyId, @Id)

**Example**

Select dba.applfn\_GetBIIdWithCompany(PmwDepId, PmwCompId) from dba.PmWorkorder

#### Example results <a href="#toc135647608" id="toc135647608"></a>

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

#### Remarks <a href="#toc135647609" id="toc135647609"></a>

* Explanation for prefix blank items
  * BIRWC -> BusinessIntelligence-Replacement-WithCompany
* Before RR (2023.05.05) known as applfn\_GetPowerBIIdWithCompany
  * Also working was different

#### Status <a href="#toc132789805" id="toc132789805"></a>

Available since 2020.R2


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://developer.ultimo.net/database/bi-overview/bi-functions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.