Solved

Crystal Report Financial / Fiscal periods

Posted on 2010-09-08
21
1,230 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
0
Comment
Question by:codedigger
  • 10
  • 6
  • 2
  • +2
21 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 33630307
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
 

Author Comment

by:codedigger
ID: 33630365
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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 33630563
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
 

Author Comment

by:codedigger
ID: 33630984
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
 
LVL 2

Expert Comment

by:coronoahcoro
ID: 33632301
but in your database does it say anything like June 2010? because you can still convert that in CR
0
 

Author Comment

by:codedigger
ID: 33632336
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
 
LVL 2

Expert Comment

by:coronoahcoro
ID: 33632399
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
 

Author Comment

by:codedigger
ID: 33632488
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
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 500 total points
ID: 33633986
 > ... 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
 

Author Comment

by:codedigger
ID: 33637976
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Assisted Solution

by:James0628
James0628 earned 500 total points
ID: 33643906
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
 

Author Comment

by:codedigger
ID: 33664635
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
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 33670046
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
 

Author Comment

by:codedigger
ID: 33686247
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
 
LVL 34

Expert Comment

by:James0628
ID: 33688701
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
 

Author Comment

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

Regards
0
 

Author Comment

by:codedigger
ID: 33693288
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
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 500 total points
ID: 33698758
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
 

Author Comment

by:codedigger
ID: 33703213
Thanks
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 33706075
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
 
LVL 34

Expert Comment

by:James0628
ID: 33706886
You're welcome.  Glad I could help.

 And thanks for the help mlmcc.

 James
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now