Link to home
Start Free TrialLog in
Avatar of RUA Volunteer2?
RUA Volunteer2?Flag for United States of America

asked on

Transitioning to a 4-4-5 fiscal calendar when you have hundreds of crystal reports that use date range calendar months

We have to transition to a 4-4-5 fiscal calendar.  The issue will be in relation to Crystal Reports.  The Date Range selection will be totally different from what we have been accustomed to with calendar months.   Can you me determine the most efficient way to manage this ?  There are a couple of hundred reports that could be affected.  A large majority use a selection criteria similar to this :

o      {Inventory_DateField} in {@DateRange-Begin} to {@DateRange-End PM} and………..

o      Where the formula {@DateRange-Begin} is defined as   tonumber(totext(dateserial(year(currentdate),month(currentdate)-{?#Months of Report Data},1),'yyyyMMdd'))

o      And the formula {@DateRange-End PM} is defined as    tonumber(totext(dateserial(year(currentdate),month(currentdate),1-1),'yyyyMMdd'))

There are Parameters stored in the report for {?#Months of Report Data} that could be 1 ( for a one month report ), 12 ( for a 12-month report ), etc….This Parameter remains as last generated unless manually changed.

I need to find a “best practices” solution most easily implemented,

Do we need to use the Data Repository to update reports with changing or dynamic formulas found in Business Objects Enterprise to have it cascaded down to all reports...?
SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RUA Volunteer2?

ASKER

Here are the Answers:
Here are a couple of questions I need to make sure I understand.

1.      When will you run the reports?

Reports will be run at various times. Sometimes as needed – others weekly, or monthly ( fiscal month )

2.      Are you looking to go back by quarters or by 4 or 5 week time periods?

Going back it would be most useful to be able to compare a 4 week month of 2017 to the same period’s 4-week month of 2016 for example.   For the most part we are talking about our Order History files

3.      Do you use calendar quarters?

We currently use calendar quarters, but under New Business we will need to report on this new 4-4-5 basis.  


4.    What is your fiscal year? Begin End.

January 1 – Dec 31st
The powers that be decided we would not set up the repository but rather set up a sql database called “Fiscal Period” that would be defined back a few years, and then forward into each new year of course.  That way the reports as we have them designed now which look back “X” number of periods would still use most of the same logic, with the change being limited to having the “@InvDate” formula (above) look at the new “Fiscal Period” table.
Avatar of Mike McCracken
Mike McCracken

Do you need help on this or does the new table solve the problem?
I need to ask some questions about the best practices to you? How you would handle it.  And Im not 100 % sure based on the initial question if i know the best ways to solve this problem. i am rusty and wanted to talk to the expert first. mlmcc Plus I am extremely worried about Nebraska vs Tennessee this year. Can you blame me those boys have not missed any meals lately..?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Crystal Reports 2008 and I am not sure what will go in the table. Assuming it will be a Primary Key of some kind of ID field. Then it will need I am guessing a month field and a year field. I was going to ask you that question. what would we need to accomplish that.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This group may want to go back and get as far back as needed. 12, 24, 36, 72 months as far back as they need. Years and years.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The Dates would only need to start at 2010 if you want you could start at 2000 but 2010 is all we need. The day the 4-4-5 period starts is Jan 1 and ends Dec 31. The last period may have an extra day in it. How do we account for that if the periods are set as 4 weeks + 4 weeks + 5 weeks = 91 Days times 4 periods = 364 Days Total. How do you build the table with Excel and convert it to SQL or do you build it in SQL straight out? Curious...?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for those comments.