How can I custom sort records in a report using a variable?
11/10/2018

To do this you will need to create a new variable which will use all required date objects and create a nested set of IF statements in order to get the correct ordering.

The crucial thing here is to have a decent spec of each individual set of outcomes and scenarios of populated or NULL dates. We would then need to ensure that we code for each scenario in the correct sequence otherwise the sorting will not work as expected.

An unexpected side effect of this route is that this variable will need to be added to the table. Unfortunately this column cannot be hidden in infoview so it needs its column size changed and colors and borders changed so it is not visible.

An example of how we can sort 3 different date columns is shown below

 

=If(
IsNull([Costartdate]);
If(IsNull([Crstartdate]);[Mpstartdate]; If([Mpstartdate]<[Crstartdate];[Mpstartdate];[Crstartdate]));
If(IsNull([Crstartdate]);
If([Mpstartdate]>[Costartdate];[Costartdate];[Mpstartdate]);
If(IsNull([Mpstartdate]);
If([Costartdate]>[Crstartdate];[Crstartdate];[Costartdate]);
If([Mpstartdate]>[Crstartdate];
If([Costartdate]>[Crstartdate];[Costartdate];[Crstartdate]);
If([Mpstartdate]<[Costartdate];[Mpstartdate];
If([Costartdate]>[Crstartdate];[Costartdate];[Crstartdate])))
)))

By using keyintelligence.uk, you agree to our use of cookies to enhance your experience.