====== Field Formats in BIPLUS ====== ===== Field Formats Basics ===== Field formats in the BIPLUS are very powerful and allow the developer to control the way certain fields look and behave, to specify conditional formatting, specify date and numeric masks, left / right / centre alignment, hide certain columns, specify column header heights, override metadata column information, etc. The following graphic shows the **Evaluate SQL** dialog of a simple report that selects information from a summary table referenced as **SALES** in an Oracle database. Using **Evaluate SQL**, the raw data is returned from the database, so that the developer can see what the data looks like and what he/she needs to deal with.

However, business user doesn't want to see information in the same way it is stored in the database. Column names might have different meanings in different business settings, numerical values are probably referring to monetary sums, ratios, percentages, counts, scores and other numeric measures. Dates are somewhat complex to process in the default database format, people want to see month names, year names, days of the week (Monday, Tuesday, etc.) and the list goes on. To facilitate multi-lingual deployments where a common database is used, a column header / field could be labelled according to the currently logged in user’s language selection (‘Report’ in English, ‘Bericht’ in German, ‘Rapport’ in French, etc.). Furthermore, values could be converted to images based on their proportions (traffic lighting), default columns could be set differently from the global default. BI+ Facilitates this type of functionality via **Field Formats**. Click the **Field Formats** tab in the developer view once the query has been constructed and the raw data is returned. In order to change the way the columns are displayed, BIPLUS enables the developer to define field formats for each column.

The **Field Formats** are applied in sequential order. The above screenshot shows Field Formats that affect column headers (Display Name), Display widths (Column width), Numeric formatting (number Format) and Date Formatting (Date Format). To see the effect of these Field formats in the end user Reporting tool, once field formats have been applied, you can click the **Viewer** button to see the report **EXACTLY** as the end user would.

What are the effects of the changes from the above diagram? : - Changed number format for the COST_OF_SALES & AMOUNT columns. - Changed column name of SALESMAN to 'Agent' using the **Display Name** Field Format (FF). - Changed SALE_DATE column's date format and display name. - Changed the column default width of the AGENT, DATE & PRODUCT columns. ===== Types of Field Formats ===== __**Cell Style:**__ The Cell Style field format is used to highlight a particular cell of a column, depending on its value. The following graphic illustrates the application of a Cell Style field format. The Cell Style format mask has two tabs: **Simple** & **Advanced**. **Simple** tab is used when there is a single condition to test. **Advanced** tab is used to write a smart / complex condition using multiple conditional operators. Right click in the Advanced tab's text area to see the provided conditional operators. If you are applying a condition on string data, then specify that data in double quotes. Example:"USA".

The condition in the above example highlights the cell of the SHIPCITY column with Style2 (from the report Theme) where **SHIPCITY = "Elgin" AND SHIPCOUNTRY = "USA"**. Running the report will show the following result where those rows that satisfy the given condition are coloured using the Cell Style FF.

__**Center Align:**__ The name itself is a self-explanatory. **Center Aligns** aligns the data of the column in the center. To enable this, select the Column Name and then select format type as **Center Align**. __**Column Group:**__ Column Group is used to group several columns of a table report under one header. For better understanding see image below. The following image shows ORDERDATE & SHIPPEDDATE combined under the **Dates** Title. SHIPNAME & SHIPCITY are combined under the **Ship Information** Title. Then both Dates & Ship Information are combined under **Order** Title. This can be achieved using Column Group field format.

To create Column Group, select a column from which the grouping begins. The Format mask is used to set the header caption and color style. 'Other Info' is used to define the number of columns to group under current header. The next screenshot shows that from ORDERDATE, the next two columns are grouped under header Dates and so on...

__**Column Header Align:**__ This is used to align the header of a column (Column Name) to the Left, Center or Right. It can also be used to display the column header vertically. Select a column name to apply this formatting, then select 'Format Type' as **Column Header Align** and then select 'Format Mask' (**Left, Right, Center, Vertical**) to suit your requirement.

__**Column Header Height:**__ When a vertical label for the column/s is applied, then you need to adjust the column header height depending upon the length of label. This can be achieved using the **Column Header Height**. To apply this field format just selects a column and format type as **Column Header Height**. In the format mask it accepts only integer values to depict how many lines tall the header should be.

In the above example, we have applied a **Column Header Height** format to the Country column because it is a vertical label. If we do not apply that then we will not be able to view this vertical label as it will be truncated to the normal height.

__**Column Width:**__ When a BIPLUS table report is executed its column are adjusted according to the size of BIPLUS window. The default column width is 15 unless overridden in the settings tab. To override the default column width, use this field format. Why this is necessary? See the following report **without** using column width field formatting.

And this is how **Column Width** is applied. The Format Mask accepts integer values only and this width specified as an integer is actually the number of characters.

After applying the column width field format, the report has saved significant space and looks far better (more readable) than before.

__**Date Format:**__ Date formatting is not constant throughout the world. The date format is a standard way to express a numeric calendar date that eliminates ambiguity. Although each date basically displays the day, month, and year, their presentation, order and separators vary greatly. For example, in North America it is usual to write the month before the day of the month (March 10) whereas Europeans generally write the date before the month (10 March). Select a Date or datetime column and open the format mask to invoke the date formatting dialog. Select the date form that satisfies your requirement or build in up in pieces using the **Append** button. Click the **Evaluate** button to see how it will look and edit accordingly.

BI Plus supports the various Java Date Formats. Beware of language specific settings as this varies and is based on the operating system and browser language settings.

The following image shows DATE 1 is the default date format, but after applying a date field format, you can show date formats to suit any requirements. See the applied date format on the column DATE2, DATE3, DATE4 and DATE5.

__**Display Name:**__ Display Name works as an alias name for a column header. Select a column and its 'Format Type' as **Display Name** and enter its alias name in the 'Format Mask' field. When BI Plus runs in multiple concurrent languages, you can set the display name of a column to display different values depending on the language being run. __**Drillthrough:**__ The idea of drillthrough supports reusable objects. Drillthrough calls reports by directly drilling down on a column and it can navigate to any report. This topic is covered in detail in the Drilldown and Drillthrough topics. __**HTML Content:**__ If HTML content needs to be rendered in a report, then the **HTML Content** field format is helpful. In the following report, HTML content has been added, which converts the color of the SALESMAN column to RED.

Enable **HTML Content** from Field Formats.

On running the report, see how the rendered HTML content shows the SALESMAN column set to red.

__**Hide:**__ The **Hide** Field Format allows you to hide a selected column which you are using in SQL query but you do not want to show that column in the output. To apply this feature select a column name on which to apply and format type as 'Hide'. There are several reasons why you would want to do this. For instance, if you want to extract the month and year (Mar 2013) from a date column, but sort it logically in descending month order (Mar 2013, Feb 2013, Jan 2013), you will need to extract another instance of that date in YYYYMM format, then sort it by this column. Since 201303, 201302 and 201301 are far less intuitive to end users than 'Mar 2013, Feb 2013, Jan 2013), you then hide the YYYYMM field as it is used purely for sorting and does not clutter the report with unnecessary information. __**Image:**__ The Image field format is used to display an image for a column value for a given condition. Select this field format based on the need to apply the condition using conditional operators. You can use any image, by copying the image to **C:\BIPLUS\Tomcat7\webapps\BIPLUS\images\report** location and then selecting that image while creating a condition. (This path may be little different depending on where you have installed BI PLUS and the name of your BI PLUS environment.) The following image shows how to create conditions for the image field format. Just select a conditional operator, value and image.

Running the report with image field formats yields the following result.

__**Left Align:**__ Aligns the selected columns to the left side of the cell. Just select the Column Name and select format type as Left Align. Leave the 'Format Mask' and 'Other Info' fields blank. This is used when there is a requirement to left align a numeric field, which is right-aligned by default. Text and date fields are left-aligned by default. __**Mouse Over:**__ This is used as a tool-tip to display when the user performs a mouse-over on a certain column. To use this field format define a master key on the column whose value you want to show in the tooltip. You can then hide the column, so it reduces the space taken on the report. In the format Mask value, specify the master key you have just defined as the tooltip.

The following example displays the name of the country when the mouse is moved over the city column.

You can also use HTML tags to create an advanced tool tip. The illustrated example below shows three master keys (COUNTRY, SOLDBY, ORDERTOTAL). In the format mask, the following HTML code was added. When the mouse is moved over the CITY column, the tool tip will show the Country with its flag, the SOLDBY value and the ORDETOTAL amount.

The advanced HTML tool-tip will look like the graphic below.

__**Number Format:**__ The Number Format works similarly to the Date Format field format. You may need to change the number format according to your need. For example, the number format for currency in the United States is **$1,000,000.00** and in Spain it is **€1.000.000,00**. In Spain, comma is used for decimals and period is used for thousands. Therefore, we need a way to format numbers. The following screen what your data looks like without number formatting.

When defining a number format, you can select any number formats or specify your own format in the **Format** field.

After applying number format your output will look like the graphic below.

__**Right Align:**__ Aligns the selected column's data to the right. Numbers are right-aligned by default so this applies to non-numeric data. Select the Column Name to right-align and select format type as Right Align. Leave the 'Format Mask' and 'Other Info' fields blank. __**Row Style:**__ The Row Style field format is similar to the **Cell Style** field format, where you can highlight an entire row for a specified condition with a selected style color. When defining Row style Using the Advanced Option. For String values you can write COLUMN = “VALUE”. In some databases you need to TRIM the value before equating: TRIM(COLUMN) = “VALUE”. __**Upper Case:**__ Self Explanatory: This field format converts all data in the selected column into upper case. __**Value Map:**__ Value Map is used to map replacement values instead of the original value. In the format Mask of the value map you can write several types of statements and in various formats: * Simple : **USA = United States of America** so when country = USA will appear it will be replaced with 'United States of America'. * HTML : **USA = USA** so where country = USA then it will be replaced with bold 'USA'. * SQL: Start writing sql statement with **# SQL:** then your SQL query. __**Function:**__ At the time of this manual, a selected number of BI Plus internal functions existed. These can be used via Field Formats to invoke external processing. * **Accumulate:** gives a list of the successive accumulated totals of elements in the list. * **AddCalculatedColumn:** Using this function you can create a calculated column values. In the example below we are substrating 1 from Amount value. * **RowNumber:** Oracle has built in RowNumber function but other databases do not. This displays RowNumbers in the selected column. Following image shows that how Accumulate, AddCalucultedColumn and RowNumber functions are applied. {{:biplus:documentation:applying_accumulate_calccolumn_rownum.png?nolink&450|}} If the effect you will get the following output. {{:biplus:documentation:ff_Accumulate_calcColumn_rowNum.png?nolink&550|}} * **BSHFunction:** * **Cast:** * **Concatenate:** * **CreateGroupBands:** * **DynamicFieldFormats:** * **FormatData:** * **Fraction:** calculates the percentage of respective value. * **Hierarchy:** * **HierarchyLevel:** * **HierarchyPath:** * **HierarchyRowNumber:** * **HierarchySummary:** * **Indent:** * **ParetoABCAnalysis:** * **Pivot:** * **Rank:** * **Transpose:** * **Unpivot:** BI Plus can be extended with custom functionality. Generally this is in the form of a Java Beanshell Scripting (BSh).