Category Archives: OBIEE

How to Google any column value from OBIEE

Now a days end users demand a lot of GUI interactivity in Obiee reports. They are really happy if obiee reports are full of helpful functionalities. One of them is to Google out the value they click on. So this was actually my business requirement few days back. Its not much tough to implement and requires no config or RPD changes.

Steps to do it :

  1. Create a new Action. Go to New -> Action -> Navigate to a Web PAge
  2. Type http://www.google.com/search?q=:   in url section on window
  3. then click on Define Parameters in right side of window.Remove the “optional” mark for the parameter like below:
  4. img1

    Save this action with any name you remember.

  5. Now go to your Analysis, where you wanna call this action through any column Interaction.
  6. On column properties -> Interaction -> Primary Interaction, select Action Links.
  7. Now click on + sign to add Action link
  8. Now in new pop up window, clink on Select existing action
  9. Identify your Saved action and click ok.
  10. Now edit this Action link and select the “column value” as the parameter value, as shown below
  11. then Select the relevant column and tick the boxes for “hidden” (and “fixed”) options. click ok to save.
  12. click ok and check your results. When you click on any mapped column value, Google window will open up searching that value.

 

Follow For more !!

 

How to get YYYYMM format from calendar date without using CAST as Char

So today i am here to discuss the scenarios of CAST function in OBIEE11g or 12c both. We always see that we always run behind using CAST function for conversion to Char/Int/Date etc. But issue appears when we have to get a diff format in Date functions.

For example, i had a scenario where user wanted to see report in pivot form, with Prompt  Date range as 2017 / 01  to 2017 / 04.

I tried getting above values in Prompt by using Convert Calendar date to Char using Cast function. But when i have to pass this prompt value to my report, it was not working as expected. If  i select Date range from 2017 / 01  to 2017 / 04, it use to show only data in report for 2017 / 01 , 2017 / 02, 2017 / 03.  It use to omit 2017 / 04.

To resolve this issue, we used a simple logic in Report Prompt, as below. This gives us the output in little different format , but still no CAST is used,so no conversion to Char and then having issues passing value.

Using below idea, we are now able to get output in proper format and it passes correctly to report and filters it.

imgg

Output shows below:

Follow for more 🙂

How to navigate to external links from obiee reports

Sometimes we get different business requirements, which are meaning-full but little trick to implement in OBIEE. Similarly , last week i got a requirement for business to add a hyperlink on a report column, which when clicked goes to an external link. also, along with it i have to pass the CodePin and Number.

I tired many ways of passing parameters in Go Nav, GO url format. But it didnt work. Finally <a Href> worked, so sharing my solution with you.

Step 1: Edit column formula and place your code in below format. Concat all the parameters to be passed. Set Target and give it a name.

Step 2: Modify the same column and set its data format as below.

Let me know if you face any issues.

Follow for more !!

 

How to Remove Null Value from Dashboard prompts in OBIEE

Currently i am facing a weird issue in Dashboard prompts of OBIEE 12c.There are invalid Null values that are not present in database but showing in prompt columns in OBIEE. For some of the columns Null Values is valid but for other it is not. So Business wants to display null value in prompt only if it is valid and appears in DB column as well.

There are two ways to fix this issue,

a) edit this prompt and check the ‘Require user input’ property (doesnt work max times)

b) At RPD level , uncheck the Nullable option under the physical column as shown below:

obiee snippet

Then restart / recycle all the servers. Untill then this change wont get impacted. Do it for only necessary columns which will never have NULL value in DB also.

Follow for more on my FB page

or my OBIEE group

Different Tool tip for different column values in OBIEE11g/12c

In OBIEE 11g or 12c, we can add ToolTip to the report on columns. Last week i had a requirement to display different tooltips/text on every column value to users when user hovers on different row in that column of a report.
This is not an OBIEE feature. To get a solution to it, we can try using Javascript in Narrative view. But i tried a different solution.

Req: We have to display different text over different column values. For example below, we have to show Response values in ToolTip, when users hovers over the Product column. Responses can differ.

Step 1: So we created a Dummy KPI in RPD, called Responses and stored the different values (Good/Fair etc ) in it.
Step2: Now in Obiee Report, over the Product column made few changes:
     a) In the ‘Column Properties’ , under ‘Data Format’ tab, I am choosing HTML
     b) In column edit formula wrote below:

‘ || “Dim”.”Product” ||’

Note:
i) You can skip using HTML.      
ii) You can write a Case formula in place of Product column, ie column on which you want the hover text.
iii) For Numeric column value, use Cast function to convert it to Char().

Courtesy: Google

Steps to configure Write Back Functionality in OBIEE 11g

Write Back is the feature/ability in OBIEE to allow the user to enter a value or values directly into a report and have those values written to an actual physical table in the database.You can use this functionality only in a table view. If you want to use it on a pivot, you must first transform your table view as a pivot view

RPD changes to set Write Back :

Step 1:Physical Layer – Select a table to which you would like to Writeback. Double click it to open the Physical Table Properties.In General tab, uncheck Cache-able

Step 2: Business Model & Mapping Layer -Go to the table column (same as the physical layer.Double click column to open the logical column properties. In General tab, check Writable option.

Step 3: Presentation Layer –Double click the column and click on Permissions. Now in the General Tab -> Permissions -> Set permission -> Read / Write (Radio Button), on the User / Application Role which you want to authenticate for Writeback feature.

Step 4: User/Application Role – In RPD, Go to -> Manage-> Identity Manager -> Identity Management (Left pane) -> Select Application Role (to which you need Write Back Permission) -> Permission -> Query Limits (tab) -> Select Database (to which you need direct database execution rights) -> Change option from ignore to allow to the field named “Execute Direct Database Requests”.

Write Back Template :

Step 1: Check the “LightWriteback” tag within  the server instance tag of instanceconfig.xml file. If it not true, set it to TRUE. If this entry already exists then no modifications required. If modified then restart OracleBI Presentation Services for this change to be effective.

TRUE

Step 2: Writeback Template. You may give any name for the Writeback.xml file. Here for example,we are using the file name as Writeback_sample.xml

Things to do before you start the Template:

  • We can use the column position or by column id in the XML definition. 
  • We must include both insert and update statements in the template.

Template to be placed in the path: $ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages

 
 
 
[ Note -Above insert & update code is only for sample purpose & content within are comments. If we do not want to include SQL commands within the elements, then we must insert a blank space between the opening and closing tags. eg. rather than ]

In the above xml code, we have few important tags –

  • WebMessage name – The name here will be used as Writeback template name in analysis -> Table Properties -> Write Back.
  • Write Back ConnectionPool – Connection pool name for Write Back.
  • insert Based on your requirement or leave blank ( if not required)
  • update Based on your requirement or leave blank ( if not required)

Now lets proceed to know what changes we need to make in analysis for the write back report.

C- Changes in Analytics :

Step 1Give Privilege to the Role for Write Back ( Analytics Home Page ->Administration -> Manage Privilege -> Write Back & add the role for Writeback privilege). 
Step 2Now you need to go to that column, right click, open Column Properties -> Writeback tab-> Check ‘Enable Write Back’ of the field needed for write back, from criteria tab.
Step 3: Now edit & go to Table Properties view -> writeback tab & tick the ‘Enable Write Back’ & mention Template Name as mentioned in Writeback file ( WebMessage name=WriteBack_Template_Example” ). You may also rename Apply/Revert/Done Buttons & set positions.

Now please test the report for writeback features.

Courtesy : Google 🙂