Creating a current FiscalMonth Filter?

[petperson]

I am trying to create Fiscal Month filter, but our FM does not follow the calendar month. I cannot use the dynamic date function to do this. Does anyone have an example of the code used to create a current Fiscal Month filter? thanks!

 

[JRO061601]

I'm not familiar with an easy way to code a solution for this problem. The typical solution is to maintain multiple Time hierarchies in the database. In the same way you have a table or set of tables that map the date in your fact table to a calendar time dimension, you can build a table or set of tables that maps date into a fiscal time dimension. Then you simply incorporate it into the project and filter on it like you would any other attribute.

 

[chael]

Why not try creating custom groups? Even if you don't follow a traditional calendar system (i.e. you use an Aztec vs. Gregorian) you can always group "days" into logical combinations that would make up what you consider to be a fiscal month.

Once you have that you could use object prompts to act as a filtering device.

I can't recall off-hand if you can filter on Custom Groups. Anywone want to comment on that?

 

[JRO061601]

Sounds like it should work, although you'll have a 'WHERE IN' clause with ~30 dates in it. You'll have to ask a DBA if that is smart from a SQL/Query Optimization perspective.

I got the impression that 'petperson' wanted a dynamic current fiscal month filter though. I read this to mean you didn't want the end-user to have to pick the current fiscal month from a list.

Another way to create a static list of fiscal months would be to simply create filters that qualify on 'DATE BETWEEN X AND Y', and name the filters appropriately.

And if you think you can handle some overhead for this requirement, you could create a filter named 'Current Fiscal Month', include it in your reports, and then simply change the underlying definition manually once a month. As long as you don't change the name of the filter, any report you build using this filter should pick up the new definition seamlessly. Not an ideal solution, but it would work.

 

[nlim]

it also depends on what the formula from FM to CM is. You can use a very powerful combination of features to do this.

Let's say your formula is that fiscal month = calendar month-3 ie April is fiscal month 1.

1. create a dynamic date value prompt default to today's date. Name it TodayDate

2. create a filter defined as "Fiscal month attribute" = custom. The custom expression should be month(?TodayDate)-3

3. when you run a report, the sql will look something like

select ...
from ...
where fiscalmonthid = month("10/9/2002&quot - 3

good luck and let us know if this works

 

[bs1211]

Why not create a date table that has fiscal month in it along with date. Then you could use a relationship filter to provide the current fiscal month based on todays date. Or for that fact any date. I have tried to leverage the DW as much as I could. I don't trust the tricks in MSI.

 

[nlim]

less management at the data level and less breakpoints to monitor. abstraction layer is supposed to minimize data level work.

However bs1211's point is well taken, old timers are more confident with things within their control.