The following information was provided in our April Feature Class about Advanced Expressions.
The expressions below can be copied then pasted into the report you are creating, but we recommend you copy and paste any expressions into Notepad, to remove formatting, and then copy/paste from Notepad into the report you are creating.
Expressions can:
- Add, multiply, subtract, or divide two or more existing fields
- Convert integers into ratios based on numerator/denominator fields
- Concatenate or add text to field output
- Items in the expression section override the designed field return
Access the Expressions box via the Advanced Field Settings (cog) on the Fields and Summary pages.
Common Support Question: When using ‘Add Subtotals’ and fields such as Invoice Number, Booking ID, etc. (any field that is numeric), we advise to change Subtotal Function to None or Count within Advanced Field Settings to avoid possible report failure.
Different Variations for Date and Time on Reports:
The two links below provide details on date conversion that are used and provide other format options.
https://www.sqlservertutorial.net/sql-server-system-functions/convert-datetime-to-string/
http://www.sql-server-helper.com/tips/date-formats.aspx
Segment Level - Date and time expressions:
Typically, if a report is run in PDF format, the date and time will show as you select in the Format drop down field. However, if you’re not using an expression for date and time and exporting the report to excel or excel data only, the cell data view may show the full date and time together.
This format is the standard 101 = mm/dd/yyyy
CONVERT(VARCHAR(11),[rb_segments].[arrival date and time],101)
CONVERT(VARCHAR(11),[rb_segments].[departure date and time],101)
Provides European dates with the month spelled out example: 17 Feb 2020
CONVERT(VARCHAR(11),[rb_Bookings].[issued Date],106)
CONVERT(VARCHAR(11),[rb_Bookings].[start Date],13)
Adds space between date and AM/PM result shows as 6:07 AM
SUBSTRING(CONVERT(varchar(14),[rb_segments].[arrival date and time],22), 10, 11)+ ' ' + RIGHT(CONVERT(VARCHAR(30), [rb_segments].[arrival date and time], 9),2)
SUBSTRING(CONVERT(varchar(14),[rb_segments].[departure date and time],22), 10, 11)+ ' ' + RIGHT(CONVERT(VARCHAR(30), [rb_segments].[departure date and time], 9),2)
Convert time to 24 HR - When exported to excel data only you will see the 24 hour time only, no date will be shown.
CONVERT(VARCHAR(5),[rb_segments].[departure date and time],114)
CONVERT(VARCHAR(5),[rb_segments].[arrival date and time],114)
CONVERT(VARCHAR(5),[rb_citypairs].[departure date and time],114)
CONVERT(VARCHAR(5),[rb_citypairs].[arrival date and time],114)
Segment Level - When using mileage and want refunds to calculate mileage as a '0':
CASE WHEN [rb_bookings].[transaction type] = 'REFUND' then([rb_segments].[Segment Mileage]*-1) else [rb_segments].[Segment Mileage] end
Segment Level - When using the airline code alone this expression will make DL 006 or AC 014 instead of leaving zeros off:
CASE WHEN len([rb_bookings].[airline number]) = 1 then '00'+ [rb_bookings].[airline number] when len([rb_bookings].[airline number]) = 2 then '0' + [rb_bookings].[airline number] else [rb_bookings].[airline number]end
Segment Level - Remove the - at end of ticket # unless it’s a conjunctive ticket:
CASE WHEN [rb_segments].[segment leg ID] = 1 AND LEN([rb_bookings].[document number]) = 11 then CAST([rb_bookings].[document number] as NCHAR(10)) WHEN [rb_segments].[segment leg ID] = 1 AND LEN([rb_bookings].[document number]) > 11 then [rb_bookings].[document number] end
Booking Level - Remove the - at end of ticket # unless it’s a conjunctive ticket:
CASE WHEN LEN([rb_bookings].[document number]) = 11 THEN CAST([rb_bookings].[document number] as NCHAR(10)) ELSE [rb_bookings].[document number]end
Booking Level - Want ticket number for air in one column and service fee in the next column:
CASE WHEN [rb_bookings].[booking type] = 'air' THEN [rb_bookings].[document number] ELSE ' ' END
CASE WHEN [rb_bookings].[booking type] = 'serv fee' THEN [rb_bookings].[document number] ELSE ' ' END
Booking Level - Show Reissued Bookings:
CASE WHEN [rb_bookings].[ticket exchanged] = 'True' then 'Reissued' end
CASE WHEN [rb_bookings].[ticket exchanged] = 'True' then 'True - Reissued' else 'False' end
Booking Level - Totals for Air, Car and Hotel separately, because if use a pivot table, it can’t be exported to Excel Data Only. Column Grouping is good to use here:
SUM(CASE WHEN [rb_bookings].[booking type] = 'AIR' then [rb_bookings].[total paid] end)
SUM(CASE WHEN [rb_bookings].[booking type] = 'CAR' then [rb_bookings].[total paid] end)
SUM(CASE WHEN [rb_bookings].[booking type] = 'HOTEL' then [rb_bookings].[total paid] end)
Booking Level - Have a UDID value show as money - numeric version rounded the data:
The best way to deal with casting text to money is with the MONEY cast. Numeric works, but when a value is cast as just NUMERIC it will always round. You can use NUMERIC but there are some additional parameters required.
Some examples:
Using MONEY, the best method would be:
CAST([RB_UDID_03].[UDID 03] AS MONEY)
Using NUMERIC, you would need to add to additional values:
CAST([RB_UDID_03].[UDID 03] AS NUMERIC(18,2))
The (18, 2) basically means, up to 18 total digits including 2 places after the decimal.
In both examples above, a text value of the following: 545.9846
Using MONEY is converted to 545.98
Using NUMERIC(18,2) is converted to 545.98
Using the original CAST of just NUMERIC would convert it to 546.00
sum(case when numeric([rb_UDID_28].[UDID 28]) = 1 then CAST([rb_UDID_28].[UDID 28] as money) else 0.00 end)
Booking Level - Convert UDID to numeric field and use in a calculation:
UDIDs pull as a text field, you can do the following to change the field to a numeric value. We suggest using a field that has numeric values, like Tax 1-4 Amounts etc. Example is calculating the UDID - Total paid for savings difference. You will get an error if all the values in the UDID are not numeric or show (BLANK).
CAST([rb_udid_70].[udid 70] as NUMERIC) - [rb_bookings].[total paid]
The following expressions are discussed in our monthly Report Builder - Advanced Features Webinar.
Note: To see the list of our upcoming live client training webinars and to register, please go to this page on our website: https://www.grasptech.com/webinars
These 2 options can be used when you’ve created an air segment report and will make it so the total paid doesn’t show as the 'Total' for each segment.
The first segment shows the total and the other segments show as 0.00
CASE WHEN [rb_segments].[segment leg ID] = 1 then [rb_bookings].[total paid] else 0.00 end
The first segment shows the total and the other segments show blank
CASE WHEN [rb_segments].[segment leg ID] = 1 then [rb_bookings].[total paid] end
This statement is similar to the one above, but will show total paid once and allow for service fee amount to show.
CASE WHEN [rb_segments].[segment leg ID] = 1 then [rb_bookings].[total paid] WHEN [rb_bookings].[booking type] = 'Serv Fee' then [rb_bookings].[total paid] else 0.00 end
This will remove the world 'blank' when there is no value for a UDID. The space after the single quote and before the (Blank) is key to this format.
case when [rb_udid_15].[udid 15] = ' (BLANK)' then '' else [rb_udid_15].[udid 15] end
This will allow multiple fields to all show in one column, using example of Vendor info, which is especially helpful if creating a hotel report.
[rb_bookings].[Vendor Address] + ' ' + [rb_bookings].[Vendor City] + ', ' + [rb_bookings].[Vendor State] + ' ' + [rb_bookings].[Vendor Zip]