📈Excel Addin

You can efficiently transfer data stored in the ObX historian to an Excel spreadsheet using the ObX Excel add-in. This integration provides a robust reporting tool, simplifying daily report analysis and other related tasks.

ObX Excel Addin

The ObX Excel add-in is located within the same directory where you installed the ObX historian component. Specifically, you can find it in the {installation_path}/Excel Addin folder of the ObX historian component's installation directory. It's pre-installed and readily available, so all you need to do is activate the add-in within Microsoft Excel.

Excel Addin Installation

The ObX Excel add-in is not automatically included in your Excel sheets initially. To integrate it, you must manually adjust your Add-in settings within Excel.

The Excel add-in can be installed on any client computer with Microsoft Excel installed. Ensure that you only install the add-in on those clients where you wish to optimize its functionality.

  1. Open your Microsoft Excel and goto -> Options.

  2. Goto -> Add-ins Tab, and under it select to Go button.

  3. The add-in dialog will open, and click the browse button.

  4. This will open the default folder for you Excel's addin, typically in the C:\Users\[yourUserName]\AppData\Roaming\Microsoft\AddIns folder.

  5. Copy the ObX Excel addin to this Folder, you can simply Copy and Paste the addin in the open folder dialog.

  6. Then select the ObX Excel Addin R3.00.xlam from the folder, and Click OK.

  7. From the list of Addin, make sure the ObX excel Addin R3.00 is checked. then Click OK.

  8. You notice a new Tab in your Excel sheet named ObX.

Excel Addin Buttons

Here, we will delineate the diverse operations performed by the buttons within the Excel Add-in.

Button Name
Icon
Functions

Run

This feature facilitates the automatic refresh of data within Excel. It prompts users to specify the frequency of updates through a new dialogue box. This functionality proves beneficial for real-time data visualization within Excel spreadsheets. However, it's important to note that utilizing this auto-update feature may lead to Excel crashes, particularly when handling large datasets. Therefore, caution should be exercised when employing it.

Stop

This action will halt the execution of any programmed automatic updates that were initiated through the "run" button configuration.

Refresh

Instead of enabling automatic updates, you have the option to manually update your data by pressing a designated button. This feature is particularly beneficial for managing both real-time and historical data sets, and it helps to reduce the strain on Excel's resources, thereby mitigating the risk of potential crashes.

Query Data

This tool facilitates the configuration of OData functions, which are integral to the ObX Excel add-in. OData functions enable users to efficiently retrieve data by specifying parameters or arguments they input.

List Tags

To enumerate all accessible tags or parameters associated with particular data sources.

Set Timezone

To accurately synchronize data with time standards, it's essential to configure the timezone corresponding to your geographical location. Failing to do so may result in discrepancies in temporal data representation and processing.

Save Report

This tool enables users to customize and preserve their Excel spreadsheet into a new document devoid of formulas, facilitating effortless sharing of the report without the necessity of the Excel add-in. Additionally, it offers the functionality to save the report in PDF format.

AutoReport Config

This tool facilitates the customization of automated report template generation and storage.

Set Credentials

This feature enables users to set up their credentials (username and password) for connecting to the ObX historian. It is essential to ensure that the Excel Report user has been properly established in the ObX Admin Tool.

OData Function

OData function serves as the operational function utilized by the ObX Excel Add-in to streamline the retrieval of data from the ObX Historian. This function comprises several parameters that require input. Depending on these parameters, it retrieves data sourced from the ObX Historian.

Below are the list of parameters / arguments of Odata function:

Argument
Description
Values

Tagname

The name of the Tag in the ObX historian.

Type: String Remark: Required

Item

The item of the Tag to return by the ObX historian.

Type: String

Remarks: Required Could be the ff: value - The value of the tag selected in Tagname description - The description of the selected in Tagname engmax - The configured maximum value of the Tag

engmin - The configured minimum value of the Tag status - the status of the Tag timestamp - the returned timestamp of the query. unit - the configured unit of the Tag.

StartTime

The time where to start the query.

Type: String Remarks: optional

Default: now()

Format: YYYY-MM-DD hh:mm:ss Where: YYYY - the year e.g. 2021, 2022 and etc. MM - the month (zero leading) e.g. 01 for Jan, 02 for Feb and 10 for Oct. hh - the time in hour. Military format, 1 PM = 13. mm - the minutes in time 0-60 (zero leading for single value) ss - the seconds in time 0-60 (zero leading for single value)

EndTime

The time where to end the query.

Type: String Remarks: Optional Default: now()

Format: YYYY-MM-DD hh:mm:ss Where: YYYY - the year e.g. 2021, 2022 and etc. MM - the month (zero leading) e.g. 01 for Jan, 02 for Feb and 10 for Oct. hh - the time in hour. Military format, 1 PM = 13. mm - the minutes in time 0-60 (zero leading for single value) ss - the seconds in time 0-60 (zero leading for single value)

Interval

The sampling rate determines the frequency at which data is collected within a specified time range. For instance, if you have a one-hour dataset with data points recorded every second, you'll end up with 3600 data points in total. However, in certain scenarios, you may want to reduce the data volume by down-sampling.

Down-sampling involves reducing the number of data points by setting a larger sampling interval. For example, if you set the interval to one minute instead of one second, you'll now only have 60 data points instead of the original 3600. This process helps in managing and analyzing large datasets more efficiently.

Type: String

Remarks: optional

Default: 1s Same concept of $_interval as discussed here. 1m = 1 minute 10s = 10 seconds 1h = 1hour 2d = 2 days 1M = 1 month

Agg

You have a selection of aggregation functions available for data transformation purposes. Aggregation is a powerful method for converting raw data into meaningful information. For instance, if you have one hour's worth of data, with a sampling interval set to one minute, you anticipate obtaining a dataset consisting of 60 data points sampled every minute.

Type: String

Remarks: optional

Default: first List of aggregations: first - Return the first value of the Tag on a specific Interval. mean - The average value of the Tag on a specific Interval. median - The middle value of the Tag on a specific Interval. mode - The most frequent value of the Tag on a specific Interval.

sum - The sum of all values of the Tag on a specific Interval.

min - return the minimum value of the Tag on a specific Interval. max - return the maximum value of Tag on a specific Interval. Distinct - Return all unique values of the Tag on a specific Interval.

Count - Return the number of data points of the Tag on a specific Interval.

last - Return the last value of the Tag on specific Interval.

spread - Return the difference between the last and first value of the Tag on a specific Interval. Highly used for getting consumption data of Energy (kwH) for accumulative value.

Bounding

Bounding refers to the process of defining the behavior of data when values are absent within a specified time range. Imagine you have a dataset capturing trends over one hour, with samples taken every minute. If there's a gap in the data within that hour, bounding is the mechanism that steps in to provide values for the missing data points.

Type: String Remarks: optional Default: null List of Bounding functions: none - It will automatically escaped those missing values null - It will automatically fill-in missing values with "null" string. previous - It will automatically fill-in missing values with the previous value. linear - It will automatically fill-in missing values with the average values between the previous and next value.

Orientation

The orientation of the returned data. Weather it is vertical or horizontal.

Type: Integer Remarks: optional Default: 0 0 - Vertical orientation 1 - Horizontal orientation

RecentFirst (OrderBy)

Whether the returned data is in the most recent (Ascending order) or vise versa (Descending order)

Type: Boolean Remarks: optional Default: FALSE False - the returned data is from oldest to recent (Ascending order) True - the returned data is from recent to oldest (Descending order)

SIPAdd (IP Address)

The IP address of the computer hosting the ObX WebUI component.

Type: String Remarks: Required IP address : xxx.xxx.xxx.xxx DNS - DNS name defined to your host file.

Org_name (Datasource)

The datasource name in the ObX historian.

Type: String Remarks: optional Default: OBS A valid datasource name in the ObX Admin tool as discussed here.

Dec_num (Decimal #)

The number of decimal places of the returned data.

Type: number Remarks: optional Default: 2

Historian_instance

The ObX historian instance. Make sure to properly set the IP address of the ObX historian in the conf flle as discussed here.

Type: String Remarks: optional Default: db If you are only running single instance of ObX historian component, then just leave this to default value.

The good thing is you don't have to manually input all arguments in the Odata function, the Query Data tool (as discussed here) is a user-friendly UI component to easily define and configure the OData function. ☺️

Creating Excel Users

Prior to utilizing the Excel add-in, it's imperative to generate credentials for accessing the ObX historian data. This step is crucial because lacking the proper credentials will prevent you from retrieving data from the ObX historian.

You can create the Excel user provided here

Create Excel Report User

The user created in the Excel Report will be the credentials use to access the ObX historian. The default user credentials is: username: excel password: excel

Setting User Credential

This is where we will set the credentials used to connect to our ObX historian.

  1. Open your Microsoft Excel

  2. On the Tab above, head over to the ObX tab by clicking it.

  3. From the ObX menu, click on the Set Credentials under the API Credentials section.

  4. Enter the valid username and password in the form, then Click Save button.

Upon changing the credentials, it will ask you to input your existing password, if this is the first time to set the credentials, the default password will be excel .

Changing Credentials
PreviousDashboard GenerationNextAPI Documentation

Last updated