Tuesday, December 3, 2013


Not a Meta Tag, but required anyway

Custom function: YMD_BETWEEN

YMD_BETWEEN returns an 8-character string that represents the number of years, months, and days between two dates.  The output format is:


·         The 1st and 2nd characters (01) represent the number of years between the two dates.
·         The 4th and 5th characters (02) represent the number of months between the two dates.
·         The 7th and 8th characters (03) represent the number of additional days between the two dates, after allowing for the years and months.

YMD_BETWEEN takes two required arguments, and one optional argument that will be discussed later. 

·         Argument #1: Any date
·         Argument #2: Any date
·         Argument #3: Calculation type (default = 0)

The order of the dates in the arguments (earliest, latest) is irrelevant.  YMD_BETWEEN will always return a positive number of time periods, starting with the earlier date and ending with the later date, irrespective of the order in which the dates are provided in the arguments.  By default, the difference is computed as of the end of the day for both days. 

For example, the difference between 30Dec2008 and 31Dec2008 is only 1 day, because by default YMD_BETWEEN assumes that the time is approximately 23:59:59 on each date.

The code for the YMD_BETWEN function is included at the end of this blog post.

In its default mode, YMD_BETWEEN evaluates the dates using the following logic rules, in order:  Once a pair of dates meets a logical condition, the rule for that condition is applied, and no future logical conditions are tested.

Apply the rule for the first TRUE condition:

   1.        If both of the dates represent the ending date of their respective months, then zero days are reflected in the result.    Notice that the order of the date arguments doesn't matter.

Example: YMD_BETWEEN('29FEB2008','31AUG2009') = 01.06.00 (both dates = month end)
Example: YMD_BETWEEN('28FEB2009','30APR2008') = 00.10.00 (both dates = month end)

   2.        If the later date represents the ending date of its month, YMD_BETWEEN counts full months working backwards from that ending date, and the number of days returned is the number of days remaining in the month represented by the earlier date.

Example: YMD_BETWEEN('27FEB2008','31AUG2009') = 01.06.02

This represents the six full months of Mar 1 through Aug 31, plus the last 2 days of February 2008 (28th and 29th).   In the example, remember that 27FEB2008 is interpreted as representing the end of that date, so there are only 2 days remaining in the month of February 2008.

Example: YMD_BETWEEN('31AUG2009', '27MAR2008') = 01.05.04

This represents the five full months of Apr through Aug, plus the last 4 days of March (28th through 31st).

   3.        If the earlier date represents the ending date of its month, YMD_BETWEEN counts full months working forwards from that ending date, and the number of days returned is the day number of the later date.

Example: YMD_BETWEEN('31AUG2007', '24MAR2008') = 00.06.24

This represents the six full months of Sep through Feb, plus 24 days in March.

   4.        When both dates have the same day number (e.g. August 25th and December 25th), the number of days returned is 0.

Example: YMD_BETWEEN('25AUG2007', '25DEC2008') = 01.04.00

   5.        When the day number of the later date is greater than the day number of the earlier date, full months are counted starting with the day number of the earlier date, and the number of days is the result of subtracting the day number of the earlier day from the day number of the later date.

Example: YMD_BETWEEN('15AUG2007', '27DEC2008') = 01.04.12

In this example, the day number of the later date (27th) is greater than the day number of the earlier date (15th).  Therefore, Rule 5 counts Sep 15 as 1 month, Oct 15 as 2 months, Nov 15 as 3 months, and Dec 15 as 4 months, then counts 12 days (Dec 16th through 27th).

   6.        The only other possibility is that the day number of the earlier date is greater than the day number of the later date.  When that is the case, full months are counted starting with the day number of the earlier date, and the number of days is (A) the day number of the later date plus (B) the number of remaining days in the month immediately preceeding the month of the later date.

Example: YMD_BETWEEN('27AUG2007', '20DEC2008') = 01.03.23

Rule 6 counts Sep 27 as 1 month, Oct 27 as 2 months, and Nov 27 as 3 months.  There are 3 days left over at the end of November (28th through 30th), plus 20 days at the beginning of December for a total of 23 days.

Note: The decision to use this method for Rule 6 was a conscious one.  An alternative, which was explored, would be to calculate the months as the number of full calendar months in between the two dates, then calculate the days as the number of stub days from each of the two dates.  The results of this alternate method would be slightly different from the selected method under certain conditions.

Example: 04Jun2010 to 02Aug2010.

Method 1 (in use): Begin with 04Jun2010, and count forward to 04Jul2010 as 1 month.  There are 27 days remaining in July (5th through 31st) plus 2 days in August, for a total of 29 days.

Method 2 (considered): Count the intervening month of July 2010 as 1 full month.  There are 26 days remaining in June (5th through 30th) plus 2 days in August for a total of 28 days.

Method 1 was chosen for two reasons.  First, by counting months starting with the earlier date, it is consistent with Rule 5.  Second, Method 2 is inappropriate for Rule 5, because it could result in silly results such as zero months plus 54 days for Mar05 through Apr28.

Alternate 3rd argument:

Including the number 1 as the 3rd argument will calculate differences from the beginning of the earlier date to the end of the later date.  All rules are adjusted accordingly, as shown in the examples below.

Example: YMD_BETWEEN('29FEB2008','31AUG2009') = 01.06.00
Example: YMD_BETWEEN('01MAR2008','31AUG2009',1) = 01.06.00
Example: YMD_BETWEEN('29FEB2008','31AUG2009',1) = 01.06.01

Example: YMD_BETWEEN('01MAY2008','28FEB2009',1) = 00.10.00
Example: YMD_BETWEEN('30APR2008','28FEB2009',1) = 00.10.01

Example: YMD_BETWEEN('27FEB2008','31AUG2009',1) = 01.06.03
Example: YMD_BETWEEN('28FEB2008','31AUG2009',1) = 01.06.02

Example: YMD_BETWEEN('27MAR2008','31AUG2009',1) = 01.05.05
Example: YMD_BETWEEN('27MAR2008','01SEP2009',1) = 01.05.06

Custom Function: MOS_BETWEEN

The MOS_BETWEEN function is intended as an improvement to Oracle's MONTHS_BETWEEN function.  Building on the YMD_BETWEEN custom function described above, MOS_BETWEEN will calculate the number of months and portions of a month based on the value of YMD_BETWEEN.  Just like MONTHS_BETWEEN, MOS_BETWEEN will return a negative or positive number, based on the position of the earlier and later dates in the list of arguments.  However, MOS_BETWEEN corrects some shortcomings present in Oracle's MONTHS_BETWEEN function.

Consider these two calls to Oracle's own Months_Between function:

Select months_between('28FEB2009','31JAN2009') from dual;
Value returned: 1

This makes sense.  There is exactly one month between the end of January and the end of February.

Now let's ADD one day to the range.

Select months_between('28FEB2009','30JAN2009') from dual;

Instead of starting with January 31, we'll start with January 30.  Logically, the result should add one day, or 1/31st of a month, so we should see something like 1.03225.

Value actually returned: .9354

This, of course, makes no sense whatsoever, unless you are looking to win a bar bet by proving that 30 is greater than 31.

Using the Mos_Between function produces reasonable results:

Select mos_between('28FEB2009','31JAN2009') from dual;
Result: 1

Select mos_between('28FEB2009','30JAN2009') from dual;
Result: 1.032258065

Function Code

create or replace function YMD_BETWEEN
(date1_in IN date, date2_in IN date, type_in in number default 0 )
return varchar
IS cvalue varchar(20);
cursor tb is
select *
(select YY || '.' ||
       MM || '.' ||
       DD  as YMD
(select substr(to_char(100 + trunc(MOS/12,0)),2,2) as YY,
        substr(to_char(100 + mod(MOS,12)),2,2) as MM,
        substr(to_char(100 + DD),2,2) as DD
(select Two.*,
        End_Mo - Begin_Mo
      - case when to_char(Begin_Date+1,'DD') = '01'
              and to_char(End_Date+1,'DD') = '01' THEN 0
             when to_char(End_Date+1,'DD') = '01' THEN 0
             when to_char(Begin_Date+1,'DD') = '01' THEN 1
             when to_char(End_Date,'DD') = to_char(Begin_Date,'DD') then 0
             when to_char(End_Date,'DD') < to_char(Begin_Date,'DD') then 1
             else 0 end as MOS,
        case when to_char(Begin_Date+1,'DD') = '01'
              and to_char(End_Date+1,'DD') = '01' THEN 0
             when to_char(End_Date+1,'DD') = '01' THEN M1Days
             when to_char(Begin_Date+1,'DD') = '01'
             then to_number(to_char(End_Date,'DD'))
             when to_char(End_Date,'DD') = to_char(Begin_Date,'DD') then 0
             when to_char(End_Date,'DD') > to_char(Begin_Date,'DD')
             then to_char(End_Date,'DD') - to_char(Begin_Date,'DD')
             else to_char(add_months('01' || to_char(End_Date,'MONYYYY'),0)-1,'DD')
                - to_char(Begin_Date,'DD') + to_char(End_Date,'DD')
             end as DD
(select One.*,
       to_char(Begin_Date,'YYYY')*12 + to_char(Begin_Date,'MM') as Begin_Mo,
       to_char(End_Date,'YYYY')*12 + to_char(End_Date,'MM') as End_Mo,
       to_char(add_months('01' || to_char(Begin_Date,'MONYYYY'),1)-1,'DD')
     - to_char(Begin_Date,'DD') as M1Days
(select Begin_Date - case when type_in = 1 then 1 else 0 end as Begin_Date,
(select case when date1_in < date2_in then date1_in else date2_in end as Begin_Date,
        case when date1_in < date2_in then date2_in else date1_in end as End_Date
 from dual)) One) Two)));

open tb;
fetch tb into cvalue;
close tb;
return cvalue;


create or replace
function MOS_BETWEEN
(date1_in IN date, date2_in IN date, type_in in number default 0 )
return number
IS cvalue number;
cursor tb is
select (to_number(substr(YMD,1,2))*12
     + to_number(substr(YMD,4,2))
     + to_number(substr(YMD,7,2))/31)
     * case when date1_in < date2_in then -1 else 1 end AS MOS
(select YMD_BETWEEN(date1_in, date2_in, type_in) as YMD from dual);

open tb;
fetch tb into cvalue;
close tb;
return cvalue;


Monday, December 2, 2013

Excel - Extracting a Delimited String from a Text Value

Blog Index


I had a need to do something in Excel today that I had not done before.


I have this string:


All Products:Home:Appliances:Refrigerators:AJ275644. 


There are five "nodes" in that string, separated by colons.  I need to extract the third node (Appliances).


Using only the MID function is possible, of course, but that would require finding each successive occurrence of the colon.  If I needed the 17th colon instead of the 3rd, using MID by itself would result in a very cumbersome formula.


So let's put that string in cell A1 and see if we can extract all of the characters between the 2nd and 3rd colons.


Cell: A2

Formula: =SUBSTITUTE(SUBSTITUTE(A1,":","]",3),":","[",2)

Result: All:Products:Home[Appliances]Refrigerators:AJ275644

This formula has changed the 3RD occurrence of ':' to ']', and the 2nd occurrence of ':' to '['.


There is nothing special about using '[' and ']'.  I picked characters that I was pretty sure wouldn't be in the original string.  You can use any ASCII character, such as char(7) which is the Bell ding, or char(177) which is the plus/minus character.  For example, we could use SUBSTITUTE(A1,":",CHAR(177),2) to replace the second colon with the plus/minus sign.


Cell: A3

Formula: =MID(A2,FIND("[",A2)+1,FIND("]",A2)-FIND("[",A2)-1)

Result: Appliances

This extracts all characters between "[" and "]".


If desired, we can combine it together into one cell, replacing all references to cell A2 with the formula from cell A2:


Formula: =MID(SUBSTITUTE(SUBSTITUTE(A1,":","]",3),":","[",2),FIND("[",SUBSTITUTE(SUBSTITUTE(A1,":","]",3),":","[",2))+1,FIND("]",SUBSTITUTE(SUBSTITUTE(A1,":","]",3),":","[",2))-FIND("[",SUBSTITUTE(SUBSTITUTE(A1,":","]",3),":","[",2))-1)



Wednesday, November 20, 2013

Groups vs Calculated Items in OBIEE 11g

Blog Index

OBIEE 11g features a new ability to create custom groupings of attribute column members, and there are two separate mechanisms that may be used: Groups and Calculated Items.  What is the difference between them?

   1.        Create this new analysis from the Sample Sales subject area.  The filter returns all values of P1 Product that contain the letter 'p'.

   2.        Modify the Table view to add a row of grand totals.  Notice that the formula for 20 Actual Unit Price is correctly recalculated on the grand total row at 8.926 per unit of quanitity sold.

Now we'll create the first new object, a Custom Group called Group P.

   3.        On the Results tab, click the New Group icon on the toolbar.

   4.        Use the same logic as shown above to select the same 5 values of P1 Product and click OK.

   5.        Notice that a new row labelled Group P has been added to the analysis, and the values on that row are correct.

Now let's create a Calculated Item

   6.        Click the New Calculated Item icon on the toolbar.

   7.        Use the same logic to make the same 5 values of P1 Product available in the left window.

   8.        One at a time, highlight a value in the left window, and shuttle it to the right window using the icon.  After shuttling a value, follow it with a + sign (except for the last one) so that the final formula looks like this:

   9.        Click OK to close the Calculated Item dialog.

10.        The results now show a new row for Item P, but not all of its values are correct.

So there we see the difference between Groups and Calculated Items.  Custom Groups respect and reevaluate the formulas for calculated columns such as Actual Unit Price.  In other words, the aggregation methods for the columns are retained.  But Calculated Items perform the exact calculation entered on the New Calculation Item screen, no matter what fact column is involved.  In this case, if you add the 5 values of Actual Unit Price, the total is 45.952.

Which begs the next question: Under what circumstances would you ever want to use a Calculated Item instead of a Custom Group?  I'm not able to think of a reason.

Each of these new objects may be saved for future use with other analyses.

11.        On the Results tab, expand the Selection Steps panel at the bottom of the screen.

12.        Click on the Group P link then select Save Group As.

13.        Save the group as Group P in the proper subject area folder.  Group P is now available for reuse in the Selection Steps panel for any other analysis.

14.        The same process applies for Item P as well.

Wednesday, October 30, 2013

Creating Simple Dashboard Buttons

Blog Index


In versions of OBIEE prior to 11g, the Reset button associated with a dashboard prompt could only roll back to the most recently applied value, not the original default.  We used the method described below to add a true Reset button to the dashboard.  This button reset all prompts back to their default values. 


That same method could also be used to create a button to launch almost any website.  An example of that is shown below as well.


Dashboard Prompt Reset Button


   1.        Add a Text object to the dashboard page.


   2.        Add this code for the Text object.


<div class="XUIPromptEntry minibuttonOn"><a href="#" onclick="return PersonalizationEditor.removeDefaultSelection(false ) ">Reset Prompts</a></div>


   3.        Click the Contains HTML Markup checkbox.


   4.        Click OK to save the Text object.


   5.        Add a dashboard prompt to the page.


   6.        Save and run the dashboard page, make a prompt selection click Apply.


   7.        Click the Reset Prompts button to test the reset functionality.


Website Launch Button


We can use the same technique to navigate to a website (in this example, Oracle.com).


   1.        Add another Text object to the dashboard page.


   2.        Add and save this code (don't forget the HTML Markup checkbox).


<div class="XUIPromptEntry minibuttonOn"><a href="http://www.oracle.com/" target="_blank" >Oracle</a></div>


Note: The target="_blank" tag opens the content in a new browser window or tab.


   3.        Run the dashboard page and test the button.

Friday, October 25, 2013

OBIEEWanderings Blog Index

Copy Sections between Dashboard Pages

Blog Index

Have you ever wanted to copy or move a section from one dashboard page to another, or even move a section or column into a different dashboard? The information below will show you how.
   1.        Open Catalog Manager.
   2.        Attach in Offline Mode to the catalog containing the dashboard that contains the section or column that is to be moved or copied. 
   3.        Drill down to find the dashboard containing the source object.  Note that dashboards are stored under the _portal folder.  In this example, my dashboard is called Class Links, stored in the Training folder.
   4.        The section that I want to move is currently located on the OBI291 page of the Class Links dashboard.  Double-click the OBI291 page icon on the right side of the screen.
   5.        On the Properties dialog that appears, click the Edit XML button near the top right.
   6.        Now you need to find the XML for the section that you want to copy.  The XML tag that begins a section is <sawd:dashboardSection>.  Scroll through the XML until you find that tag at the beginning of the section you want to copy. 
(The similar tag for an entire column is <sawd:dashboardColumn>.)
   7.        Highlight everything starting with <sawd:dashboardSection> and ending with the subsequent </sawd:dashboardSection> ending tag.  (If you want to move two or more adjacent sections, just highlight more XML, but be sure to end with one of the </sawd:dashboardSection> tags.
Helpful hint: I find it easier to just copy/paste the entire XML into Notepad then highlight what I want.
   8.        Copy the highlighted XML to the Windows clipboard (right-click + copy, or ctrl-c).
   9.        Click Cancel twice to close the dialog boxes.
10.        Double-click the receiving dashboard page.  It may be a page in any dashboard.
11.        Click the Edit XML button.
12.        Click the Edit button below the XML.  This will allow you to edit the XML for the dashboard page.
13.        Find an existing occurrence of a section ending tag (or, if you are copying a column, find a column ending tag).  A section ending tag is </sawd:dashboardSection>.
14.        Immediately following the existing section ending tag, hit the Enter key to insert a blank row.
15.        On the blank row, right-click + Paste to paste the XML that you copied to the clipboard earlier.
16.        One last thing: Look at the top of the XML you just pasted.  It will start with something like this:
<sawd:dashboardSection name="Section 9"
If two sections on the same page have the same name, both sections will appear in the dashboard, but they will share the same set of editing icons in the dashboard editor, which is probably not a good thing.  Change this name= parameter to some other number that isn't used on this dashboard page.
17.        Click OK twice to close the dialogs.  The process is complete.
18.        Access your modifed dashboard page to see the copied section.