Selection Filter pass in detail report [message #645785] |
Tue, 15 December 2015 01:54 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/7f6da926c2693575f2efa5d0e4b217c5?s=64&d=mm&r=g) |
yash.amit@gmial.com
Messages: 1 Registered: December 2015
|
Junior Member |
|
|
Hi All,
We have main report and date prompt. Based on date selection we are getting report on week wise.
Suppose we selected 1-Jan-2015 to 31-Jan-2015 then we get five week data. As per W1,W2,W3,W4 and W5. We have enabled drill option on main report. Now we are passing prompt data to detail report in the narrative view by presentation variable to show the selected filter
Issue When we click any of the week data we are getting same filter date that is 1-Jan to 31-Jan.
As per requirement we need to show the date as per week start date and end date. Let's say if we click on W2 data then detail report should show filter criteria as 4th Jan to 10th Jan .
Please advise how can we achieve same in OBIEE 11g.
Thanks,
Yash
|
|
|
Re: Selection Filter pass in detail report [message #645895 is a reply to message #645785] |
Wed, 16 December 2015 01:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
I don't use OBIEE.
However, here's an example which shows how to extract minimum and maximum dates per weeks. See if you can use it in your case.
SQL> alter session set nls_date_format = 'dd.mm.yyyy';
Session altered.
SQL> with
2 dates as
3 (select trunc(sysdate, 'yyyy') + level - 1 datum
4 from dual
5 connect by level <= 20
6 ),
7 weeks as
8 (select datum, to_number(to_char(datum, 'ww')) week
9 from dates
10 )
11 select week,
12 min(datum) min_date,
13 max(datum) max_date
14 from weeks
15 group by week
16 order by week;
WEEK MIN_DATE MAX_DATE
---------- ---------- ----------
1 01.01.2015 07.01.2015
2 08.01.2015 14.01.2015
3 15.01.2015 20.01.2015
SQL>
|
|
|