Skip to Content

Creating Quarterly Data Reports using Crystal Reports 2008

Business Requirement: – To create QTD (Quarter To Date) Reports by grouping the data based on the Employee’s Date of Joining, for understanding and keeping track of the Number of Employees joined per Quarter.

Steps

1)      Create a Blank Report using Crystal Reports 2008.

2)      Add the required database and tables (Employee) to the report by creating a new database connection at Database Expert option available at Database Menu.

3)      Now select the required fields from the Employee table for the report at the field explorer and drag them to the detail section of the report.

4)      Go to the Formula Fields option at Field Explorer and create a new Formula say fr_QTD.

5)      Insert the below code at the formula bar window for fr_QTD

 Note:-(Make sure to consider the date type field for below calculation)

if month ({Employee.Date of Joining}  ) in [1,2,3] then “QTD1”

Else if month ({Employee.Date of Joining}) in [4, 5, 6] then “QTD2”

Else if month ({Employee.Date of Joining}) in [7, 8, 9] then “QTD3”

Else if month ({Employee.Date of Joining}) in [10, 11, 12] then “QTD4”

 

6)      Now go the Insert Menu and select the Group option.

7)      A new Insert Group window pops up with Common and Options Tab.

8)      At the Common Tab select the @fr_QTD from the drop down menu of the records to be grouped on option, and click OK.

9)      The above action will group the report data based on the Quarters where group header will have below values as per the Employees Date of Joining.

QTD1 = Quarter 1

QTD2=Quarter 2

QTD3=Quarter 3

QTD4=Quarter 4

10)   Now to get the Total Number of Employees Joined per Quarter, we would be required to create a Running Total say #fr_Total_Emp at the Running Total Fields Option from Field Explorer.

11)   At the Running Total window bar for #fr_Total_Emp option set the below values

a) Field to summarize = Employee Name

b) Type of summary=count

c) Check the radio button option: – For each record at Evaluate option.

d) Check the radio button option: – On change of group at Reset option and set the value as the Group 1 value @fr_QTD.

Finally click on OK.

12)   At the Report Design Tab drag the Running Total Field #fr_Total_Emp to the Group 1 Footer section to get the total number of the Employees joined per Quarter.

13)   Finally run the report.

Result: – Once we run the report we would get the Employee data grouped based on Quarter along with the Total number of Employees joined in the corresponding Quarter.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Sathyananda Paul

    Hi Ashsih

    I am new to CR . I read some of your article and  the way you provide solution is excellent. Very crisp and detail oriented. The above one is some what closer to what I looking for

    I have been struggling to create a report using two dates which are grouped monthly. The objective is to  plot trend graph.

    The data base in spread sheet from  clear quest (Defect tracking tool). I have defect id and its submitted date and closed date.

    I would like to create running total of sub date and closing date and then plot them.

    I tried doing this and i get correct  defect distribution for Sud date however for closed date the distribution is not ok as the X axis is  based on Sud date.

    Can you help me with this.

    I can share the spread sheet if you would like to advice me on this.

    Thanks and Regards

    Paul

    .

    (0) 

Leave a Reply