RSS

Last N months(Rolling months) data By year-month prompt in OBIEE

19 Sep

Normally we will have the scenarios like to get data for last N no.of months from the selected date. Rather than this normal scenario if the user wants the same report but with the year and month prompts only instead of the calendar date.
We developed this approach in the following procedure (I myself worked on sample sales rpd, which comes with OBIEE 10.1.3.4.0 by default).

 

1. Create a dashboard prompt with a). Year, b). Month and c). no. of rolling months.

2. Assign presentation variables var_year, var_month, and var_rollno to year with drop down list control, month with drop down list control and no.of rolling months with edit box control respectively.

  

3. Now create a request with 4 columns respectively “D0 Time. T05 Per Name Year”, “D0 Time.T32 Cal Month”, “D0 Time. T00 Calendar Date and again another “D0 Time. T00 Calendar Date”.

4. Rename the second “D0 Time. T00 Calendar Date Column As “Last n th Month Date

5. Write the following formula for “D0 Time. T00 Calendar Date Column

 

MAX(“D0 Time”.”T00 Calendar Date”)

 

6. And Write the following formula for “Last n th Month Date” Column to get last nth month date for respective Calendar Date

 

MIN(TIMESTAMPADD(SQL_TSI_MONTH,-CAST(@{var_rollno} AS INT),”D0 Time”.”T00 Calendar Date”))

 

7. Create a filter on “D0 Time. T05 Per Name Year by assigning this to presentation variable “var_year” and “D0 Time.T32 Cal Month to “var_month” presentation variable.

8. Save this report with “Year Month Supporter”.

 

9. Now create the main report in which we are showing the Monthly wise Quantities. So take the columns “D0 Time. T05 Per Name Month”, “F2 Units”.”2-01  Billed Qty  (Sum All) Columns and save the report

  

10. And create the filters based on  the  Last n th Month Date” and “T00 Calendar Date” Columns from “Year Month Supporter” Report

    

11. Now create A dashboard page and add the Dashboard prompt and the main report

12. Select  the Year ,Month from the drop down list and enter no. of Rolling months in edit box

 

13. You will get the last n months data for your given input values

14. For eg:I have taken year=2008,month=9 and  no. of rolling months=11 so the report is

Advertisements
 
25 Comments

Posted by on September 19, 2008 in OBI EE

 

25 responses to “Last N months(Rolling months) data By year-month prompt in OBIEE

  1. Sridhar Kasthuri

    September 22, 2008 at 3:33 am

    Hey Siva ,

    Awesome i was facing with similar suitation and your example helped me alot .One more thing can you explain about Add to briefing book and Disconnected analytics as i couldn’t find more information on that other than Admin help

    Keep Rocking
    Sridhar Kasthuri

     
  2. Gaurav Jain

    December 17, 2008 at 8:03 am

    How can I show Opening And Ending Balance Transaction and Current transaction month ? Let say e.g if in Empoyee Sala table emp earn sal till dec. But want to generate report for Sal earn till Nov. Month and also currect transaction of dec month.

     
  3. Shiva Molabanti

    December 22, 2008 at 12:46 pm

    Gaurav Jain,

    you can do this in two ways..

    1.You can use ToDate(<>, <>) functionality
    2.you can have month column in criteria and set the upper filter for your query like “date<=Current_date” Then you can get data till date..

    -Shiva

     
    • sara

      March 11, 2012 at 8:07 am

      Hi shiva ,

      i would like to know how i can add custom value in a column on obiee,

      for example i have a colum and i have added one data in that using the single quotes in obie edit colum formula page can i add more than one data in that same column one after the other .please let me know thankyou
      sara

       
      • Suresh

        September 15, 2012 at 6:04 am

        Hi Sara,

        I have also same doubt if u know the solution let us explain me with the following steps. Appreciate with early quick responce.

        Best Regards
        Suresh

         
  4. sue

    May 21, 2009 at 11:29 am

    Thank you very much for this detailed, precise description of how to solve this problem! It helped me solve this exact issue that I was dealing with!

     
  5. Deep

    September 11, 2009 at 2:14 pm

    Hello,

    I have an aggregate table with year, month, no of orders. I need to calculate ytd. Can you please help me how to do it without using to_date() function. to_date() is not working as I do not have a physical time dimension.

    thanks.

     
  6. Krishna

    March 16, 2010 at 2:35 pm

    Am pursing OBIEE Regarding this i want guidance from your side

     
  7. Sandeep

    September 16, 2010 at 3:55 pm

    Nice post, it was helpful. Thanks.

     
  8. kumar

    November 1, 2010 at 10:29 pm

    Hi ,

    I am working on dashboard reports.I have 3 coloums and 2 prompt. Based on the prompt the three colums should display the result.Prompts are year and month.

    Suppose if you select 2010 feb then the columns should display 2010 feb 2010 jan 2009 dec values.
    and I am using a presentation variable in these colums.

    Can some one help me with this.

    Thanks,
    Kumar

     
  9. Nupur

    March 1, 2011 at 10:23 am

    Awesome,helped me resolve a very critical report.

    Thanks, keep it up!

     
  10. Dipak

    April 18, 2011 at 3:26 pm

    If I have a requirement where I need to publish report as follows –
    Current Date: 1 Jan-2012
    ====================================================================
    Q1 Total = 100
    —————————–
    Q2 Total = 200 Quarter on Quarter difference = 100
    —————————–
    Q3 Total = 400 Quarter on Quarter difference = 200
    —————————–
    Oct-2011
    Toal = 200 Difference since last quarter = 200

    Nov-2011
    Toal = 150 Difference since last month = 50

    Dec-2011
    Total = 250 Difference since last month = 100
    ———————————
    Q4 Total = 600 Quarter on Quarter change = 200
    ==================================================================
    Please Note:
    a] In my system I am loading data month wise.
    b] For current quarter it should segregate month wise.
    i.e. Q1 report will give details of JAN-FEB-MAR,
    Q2 report will give details of APR-MAY-JUN,
    Q3 report will give details of JUL-AUG-SEP.
    ====================================================================

     
  11. Amrutha

    September 28, 2011 at 6:27 am

    Awesome step by step description.really helpful.Thank you so much

     
  12. Amar

    September 28, 2011 at 9:00 am

    HI,

    I have requirement like i need to show max month in a report, i have columns year , year/month in the report, in any year max month could be current month or previous months, i tried using MAX(Time.Month) but its not working

     
  13. suri

    October 29, 2011 at 2:24 pm

    Thanks for the article. This was very help full…

     
  14. sunny

    May 11, 2012 at 1:41 am

    how to get the Last 11th month and how did you changed the date coloumn to accept number 11

     
  15. Arun

    March 5, 2013 at 12:18 pm

    Hi Shiva,

    I have an issue with the AGO function.
    I have used a formulae some thing like below.

    Ago(“CCLRDC”.”Fact – Balances Measures”.”YTD Balance” , “CCLRDC”.”Period”.”Month” , 3)

    Similarly like for month 2 and month 1.Well the point is I need a ytd bal for the prev 3 months and current month too.
    When I pass the filter as APR-2012 im getting data for APR-12,MAR-12,FEB-12,JAN-12 But
    When I pass the filter as MAR-2012 im only getting data for MAR-12,FEB-12,JAN-12 but could not get data for DEC-11

    Please help me out in this

    Thank You,
    Arun

     
  16. milind

    January 15, 2014 at 6:55 am

    AWESOME logic buddy..you made it very simpler

     
  17. gayathri

    March 22, 2014 at 3:32 pm

    Hi shiva please le tme kno how to get the past 4 quarters data in report dynamically.

     
  18. Joao

    September 22, 2014 at 9:31 am

    Hi,

    Can you explain to me why this is necessary: MIN(TIMESTAMPADD(SQL_TSI_MONTH,-CAST(@{var_rollno} AS INT),”D0 Time”.”T00 Calendar Date”)) ?

    From what I understant you are doing MIN(date x months ago). Isn’t this the same as MIN(“D0 Time”.”T00 Calendar Date”) ?

    Why not just MIN(“D0 Time”.”T00 Calendar Date”) ?

    What is the difference in results?

    At the moment I cannot test both codes as I do not have access.

    Thank you.

     
    • R

      August 12, 2015 at 9:17 am

      Hi I am looking at Trailing 12 months in OBIEE. Could you please help/ I am suing MSUM funcition bt it is not working

       
  19. Joao

    September 22, 2014 at 10:19 am

    From what I understand with

    MIN(TIMESTAMPADD(SQL_TSI_MONTH,-CAST(@{var_rollno} AS INT),”D0 Time”.”T00 Calendar Date”))

    you are getting ONE SINGLE VALUE where the date is the same day as D0 Time”.”T00 Calendar Date, but x months ago.

    Is that the reason for the MIN?

    If so, kindly reply and disregard my previous comment.

     
    • R

      August 12, 2015 at 9:17 am

      Hi I am looking at Trailing 12 months in OBIEE. Could you please help/ I am suing MSUM funcition bt it is not working

       
  20. Rajkishore

    April 13, 2016 at 7:37 pm

    Use PeriodRolling

     
  21. Abhay Jain

    August 24, 2016 at 8:44 am

    Hi folks , i have generate revenue of jan-july 2016 in OBIEE , i took opportunity revenue line face from specific Subject area, can someone help me to by query or filter .

    thanks in advance

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: