I am trying to arrange the data in a way i can use it to enter in Report Text that has a set template but pulls data from the table based on column names.
Data in Table :
| Name | Contract ID | Start Date | End Date | Status | Description |
| ABC | 2 | 2023-03-21 | 2023-08-11 | open | |
| ABC | 3 | 2024-03-25 | 2024-04-26 | open | Active FD |
| ABC | 4 | 2024-04-27 | 2024-06-02 | open | suspension |
| ABC | 5 | 2024-06-03 | 2024-08-16 | open | active FD |
Template with fields from above data table:
The employment relationship with the company with the current contract a began on 03/21/2023.(earliest start date of the contract)
- the next call period was from 03/25/2024 to 04/26/2024, returning to the suspension period later.
- the current calling period began on 06/03/2024 until the estimated date of 08/16/2024
basically i need the records per employee in one row as the report text tool is set up to create template per employee but it needs to identify the correct periods when contract started , is active or in suspension. i tried using cross tab but couldnt get the data arranged in a way that can work for .
any guidance will be appreciated.
Thanks
