FIlter for Current Fiscal Month?

[petperson]

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):

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

Thanks!

 

[nlim]

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
------------
currentfiscalmonthid

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
(select
months.FISCAL_MO_NBR
from days, months
where day_dt = trunc(sysdate-1)
and days.fiscal_mo = months.fiscal_mo
group by ...

 

[petperson]

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!

 

[lincon]

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)

 

[nlim]

try basic attribute qualification instead.
fiscal date ID EXACTLY Custom

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

trun(sysdate-1)

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.

 

[petperson]

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,
a12.FISCAL_WK FISCAL_WK,
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
a12.FISCAL_MO_NBR = pa1.FISCAL_MO_NBR
and a11.DAY_DT in (select distinct day_dt from days where day_dt=trunc(sysdate-1))
group by a12.FISCAL_MO,
a12.FISCAL_WK,
a11.DAY_DT

 

[mstrNate]

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 &quot;Current Month&quot; (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.

Nate

 

[nlim]

I think you need to change the following where clause

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

to

&quot;a12.fiscal_month_nbr in (select distinct fiscal_month_nbr from days where day_dt=trunc(sysdate-1))&quot;

I would use lincon's suggestion but change it to

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

 

[petperson]

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..

elect a12.FISCAL_MO FISCAL_MO,
a12.FISCAL_WK FISCAL_WK,
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,
a12.FISCAL_WK,
a11.DAY_DT

 

[nlim]

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...

 

[petperson]

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 (&quot;#0 in (select distinct fiscal_mo_nbr from days where day_dt=trunc(sysdate-1))&quot;, [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..!

 

[mstrNate]

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 &quot;Dynamic Date, This Month, Day 1.&quot; Then for the second value, select &quot;Dynamic Date, Today, Minus 1 day.&quot; Click ok and save that filter. Then add it to a report that you have, with the month attribute on it.

HTH,
Nate

 

[lincon]

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.

 

[petperson]

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!!!