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

Creating a current FiscalMonth Filter?

Creating a current FiscalMonth Filter?

Creating a current FiscalMonth Filter?

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!

RE: Creating a current FiscalMonth Filter?

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.

RE: Creating a current FiscalMonth Filter?

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?

RE: Creating a current FiscalMonth Filter?

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.

RE: Creating a current FiscalMonth Filter?

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") - 3

good luck and let us know if this works

RE: Creating a current FiscalMonth Filter?

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.

RE: Creating a current FiscalMonth Filter?

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.

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