How to display results for each year dynamic column in Crystal report

Question

How to display each years cost in dynamic column (Max 3 years) in Crystal report. Parameters : Date From and Date To Crystal report Version : 2013

Table : Jobs

+-------+------------+------------+
| EQ_no |  Job_Date  | Total_Cost |
+-------+------------+------------+
|  1006 | 01/30/2017 |        250 |
|  1006 | 01/31/2018 |        350 |
|  1006 | 01/01/2019 |        150 |
|  1006 | 02/01/2019 |        322 |
|  1006 | 05/05/2019 |        450 |
|  1006 | 02/02/2020 |        500 |
|  1006 | 02/03/2021 |       1212 |
| 29198 | 02/04/2017 |       3000 |
| 29198 | 02/05/2018 |        250 |
+-------+------------+------------+

Table : Equipment

+-------+-----------+
| EQ_no | Serial no |
+-------+-----------+
| 1006  | MDRSC12   |
| 29198 | FDRSC13   |
|  6218 | REAFC14   |
+-------+-----------+

Result:

+-------+-----------+------+------+------+
| EQ_no | Serial no | 2018 | 2019 | 2020 |
+-------+-----------+------+------+------+
|  1006 | MDRSC12   |  350 |  922 |  500 |
| 29198 | FDRSC13   |  250 |    0 |    0 |
|  6218 | REAFC14   |    0 |    0 |    0 |
+-------+-----------+------+------+------+

---

If date from 1-jan-2018 to 1-June-2020 then show each years total cost 2018,2019 & 2020.
If date from 1-jan-2020 to 1-June-2021 then show each years total cost of 2020 & 2021 only.

Answer 1

Create a Formula Field that uses the Year() function to extract only the 4 digit numerical year from your Job_Date field. Name this field whatever you like, but I will call it "JobYear" going forward in this answer.

The formula will be Year(Job_Date);.

Now create a second Formula Field that uses the same function to extract the 4 digit numerical year from today's date. I will call this formula field "CurrentYear" going forward.

This formula will be Year(CurrentDate);.

Now create 3 Running Total Fields. Name them something like ThisYear, LastYear, and TwoYearsAgo. Set all three of these fields to summarize the Total_Cost field. Set the reset conditions to whatever is most appropriate for your report, and then set the evaluate conditions to use a formula and use the following formulas for each one.

For ThisYear the formula should be CurrentYear = JobYear;.

For LastYear the formula should be CurrentYear - 1 = JobYear;.

For TwoYearsAgo the formula should be CurrentYear - 2 = JobYear;.

This will allow the running total fields to summarize the total cost for any job into the correct buckets based upon the year the job was completed.