How to create a RDL Loyalty Report Template

Quick summary:
  1. Create Loyalty program specific Custom Values
  2. Go to Administration | Reports and create a blank new report
  3. Add Movements report query
  4. Add Services report query
  5. Add Movements custom property columns
  6. Generate report template
  7. Open template in Report Builder and customize it
  8. Add grouping based on debtor name
  9. Add table cell formulas for counting entries
  10. Add custom code functions to enable revenue calculation
  11. Upload the template back into the report
  12. Download the report from FBO One and export it to Excel

Below is the above process outlined in screenshots

 

Create Loyalty program specific Custom Values

Create the "Counts towards loyalty program" custom property with 2 predefined values "Included" and "Excluded", which will indicate whether a product is part of the loyalty program or not

 

Create the Loyalty Program Start Date custom property

Validation expression: ^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$

Create the Loyalty Program End Date custom property

Validation expression: ^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$


Go to Administration | Reports and create a blank new report

Filter out the newly created report for a clearer view and click "Edit RDL Template" to prepare the editing it

Add Movements report query

C# Filter Expression:

((Func)(() =>
    {
      DateTime? startDate = null;
      if(row["Loyalty Program Start Date"] != null)
            startDate = DateTime.Parse(row["Loyalty Program Start Date"].ToString());
      else
           //if no startDate exists, it means that no loyalty program applies to this row of data
            return false;

      DateTime? endDate = null;
      if(row["Loyalty Program End Date"] != null)
           endDate = DateTime.Parse(row["Loyalty Program End Date"].ToString());

      bool showRow = false;
      if ((row["ArrDepActualDateTimeUTC"] != null && DateTime.Compare((DateTime)row["ArrDepActualDateTimeUTC"], startDate.Value) >= 0))
           showRow = true;

      // if the endDate is not yet specified it means that the loyalty program is active
      if(endDate != null)
      {
            if ((row["ArrDepActualDateTimeUTC"] != null && DateTime.Compare((DateTime)row["ArrDepActualDateTimeUTC"], endDate.Value) < 0))
                 showRow = true;
            else
                 showRow = false;
      }

   return showRow;
   }
))();

Add Services report query

C# Filter Expression:

row["Counts towards loyalty program"]!=null && row["Counts towards loyalty program"].ToString().ToLower()=="included" && 
((Func)(() =>
      {
            DateTime? startDate = null;
            if(row["Loyalty Program Start Date"] != null)
                  startDate = DateTime.Parse(row["Loyalty Program Start Date"].ToString());
            else
                  //if no startDate exists, it means that no loyalty program applies to this row of data
                  return false;

            DateTime? endDate = null;
            if(row["Loyalty Program End Date"] != null)
                  endDate = DateTime.Parse(row["Loyalty Program End Date"].ToString());

            //only services with a valid price calculation time that fall inside the start-end date are taken into consideration
            if (row["PriceCalculationDateTime"] == null ||
               (DateTime.Compare((DateTime)row["PriceCalculationDateTime"], startDate.Value) < 0 &&
               (endDate.HasValue && DateTime.Compare((DateTime)row["PriceCalculationDateTime"], endDate.Value) >= 0)))
                  return false;

            bool showRow = false;
            if ((row["MostActualArrivalDateTime"] != null &&
                 DateTime.Compare((DateTime)row["MostActualArrivalDateTime"], startDate.Value) >= 0) ||
                 (row["MostActualDepartureDateTime"] != null &&
                 DateTime.Compare((DateTime)row["MostActualDepartureDateTime"], startDate.Value) >= 0))
                        showRow = true;

            // if the endDate is not yet specified it means that the loyalty program is active
            if(endDate != null)
            {
                  if ((row["MostActualArrivalDateTime"] != null &&
                        DateTime.Compare((DateTime)row["MostActualArrivalDateTime"], endDate.Value) < 0) ||
                        (row["MostActualDepartureDateTime"] != null &&
                        DateTime.Compare((DateTime)row["MostActualDepartureDateTime"], endDate.Value) < 0))
                              showRow = true;
                  else
                              showRow = false;
      }

      return showRow;
}
))();

Add Movements custom property columns

Generate report template

We will delete all tables and create a new one

 

Open template in Report Builder and customize it

Generated template includes 4 tables with all values from all 4 datasets

Add grouping based on debtor name

After giving it a nicer look we will step towards building in some functionality and the first one is grouping by debtor name.

Right click the Row Groups scetion on the bottom of the Report Builder screen and select the "Group Properties" menu point to view more details

Add sorting so that the displayed values are in the desired order. Sorting can be done by selecting the "sorting" tab and entering the columns after which to sort.

Special case here is the first sorting column which is an expression, that would translate as "number of all movements belonging to a debtor".

So first sorting criteria is the "Total movements' (from highest number to lowest) and the second one by "Debtor name" (in alphabetical order)

 

Add table cell formulas for counting entries

The "Total movements" column is an expression, that counts all the movements belonging to a debtor

=LookupSet(Fields!DebtorName.Value, Fields!DebtorName.Value, Fields!OrderKey.Value, "Movements").Length()

Arrivals and departures are marked with the letter "A" or "D" so basically we need to count the letters.

For the total number of arrivals belonging to a certain debtor we used the following expression: (same logic goes for total number of departures)

=REPLACE(Join(LookupSet(Fields!DebtorName.Value,Fields!DebtorName.Value,Fields!ArrivalOrDeparture.Value,"MovementsDataProviderRow"), ""), "D", "").Length

1. used a LookupSet function to get all arrival/departure values related to adebtor

2. used the JOIN function to concatenate this values, ending up with a sequence similar to: AADDADA

3. used the REPLACE function to replace all "D" characters from the sequence with empty strings (resulted sequence: AAAA)

4. used then the "Length" function to retrieve the length of the resulted sequence (resulted value: 4)



Add custom code functions to enable revenue calculation

For the total handling revenue used the following expression: (similar logic applies to average handling, only difference is that average function has a second parameter which is the total number of movements)

=Code.SumLookup(LookupSet(Fields!DebtorName.Value, Fields!DebtorName.Value, Fields!AmountExclVAT.Value, "Services"))

1. used a LookupSet function to retrieve all AmountExclVAT values belonging to a certain debtor

2. SUM over a LookupSet is not possible, so a VB code was created to sum up the values

3. click o the report (blue-ish background to select the report itself), in the properties section Code property should appear, revealing the created VB functions

The 2 VB functions are:

 

Function SumLookup(ByVal items As Object()) As Decimal
If items Is Nothing Then
Return Nothing
End If
 
 Dim total As Decimal = New Decimal()
total = 0
 
 For Each item As Object In items
total += Convert.ToDecimal(item)
Next
 
 If total = 0 Then
Return Nothing
Else
Return total
End If
End Function
 

Function AvgLookup(ByVal items As Object(), ByVal noOfMovements As Decimal) As Decimal
Dim total As Decimal = New Decimal()
total = SumLookup(items)
 
 If total = 0 Then
Return Nothing
Else
Return total/noOfMovements
End If
End Function

 

 Upload the template back into the report

Enter the "Edit RDL Template" page and click on the "Upload template" button.

Upload the template, fill some description and the template name and then click "OK" to save the file

After uploading the template file you will notice that the template file was assigned to the report and therefor mentioned in text below the "Remarks" textbox

Click the "View report" button to generate a new report.

 

Download the report from FBO One and export it to Excel

Enter filter values and click on the "Download" button to generate a new report.

A new page is opened depicting the generated report. In order to export it to Excel choose the "Save to Excel" button as shown in the image below:

The exported excel file will look exactly the same as the generated report in the FBOOne report viewer:

Exported Excel file can be downloaded from here: Loyalty Report_Template.xlsx

You can also download the report file used in this briefing from here: Loyalty Report_Template.rdl