Crystal Report Financial / Fiscal periods

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

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

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

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

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?

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

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

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.

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

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

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

Regards

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

Thanks

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

You're welcome.  Glad I could help.

 And thanks for the help mlmcc.

 James