1 2 Previous Next 18 Replies Latest reply: Aug 22, 2013 7:37 AM by 1001368

# How to disply last 4months data in obiee 11g

Hi All,

In my report i need to disply last four months data in current year. month data format is like 2013/07.

reoprt like this

Month           order item cost

-------------------------------------------------
2013/07     20000

2013/06     50000

2013/05      2000000

2013/04     70000

month will go forward i need to disply recent month data.

• ###### 1. Re: How to disply last 4months data in obiee 11g

Hi,

Did you try the PERIODROLLING function in the Time Series Calculations section. See below URL:

OBIEE TRAINING: OBIEE 11G Creating Time Series Measures

If this does not work we can come up with a few other methods to get this solved but the above seems to be  a good bet..

mark if it helps..

• ###### 2. Re: How to disply last 4months data in obiee 11g

I need to disply for last 3months separately not the sum.time series function is not worked for me.

thx,

• ###### 3. Re: How to disply last 4months data in obiee 11g

I do not understand your response here. Displaying the Sum or individual results depends on the type of columns you have in your report.

Could you please explain a little bit in detail so I can understand your requirement exactly.

• ###### 4. Re: How to disply last 4months data in obiee 11g

Check this,It is releated to quarters

OBIEE: Show data of past 4 quarters

Mark if helps,

Thanks,

• ###### 5. Re: How to disply last 4months data in obiee 11g

Hi ,

When i am using below queryes displying only one month.Please find the query.

For Greater  thn

CASE WHEN SUBSTRING(Time.Month,4,1 ) ='1'  THEN CAST((CAST(Time.Year AS INT)-1) AS CHAR(4))||'/'||'01'  WHEN SUBSTRING(Time.Month,4,1 ) ='2'  THEN CAST((CAST(Time.Year AS INT)-1) AS CHAR(4))||'/'||'02' WHEN SUBSTRING(Time.Month,4,1 ) ='3' THEN CAST((CAST(Time.Year AS INT)-1) AS CHAR(4))||'/'||'03' ELSE CAST((CAST(Time.Year AS INT)-1) AS CHAR(4))||'/'||'04' END

for less thn

CASE WHEN SUBSTRING(Time.Month,4,1 ) ='1' THEN CAST((CAST(Time.Year AS INT)-1) AS CHAR(4))||'/'||'04' WHEN SUBSTRING(Time.Month,4,1 ) ='2' THEN CAST(Time.Year AS CHAR(4))||'/'||'01' WHEN SUBSTRING(Time.Month,4,1 ) ='3' THEN CAST(Time.Year AS CHAR(4))||'/'||'02' ELSE CAST(Time.Year AS CHAR(4))||'/'||'03' END

Thnaks,

• ###### 6. Re: How to disply last 4months data in obiee 11g

Hi，

You can use filters，You can calculate this year's Penultimate month  ，do  other month greater than the month.

MUYU

• ###### 7. Re: How to disply last 4months data in obiee 11g

Yes, when I used filter on both syntax displying Else condition values.

• ###### 8. Re: How to disply last 4months data in obiee 11g

One quick question:-

For how many years you need this data?

Thanks,

• ###### 9. Re: How to disply last 4months data in obiee 11g

one year only and current month and back 4months

• ###### 10. Re: How to disply last 4months data in obiee 11g

Then Go Ahead with the Case Statement

Define 4 columns in Analytics like August,July,June,May,April

August:-Case when Month_Code = '8' Then 'Order_Month_Cost' Else '0' END

Same for remaining months

Display this report in Pivot Table View

Try this and let me know the outcome.

Mark if helps,

Thanks,

• ###### 11. Re: How to disply last 4months data in obiee 11g

In Case month will be change dynamically like Sep then how it working this?

in warehouse we are not using month_code.we are using BIapps

Please let me know any other way.

Thanks,

• ###### 12. Re: How to disply last 4months data in obiee 11g

Try this query in direct database request

SELECT *

FROM myTable

WHERE (year, month) IN

(

SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -level), 'RRRR') AS year,

FROM dual

CONNECT BY LEVEL < 4

)

Thanks,

• ###### 13. Re: How to disply last 4months data in obiee 11g

Use this logic.

If the current month is August. The query will return the data for the month 'May'.

u need last 4 months -May, june, july, August

Apply filter.

• ###### 14. Re: How to disply last 4months data in obiee 11g

Filter condition displaying error

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 1861, message: ORA-01861: literal does not match format string at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000)

SQL Issued: SELECT 0 s_0, "Service - CRM Service Requests"."Time"."Month" s_1, TIMESTAMPADD(SQL_TSI_MONTH,-4,(TIMESTAMPADD(SQL_TSI_DAY,1,(TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE)*-1, CURRENT_DATE))))) s_2, "Service - CRM Service Requests"."Service Request Facts"."# of SRs" s_3 FROM "Service - CRM Service Requests" WHERE ("Time"."Month" >= TIMESTAMPADD(SQL_TSI_MONTH,-4,(TIMESTAMPADD(SQL_TSI_DAY,1,(TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE)*-1, CURRENT_DATE)))))) FETCH FIRST 65001 ROWS ONLY

This is logic displaying  april first date like --- 4/01/2013