OBIEE Aggregate BY part 2

About two year ago I wrote an article on using the BY statement to “pin” your calculation on certain level. (http://obiee101.blogspot.com/2008/02/obiee-aggregate-by.html). Recently Kurt Wolf of KPI partners did a good analysis on how to “pin” the calculations on for the complete request. (http://kpipartners.blogspot.com/2009/12/aggregate-function.html) Here is a simple implementation of his findings.

Let’s start with a simple report, YEAR and AVG PRICE ("F1 Revenue"."1-01  Revenue  (Sum All)"/"F2 Units"."2-01  Billed Qty  (Sum All)"):

If we drill down the time dimension, we will see the AVG Price change accordingly:

We can “pin” an extra avereg column to the calendar year by using the AGGREGATE BY function (AGGREGATE(("F1 Revenue"."1-01  Revenue  (Sum All)"/"F2 Units"."2-01  Billed Qty  (Sum All)") by "D0 Time"."T05 Per Name Year")) (IT’S NOT in the formula editor, you will have to type it!)

 

But what if we want it for the whole report? Simple leave the BY part empty: (AGGREGATE(("F1 Revenue"."1-01  Revenue  (Sum All)"/"F2 Units"."2-01  Billed Qty  (Sum All)") by )

Till Next Time

OBIEE Navigating from report to report

On the forums every so often you see the question: “I want to navigate from report A to report B and pass the value where the user clicks as filter” . This is actually quite simple in OBIEE. Let’s start with a simple  base report showing a list of available markets:

Next create a target report containing markets and revenue:

Next go back to your base report and open the column properties screen for your “Market” column:

Set the navigation target:

If you now test the report you will see that the clicked value isn’t passed as filter

This is because the report isn’t let “filter” aware. Go to your target report and set a filter on the “Market” column of the type is prompted.

If you now test it again, you will see the filter value is passed:

Till Next Time

OBIEE PATCHES 10.1.3.4.1 part 2

Some interesting new patches have been released:

Patch ID	Description	Updated	Size
9149026	Oracle BI Suite EE: Patch: NQSSERVER CRASHES RUNNING QUERIES AFTER UPGRADE TO 10.1.3.4.1 FROM 10.1.3.4.0	24-dec-09	28.1 MB
8342897	Oracle BI Suite EE: Patch: CANCEL A QUERY AND RE-RUN : ESSBASE RETURNS ALREADY CONNECTED TO SERVER""	24-dec-09	28.1 MB
8599681	Oracle BI Suite EE: Patch: DATE FORMAT ISSUE ON THE DASHBOARD PROMPT	24-dec-09	12.9 MB
9179171	Oracle BI Suite EE: Patch: MERGE REQUEST ON TOP OF 10.1.3.4.1 FOR BUGS 8444119 8561377 8664686 8561472	11-dec-09	2.9 MB
9024802	Oracle BI Suite EE: Patch: SUBOPTIMAL QUERY GENERATED IN SOME HIERARCHY WITH TERADATA BACKEND	7-dec-09	28.1 MB
9143304	Oracle BI Suite EE: Patch: MERGE REQUEST ON TOP OF 10.1.3.4.1 FOR BUGS 9081493 8599681	1-dec-09	13.0 MB
9139499	Oracle BI Suite EE: Patch: MERGE REQUEST ON TOP OF 10.1.3.4.1 FOR BUGS 8599681 8921914 9073754	21-nov-09	13.3 MB
7195230	Oracle BI Suite EE: Patch: GOVERNANCE RULES THROUGH SOAP : RETAIN RULES FOR DIFFERENT CATEGORY	20-nov-09	273.3 KB
8885426	Oracle BI Suite EE: Patch: UPGRADING TO 10.1.3.4.1 RESULTS IN INCORRECT TRANSALTION OF NO RESULT TO &#39	20-nov-09	390.2 KB
8978017	Oracle BI Suite EE: Patch: ADD OR REMOVE PROGRAMS" SHOWS WRONG VERSION FOR BI OFFICE ADD-IN"	19-nov-09	34.3 MB
9081493	Oracle BI Suite EE: Patch: MERGE REQUEST ON TOP OF 10.1.3.4.1 FOR BUGS 8439796 8468309	6-nov-09	1.7 MB
7438317	Oracle BI Suite EE: Patch: BI SERVER TAKES OVER 30 MINUTES TO STARTUP	6-nov-09	519.9 KB
8803399	Oracle BI Suite EE: Patch: ASSERTION_FAILURE ERROR WHEN DASHBOARD NAME CONTAINS KOREAN CHARS	6-nov-09	1.6 MB
8927890	Oracle BI Suite EE: Patch: UPDATE FOR OBIEE 10.1.3.4.1	20-okt-09	191.2 KB
8669206	Oracle BI Suite EE: Patch: FIREFOX 3.0 REFRESH FAILURE CAUSES UNEXPECTED BEHAVIOR IN DRILLING/VIEW SELECTOR	12-okt-09	110.8 MB
8760212	Oracle BI Suite EE: Patch: COMMANDS FOR FULL AND INCREMENTAL SHOULD ALLOW DB SPECIFIC TEXTS	12-okt-09	6.8 MB
8743856	Oracle BI Suite EE: Patch: EXECUTION PLAN DOES NOT UPDATE LAST TASK AS COMPLETED	12-okt-09	6.8 MB
8990093	Oracle BI Suite EE: Patch: MLR BACKPORT FOR BASE BUGS 8797200 8909410 8603005	6-okt-09	13.1 MB
8565823	Oracle BI Suite EE: Patch: MODIFYING VIEWS.CSS .PTSECTSTABLE PADDING 0PX DOES NOT HAVE ANY IMPACT	25-sep-09	179.9 KB
8797200	Oracle BI Suite EE: Patch: METADATA CACHE IS NOT RELEASED WHEN THE GROUP CHANGES FOR THE USER	18-sep-09	13.1 MB
8633968	Oracle BI Suite EE: Patch: MLR BACKPORT FOR BASE BUGS 8331209 8371708 8372436	17-sep-09	906.9 KB
8284585	Oracle BI Suite EE: Patch: NAVIGATION/DRILL DOES NOT WORK WHEN THE COLUMN BEING DRILLED IS IN POSITION 11+	8-sep-09	9.2 KB
8796912	Oracle BI Suite EE: Patch: DISCONNECTED DOESN'T WORK ON VISTA AS NON-ADMIN USER	3-sep-09	128.7 KB
8685156	Oracle BI Suite EE: Patch: BLR BACKPORT OF BUG 8394579 ON TOP OF 10.1.3.4.1 (BLR #147542)	28-jul-09	1.3 MB
8650261	Oracle BI Suite EE: Patch: BLR BACKPORT OF BUG 8595693 ON TOP OF 10.1.3.4.1 (BLR #143831)	24-jul-09	313.3 KB
8685120	Oracle BI Suite EE: Patch: MLR BACKPORT FOR BASE BUGS 8680924 8674235 8608837 8567128	21-jul-09	624.6 KB
6702999	Oracle BI Suite EE: Patch: REPORT AGGREGATE - RANK HAS DIFFERENT BEHAVIOR VS RANK WITH AGGREGATE	19-jun-09	147.7 KB
8616993	Oracle BI Suite EE: Patch: BI OFFICE PATCH	19-jun-09	54.0 MB
8611209	Oracle BI Suite EE: Patch: MLR BACKPORT FOR BASE BUGS 8332167, 8290868, 8350962	18-jun-09	716.0 KB
8238481	Oracle BI Suite EE: Patch: NQSERROR14026 OCCURED IRREGULARLY	9-jun-09	10.3 KB
8439796	Oracle BI Suite EE: Patch: PRIVILEGE ERROR DISPLAYED ON SELECTION OF DELIVERS RECIPIENTS	4-jun-09	1.6 MB

Yes you need a metalink/support account to download them. No, I will not download them for you and redistribute them. Ask your local Oracle representative for support.

Till next time

OBIEE a new year

The last months of 2009 have been very very hectic. At lot of customers wanted to finish there projects, not leaving me a lot of blog time.

Still we had 343.706 page views on OBIEE101, thanks guys!

This year (2010) we will hopefully see the all new OBIEE 11g. Rumors say at the beginning of Q3. The BI market is still recovering from the “crash” resulting in many short term projects which are mainly maintenance or extensions to existing systems.

Hope you all have a good year and see you around at the forums, the blogs or the conventions.

Till Next Time

John

OBIEE Top N Months across

I was asked how to get the Top 10 customers for the year 2007 and put there revenue per month. First make the basic report and filter, put the revenue column in twice:

Open the second revenue column and change the formula to:

SUM("F1 Revenue"."1-01  Revenue  (Sum All)" BY "D1 Customer"."C1  Cust Name")

Create a Top N filter for the second revenue column:

Switch to PIVOT table:

Place the columns in the right places, set the aggregation:

Till Next Time.

OBIEE Making an OR filter

Question I got from the blog, how to make an or filter in OBIEE.

Say you want the revenue for 2008 or the 2007 Q1. First make your report including the filters:

Next click “AND” to change into OR . This way you can make complex filters:

Till Next Time