0

I have created a graph to show 6 months moving average column for last 13 months

Now users want to drill on these 6MMAs for last 6 months data contributed to that moving average.

Say , for Mar 2016 the contributing months are 10/2015, 11/2015, 12/2015, 01/2016, 02/2016 and 03/2016. So when user clicks on Mar 2016 data point they should see all contributing projects for that month i.e. 10/2015, 11/2015, 12/2015, 01/2016, 02/2016 and 03/2016.

For Feb 2016 the contributing months are 09/2015, 10/2015, 11/2015, 12/2015, 01/2016 and 02/2016. So when user clicks on Feb 2016 data point they should see all contributing projects for that month i.e. 09/2015, 10/2015, 11/2015, 12/2015, 01/2016 and 02/2016.

And so on..

How can I achieve this?

lalit
  • 1
  • 1

2 Answers2

0

Create one more analysis which will show data for all months/year. And create a drill down from your master report which will be navigated to the detail report . Also on what column basis you want to display your detail report ,give "column as prompted " there.It will be like "Master-Detail" analysis.

So when you will click on master report,that value will be passed as a parameter to detail report through "is prompted".And required value will be displayed.

PFB the link for detail explanation. http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi1113/actionframework/actionframework.htm

Hope it is helpful.

mona16
  • 46
  • 8
  • I have already created detailed analysis and the month is prompted in that. I also added that analysis in the interaction of graph. But the problem is if I make month prompted it shows me only projects for that month but I want to see projects for last 6 months. So when I clicks on Mar,2016 it shows me projects for Mar,2016 but my requirement is to see all projects from Oct,2015 to Mar,2016. – lalit Apr 01 '16 at 20:27
  • you can try a work around.write a formula in your month column in your detail report. --Extract year from the presentation variable and month should be latest value of the month column and (month-5).Try with this. Try to create the detail report which will basically show the last 5 months data.and pass the variable accordingly. – mona16 Apr 04 '16 at 07:13
  • try some thing like this in your date column in detail report. use filter on you date column Time.Date <= cast(current_date as date) and Time.Date >= TIMESTAMPADD(SQL_TSI_MONTH, -3, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) – mona16 Apr 04 '16 at 07:25
0

you can try a work around.write a formula in your month column in your detail report. --Extract year from the presentation variable and month should be latest value of the month column and (month-5).Try with this. Try to create the detail report which will basically show the last 5 months data.and pass the variable accordingly. – mona16 12 mins ago

try some thing like this in your date column in detail report. use filter on you date column Time.Date <= cast(current_date as date) and Time.Date >= TIMESTAMPADD(SQL_TSI_MONTH, -3, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

mona16
  • 46
  • 8
  • http://arjunonobiee.blogspot.in/2015/08/last-n-monthsrolling-months-data-by.html check this too. – mona16 Apr 04 '16 at 08:07