Creating Word / Tag Clouds in Obiee 11g

Like we discussed in my other blog over D3 visualizations in Obiee 11g, we have another data visualization technique called “word clouds”, that is used to graphically show word distributions in unstructured data sets through font sizes and orientations. For example, the word cloud shown below display the company names in different sizes/fonts.

It can be implemented in OBIEE11g reports, without using any extra help. We just have to create a dummy column using any logic to get dummy values ranging between 5 to 70 and then in Narrative View we can use this column in font size of Company column.

for example: I created a dummy column C1 like (count Of Transaction) * 5) / Max (count Of Transaction). This will give me value range from 5 to 70 ( you can range your values depending on Font size required in Cloud).

Now in Narrative View, while displaying the Company name column, mention Style (font-size = @C1;) and its done.

Do try it.

OBIEE11G : conditional-formatting for rows

What is Conditional formatting? In OBIEE, its actually applying GUI formats to our analyses columns like $, %, background color etc, based on a single condition or we can say using single logical operand. In this blog, will discuss the possibility of using multiple conditions together to get 1 specific row-column formatting. In OBIEE we get many complex requirements (Table/Pivot) like highlighting the value for Year = 2015 and region = East and data range from 0-200, then orange, 201-400 then green. But using OBIEE GUI conditional Formatting this is not feasible. We can do hit/trials by writing one condition , then override it with another condition, to get our results but it does not always work. OBIEE11g GUI doesn't allow us to write complex conditions.

For example: I have a business requirement to highlight row where region = EAST, and we have to show data with different colors in different ranges (like image below) :

If i try to achieve it using OBIEE GUI, column properties-> Conditional Format option, it will be writing multiple condition to override one another like:

But if we notice the conditions, it will effect all values of table which have value between data ranges, and not specifically only row which has  value EAST, like below:

Now lets proceed to achieve our complex requirement by playing with Advanced Tab->  Analysis XML.OBIEE reports are stored in an XML format. We can read it and find lot of information about functionality the GUI doesn’t allow to perform but can be coded manually.If you take out a copy of your report you will find that conditions are written in conditional formatting blocks, using logical operators like "Equal", "Between".

 
 We can similarly apply "AND" operator and write a different format condition like below:

Now when we go to column properties of data range column and see conditional Format tab, we found different condition, like sawx:exprsawx:logicaland. This explains usage of AND Operator.
If you try to edit it from OBIEE GUI, you cannot do it as formula is not seen. Hence, if again any change required, go to Advanced XML and make changes.

 

Note: Always keep a backup of your XML , before making any changes to it, since it not a play game.

Thanks :)


 

Using Repository Variables in Conditional Formatting in OBIEE 11G Reports

Using Variables in Conditional formatting in OBIEE  11G

You can reference variables in several areas of Oracle BI Enterprise Edition, including in analyses, dashboards, KPIs, agents,filters and conditional formatting. There are two types of variables that you can be created through RPD :

• Session
• Repository

Session Variables

A session variable is a variable that is initialized at login time for each user. When a user begins a session, the Oracle BI Server creates a new instance of a session variable and initializes it.

There are two types of session variables:

• System — A session variable that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes.

System session variables have reserved names that cannot be used for other kinds of variables (such as static or dynamic repository variables and non-system session variables).

• Non-system — A system variable that the administrator creates and names. For example, the administrator might create a SalesRegion non-system variable that initializes the name of a user's sales region.

The administrator creates non-system session variables using the Oracle BI Administration Tool.

Repository Variables

A repository variable is a variable that has a single value at any point in time.

There are two types of repository variables:

• Static — Repository variables whose value persist and do not change until the administrator decides to change them.
• Dynamic — Repository variables whose values are refreshed by data returned from queries.

The administrator creates repository variables using the Oracle BI Administration Tool.

Syntax for using Repository variable in Conditional Formatting:

We have a reporting requirement where the Client wants conditional Formatting to be maintained from DB / RPD. So we tried lot of ways using dynamic filters, BINS but nothing worked out. Then we made a way by creating CASE statements and then utilizing its value in Conditional Formatting.

1)    Repository Variables are defined using the Variable Manager within the Oracle BI Administrator (Manage > Variables).

2)    Create a Dynamic variable and a INIT block in RPD

Now we have 4 Dynamic variables seen above. 

3)    We planned to have conditional formatting  using 2 values as below in Answers Page, Column Threshold_value (column -> Edit Properties-> Formula) :

Case when Threshold_value < 143.1_low then 0 When  (Threshold_value >= 143.1_low and Threshold_value < 143.1_high) then 1 Else 2  END

4)    Then go to same column-> Column properties-> Conditional Formatting

Add condition on column “ Threshold_Value” as :

Threshold_value = 1 , and in formatting set color = Red

Threshold_value=2 , and set color = Yellow.