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
300 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
Question by:garyjgs
• 12
• 9
21 Comments

LVL 14

Expert Comment

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

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

ID: 36713297
Changed the mm to MM and the totext worked.
0

LVL 14

Expert Comment

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

Author Comment

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

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

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

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

Author Comment

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

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

Author Comment

ID: 36713627
0

Author Comment

ID: 36713635
0

Author Comment

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

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

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

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

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

LVL 14

Expert Comment

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

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

Author Comment

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

LVL 14

Accepted Solution

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

Question has a verified solution.

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

### Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customerâ€™s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. â€¦
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â€¦
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This videoâ€¦

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

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