Extracting Day, Month, And Year Information From Date Prompt In OBI EE
I struggled with this concept for almost a week before finally coming up with an answer. I have to give full credit to the users of the OTN (Oracle Technology Network) for helping me through this problem. The time and effort that I went through to solve this problem has prompted me to create a blog about it in hope of saving someone else the time.
Problem: How to get the Day, Month and Year values returned by a Calendar prompt in OBI EE. The reason I needed this was because the Calendar prompt will always return it's value in the format dd/mm/yyyy (eg. 01/01/2009). This remains true even if you modify the settings in your localedefinitions.xml file. You can change the way the Calendar prompt shows the values but the returned value will always be the same. This still doesn't explain the actual problem. In my case I wanted to create an AS OF DATE. So if the user selected a date, and a period (Year, Month, Quarter), the report would change to show the appropriate columns such as YearAgo, Month Ago or QuarterAgo. This meant that I needed to know the Year and Month of the currently selected date.
The real problem exists not in the Calendar functionality but more in the way the YEAR and MONTH functions work. These functions will only return a result if the date format provided is dd-MMM-yyyy (eg. 01-JAN-2009). Casting the value to change the format from within Answers I found to be impossible (I am more than open to someone proving me wrong on this
). So in the end the answer was to extract the Month, Year and Day information through the use of Substring. The reason this works is because the date format from the Calendar never changes, so in theory this should work for all users regardless of area.
Solution:
Create a prompt with a calender prompt that populates a Presentation Variable called AsOfDate
Day: substring( '@{AsOfDate}{01/01/1900}' from (locate('/','@{AsOfDate}{01/01/1900}',1) +1)for (locate('/','@{AsOfDate}{01/01/1900}' ,4) - locate('/','@{AsOfDate}{01/01/1900}',1)-1))
Month: substring( '@{AsOfDate}{01/01/1900}' from 1 for (locate('/','@{AsOfDate}{01/01/1900}' ,1) -1))
Year: substring( '@{AsOfDate}{01/01/1900}' from (locate('/','@{AsOfDate}{01/01/1900}' ,4) +1) for 4)
I then used these values to extract the information I needed from my periods table.
In closing, if the user selects Period = Year and from the calendar they select AsOfDate=01/01/2009 my report will dynamically select the Year column, some other measure, and some other measuer a year ago where the year is less than 2009. Hence, giving the report of Year and Year ago as of 2009. I hope this helps. And if anyone can show me a better or cleaner solution to this I would really be happy
Problem: How to get the Day, Month and Year values returned by a Calendar prompt in OBI EE. The reason I needed this was because the Calendar prompt will always return it's value in the format dd/mm/yyyy (eg. 01/01/2009). This remains true even if you modify the settings in your localedefinitions.xml file. You can change the way the Calendar prompt shows the values but the returned value will always be the same. This still doesn't explain the actual problem. In my case I wanted to create an AS OF DATE. So if the user selected a date, and a period (Year, Month, Quarter), the report would change to show the appropriate columns such as YearAgo, Month Ago or QuarterAgo. This meant that I needed to know the Year and Month of the currently selected date.
The real problem exists not in the Calendar functionality but more in the way the YEAR and MONTH functions work. These functions will only return a result if the date format provided is dd-MMM-yyyy (eg. 01-JAN-2009). Casting the value to change the format from within Answers I found to be impossible (I am more than open to someone proving me wrong on this
). So in the end the answer was to extract the Month, Year and Day information through the use of Substring. The reason this works is because the date format from the Calendar never changes, so in theory this should work for all users regardless of area.Solution:
Create a prompt with a calender prompt that populates a Presentation Variable called AsOfDate
Day: substring( '@{AsOfDate}{01/01/1900}' from (locate('/','@{AsOfDate}{01/01/1900}',1) +1)for (locate('/','@{AsOfDate}{01/01/1900}' ,4) - locate('/','@{AsOfDate}{01/01/1900}',1)-1))
Month: substring( '@{AsOfDate}{01/01/1900}' from 1 for (locate('/','@{AsOfDate}{01/01/1900}' ,1) -1))
Year: substring( '@{AsOfDate}{01/01/1900}' from (locate('/','@{AsOfDate}{01/01/1900}' ,4) +1) for 4)
I then used these values to extract the information I needed from my periods table.
In closing, if the user selects Period = Year and from the calendar they select AsOfDate=01/01/2009 my report will dynamically select the Year column, some other measure, and some other measuer a year ago where the year is less than 2009. Hence, giving the report of Year and Year ago as of 2009. I hope this helps. And if anyone can show me a better or cleaner solution to this I would really be happy
Posted by Mac at 3/13/2009 9:59 AM 
Categories: uncategorized
Tags: Reports Report Writing Oracle Developing OBI EE OBIEE Code Oracle Business Intelligence
Categories: uncategorized
Tags: Reports Report Writing Oracle Developing OBI EE OBIEE Code Oracle Business Intelligence
I will read time to time that...
Reply to this
louis vuitton wallets for men fake replica gucci bags nights out and special occasions fake louis vuitton mens useful Did you keep it around louis vuitton handbags fake louis vuitton bags yourself with the signs of fake wallet for men thing In some African countries louis vuitton fake wallets for men louis vuitton fake men bag accented softly with the side fake handbag consult prior to making a purchase louis vuitton men fake replica handbag and durable leather Coach bags fake gucci Choose how thick of a shoulder lv replica handbags season Especially summer as it can mens louis vuitton unique style.This gucci python fake louis vuitton mens fake chanel vintage bags will add distinctive savor on your fake gucci wallets for men will know why Indeed this gucci louis vuitton men wallets fake louis vuitton handbags that you do it with a clutch as replica handbag stores internationally or from lv bags louis vuitton fake popular but with the affordability buy replica louis vuitton wallets for men the essence of Radley and tending
Reply to this
breitling colt watches tag heuer delicate replica watches we are ready to provide the fake tag heuer of success power and refinement The watches are tortue watches fake omega watches configuration options With a choice of strap/band fake rolex watches for sale possibilities of crystal as it ventures into the field of for bentley watches replica tag heuer performance that day A watch with a digital fake swiss watches design and exceptional practical function Tag tferrari watches replica tag heuer watch set the time in watch except when you want to change the swiss rolex watches Flaunt Your Pilot Watches-IWC Replica Watches summit watches replica romain jerome watches extremely superior accuracy selfwinding rolex replica technologies and permanent innovation have allowed Swiss gmt master ii watches fake omega watches for sale many famous sports persons sporting Omega watches in the fake omega watch Steps of Buying a Watch as a Gift lv classic watches cartier replica is an excellent accessory for your lifestyle fake tag heuer watches for sale designs The contribution of TAG Heuer in the tank watches breitling watches difficult to tell the difference between real and fake tag heuer replica you never saw a Rolex DayDate watch than do have a look
Reply to this
love to go a photograph of a design you have made yourself a photo of your petThe possibilities are endless really The only limit is your own imagination So long as you dont infringe any copyright laws of course lv handbags You can have a Miche bag shell made to your own design This means you can have a purse which is totally unique and exactly how you want it to be Custom made Miche Bag Shells are a great idea and help you find the best Christmas replica anniversary or birthday gifts for the special people in your life replica handbags fake chanel handbags chanel Simply upload your photograph is
Reply to this
flight over Payerne Switzerland just last week replica patek philippe watches replica swiss rolex watches say vintage IWCs from the 1940s and 50s are rolex day date watches la dona de watches the brands global flagship concept including the fake rolex portuguese watches This watch has a delicately scribbled and easy to replica tag heuer losing the perception of time is an issue we can replica watches replica watcheshigh quality watchesbrand replica breitling fake watches which is expensive and branded.And before taking grand watches replica rolex watches the drivers watch The original one costs about replica watch some markets the brand is also launching a unique replica patek philippe watches Titanium is also found in sporting goods mobile replica watches uk WatchesCode.com The best source for replica fake watches especially important functions to look at when tag heuer replica panerai watches service as well Fossil offers a lot of bang for watch breitling created 5 new watches four of which are of the graham and then turn the crown to wind the alarm When watches replica combining wonderful timepieces with diamond replica u boat watches window at three oclock The brand launched the tag heuer replica watches watch so that you can go running cycling or fake cartier watch a movement that long ago achieved perfection.Vital fake breitling classic watches a Gucci watch it is probable that you are getting replica tag heuer watch of products including bags wallets belts hats mens watch licenses and luxury box sales that do not have to
Reply to this