Solved

How do I have dates from 2010 and 2011 with the same day and month on the same row in Crystal Reports?

Posted on 2011-09-27
21
295 Views
Last Modified: 2012-08-14
I am writing a Crystal Reports with transaction dates comparing activity for the same days in 2010 and 2011.  For example, 02/02/2011 with $5 and 02/02/2010 with $7.  I need to have these dates on the same row for each year:
             2011    2010  Difference
02/02          5         7            2
I need to make a calculation of the difference between 5 and 7 for comparative analysis.
0
Comment
Question by:garyjgs
  • 12
  • 9
21 Comments
 
LVL 14

Expert Comment

by:LinInDenver
ID: 36713151
i would start with a formula that just does MM/DD, and group by that. I think totext(datefield,'mm/dd') would work - but I don't have crystal on this machine to confirm...

next formula would be 2011 amounts
if year(datefield)=2011 then Amount else 0

next formula would be for 2010 amounts
if year(datefield)=2010 then Amount else 0

Insert these two formulas on your detail field, then right click and insert summary, sum.

create one more formula for the difference, which will be basically sum(2011amts,groupfield) - sum(2010amts,groupfield). place this in the group footer.

suppress your detail rows.
0
 

Author Comment

by:garyjgs
ID: 36713241
totext(datefield,'mm/dd') return 00 for the month/the correct value for the day is returned,
example, 00/17 for July 17
0
 

Author Comment

by:garyjgs
ID: 36713297
Changed the mm to MM and the totext worked.
0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 36713361
I knew it was something like that! It can be picky, can't it. :)
0
 

Author Comment

by:garyjgs
ID: 36713405
I do, however, still don't have amounts matching with month, day.
9-27-2011-4-50-52-PM.jpg
0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 36713454
Okay,

Insert a Group on the second column, and then insert sums on the 4th and 5th columns into the group footer.

Then create one more formula for the difference, which will be basically sum(2011amts,groupfield) - sum(2010amts,groupfield). place this in the group footer.
0
 

Author Comment

by:garyjgs
ID: 36713458
For example in the above screen print, I need to have one row for each day/month.
01/11 should have 1,090.94  and  1,177.13 in one row.
0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 36713467
yes, please follow my suggestion, that should do it.
0
 

Author Comment

by:garyjgs
ID: 36713573
What I find interesting is that the first row and last row of each group as illustrated in this resulting view have 0 in the first and last row of the group.
9-27-2011-5-39-16-PM.jpg
0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 36713601
Can you provide a screenshot of:

1) UnSuppress (or UnHide) your detail sections
2) Also showing the gray section names on the left hand side (i.e.: "Detail, Group Footer 1").

I just want to make sure your formulas are in the right spots.

Please also copy and paste your formula for %Difference into your reply.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:garyjgs
ID: 36713627
0
 

Author Comment

by:garyjgs
ID: 36713635
0
 

Author Comment

by:garyjgs
ID: 36713644
Formula:

if Sum ({@DATE 2010}, {@MONTH-DAY}) = 0 then 0 else

( Sum ({@DATE 2010}, {@MONTH-DAY}) - Sum ({@DATE 2011}, {@MONTH-DAY}))/Sum ({@DATE 2010}, {@MONTH-DAY})

0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 36713674
Thanks - that all seems to be OK.

When you go to the PREVIEW tab, do you see 2 detail rows in groups  07/04 and 07/11?
0
 

Author Comment

by:garyjgs
ID: 36713838
Good question.  When reviewing the report Preview with details, there is on row for 07/11 on page 28 and there is a row for 7/11 on page 29.  The same scenario for 7/4, one on one page and another on the page following.
0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 36713867
The group footer should only appear once, at the end of your group. Are you seeing Group Footer 1 in the mix anywhere on the preview? (i.e.: is 7/11/2010 a part of a different Group 1 than 7/11/2011)?

In addition, You may want to right click on group 2 and change group - on the 2nd tab, click the box for "keep together"
0
 

Author Comment

by:garyjgs
ID: 36713931
I checked that and the rows for the group still go to a second page..
0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 36714066
would you mind posting a screenshot of the preview tab, with the section names (left hand side) in gray included?
0
 

Author Comment

by:garyjgs
ID: 36714384
See sample report.
9-27-2011-10-27-13-PM.pdf
0
 

Author Comment

by:garyjgs
ID: 36714387
See screen shot.
9-27-2011-10-29-42-PM.jpg
0
 
LVL 14

Accepted Solution

by:
LinInDenver earned 500 total points
ID: 36714423
This is being caused by Group 1, which I think is Command.Semana

If that is the same "Semana" as displayed on screen, the PDF shows:
Page 1, Semana = 29 for 7/25
Page 2, Semana = 30 for 7/25


Group 2 will only group things within Group 1... so if you can remove Group 1 of Semana that should fix it.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

14 Experts available now in Live!

Get 1:1 Help Now