How to get min and max record data in a group

[P]

Hello,

Is the following scenario possible in the standalone report designer without processing the raw data in sql query beforehand? 

I have an example list with participants - see attached InitialData.png

Each participant has taken part in multiple events. I need to show one row for each participant. On this same row I need to show data only for the first and the last events he has taken part in. First and last according to the date of the event, not its ID. At the same time i need to show/compare participant data from these two events - first and last - and show it on the same row accordingly. 

Example of the result data - see attached ResultData.png

I can group the data based on ParticipantId. But how can I find and show data for the first and last event?

 

Thanks in advance

 

 

[Neli]

Hello,

You may include the logic for getting the data for the first and last event in the SELECT Statement. For example, you can use Having Max or a subquery to get the last start/ end date for the participant.

Regards,
Neli
Progress Telerik

[P]

Ok, so it is not possible in the designer itself. Thanks for the answer.

 

Best Regards

[Neli]

Hello,

Basically you may test using table groups and the aggregate function Max. The approach is demonstrated in the attached video and report.

Check the files and let me know if that's the desired outcome.

Regards,
Neli
Progress Telerik

[P]

Hi,

Thanks for this example! What I am still missing is how to access the other columns from the rows on which I have found the min and max dates.  

For instance: 

participantId | first eventStartDate | first eventEndDate | last eventStartDate | last eventEndDate | changed location

10 | min(eventStartDate) | eventEndDate from the row on which I found min(eventStartDate) | max(eventStartDate) | eventEndDate from the row on which I found max(eventStartDate) | compare location column from the row with min(eventStartDate) and max(eventStartDate)

 

Could this be achieved through the Value expression or I need to add more groupings?

 

Best Regards

 

[Neli]

Hello,

Can you also give us more details about the scenario, as well as sample data in CSV format, so we can check? Are participantId | first eventStartDate | first eventEndDate | last eventStartDate | last eventEndDate | changed location in the same table in the datasource?

Regards,
Neli
Progress Telerik

[P]

Hi,

all the data comes from one data source.

I tried attaching a sample report but only image files are allowed - so you can see a picture of the sample report.  

Best Regards

[Neli]

Hi,

Basically, you can add the first/ last event start/end date bt adding new columns in the table: right-click over the table -> Insert Column -> Right/ Left. However, when it comes to showing the location (first event location and last event location), it will always show the first record. For that reason, you may test implementing a User Function which will return the location based on the event date and another which will return the comparison of location.

Regards,

Neli
Progress Telerik