Can I create a new Group by column in OBIEE analytic Report?

Question

Here I have a report:

ProductID Price
LCD1 12
LCD2 11
LCD3 10
TV 100
GAME 50

I hope to add a new column on this report in OBIEE and the result should be like below:

ProductID Price New_ProductID_Grouped
LCD1 12 LCD
LCD2 11 LCD
LCD3 10 LCD
TV 100 TV
GAME 50 GAME

I hope to show LCD* products as LCD in the New_ProductID_Grouped field.

Is it possible in OBIEE?

I hope to create the column and then do hierarchy later.

Thanks in advance.

Answer 1

Do you want to add this column only for that analysis? It is usually better to define that column in the RPD, this will make it available to all reports and analysis.

If you only want that column for this particular analysis there are at least 2 approaches:

A) If you can determine the value by using a query (i.e. you can programmatically find out if the product is LCD, TV or Game):

A1 - Drag the ProductId column again to the criteria tab and change the name to "Product Type", you will now have 3 columns: "ProductId", "Price" and "Product Type"
A2 - Edit the formula of the new column using the LSQL that will calculate the product type based on the productID for example:

 CASE ( SUBSTRING("Products"."ProductID" FROM 0 FOR 3) )  
  WHEN 'LCD' THEN 'LCD Group'       
  WHEN 'TV' THEN 'TV Group' 
  ELSE 'Game Group' 
 END

A3 - Add the new column to the Table / PivotTable, you might need to hide the productID column if you want to see data aggregated at the type level.

B) If you have to manually choose the type (i.e. You have to look at the productID to determine if the product is LCD, TV or Game):

B1 - Click the "New group" button in the results tab
B2 - Use "LCD Group" as Display Label and select ProductID from the "Values From" list
B3 - Manually select the products that should be labeled as LCD
B4 - Repeat B1-B3 for "TV" and "GAME"
B5 - Preview the report; notice that you can drill down to ProductID by clicking the group.

Hope this helps