Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

FIlter for Current Fiscal Month?

FIlter for Current Fiscal Month?

FIlter for Current Fiscal Month?

Am looking into building a filter to dynamically calculate current fiscal month using custom expressions (apply simple). I have some code that returns the current fiscal month using (sysdate -1), just not sure how to build it into a filter. Any ideas?  Here's the code I have (Oracle):

trunc(sysdate) CURRENT_DAY_DT,
days.day_dt       BUSINESS_DAY_DT,
from days, months
where day_dt = trunc(sysdate-1)
and days.fiscal_mo = months.fiscal_mo;


RE: FIlter for Current Fiscal Month?

try using report as filter in 722...

1) create a report that will returns a single row of data that looks like this.

Fiscal Month

you will likely need to create fiscal year, month, date hierarchy.  And you will need a report filter that says fiscal date = (sysdate -1)

when you run this report, it should return only a scalar value.

2) then add a shortcut to this report in your "current fiscal month" filter.

3) when you use this filter in any report, the sql should look like this

select ...
from ...
where ...
and months.fiscal_mo in
     from days, months
     where day_dt = trunc(sysdate-1)
     and days.fiscal_mo = months.fiscal_mo;)
group by ...

RE: FIlter for Current Fiscal Month?

I have the date hierarchy, so I'm trying to create the filter that shows day_dt = sysdate -1.  I'm trying it with the applysimple function, but not getting the syntax right. The manual provides examples, but I'm off somewhere. I'm trying to do this with an advanced qualification  -do you have any examples of the syntax??  thanks!

RE: FIlter for Current Fiscal Month?

You can use the applycomparison function at addvanced filter,the fillowing is example:
ApplyComparison ("#0 in (select distinct day_dt from days where day_dt=trunc(sysdate-1))", day_dt@ID)

RE: FIlter for Current Fiscal Month?

try basic attribute qualification instead.  
fiscal date ID EXACTLY Custom

You may not need applysimple, instead type the following into the Custom text Box.


Another choice is to use the dynamic date function in the filter.  It allows you to pick today - 1.  Just click on the calendar icon next to the expression text box.

RE: FIlter for Current Fiscal Month?

thanks for the suggestions...I've tried them both but still cannot achieve the results I'm looking for. I am trying to simulate the report as it currently sits with a filter that is hard coded for Fiscal_month = 200302 and day_dt < 4/2/03 in order to return all day dates in the month. I need my filter to dynamically display all day dates of the fiscal month, on the last day of the FM I should see about 30+ day dates, on the first day of the new FM, I should see 1 day date. With the suggestions on creating the filter, I am only displaying one day date (yesterday) due to the sysdate-1. Any other suggestions??  thanks!!  Here's the SQL:

select a12.FISCAL_MO  FISCAL_MO,
    a11.DAY_DT  DAY_DT,
    ((((NVL(sum(a11.SALES_AMT), 0) - NVL(sum(a11.RETURN_AMT), 0)) - (NVL(sum(a11.DISCOUNT_AMT), 0) - NVL(sum(a11.DISCOUNT_RETURN_AMT), 0))) - (NVL(sum(a11.SPECIAL_SALES_AMT), 0) - NVL(sum(a11.SPECIAL_RETURN_AMT), 0))) - NVL(sum(a11.POS_COUPON_AMT), 0))  WJXBFS1
from SALES_DAY_SKU a11,
    DAYS a12,
    ZZT3M0701HBMQ000 pa1
where a11.DAY_DT = a12.DAY_DT and
 and    a11.DAY_DT in (select distinct day_dt from days where day_dt=trunc(sysdate-1))
group by a12.FISCAL_MO,


RE: FIlter for Current Fiscal Month?

Something you could try if you have access to update views and such in the database.  If you can create a view on top of the regular time dimension lookup table(s), and add a new column to it that indicates "Current Month" (or Day or Week ...), this flag can then be used in your filtering.  

If you have that flag column created, then you can create an attribute in MSTR, and once that attribute is created, then you create a filter that requires the attribute value to be 1 (i.e. Current Month = True).  

Once that is done, you then create the report, put that filter in the filter section, and place Day on the report template.  This will bring you back all the days in a month up to the current date with any data (sales data for example).  Hope that helps.  Let me know if that's not clear enough.


RE: FIlter for Current Fiscal Month?

I think you need to change the following where clause

"a11.DAY_DT in (select distinct day_dt from days where day_dt=trunc(sysdate-1))"


"a12.fiscal_month_nbr in (select distinct fiscal_month_nbr from days where day_dt=trunc(sysdate-1))"
I would use lincon's suggestion but change it to

ApplyComparison ("#0 in (select distinct fiscal_month_nbr from days where day_dt=trunc(sysdate-1))", fiscal_month_nbr@id)

RE: FIlter for Current Fiscal Month?

nlim - I did get the SQL to change to your suggestion..but I am now getting data for ALL fiscal months (as far as our history goes). I need to only return data for the current fiscal month. For example, today is the first day of our FM =3. Even though today is in FM 3, the report should show all days in FM = 2; which would be 3/3/03 - 4/6/03. I am trying to dynamically display this.  Then tomorrow, the report would show only 4/7/03 and then each day display the previous day. Here is the SQL..

    a11.DAY_DT  DAY_DT,
    ((((sum(a11.SALES_AMT) - sum(a11.RETURN_AMT)) JXBFS1
from SALES_DAY_SKU a11,
    DAYS a12
where a11.DAY_DT = a12.DAY_DT
 and    a12.FISCAL_MO_NBR in (select distinct fiscal_mo_nbr from days where day_dt=trunc(sysdate-1))
group by a12.FISCAL_MO,

RE: FIlter for Current Fiscal Month?

you need to run this sql to see if you get a single value.

select distinct fiscal_mo_nbr from days where day_dt=trunc(sysdate-1).  

if not you will need to tweak it to give only the current fiscal month number.

otherwise, the sql looks right overall...

RE: FIlter for Current Fiscal Month?

Yes, I do get a single value (2) for that SQL. I think I may be confused as to what I should be doing with this. If I understand right, create a report using the above SQL to generate a single value (it does). Then use this report in a filter along with the advanced qualification which says'ApplyComparison ("#0 in (select distinct fiscal_mo_nbr from days where day_dt=trunc(sysdate-1))", [Fisc Mth Nbr]@ID)'.  The result is one filter with the advanced qualification and the report in it?  When I do this, I can't seem to get the desired results of ONLY day dates in the current fiscal month. I get ALL day dates..I'm frustrated!!  thanks..!

RE: FIlter for Current Fiscal Month?

You can try creating a filter on the date attribute, if it's ID has a format type of date or datetime, by using dynamic date values.  

Get into the Filter Editor, and choose Create an Attribute Qualification.  Qualify on the Id (for Day or Day_Dt), and select Between as the Operator.  Then for the first value, click on the calendar to the right of the box, and select "Dynamic Date, This Month, Day 1."  Then for the second value, select "Dynamic Date, Today, Minus 1 day."  Click ok and save that filter.  Then add it to a report that you have, with the month attribute on it.  


RE: FIlter for Current Fiscal Month?

I think FM's first day isn't  the first day of a month,so danamic date not slove the problem.
You can run the sql statement in pl/sql or sql plus and see the result it isn't correct.

RE: FIlter for Current Fiscal Month?

Thanks to everyone for all your help!  I got the filter to work correctly..turns out I was using Fiscal Month Number instead of Fiscal Month!  But the syntax I had was correct..thanks to all of you!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close