Category Archives: OBIEE Reports

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 !!

 

Advertisements

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

How to calculate Week Start Date and Week End date using DAY_DT in Obiee 11g

In OBIEE 11g, many times we have to calculate on the fly, the various dates functions like Week_Start_date, Week_End_Date, Previous_Yr_Date etc,  just using the Date column. Few projects have Date Dimensions not properly structured to have all columns. So here we have the logical sql’s to calculate the same:
We can calculate the week start date using the below sql:
TIMESTAMPADD(SQL_TSI_DAY,- DAYOFWEEK(MIN(“Dim – Date”.” Date Column”)) + 1, MIN(“Dim – Date”.” Date Column”))
For Week End Date, use the below Sql:
TIMESTAMPADD(SQL_TSI_DAY, 7-DAYOFWEEK(MAX(“Dim – Date”.” Date Column”)), MAX(“Dim – Date”.”Date Column”))
DAYOFWEEK returns an integer in the range of 1 to 7. A value of 1 represents the Monday.
Courtesy: Google 
🙂

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 🙂


 

New Features in OBIEE 11.1.1.9.0 release:


 Some more new features have been introduced in latest OBIEE 11.1.1.9.0 release:-

Analysis Enhancement:

1.     You can initiate a search within the subject area displayed in the subject area pane; the option is “Subject Areas Pane”.
2.     You can save columns to Presentation Catalog and reuse the same for other analysis.You can also change the properties of the new column in the Criteria tab but be aware that conditional formatting and conditional action links for the column are not saved to the catalog. Regular action links are saved for the column as well as the format properties.The saved column can be edited from the catalog pane and catalog page. All the changes that you make in the catalog are applied to every analysis that used this column. If you modify it inside a particular analysis, these changes are valid only for the analysis that you are working on.To be able to save new measures as columns you should have the Save Column privilege.
  
 
3. In this version we find a new new type of variables: the Global Variables. Global Variables can be defined in the context of an analysis and can be used in other analysis. Useful to do some intermediate reusable calculations.
4. Radio button and check box dashboards prompts can be showed horizontally. When you create a new dashboard prompt using check box or radio button under Options, you can select between horizontal or Vertical Layout appears under Options.

Export & Print options:

1.     In this version, you will find more options in the Print and Export Options dialog. You can select if you want to include charts, images and formatting or to specify column properties like the column width and to wrap the text in columns.

2.     Export menu no longer includes the Excel 2003 and PowerPoint 2003 options
3.     It is possible to set a limit to the number of rows downloading as CSV format

New RPD checkout option:

1.     If you need to check out an entire repository rather than using projects for multiuser development, you can use the Whole Rpd Checkout option


New Option in Administration Tool:

1.     Display Translation Key in presentation tree and Edit Presentation names options are now available in Administration Tool option for this release. Translation keys have been added to all presentation objects. The translation key is automatically populated and by default it matches the presentation object’s name; however, you can modify the translation key. When you externalize display names in the Presentation layer and run the Externalize String utility, the results contain the translation key. You can right-click any Presentation layer object, such as a subject area, presentation table, or presentation column, and choose Externalize Display Names > Generate Custom Names or Externalize Descriptions > Generate Custom Descriptions to externalize strings.

Report/Dashboard Changes:

1.     Once you are in editing dashboard, you might see the Advanced Page properties option that set the scope of parameters in cases of Go URL, Prompted URL and Navigate to BI content:

 2. In Tables, Pivot and similar you can see Column properties from the edit window as well.

    
 

Courtesy :  Google 🙂