Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Previous Month, Current Month, Percent Change Post Reply Post New Topic
Author Message
lcrawfor
Newbie
Newbie


Joined: 27 Dec 2007
Online Status: Offline
Posts: 1
Quote lcrawfor Replybullet Topic: Previous Month, Current Month, Percent Change
     Posted: 27 Dec 2007 at 4:47pm
I need to create a report which shows the count of orders for the previous month, the current month and the percent change. An example follows.

I suspect I need to use sql expressions but I can't figure it out. Anbody else know how to do this?

Crystal Reports X1, SQL  Server 2005

Orders table

CUSTNUMB       VARCHAR(15)
ORDER_DATE    DATETIME
REGION             VARCHAR(10)
ORDER_NUMB   INT


Orders Monthly Summary Report


Region                     2007 - 07                           2007-08                Percent Change


East                           3,478                                 4,568                         31%

North                          2,364                                 2,578                          9%

South                         7,634                                 7,189                         -6%

West                         1,891                                  2,231                        18%


Total                        13,665                                16,566                          21%

IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 28 Dec 2007 at 5:16am
Create a report and group by region.  Adjust your selection criteria to restrict your order dates to the two months in question.  I am going to presume that you do this with a parameter field, called ?RptMonth, which is the Date of the end of the current month.

Suppress everything but the report header and footer, and the group footer.

Create three formulas.  The first, @PrevMonth, will look like:

    IF Month({ORDER_DATE}) = Month(?RptMonth) THEN 0 ELSE 1

The second, @CurMonth, will look like:

    IF Month({ORDER_DATE}) = Month(?RptMonth) THEN 1 ELSE 0

The third @PctChange, will look like:
    (SUM(@CurMonth,{REGION}) - SUM(@PrevMonth,{REGION}))/100*SUM(@CurMonth,{REGION})


In the group footer, put your Region, Sum(@PrevMonth), Sum(@CurMonth), and @PctChange.
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.