An expression is a SQL injection which overrides the default output of a field in favor of whatever custom SQL is placed in the query.
Expressions can:
- Add, multiply, subtract, or divide two or more existing fields.
- Convert integers into ratios based on another numerator/denominator fields.
- Concatenate or add text to field output.
Expressions cannot:
- Create new ad-hoc fields.
Applied to the ‘Expressions’ box located in the cog on the Fields and Summary pages.
This box accepts simple code which allows us to perform different details needed for field data.
The expression will contain the bracketed table and bracketed field names and override the field data selected.
Functions available for use with Report Builder expressions:
- avg([Table].[Field]) produces the average of all Field values.
- cast([Table].[Field], AS DATATYPE) e.g., "cast([Table].[Field] as varchar)" Converts a value from one data type to another.
- count([Table].[Field]) produces a count of all Field values.
- count (Distinct [Table].[Field]) produces a count of all distinct Field values.
- datediff(part, [table].[begindate], [table].[enddate]) allows you to calculate the difference between two datetimes with a user selected level of granularity, for example the number of days, hours, or minutes between both input values.
- isnull([Table].[Field], x) checks to see if there is a null value in Field and replaces it with ‘x’
- len([Table].[Field]) returns the length of a string.
- max([Table].[Field]) produces the maximum Field value within a given range.
- min([Table].[Field]) produces the minimum Field value within a given range.
- round([Table].[Field], x) takes a decimal value and rounds it to x digits.
- sum([Table].[Field]) produces the sum of all Field values.
- case when ([Table].[Field]) > 10000 then 'valid' else 'invalid' end returns the text string valid if Field is greater than 10,000, and the text string invalid if Field is 10,000 or less.
Sample Expressions:
Example – Ticket number for air in one column and service fee in 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
Example – Convert Null result from field to blank.
ISNULL([rb_bookings].[original Document Number],' ')
Example - Cast UDID as money and multiply by duration
cast([rb_udid_85].[udid 85] as money) * [rb_bookings].[booking duration]
Example – Change Vendor Name based on Total Paid.
CASE WHEN ([rb_bookings].[total paid] = 13.00) then 'PRE PAYMENT' else [rb_bookings].[Vendor Name] end
Example – Place Invoice Number and Booking Transaction Type in the same field.
(CAST([rb_bookings].[Invoice Number] AS nchar) + (CAST([rb_bookings].[Transaction Type] AS nchar(1))))
Example –Add 15 % to Total Paid field.
- [rb_bookings].[total paid] *.15 + [rb_bookings].[total paid]
Example –Show Total Paid for fees only.
CASE WHEN [rb_bookings].[booking type] = 'Serv Fee' then [rb_bookings].[total paid] else ‘ ’ end
Example - add the zero(s) to Carrier Code and combine with document number.
CASE WHEN len([rb_Bookings].[Airline Number]) = 1 then '00'+ [rb_Bookings].[Airline Number] + [rb_Bookings].[Document Number] when len([rb_Bookings].[Airline Number]) = 2 then '0'+ [rb_Bookings].[Airline Number] + [rb_Bookings].[Document Number] else [rb_Bookings].[Airline Number] + [rb_Bookings].[Document Number] end
(RIGHT('000'+ CONVERT(VARCHAR(6), isnull([rb_Bookings].[Airline Number],'')),3) + left([rb_Bookings].[Document Number],10))
Field will show 0011234567899 for AA bookings 0161234567899 for UA bookings etc.
When using the airline code alone use this expression will make DL 006 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
To remove the dash at the end of the Document Number field
CAST([rb_bookings].[document number] as NCHAR(10))
To remove the dash at the end of the Document Number except when the ticket is Conjunctive.
CASE WHEN LEN([rb_bookings].[document number]) = 11 then CAST([rb_bookings].[document number] as NCHAR(10)) else [rb_bookings].[document number]
To add zeros in front of invoice number or use as template to add data in front of any field
('000' + (CAST([rb_bookings].[Invoice Number] AS nchar))
To show the first two characters of the field – VI45-XXX-1234
CAST([rb_bookings].[payment number] as NCHAR(2)) Value will return VI, AX, MC etc.
To only show the card number with 15 total characters that data looks like the example below.
SUBSTRING(LTRIM(RTRIM(CAST([rb_bookings].[payment number]as nchar))),4,15)
Example - Trip Type - One Way, Round Trip or Open Jaw.
CASE WHEN [rb_bookings].[booking type] = 'Air' AND LEFT([rb_bookings].[Routing],3) = RIGHT([rb_bookings].[Routing],3) THEN 'Round Trip' WHEN [rb_bookings].[booking type] = 'Air' AND LEFT([rb_bookings].[Routing],3) <> RIGHT([rb_bookings].[Routing],3) AND [rb_bookings].[start date] <> [rb_bookings].[end date] THEN 'Open Jaw' WHEN [rb_bookings].[booking type] = 'Air' AND LEFT([rb_bookings].[Routing],3) <> RIGHT([rb_bookings].[Routing],3) AND [rb_bookings].[start date] = [rb_bookings].[end date] THEN 'One Way' else 'OTHER' END
- Drop the expression above into the Routing or Booking Type field and label Trip Type.
Example: Translate codes in UDID to description
CASE WHEN [rb_Udid_22].[udid 22] = 'HC' then 'Passenger traveling with client staying at another hotel'
When [rb_Udid_22].[udid 22] = 'HL' then 'Lowest Rate was Obtained'
When [rb_Udid_22].[udid 22] = 'HN' then 'No Preferred in area'
When [rb_Udid_22].[udid 22] = 'HP' then 'Passenger Preference'
When [rb_Udid_22].[udid 22] = 'HS' then 'Preferred Sold Out'
When [rb_Udid_22].[udid 22] = 'YH' then 'Declined lower hotel rate due to location'
else [rb_Udid_22].[udid 22] end
Advance Purchase
If you want to use an expression instead of the arithmetic to show Advance Purchase. Add the issued date field rename it, change the function to days old and then add the following expression.
DATEDIFF(dd,[rb_bookings].[issued date],[rb_bookings].[start date])
The following expression is used for grouping the Adv. Purchase days together.
CASE WHEN (DATEDIFF(dd,[rb_bookings].[issued date],[rb_bookings].[start date]) <= 2) then '00 to 02'
WHEN (DATEDIFF(dd,[rb_bookings].[issued date],[rb_bookings].[start date]) = 3) OR (DATEDIFF(dd,[rb_bookings].[issued date],[rb_bookings].[start date]) < 7) then '03 to 06'
WHEN (DATEDIFF(dd,[rb_bookings].[issued date],[rb_bookings].[start date]) = 7) OR (DATEDIFF(dd,[rb_bookings].[issued date],[rb_bookings].[start date]) < 14) then '07 to 13'
WHEN (DATEDIFF(dd,[rb_bookings].[issued date],[rb_bookings].[start date]) = 14) OR (DATEDIFF(dd,[rb_bookings].[issued date],[rb_bookings].[start date]) < 21) then '14 to 20'
WHEN (DATEDIFF(dd,[rb_bookings].[issued date],[rb_bookings].[start date]) > 20) then '21+' else 'OTHER' end
To change dates field to numeric items and subtract them to show as a number and not date field.
Make the field a numeric field (e.g., invoice number), then enter the formula cast([rb_bookings].[issue date], as numeric) – cast([rb_bookings].[booking date], as numeric), and finally go back and change the field to a date field (e.g., invoice date). It kept the numeric format from invoice number and enabled the date calculation to display as a number.
Example - Shows count greater than number then number else 0
case when count([rb_bookings]. [0_LINK_bookingID]) >9 then count([rb_bookings].[0_LINK_bookingID]) else '0'end
Note: Add to the filter tab the field at the bottom of the drop down that shows the Calc field that was created. Filter field to be greater than 0
Agent vs Online in RB
First you will need to bring in the data sources Agent Bookings Table.
Then in the Fields, add Agent Interface ID
Add following expression:
CASE WHEN [rb_AgentBookings]. [Agent Interface ID] = 'CB' OR [rb_AgentBookings]. [Agent Interface ID] = 'ZZ’ OR [rb_AgentBookings]. [Agent Interface ID] = 'Z1' OR [rb_AgentBookings].[Agent Interface ID] = 'Z2’ then 'ONLINE' else 'AGENT' end
CASE WHEN [rb_bookings]. [Inside Agent] <> 'CONCUR RESERVATION’ THEN 'AAA AGENT' Else 'ONLINE' end
Show Reissued Bookings
CASE WHEN [rb_bookings].[ticket exchanged] = 'True' then 'Reissued' end
CASE WHEN [rb_bookings].[ticket exchanged] = 'True' then 0 else SUM([rb_segments].[segment mileage]) end
Date and time expressions
Typically, if you are not using an expression for date and time and exporting the report to excel or excel data only.
The cell data view will show the full date and time format and not just the date or time that was selected as seen in the screenshot below.
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)
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
Convert time to 24 HR - when exported to excel data only you will see the 24 hour time only
When exporting the report to excel data only the time is the only value seen
- 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)
The following expressions provide the 24-hour time – Hour only time.
- CONVERT(VARCHAR(2),[rb_segments].[departure date and time],114)
- CONVERT(VARCHAR(2),[rb_segments].[arrival date and time],114)
The following expressions provide the 24-hour time – Minute only time.
- SUBSTRING(CONVERT(VARCHAR(5),[rb_segments].[departure date and time],114),4,2)
- SUBSTRING(CONVERT(VARCHAR(5),[rb_segments].[arrival date and time],114),4,2)
Convert time to 12 HR - when exported to excel data only you will see the 12-hour time only
- CONVERT(varchar(15),CAST([rb_GDSsegments].[departure date and time] AS TIME),100)
- CONVERT(varchar(15),CAST([rb_GDSsegments].[arrival date and time] AS TIME),100)
To show the total paid once when using segments table
CASE WHEN [rb_Segments].[Segment leg ID] = 1 then [rb_bookings].[total paid] else 0 end
UDIDS
For Individual UDIDs 1 – 99 and left joining to the Bookings table.
When you add your field, you will need to include the following expression.
- case when [rb_udid_01].[udid 01] = ' (BLANK)' then '' else [rb_udid_01].[udid 01] end
Notice the key to the expression is the space between the single quote and parentheses that I have highlighted below.
The two single quotes after then replaces the (BLANK) with empty cells, you could put something like ‘Unknown’ between the two single quotes as an option.
To show UDIDs 1-99 and higher.
Data Source Tab
Locate rb_UDIDs Table from the drop down and left joining to the Bookings Table.
- Note: The rb_UDIDS table will provide all UDIDs that is available from BOS 1-999+.
Filters Tab
Locate from the rb_UDIDS Table, UDID Number field and select Isn’t Blank.
- This will only populate the UDIDs that contain data overall.
- Another option replace Isn’t Blank and select equals popup to check specific UDIDs needed for the report.
- Be sure to check the blank box provided for the field option that was selected. .
- Checking this box tells the report that you want to see data or blank values for this field
- By not checking the blank box the data will only return bookings where all UDIDS listed contain a value.
Fields Tab
Locate UDID Value from the UDIDs Table at the bottom of the drop down and use the following expression.
- CASE WHEN [rb_UDIDs].[UDID Number] = 03 then [rb_udids].[UDID Value] else '' end
To show Multiple UDIDs, keep selecting the UDID Value and change the number for the expression.
- CASE WHEN [rb_UDIDs].[UDID Number] = 115 then [rb_udids].[UDID Value] else '' end
UDIDS with numeric values
When UDIDs contain numeric/monetary data that will be summed or calculated within the report. Always locate a numeric or fare field to add the expression.
Examples of fare fields: Total Paid, Tax 1 Amount – Tax 4 Amount, Base Fare, GST Amount etc.
Fields Tab
Using a fare field to add a UDID expression that has been casted to a Money or Numeric value.
- Total Paid field, renamed Full Fare, used the following expression. (Blue Font)
- (CAST([RB_UDID_70].[UDID 70] AS MONEY))
- Total Paid field renamed Savings, used the following expression (Yellow Cell)
- (CAST([RB_UDID_70].[UDID 70] AS MONEY)) - (CAST([rb_bookings].[total paid] AS MONEY))
Checked Add Subtotal Box and save the report.
Show all UDIDS for the data
Go to the Fields table to select Add Pivot
- Select UDID number from the table and Group.
- Select UDID Value and Group or Maximum to provide the largest value – rename field UDID
- Select booking ID from the rb_UDIDs table and count, then open advanced cog to hide the field and select subtotal function = NONE.
Example of the report headers for the data.
Note: Removing the booking ID from the rb_UDIDs table the headers will look like this. 
CO2 Expression
Hotel
- [rb_bookings].[booking duration]*[rb_bookings].[Number of Units]*31.3
There is a carbon footprint industry benchmark, based on the methodology of the Hotel Carbon Measurement Initiative (HCMI), with the accepted standard for the hospitality industry being 31.1kg CO2 per room night.
Car
Car Rental CO2 Emissions - Car Rental emissions are calculated for each booking attached to a trip. The calculations are as follows:
[100 Miles/day] x [Car CO2 Index] = kg of CO2
|
Engine Size |
kg of CO2 per pkm |
|
Small <1.4 L (mini, compact, etc) |
0.14358 |
|
Medium 1.4 L – 2.0L |
0.17193 |
|
Large > 2.0 L (SUV, Sports, 4x4) |
0.21647 |
For Car:
Classification for Size of Car based on class
CASE WHEN CAST([rb_bookings].[class] as NCHAR(1))= 'M' or CAST([rb_bookings].[class] as NCHAR(1))= 'N' OR CAST([rb_bookings].[class] as NCHAR(1))= 'E' OR CAST([rb_bookings].[class] as NCHAR(1))= 'H' OR CAST([rb_bookings].[class] as NCHAR(1))= 'C' OR CAST([rb_bookings].[class] as NCHAR(1))= 'D' THEN 'Small' WHEN CAST([rb_bookings].[class] as NCHAR(1))= 'I' or CAST([rb_bookings].[class] as NCHAR(1))= 'J' OR CAST([rb_bookings].[class] as NCHAR(1))= 'S' OR CAST([rb_bookings].[class] as NCHAR(1))= 'R' OR CAST([rb_bookings].[class] as NCHAR(1))= 'F' OR CAST([rb_bookings].[class] as NCHAR(1))= 'G' THEN 'Medium' WHEN CAST([rb_bookings].[class] as NCHAR(1))= 'P' or CAST([rb_bookings].[class] as NCHAR(1))= 'U' OR CAST([rb_bookings].[class] as NCHAR(1))= 'L' OR CAST([rb_bookings].[class] as NCHAR(1))= 'W' OR CAST([rb_bookings].[class] as NCHAR(1))= 'O' OR CAST([rb_bookings].[class] as NCHAR(1))= 'X' THEN 'Large' end
CO2 Calc
CASE WHEN CAST([rb_bookings].[class] as NCHAR(1)) = 'M' or CAST([rb_bookings].[class] as NCHAR(1)) = 'N' OR CAST([rb_bookings].[class] as NCHAR(1)) = 'E' OR CAST([rb_bookings].[class] as NCHAR(1)) = 'H' OR CAST([rb_bookings].[class] as NCHAR(1)) = 'C' OR CAST([rb_bookings].[class] as NCHAR(1)) = 'D' THEN SUM([rb_bookings].[booking duration]*0.14358) WHEN CAST([rb_bookings].[class] as NCHAR(1)) = 'I' or CAST([rb_bookings].[class] as NCHAR(1)) = 'J' OR CAST([rb_bookings].[class] as NCHAR(1)) = 'S' OR CAST([rb_bookings].[class] as NCHAR(1)) = 'R' OR CAST([rb_bookings].[class] as NCHAR(1)) = 'F' OR CAST([rb_bookings].[class] as NCHAR(1)) = 'G' THEN SUM([rb_bookings].[booking duration]*0.17193) WHEN CAST([rb_bookings].[class] as NCHAR(1)) = 'P' or CAST([rb_bookings].[class] as NCHAR(1)) = 'U' OR CAST([rb_bookings].[class] as NCHAR(1)) = 'L' OR CAST([rb_bookings].[class] as NCHAR(1)) = 'W' OR CAST([rb_bookings].[class] as NCHAR(1)) = 'O' OR CAST([rb_bookings].[class] as NCHAR(1)) = 'X' THEN SUM([rb_bookings].[booking duration]*0.21647) end
If the car bookings do not contain the booking class that shows the type of car rented. Then use the following expression. [rb_bookings].[booking duration]*.38333
This is based on the following link and average emission for the year 4.6/12 = 0.38333
https://www.epa.gov/greenvehicles/greenhouse-gas-emissions-typical-passenger-vehicle
Air - Using Mileage
For the Calculation - Using the summary
CASE WHEN [rb_segments].[segment mileage] < 281 then sum([rb_segments].[segment mileage] *0.289) When [rb_segments].[segment mileage] > 994 then sum([rb_segments].[segment mileage] *0.1770) else sum([rb_segments].[segment mileage] *0.2028) end
For the Calculation - Using in the detail
CASE WHEN [rb_segments].[segment mileage] < 281 then ([rb_segments].[segment mileage] *0.289) When [rb_segments].[segment mileage] > 994 then ([rb_segments].[segment mileage] *0.1770) else ([rb_segments].[segment mileage] *0.2028) end
For the Separation
CASE WHEN [rb_segments].[segment mileage] < 281 then 'Short Haul' When [rb_segments].[segment mileage] > 994 then 'Long Haul' else 'Medium Haul' end
Using Kilometers
([rb_segments].[segment mileage]*1.60934)
COST PER Kilometers
CAST([rb_segments].[segment fare] as NUMERIC)/CAST(([rb_segments].[segment mileage]*1.60934)as NUMERIC)
For the Calculation - Using a summary
CASE WHEN ([rb_segments].[segment mileage]*1.60934) < 281 then sum(([rb_segments].[segment mileage]*1.60934)*0.289) When ([rb_segments].[segment mileage]*1.60934) > 994 then sum(([rb_segments].[segment mileage]*1.60934)*0.1770) else sum(([rb_segments].[segment mileage]*1.60934)*0.2028) end
For the Calculation - including in report detail
CASE WHEN ([rb_segments].[segment mileage]*1.60934) < 281 then (([rb_segments].[segment mileage]*1.60934)*0.289) When ([rb_segments].[segment mileage]*1.60934) > 994 then (([rb_segments].[segment mileage]*1.60934)*0.1770) else (([rb_segments].[segment mileage]*1.60934)*0.2028) end
For the Separation
CASE WHEN ([rb_segments].[segment mileage]*1.60934) < 281 then 'Short Haul' When ([rb_segments].[segment mileage]*1.60934) > 994 then 'Long Haul' else 'Medium Haul' end