We help IT Professionals succeed at work.

Crystal Report Financial / Fiscal periods

Roberto Madro R.
on
1,952 Views
Last Modified: 2012-06-27
I have a financial report that taps into a financial table(s), Although a previous solution provided by this site helped me a bit, I'm still havig some issues to do with Fiscal Periods, our Fiscal cycle starts 9/1 and ends on 8/31, the data however is in the database under normal calendar date model (January means January etc.),  I want to be able to select by month & year date range and have the report display by FISCAL PERIOD (a period = a month) the total sales month to month, the idea is, if I select January 09 through January 10 is to have each month in the range displayed showing the total sales, this way mgmt can visually see the April 09 data and right next to it the April 10 data.
Thx
Comment
Watch Question

Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
The easiest possible solution, IMO, is to create a calendar table in your database.  You can then join to this table so that you group or select by only those values you need. Attached are samples of two different calendar tables.  Once created, they're easily maintained and can be used by a variety of reports.

Other than using a well-defined calendar table, you're going to have to create formulas in Crystal Reports or maybe start reporting off a SQL-based datasource, like a view, command or stored procedure.

~Kurt
SampleCalendarTables.xls
Roberto Madro R.Programmer Analyst

Author

Commented:
I appreciate the attempt but I think my situation can be more effectively addressed through a Crystal-Report-Native solution / approach, I'm dealing with 3 million records and more in each of these tables with some cleansing to do, that's my challenge.

thx
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
I guess I don't understand the issue, as you've explained it.  If you're selecting data by month and year, why does it matter if it's a fiscal period or a calendar period?  If you want January 09  - January 10, why don't you just select those two months and years?  Are you really wanting to select values like "Period 01" 2009  - "Period 01" 2010?

~Kurt
Roberto Madro R.Programmer Analyst

Author

Commented:
Kurt;

Maybe I over analyzed this in my head, I'll take a wack at it and let you know, my apprehension here stems from the fact that in crystal some of my formulas will call in the "currentdate" which's a calendar date, I'd have to balance that against fiscal dates (meaning; in the database June 2010 is not month # 6, it's Fiscal month # 10) that's my concern for now but I'll forge ahead and let you know.

Thx
but in your database does it say anything like June 2010? because you can still convert that in CR
Roberto Madro R.Programmer Analyst

Author

Commented:
That's the thing here, I'd completely reverse-engineer this database if it was mine, but to your question, yes, the data when queried or viewed with any tool would look normal, meaning, you'll not see "Fiscal Period X, Y, Z", all you'd see is June, July, etc. but those June(s) and July(s) in the database are not month #6 and #7 like they're on a normal calendar.

I'm doing some testing on the side and will share with you my finding, in the meantime any nugget is appreciated.
Thx
I kind of understand right now but when I read your first question I'm getting confused again on what you are trying to do.

Could you give sample data in you db and simple format of what you are trying to accomplish?
Roberto Madro R.Programmer Analyst

Author

Commented:
Heads up, I had to slap this together so that I can get something for you to look at, but it'll give you an idea of what I'm talking about, so lets refresh.
We need the ability to have to run this report by date range
we need to Group by Month by Customer and display the total invoice / sale
We need to display multiple (not defined at this stage) "Months" / columns together so mgmt can see the difference between months, for example, if the date range is entered as 01/01/2010 - 06/01/2010, we need to pull up 01/01/2009 - 06/01/2009, and have the June(s) next the June(s), July(s) next the July(s), etc.
ForExpertExchange.rpt
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Roberto Madro R.Programmer Analyst

Author

Commented:
James;

Your closing comment ""You could use the month for the columns.  If you want a special order, like starting with September and having the same month from each year next to each other (eg. April, 2009 next to April, 2010), there are ways to do that"" capture the essence of my little project here, and to answer your overall question, our Fiscal year starts 9/1 and ends on 8/31, that means a July on the calendar year is month #7 but on the Fiscal year is month  #11, I hope this clears things up little more.

Thx
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Roberto Madro R.Programmer Analyst

Author

Commented:
I was able to do quite a bit, but one challenge remains, how do I pull side-by-side a month of the current year (fiscal or not and we don't need to worry about that now) next to an exact month from last year, I'm thinking that my approach to this is getting in the way and not so much the level of difficulty. I need a fresh approach.
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Roberto Madro R.Programmer Analyst

Author

Commented:
Works like a charm James, I thank you, but before closing the case, is there a way to calculate two values inside Crosstab? Is there a way to display "Sep, 2009" - "Sep, 2010" = 123...., is there a way.

please let me know.
Regards
CERTIFIED EXPERT

Commented:
Modifying a cross-tab like that is generally tricky, if not impossible.  You could create a group on the month, so that you'd get a sub-total for each month, but that would just add Sep, 2009 and Sep, 2010 together, so you'd have to use a formula to reverse the sign on Sep, 2010.  More specifically, the formula would have to reverse the sign on all of the figures in the current year, and you'd use that formula in the cross-tab instead of the amount field.  That would, of course, reverse the sign on all of the current year columns.  That might still be workable if the numbers were always positive (or always negative).  In that case the sign may not really matter and you could just set the field format not to show any sign.

 I'm still using CR 10.  I think they added the ability to add some calculations to a cross-tab in a later version.  That might help with something like this.  I'm not sure.  But I have the feeling that that feature was added in CR 2008, and you're apparently using CR XI.  Maybe someone who knows more about it will chime in.

 Apart from those ideas, there may not be much that you can do.  Like I said, manipulating cross-tabs is generally tricky and there are real limits on what you can do, at least in CR 10.

 James
Roberto Madro R.Programmer Analyst

Author

Commented:
I have CR10 and I'm in agreement, many thanks for the efforts and we can conside this case closed.

Regards
Roberto Madro R.Programmer Analyst

Author

Commented:
Not sure how to handle this, I tried splitting up the 500 point in some fashion but kept getting the error seen in the attachment. Your thoughts
ExpertExchangePoints.bmp
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Roberto Madro R.Programmer Analyst

Author

Commented:
Thanks
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
You can close it that way through the ACCEPT MULTIPLE SOLUTIONS link in a comment block.

Also since James is getting the points you could simply accept one of his comments.

mlmcc
CERTIFIED EXPERT

Commented:
You're welcome.  Glad I could help.

 And thanks for the help mlmcc.

 James

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.