Rolling 12 months in Business Object Information Design Tool?

[Former Member]

Hi Gurus!,

My user requirement is very specific, that the user needs to have a year prompt and he should get the rolling 12 months for all the months of that year.

eg

Sales [January 2013]=  Sales Feb[2012]+ March[2012]............................................+January[2013].

The limitation with WebI is, firstly that user has to select current year and previous year in the prompt, second he has to filter the result of current year in the report!

So we plan to achieve this in universe layer, using universe prompt. We have year and month dimension in our report.

My database is SQL Server 2008, has anybody tried achieving rolling 12 month is universe layer? Any help? The only way seems is Analytical Funtions.

Regard

SJ

[former_member4998]

Hi...

Implementing logic at universe level is good idea due to performance issues. We have implement at universe level. Please find the below sample code.

Select

DATE_ID as DATE_ID,

'Last 12 month' as Periods

from td_Date

where

CalendarDate between DATEADD(mm,-12,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

and DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

union all

[Former Member]

This requirement could be achieved simply by creating a aggregated measure based on a case statement utilizing year(sysdate) and month(sysdate) functionality and building some kind of logic to get the value where is date falls within previous 12 month data..