Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1542
  • Last Modified:

Crystal Report Financial / Fiscal periods

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
0
Roberto Madro R.
Asked:
Roberto Madro R.
  • 10
  • 6
  • 2
  • +2
4 Solutions
 
Kurt ReinhardtCommented:
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
0
 
Roberto Madro R.Programmer AnalystAuthor 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
0
 
Kurt ReinhardtCommented:
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
0
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
Roberto Madro R.Programmer AnalystAuthor 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
0
 
coronoahcoroCommented:
but in your database does it say anything like June 2010? because you can still convert that in CR
0
 
Roberto Madro R.Programmer AnalystAuthor 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
0
 
coronoahcoroCommented:
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?
0
 
Roberto Madro R.Programmer AnalystAuthor 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
0
 
James0628Commented:
 > ... 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.

 I guess that answers one question.  In your first post you said that if you selected Jan 09 through Jan 10, "mgmt can visually see the April 09 data and right next to it the April 10 data".  I was wondering how April of 2010 got in there if you were running the report for data through January.

 I take it that you want to ask for months in one year, and have the report also include the same months in the previous year.  That's one issue (and could be handled using a record selection formula).


 > ... 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.

 If they're not 6 and 7, what are they?  Are those dates actually stored as dates in the db, or do you just have something like a month name and year?
 If they're dates, is the month not correct, so that, for example, 06/30/10 is not actually June 30, 2010?

 Or are you merely saying that June is not the 6th month of your fiscal year?  If so, that shouldn't be a big problem.  Now that I've had some time to think it through, I'm guessing that that's what you meant, but saying things like "June and July are not 6 and 7" was confusing me.

 If you want to see the figures in columns, a cross-tab comes to mind.  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.

 James
0
 
Roberto Madro R.Programmer AnalystAuthor 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
0
 
James0628Commented:
OK, next question.

 If you want a column for each month, what's in those columns?  Just a single figure or multiple figures, like $'s and quantity?

 Are you just looking for grand totals or totals for one or more groups?  You mentioned customers before.  You want monthly totals for each customer?  Are there any other groups, like stores or regions?

 Let's say that you just want total sales for each month for each customer.  Try creating a cross-tab.  If it's going to be a summary for the whole report, put it in the report header or footer.  Use the date for the columns and the customer for the rows, and your sales field will be the summarized field.  Once you've added the date for the columns, click on it and click on "Group Options".  If it's a date or datetime field, there should be a "The column will be printed" option.  Select "for each month".  That should give you a column for each month.  They won't be in the order that you want, but we can change that once you get the basic cross-tab working.


 You want to enter a range of dates, or months and years, and have the report include those dates, and the same range from the previous year, correct?  Have you got that part set up?  If not, are you selecting a range of dates or a range of months and years?  What are your parameters?  Do you have a single parameter that accepts a range of values, or separate start and end parameters?

 James
0
 
Roberto Madro R.Programmer AnalystAuthor 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.
0
 
James0628Commented:
Are you using a cross-tab?  If so, what you need to do is create a formula that puts the columns in the desired order, and use that for the columns in the cross-tab.  Then the column headings may not be what you want (they'll show this new value), but you can replace the headings with different values.

 Your fiscal year starts with September, so you want that to be the first column.  Assuming that you have a date or datetime field, create a formula like the following and use that for the columns in the cross-tab.

CStr (
if Month ({your date field}) < 9 then
  Month ({your date field}) + 4
else
  Month ({your date field}) - 8, "00")
 + "-" +
 CStr (Year ({your date field}), "#")


 What that does is re-order the months, so that Sep - Dec and then Jan - Aug are numbered 01 - 12.  That number is converted to a string (01, 05, etc.) and combined with the year, so you get values like:

 Sep 2009  =  01-2009
 Sep 2010  =  01-2010
 Jan 2010  =  05-2010
 Jan 2011  =  05-2011

 To use that for the columns, select the column in the cross-tab, click "Group Options" and then select that new formula from the drop down list.  That should give you columns like:

 01-2009  01-2010  02-2009  02-2010  03-2009  03-2010  04-2009  04-2010  05-2010  05-2011 ...
     (September)              (October)              (November)            (December)              (January)


 Then in the cross-tab expert you can select the column, click on "Group Options" and on the Options tab, check "Customize Group Name Field" and select the option to "Use a Formula as Group Name".  The formula could be something like:

MonthName (Month ({your date field}), True) + ", " +
 CStr (Year ({your date field}), "#")


 That formula would produce values like "Sep, 2009", "Sep, 2010", etc. and those would be the new column headings.

 With a bit of luck, I think that will do it.

 James
0
 
Roberto Madro R.Programmer AnalystAuthor 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
0
 
James0628Commented:
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
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
I have CR10 and I'm in agreement, many thanks for the efforts and we can conside this case closed.

Regards
0
 
Roberto Madro R.Programmer AnalystAuthor 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
0
 
James0628Commented:
Forgive the obvious, but did you try to assign less than 20 points to one of the responses?  That's what the error seems to be saying.  Apart from that, I have no idea.  I don't ask questions here.  I just answer them.  :-)  So I'm not really familiar with the question closing process.  If you're still having problems, you could try the "Request Attention" link and see if you can get a mod to help.  I know you can use that to get someone to re-open a question so that you can assign the points differently.  I'm not sure if you can also use it to get help closing a question.

 James

 PS: FWIW, you used "Crystal Report XI" as the tag on your original post.  That's where I got the idea that you were using CR XI.  If you really have CR 10, you might want to use that next time.  It's not a big deal (at least, to me), but there are some differences between them, so it might affect the solutions that you get.
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
Thanks
0
 
mlmccCommented:
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
0
 
James0628Commented:
You're welcome.  Glad I could help.

 And thanks for the help mlmcc.

 James
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now