CrystalKim
MIS
OK, I have spent most of my day trying to figure out how this can be done. I have googled arrays and variables and still can't figure out if this is what I need to create in order to get my report to look the way I need it to.
Here is the scenerio. I have a report that shows two years worth of history. It contains multpiple fields that need to be calcluated in a series of ways by dates). The sample data looks like this
Company Sales Fees NetRev Expenses Region DateClsd
Abc Comp $400 $50 $450 $0 North 1/1/7
123 Comp $100 $25 $25 $75 South 1/15/8
XYZ Comp $50 $0 $50 $0 East 9/18/7
499 Inc $1,000 $250 $1,000 $250 West 1/19/8
Anyway, the end result of my report needs to be broken out by Current Month, Previous Month, Current Year YTD and Previous Year YTD.
The report would look like this:
CurMnt PrevMth CurrYTD PrevYTD
East
Sales $50 $40 $200 $100
Fees $10 $30 $60 $45
Expenses $5 $7 $2 $15
NetRev $55 $63 $258 $$130
Each region would get a report that would show their performance by specified date ranges within the two years worth of data.
lbass was nice enough to help me with my formula for sales and mid way through doing them, I realized I would have to create them for all of the other fields (there are many more than the ones on the sample data, but it is too compliacted to include them.
So as it stands right now, I have four formulas based on the sales field that look like this:
If {qryClosed.Date Closed} in date(2007,12,1) to date (2007,12,31) then {qryClosed.Sales} else 0
If {qryClosed.Date Closed} in date(2007,11,1) to date (2007,11,30) then {qryClosed.Sales} else 0
etc, etc.
Now mind you the dates on this report will change monthly as it gets updated monthly so that means I am going to have to manipulate each formula to change the date ranges. My fear is that I will have to create a series of four formulas for each field I need calculated. My guess is that this will be approx. 24 more formulas which will mean 28 formulas I will have to change the dates on monthly.
Is there a way in Crystal that I can lesson the amount of formulas I need to get this done? I have thought about variables and arrays, but it appears as if you can only declare those on data within the same field?
Sorry for the posts. My company just did a re-org and they moved me into MIS and I have been in finance for the past 4 years and haven't used Crystal since. They know I have experience w/ Crystal so now they think I can make up all of the reports in less than a day!![[3eyes]](/data/assets/smilies/3eyes.gif "[3eyes] [3eyes]")
. I have spent most of my day on google and pouring through my various Crystal books, but still can't figure out if this is even possible
Here is the scenerio. I have a report that shows two years worth of history. It contains multpiple fields that need to be calcluated in a series of ways by dates). The sample data looks like this
Company Sales Fees NetRev Expenses Region DateClsd
Abc Comp $400 $50 $450 $0 North 1/1/7
123 Comp $100 $25 $25 $75 South 1/15/8
XYZ Comp $50 $0 $50 $0 East 9/18/7
499 Inc $1,000 $250 $1,000 $250 West 1/19/8
Anyway, the end result of my report needs to be broken out by Current Month, Previous Month, Current Year YTD and Previous Year YTD.
The report would look like this:
CurMnt PrevMth CurrYTD PrevYTD
East
Sales $50 $40 $200 $100
Fees $10 $30 $60 $45
Expenses $5 $7 $2 $15
NetRev $55 $63 $258 $$130
Each region would get a report that would show their performance by specified date ranges within the two years worth of data.
lbass was nice enough to help me with my formula for sales and mid way through doing them, I realized I would have to create them for all of the other fields (there are many more than the ones on the sample data, but it is too compliacted to include them.
So as it stands right now, I have four formulas based on the sales field that look like this:
If {qryClosed.Date Closed} in date(2007,12,1) to date (2007,12,31) then {qryClosed.Sales} else 0
If {qryClosed.Date Closed} in date(2007,11,1) to date (2007,11,30) then {qryClosed.Sales} else 0
etc, etc.
Now mind you the dates on this report will change monthly as it gets updated monthly so that means I am going to have to manipulate each formula to change the date ranges. My fear is that I will have to create a series of four formulas for each field I need calculated. My guess is that this will be approx. 24 more formulas which will mean 28 formulas I will have to change the dates on monthly.
Is there a way in Crystal that I can lesson the amount of formulas I need to get this done? I have thought about variables and arrays, but it appears as if you can only declare those on data within the same field?
Sorry for the posts. My company just did a re-org and they moved me into MIS and I have been in finance for the past 4 years and haven't used Crystal since. They know I have experience w/ Crystal so now they think I can make up all of the reports in less than a day!
. I have spent most of my day on google and pouring through my various Crystal books, but still can't figure out if this is even possible