Working with Tables

Working with Tables

A ManageEngine Analytics Plus workspace could contain a collection of tables. Two tables could be related to each other using lookup columns, thus enabling you to model a relational workspace (Refer Relating tables using Lookup columns). You can create any type of report over these tables which contain the actual data.

Type of a column defines the nature of values a column can contain and the operations that can be done over the same. e.g., an Age column could be "Positive Number"  type.This column can contain only positive numbers and you could apply arithmetic operations over the same.

Table Operations

ManageEngine Analytics Plus offers a range of interactive options to work with a table. Some of the options include Filter, Sort, Format, Find and Replace values, Freeze, and Show/Hide Columns. ManageEngine Analytics Plus also allows to Export, Share and Publish a table.

Note:

  • These options will be enabled only to users who have been provided with appropriate permissions during sharing by the Administrator. Refer to the topic Sharing and Collaboration to know more about sharing.

Searching

ManageEngine Analytics Plus allows you to quickly search for specific records within a large set of data. The search box on the toolbar can be used to locate records in a table that matches the keyword that you specify.
To search for records, type the keyword that you want to search for in the search box and click on the magnifying glass icon next to the search box. All the records containing the specified keyword will be filtered and displayed as shown below. Note that the search operation will search across data contained in all columns of the table.

Filtering

ManageEngine Analytics Plus provides a Filter option to easily filter records in your table based on criteria that you specify. Depending on the data type of the column, ManageEngine Analytics Plus offers various filtering options like filters based on specific numeric ranges, date ranges, individual values, partial match, etc. ManageEngine Analytics Plus also allows you to filter on multiple columns at a time. 

Filter Pane Options: 

Following is a brief description of various options provided in the Filter Pane when you apply filters. 

  • Hide: Click this option to hide the filter pane. You can also use icons at the column header to hide/show the filter pane.

  • Clear: Click this option to remove all the filters applied.

  • Save/Save as: Click this option and type the name in the dialog box that appears to save your filter for later/repeated use. How to save a filter for repeated usage is discussed below.

  • Delete Rows: Select this option to permanently delete the filtered rows from the table. 

Sorting

ManageEngine Analytics Plus allows you to rearrange rows in a table by sorting values in one or more of its columns. You can sort a column numerically, alphabetically, or by date depending on its data type. For example, you can sort a column of dates by their date order, while a column of numbers can be sorted numerically.

The sort options available in ManageEngine Analytics Plus are:

  • Sort Ascending - Sorts text data in ascending alphabetical order (A to Z), numbers from smallest to largest (0-9), and dates from oldest to latest.

  • Sort Descending - Sorts text data in descending alphabetical order (Z to A), numbers from largest to smallest(9-0), and dates from latest to oldest.

Refer to the following video to sort a table: 

Find and Replace

The find and replace option can help you to locate a value in a column and replace it with another value. This feature can be very useful especially when you are working with large sets of data and have the need to quickly correct/rectify issues in your data set.

Note:

  • This option will be enabled only to users who have been provided Write permission by the Administrator.

Options in the Find and Replace

  • Bulk Update: Select this option if you want to replace all the values in the column with the value specified in the Replace With text box. On selecting this option the Find what text box will be disabled.

  • Match entire cell contents: Select this option to limit your search results only to cells whose entire contents match with the Find What value that you have provided. This option is selected by default for number and currency type columns.

  • Do not replace null values: Select this option when you do not want to replace existing null values in the column with the value specified in Replace With. This is applicable only when you use the Bulk Update option 

Couple of Points to Note:

  • Find & Replace operation is Case Sensitive i.e., upper and lower letters are not considered as equivalent.

  • To find a cell with an empty/null value or to replace a cell value with an empty/null value you have leave the corresponding boxes empty.

  • Currency (Ex:-$) and Percentage(%) symbols cannot be used in Find & Replace fields for currency and percentage data types.

  • Find & Replace will be applied over Filtered data if you have already applied a Filter on the table 

Show/Hide Columns

ManageEngine Analytics Plus lets you show or hide columns in a table view. 

Freeze Column

The Freeze Column option allows you to keep specific columns always visible in the table even when you scroll horizontally across the screen. For example, you can freeze columns that identify the records (Ex:- Customer Name, Product ID) in a table so that you can keep track of a record that you are looking for, even when you scroll off to the right-most column in the table.

Reorder and Resize Columns 

ManageEngine Analytics Plus allows you to rearrange the order of columns in a table using drag and drop.

Split a Column

Analytics Plus allows you to split text-based columns using separators such as comma, space, ampersand, etc., This can be used to extract user ID's  from email addresses, separate first and last names, etc.,
The Split Column can be applied over columns with the following data type:
  • Text
  • Multiline
  • Email
  • URL

 

Change Column Properties

 Each column has a set of properties like format, name, data type, etc. Column Properties option in the toolbar provides you with options to change these properties any time you want after creating a column.

Format a Column 

ManageEngine Analytics Plus offers options to change the format of a column like alignment, decimal places, date formats, currency symbol, etc., depending on its data type. Using these options you can choose how you would like to have your column data displayed in your table. 

Note:

  • Option to format columns will be disabled for shared users.

Formating options provided in the dialog box changes based on the data type of the selected column. The following table gives a brief description of these options. 

Numeric Data Type:
OPTIONDESCRIPTION
Alignment

This option can be used to horizontally align the value in the cell. Possible values are left, right and center.

Units

This option enables you to format the unit value in thousands (K), millions (M), and billions (B).

  • None - No unit formatting will be applied to the data. 
  • (Thousands) K - The data will be formatted in thousands (k).
  • (Lakhs) L - The data will be formatted in lakhs (L).
  • (Millions) M - The data will be formatted in millions (m).
  • (Crores) C - The data will be formatted in crores (C).
  • (Billions) B - The data will be formatted in billions (B).
  • (Thousands) K - (Lakhs) L - (Crores) C - The data will be auto formatted to the unit value as thousands (K), lakhs (L) and crores (B) based on the data in the corresponding column.
  • (Thousands) K - (Millions) M - (Billions) B - The data will be auto formatted to the unit value as thousands (K), millions (M) and billions (B) based on the data in the corresponding column.
  • Auto (User Locale Specific) -  The data will be auto formatted to the unit value as per the users local standards i.e., either as thousands (K), millions (M) and billions (B) or as thousands (K), lakhs (L) and crores (B) based on the data in the corresponding column. 
Separator

This option allows you to pick a decimal and thousand separator to be used.

Apply User Locale Settings - You can select this option to use separators based on the locale settings of the user. For example, in US locale a comma will be used for a thousands separator and in case of German locale a dot will be used.

Thousand - This option can be used to select the type of thousand separator. This option will be enabled for number and decimal type columns. This option will be disabled when you have chosen thte Apply User Locale settings mentioned above. Available options include: comma, dot, space and single quote.

Decimal - This option can be used to select the type of decimal separator. This option will be available only for decimal, currency and percentage columns. Also, this option will be disabled when you have chosen the Apply User Locale settings mentioned above. Available options include dot and comma.

Currency Symbol

This option enables you to choose the type of currency symbol displayed. You can select the currency symbol you want from the wide variety of symbols available in the drop down list. This option will be enabled for currency type columns.

Negative Number Display

This options allows you to specify how negative numbers are to be displayed

The available options are:

  • Enclosed in parenthesis - Displays a negative numbers enclosed in parenthesis, For example, displays '-25' as (25).
  • With negative sign - Displays a negative number with a negative sign. For example, displays '-25' as '-25'.
Decimal Places

You can use this option to set the number of decimal places to display for decimal, currency and percentage columns.

Date Data Type:
OPTIONDESCRIPTION
AlignmentThis option can be used to horizontally align date in the cell. Possible values are left, right and center.
Choose Date Format

This option allows you to pick the format for displaying date values, from the given list.

Enter your date format:
ManageEngine Analytics Plus provides this option to allow you to specify a custom date format, if the required format is not available as part of the default list provided. 

 

String/Category Data Type:
OPTIONDESCRIPTION
AlignmentThis option can be used to horizontally align the text in the cell. Possible values are left, right and center.
URL Data Type
AlignmentThis option can be used to horizontally align the text in the cell. Possible values are left, right and center.
Alternate TextThis option allows you to provide a substitute reader-friendly text that will be shown instead of the actual URL.
ImageThis option allows you to replace the URLs that point to images with the corresponding images. Click here to learn more.

Couple of points to note on the behavior of the Alternate text (for URL data type columns) feature in various scenarios:

  • Sorting - When you sort the URL data type column in a table, sorting will be done based on the actual URLs in the column.

  • Copy and Paste - When you copy and paste the alternate text in a column, the actual URL corresponding to it will be pasted.

  • Exporting - When a view with alternate text is exported, the view will be exported with the actual URLs. 

Image URL in Tables

Analytics Plus allows you to convert the image URLs in your table into the corresponding images.

Follow the steps below to do this:

  • Open the required table, right-click the required image URL column, and select the Format Column option.

  • In the popup that appears, choose Image from the Display As drop-down. Please note that the size of the image should not exceed 200 KB.

  • Choose the required dimension from the Image Dimension drop-down.

    You can also provide custom dimensions for the images. Choose Custom from the Image Dimension drop-down and provide the image's dimensions.

    You can click the Ratio icon to maintain an aspect ratio between the height and width.

  • Click OK to replace the image URLs with the corresponding images in your table.

Note: To display the converted images while exporting, ensure the view is exported as an HTML file.

Conditional Formatting

Conditional formatting feature allows you to highlight cells in a column with different backgrounds and font colors based on a condition. You have to specify the required conditions/criteria for formatting. When data in a cell meets the condition, ManageEngine Analytics Plus applies the corresponding formatting style that you have specified. Conditional formatting is currently supported in Tables, Tabular Views and Query Tables.

To apply conditional formatting:

  • Select the column (by clicking the header) on whose cells you want to apply conditional formatting./p>

  • Either right-click to get the pop-up menu or invoke the Column Properties option in the toolbar.

  • In the menu, select the Conditional Formatting option that is shown below.

  • A dialog box appears with options for specifying the conditions and selecting colors for font and background.

  • Click the drop-down arrow under the Condition header and select the type of condition that you want to apply.

  • Type the matching value that you want to use for the condition in the Value text box.

  • Select the required colors for the font and background.

  • You can add any number of conditions using the +Add Another Rule option at the bottom of the dialog box.

  • Click OK after you have added all the conditions.

  • All the cells that meet the criteria will be formatted accordingly.

Note:

  • Conditional Formatting option will be disabled for shared users.

Set Chart Drill Down Path

ManageEngine Analytics Plus allows you to drill down on your chart, enabling you to do powerful exploratory analytics. With this, You can click on any point in the chart and visualize the underlying data points as a new chart. 

You need to configure the drill-down path for any given column in the table/query table. For Date columns, ManageEngine Analytics Plus will set a default path. You can change this as needed. Once the path is configured whenever you use this column in the chart, the corresponding drill path will be followed on drill down.  

Follow the steps given below to define the drill-down path for a column in a table. 

  • Open the table over which you want to create the chart.

  • Click the corresponding column to drill down and then select the Set Drill Down path.

 

  • The Drill Down Path Settings dialog will open. Select the next column to drill down  (the next jump) from the current column using the dropbox.

 

  •  Click OK to set the path.

When you use the column (for which the drill-down path has been defined) in a chart, then clicking its corresponding data point will drill down to the next level based on the path defined.

The following screenshot illustrates a drill-down path set for a text column "Product Category". 

 

 

Rename Column

To rename a column, right-click on the name of the column and choose Rename Column from the drop-down. Enter the new name in the Column Name dialog box that opens up. 

Note: Rename Column option will be disabled for shared users. 

Change Data Type of a Column

After creating a table, it is common for you to feel the need to change an already existing column's data type. Let us suppose you initially defined a column to hold text data, you realize later that it actually contains numeric data. In such cases, ManageEngine Analytics Plus provides you with an option to change the columns data type without losing data in it. However, when changing a column's data type, all the data existing in the column must be compatible with the new data type.

Changing the data type of a column could result in failures. Change Datatype dialog box lists down the action to be taken in case an error occurs during conversion. The options are:

  • Set the default value for the column in the corresponding record: Column values will be replaced with the column's default value. You can view/edit the column's default value using Edit Design in the toolbar.

  • Set empty value for the column in the corresponding record: For every row that fails during conversion the corresponding column value will be set to empty (NULL).

  • Stop conversion and roll back the changes: The entire conversion will be aborted and the changes done till then will be rolled back.

ManageEngine Analytics Plus provides the above options to effectively handle possible errors during conversion. For example, when you try to convert a Number data type column to a Positive Number data type, in case the Number type column does not contain any negative values, then the conversion will take place without any error. In case the column has negative values, the error would occur on converting the column to a Positive Number. In such scenarios the above-mentioned options enable you to choose the appropriate action to be taken on errors during conversion. 

  • After choosing a data type for your column, click OK.

Note:
  • Remember that you cannot change the data type of a lookup column. You have to remove its lookup reference before changing the data type.
  • This option will be disabled for shared users. 

Changing a Column to Lookup

Lookup column relationships in ManageEngine Analytics Plus are similar to Foreign key relationships in Relational Database. A lookup column is a type of column whose values will be referenced from a column in another table in the same workspace i.e., it can be used to cross-reference tables in a workspace. Refer to the topics Relational Data Modeling and Joining Tables to know more about the lookup column.ManageEngine Analytics Plus provides you with an option to change a column to a lookup column. To change a column to a Lookup column:

  • Open the required table from the workspace.

  • Select the column that you want to change to a lookup column.

  • Right-click on the column and choose the Change to Lookup Column option.

  • The Change to Lookup Column dialog box will open as shown below with the Column to Lookup option which lists all the tables and their columns in the workspace as a dropdown list.

 

  • Select the column to lookup from the list.

The Change to Lookup Column dialog also provides options on how to handle errors during conversion and options to define the behavior when a row in the parent table is deleted. Here is a rundown of the options provided: If an error occurs on conversion? Changing a column to a Lookup could result in failures. This option lists down the action to be taken in case an error occurs during the Lookup column conversion. The options are:

  • Set empty value for the column in the corresponding record: For every row that fails during conversion the corresponding lookup column value will be set to empty (NULL).

  • Stop conversion and roll back the changes: If an error occurs during conversion the entire conversion will be aborted and the changes done will be rolled back. This way the table will retain the existing design & values without any lookup column being created and no changes done.

On deleting the values in the Lookup (parent) column: This section provides options to choose an action to be taken in the child table (table in which you define the lookup) when a record is deleted in the parent table (the table which you are looking up). These options are discussed in detail under Enforce Data Integrity section of the Relational Data Modeling topic.

  • Select the required options and then click OK.
Note:
  • Change to Lookup column option will be disabled for shared users.

Analyze Column

ManageEngine Analytics Plus can automatically analyze your column's data and create sample reports by picking meaningful metrics. You can get started with these reports or modify them or create your own. To analyze your column and create reports. 

  • Select a column by clicking on its header and right-click on the header.

  • In the right-click pop-up menu select Analyze Column option as shown below.

  • On successful completion of the analysis, tables reports will be generated.

  
Note:
  • Analyze Column option will be disabled for shared users of the workspace. 

Copying a Table

ManageEngine Analytics Plus allows you to save a copy of a table in your workspace.  On copying, all formatting, formulas, etc., will be retained in the new table too. Follow the steps given below to copy a table. Open the table you want to copy.

  • Click the More Options icon near the table name, the Save As option will appear. Select this to open the Save As dialog.

  • Specify a name to the new table in the Name field.

  • Select the folder to save the new table in Save in folder drop-down.

  • The With Data option allows you to choose whether to copy the table with or without data.

  • Select this to back up the data. By default, this will be selected.

  • Unselect this to copy the table design, where you can import similar data. e.g., you have a sales table for last year and want to create a new table for the current year. You can copy the table design and import the current year's data instead of creating it from scratch.  

 
  • The Retain Lookup option allows you to choose whether to retain the lookup columns defined in the original table.

  • Click OK. The table will be copied as needed. 

Exporting a Table

 ManageEngine Analytics Plus allows you to export tables that you have into various file formats like CSV, PDF, XLS, or HTML files. To export a table: 

  • Open the table that you would like to export.

  • Select Export in the toolbar.

  • Select one of the five export format options: In the Export Settings window that opens, set the required options and then click the Export button at the bottom to export. 

    • CSV -Exports the table's data as a Comma Separated Value (CSV) file.
    • PDF- Exports the table's data in Portable Document Format (PDF) file format.
    • HTML - Exports the table's data in HTML file format.
    • Excel (XLS)- Exports table as an Excel file.

  

Sharing a Table

In ManageEngine Analytics Plus you can easily share your table for other users' access. You can also set specific permissions on the shared tables so that the users would be allowed to do only permitted actions when they access the table. The various share options are provided under the Share menu in the toolbar. Refer to the topic Sharing and Collaboration to know how to share a table.  

Note:
  • Share option will be enabled only to users who have been provided that permission during sharing by the Administrator.

Publishing a Table

ManageEngine Analytics Plus allows you to publish/embed tables into your websites/web pages or as easy-to-access URLs. ManageEngine Analytics Plus also allows you to control the access privileges and permissions provided to the users who access the table. Refer to the topic PublishingOptions to know how to Publish a table.  

Note:

  • This option will be enabled only to the Administrator and Owners of the workspace.

Related topics: Add Rows and Columns | Adding Formulas (Calculations) | Relational Data Modeling | Joining Tables

 

 

Share this post : FacebookTwitter