?
Solved

Comparing current YTD to last ytd and future ytd

Posted on 2006-05-03
8
Medium Priority
?
910 Views
Last Modified: 2012-05-05
I am so lost it is not even funny.  I have been a Crystal Reports Guru now for 12 whole hours.  I guess because I can spell it.  My boss needs a report by Friday.  I have to give him a line graph for proposal fees by month for last year compared to current ytd and if possible plot anticipated goals for next year.  

I have generated a report grouped by proposal.submittal_date.  I have made the proposal.proposal_fee the summary and have chosen only the records in which the Proposal.submittal_date > 01/01/2005 12:00:00AM

I have a beautiful line graph now that starts at 01/2005 continuing through 5/2006 and I am quite pleased I have gotten this far.  However, I am stuck now.

How do I compare this YTD to last years proposal fees to date. I have tried desperately to figure it out using "Teach Yourself Crystal Reports 9 in 24 hours" and reading all of the responses here - but I am no closer now than I was this morning at 9am when I was told I would be responsible for this report.  If you cannot give me specifics, can someone PLEASE point me in the right direction!?!? I will be leaving work soon as it is nearly 10pm and I have to stop and get Tylenol on the way home before my head explodes, but I will be back at this in the morning and would be MOST grateful to any help anyone can extend.  FOREVER IN YOUR DEBT!  

Jennifer
0
Comment
Question by:jentfur
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 10

Accepted Solution

by:
Spykair earned 2000 total points
ID: 16602937
Create 2 running totals. One for 2005 and the other for 2006.

If you want to compare it month-by-month you'll have to change things around a bit and it's kinda messy:
Create a subreport  -> Insert the submittal date and fee in the detail section -> Group on submittal date -> You'll have to create 12 running totals(for each month) and a formula field to pass them as shared varaibles back to the main report.
//Running Total1
Field to summarize -> proposal.proposal_fee
Evaluate -> Use a formula -> Month( proposal.submittal_date) = 1 and Year(proposal.submittal_date = 2005)
Reset -> Never
This will pass back the total per month for 2005 to wich you can compare the months of 2006

Do this for all months

Create a formula:
//Pass variables back
Shared Numbervar TotalMonth1;
TotalMonth1 := {#Running Total1};
etc...

Formula in your main report:
//RunningTotal1
Shared NumberVar TotalMonth1;
etc...
0
 
LVL 7

Expert Comment

by:janmarini
ID: 16603251

Wow, that's quite a task your boss assigned - yikes!  I've been working with Crystal for years and I think I would panic too if I had to produce that report by Friday.  It's great that you have gotten as far as you have in such a short time.  

So in the morning (hope your headache is better! :-) spend just a few minutes to first determine how critical this report really is for your boss on Friday (Drop Dead, Priority One, Need to show the data!, or would be nice to show in a Crystal Report); then find out if it MUST be done in Crystal for this Friday or can an alternative temporary solution be prepared with the Crystal solution to follow next week?

Spykair is correct that the solution to your problem may be kinda messy - I call them the 'jump through the hoops' kind of reports.  With Crystal - if there's Data, there's a Way.  Most times you can produce a report within minutes or hours - but sometimes the design of the report just will take more time and there is no way around that.  But it always can be done.

Allocate most of tomorrow to explore Spykair's solution (or advice from other experts who chimed in while I was writing this note) - that's how I would proceed.  The experts here are awesome.  But if you find that it's getting late in the day and it's a NO GO, but your boss needs something - - - switch over to Excel for a last minute, one-time solution.  

Good luck to you!
Jan
0
 
LVL 42

Expert Comment

by:frodoman
ID: 16605082
If you have the option available, I would create the aggregate data on your database - run a script or stored procedure that will summarize all of the known and projected values into a table something like this:

Month        CurrentYR      PriorYR
Jan              9999           9999
Feb             9999            9999
...etc...

With this data it's a trivial task to generate the chart that is requested.  I have reports like what you describe and for every one of them I use a similar approach to this - generally with stored procedures that run nightly or weekly (depending on what it is) to refresh the data.

You can follow Spykair's approach - there's absolutely nothing wrong with it - but in my opinion its messy and difficult to get right the first time and to maintain later (especially for a novice).  You can do the same work in your database much easier if you have the authority to do that.

frodoman
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
LVL 10

Expert Comment

by:Spykair
ID: 16605509
-->>but in my opinion its messy and difficult to get right the first time and to maintain later (especially for a novice).  You can do the same work in your database much easier if you have the authority to do that.

I agree with frodoman

Spykair
0
 

Author Comment

by:jentfur
ID: 16606013
Thank you all!  Jan, thank you for your advice, though I am pretty sure that I work for the spawn of the devil and his father owns the firm. LOL  "It needs to be done in Crystal, it needs to be done by Friday, and I need to figure it out."  I should never have told him that I work best under pressure!

So from what I understand, I would create a subreport for 2005 and in the main report I would get the data for 2006 (and forget 2007 for now! He will be lucky to get '05 and '06 at this point!)  Is that correct?  Or would I create 2 sub reports? Maybe I should start drinking heavily - then perhaps this will make sense. LOL  

Can I create the data table that Frodoman describes using Crosstabs?  Not that I have any idea what a cross tab is except that it is Hour 14 in the learning Crystal in 24 hours book and I am only in the 11th hour - LITERALLY. LOL  Sub reports are not mentioned until Hour 16, but neither is described with much detail.  I feel like the doctor in star trek - "Damnit Jim! I am just a doctor!"  ACK!  
0
 
LVL 42

Expert Comment

by:frodoman
ID: 16607011
>>> Can I create the data table that Frodoman describes using Crosstabs?

No - don't even head down that road for this project.


>>> Or would I create 2 sub reports?

No, you want your 2006 data in your main report and your 2005 data in subreports.  You'll end up with a field in your main report which is the 2006 data for each month and another field in your main report which is a formula outputting the shared variable's value that was set in the subreport.  Now you can graph for each month in the main the two fields I just described.  This gives you two datapoints for each month which lets you show a line (or bar) side by side.
0
 

Author Comment

by:jentfur
ID: 16617228
OK I am almost there - I have the subreport with the 05 monthly totals, do I create 1 formula for EACH month in order to pass the variables back?  That is where I am not getting it, I cannot get the totals back to the main report.  any thoughts?
0
 
LVL 10

Expert Comment

by:Spykair
ID: 16627853
Sorry for only coming back to you now, I was off on Friday.

>>I cannot get the totals back to the main report
This is where the following formulas for each month will be picked up from the sub report:
//RunningTotal1
Shared NumberVar TotalMonth1;
//RunningTotal2
Shared NumberVar TotalMonth2;
etc...

Hth,
Spykair
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

862 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