It's All About the Platform.

  • BI
    June 20, 2016

Using Custom Formulas and Conditional Formatting in BI

Richard Bingham
Senior Development Manager

This is a quick-and-simple illustration of two BI features for use when creating custom analytics in Fusion Cloud Applications. The features are core parts of the underlying Oracle BI platform, and as such are highlighted for Fusion Cloud Applications users and developers who may be new to this toolset. 

Adding Custom Formulas

When creating a new analysis you commonlu want to include only the most valuable information in your output, and not a long table of rows or even a proportional graphic which does not provide a specific value. As such the creation process frequently requires some calculation and aggregation on the raw data available.

Even using the BI Composer wizard-like steps for creating a new analysis you can include formulas on your criteria values. As shown below, the wizard does not include a steps to setup complex formulas, but if you click the pencil icon in the top right you can refine your column value, entering an actual formula in the field.

 In the results you can see the simple COUNT() function applies.

Going further when using the full BI Analysis authoring screens then you have access to the formula creation features as shown below. There is extensive help on syntax in these screens, including built-in validation. What is especially useful is the fact you can copy the resulting formula string from here and paste it into the BI Composer popup above, allowing you to reuse and share a common formula without all users having to have access to the full authoring screens.


Click here for larger view. 


Applying Conditional Formatting 


First we define the conditions on the Column Properties dialogue, shown below (again accessed from the Criteria tab, under the 'cog' icon). We add a condition and as you can see there are many different formatting options you can include, not just color. This includes the use of custom CSS classes also.

Then we specify the criteria when the new format will apply. Finally we verify we have our conditions setup how we want. Note that they execute sequentially order, top-down, so the order you define can be important in the final result. In our example we'll just change the color based on value range.

Finally we check the 'Results' tab and verify it shows the colors as expected.

You can read more about conditional formatting in the Oracle BI documentation.




Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.