Report C# filter expressions

Report C# filter expressions are added by the FBO One support team only. Contact support@amsterdamsoftware.com for guidance.

C# filter expressions can be used to apply additional filtering on top of the basic report filters that can be selected when downloading a report. You can create expressions to which each column in the report must match. The advantage of using these expressions is that you do not have to manually filter in the report anymore, the file size is reduced so report generation is much quicker.

A C# filter expression is an expression that determines if the given 'row' object (report column) should be included in the output of the report. Make sure that the columns used in the expression are included in the report query columns.

Contents:

Filter expression inputs

  1. row["Column name"]. Filter on the column name for the query, the column must exist and match exactly as shown in the query.

  2. row[“Custom property name“]. Filter on the custom property for the query, the custom property must be added to the query as a new column and match exactly as shown.

Filter expression examples

Below are some examples of Report C# filter expressions:

C# filter expression to only include certain values from a column in the report, for example only certain services/products. Note some examples may be used across different queries if the column exists.

To filter on a group of values in a specific column, this is the general format:

All column values must be written in lower case.

1 2 3 4 row["Column Name"] != null && (row["Column Name"].ToString().ToLower() == "column value 1" || row["Column Name"].ToString().ToLower() == "column value 2" || row["Column Name"].ToString().ToLower() == "column value 3")
To include orders at specific Handling Stations only
1 2 row["HandlingStationIcaoCode"] != null && (row["HandlingStationIcaoCode"].ToString().ToLower() == "eddm")
Excludes orders with order fare type "scheduled" or “confirmed”, excludes secondary orders, and excludes orders with operator set as Netjets EU or Netjets USA.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 ((Func<bool>)(() => { var state = (string)row["OrderState"]; var flightType = (string)row["BGR Flight Type"]; var operatorShortName = (string)row["OperatorShortName"]; // Order must be requested or confirmed var isMatch = string.Equals(state, "requested", StringComparison.InvariantCultureIgnoreCase) || string.Equals(state, "confirmed", StringComparison.InvariantCultureIgnoreCase); if (isMatch) // And not a scheduled flight isMatch = !string.Equals(flightType, "scheduled", StringComparison.InvariantCultureIgnoreCase); if (isMatch) // And a primary order isMatch = row["Secondary order number"] == null; if (isMatch) // And not a wiggins or fedex isMatch = !string.Equals(operatorShortName, "wigginsairways", StringComparison.InvariantCultureIgnoreCase) || !string.Equals(operatorShortName, "fedex corp. - feeder", StringComparison.InvariantCultureIgnoreCase); return isMatch; }))();
Exclude split orders if the "secondary order number' is not referenced in the report query (Crew/Pax query) e.g AMS-509-2
1 2 3 4 5 6 7 8 9 10 11 ((Func<bool>)(() => { bool includeInReport = true; if (row["Order Key"] != null) { string orderKey = row["Order Key"].ToString(); bool hasSecondaryNumber = orderKey.Count(s => s == '-') > 1; includeInReport = !hasSecondaryNumber; } return includeInReport; }))();
Includes only flights with aircraft MTOW Kg is equal to or above 45500 kgs
1 2 3 4 5 ((Func<bool>)(() => { return ((int?)row["MTOW Kg"]).HasValue && ((int?)row["MTOW Kg"]).Value >= 45500; } ))();
Exclude split order (AMS-502-2) & to exclude any orders for A/C MTOW =< 2000kgs
1 2 3 4 5 6 row["Secondary order number"] == null && ((Func<bool>)(() => { return ((int?)row["MTOW Kg"]).HasValue && ((int?)row["MTOW Kg"]).Value >= 2001; } ))();
Exclude flights with Flight Type 'Non-Handled'
1 2 row["Flight type"] != null && !(row["Flight type"].ToString().ToLower() == "non-handled")

Audit query

Include audit lines from the Payment table only that starts with “Payment” text
1 2 3 4 5 row["Master table"] != null && row["Master table"].ToString() == "Order" && row["Action"].ToString() == "Remark" && row["Table"] != null && row["Table"].ToString() == "Payment" && row["Description"] != null && row["Description"].ToString().StartsWith("Payment ")
Include audit lines from the InvoicePdf table to show created invoices
1 2 3 row["Master table"] != null && row["Master table"].ToString() == "Invoice" && row["Table"] != null && row["Table"].ToString() == "InvoicePdf" && row["Action"] != null && row["Action"].ToString() == "Insert"
Include audit lines showing workflow changes
1 2 row["Description"] != null && (row["Description"].ToString().ToLower() == "workflow state changed from 'yu lounge | yu lounge' to 'yu lounge | cancelled'")
Include audit lines showing deleted products from an order
1 2 3 4 row["Master table"] != null && row["Action"] != null && row["Action"].ToString() == "Delete" && row["Master table in db"] != null && row["Master table in db"].ToString() == "OrderLine" && row["Column"] != null && row["Column"].ToString() == "Parent order"
Include audit lines for price agreements
1 2 (string)row["Table in db"]=="PriceAgreement" && (string)row["Table in db"] == (string)row["Master table in db"]
Include audit lines for contact changes
1 ((string)row["Table"]).ToLower() == "contact" && ((string)row["Action"]).ToLower() != "remark"
Include audit lines for contract changes
1 2 3 4 5 6 7 8 9 ((string)row["Table in db"]=="Contract" && (string)row["Master table in db"]!="Contract" && (string)row["Column in db"]!="ProductGroupID") || (string)row["Table in db"]=="Credit" || (string)row["Table in db"]=="CreditType" || (string)row["Table in db"]=="Fuel contract" || (string)row["Table in db"]=="Fuel contract operator" || (string)row["Table in db"]=="Fuel contract VAT payment" && (string)row["Table in db"] == (string)row["Master table in db"]
Include audit lines for manual unit price override per borderline
1 2 3 4 5 6 ((Func<bool>)(() => { var isUnitPriceChanged = false; var isOrderLine = (string)row["Master table in db"] == "OrderLine"; if (isOrderLine) isUnitPriceChanged = ((string)row["Description"]).StartsWith("Unit price changed from "); return isOrderLine && isUnitPriceChanged; } ))();

Aircraft types query

Show aircraft types equal and below 100000 MTOW kg
1 2 3 4 ((Func<bool>)(() => { return ((int?)row["MTOW Kg"]).HasValue && ((int?)row["MTOW Kg"]).Value <= 100000; } ))();

Aircraft registrations query

Show default debtors only for aircraft registrations
1 row["Debtors"] != null
Show default operators, excluding 'unknown operator'
1 2 row["Operators"] != null && (row["Operators"].ToString().ToLower() == "unknown operator")

BulletinBoardMessages query

To show/hide Expired or past review date messages, and to exclude merged contact messages appearing on the bulletin board
1 2 3 row["Is expired"].ToString().ToLower() == "true" || row["Is review date past due"].ToString().ToLower() == "true" || row["Message text"].ToString().ToLower().Contains("was merged into")
To show messages that have an expiry or review date
1 2 row["Expiration date"] != null || row["Review date"] != null

Contacts query

Include only contacts that are debtors
1 row["Is debtor"] != null && row["Is debtor"].ToString().ToLower() == "true"
Include only contacts that are marked as debtors or operators
1 2 3 4 row["Name"] != null && (row["Is organization"].ToString().ToLower() == "true") && (row["Is trip support provider"].ToString().ToLower() == "false") && (row["Is supplier"].ToString().ToLower() == "false")
Include supplier contacts that are based in a certain country
1 2 row["IsSupplier"] != null && (row["IsSupplier"].ToString().ToLower() == "true") && (row["CountryCode"].ToString().ToLower() == "united states of america (us)")

Movements query

Show network missed opportunities movements
1 2 3 4 5 6 7 8 9 10 11 12 13 row["Aircraft registration code"] != null && !(row["Flight type"].ToString().ToLower() == "cargo") && ((Func<bool>)(() => { // FBO Locations var fboNetwork = new string[] { "EDDK", "EDDS", "EDDL", "EDDF", "EDDB", "EDDM", "EDDH", "EDDP", "EDFE", "EDDT"}; var handlingStationIcao = ((row["Handling ICAO"] as string) ?? "").ToUpper(); var destinationIcao = ((row["From/To ICAO"] as string) ?? "-").ToUpper(); var isFboNetwork = fboNetwork.Contains(handlingStationIcao) && fboNetwork.Contains(destinationIcao); bool showInReport = isFboNetwork; return showInReport; }))();
Exclude movements with flight type ‘Non-handled’
1 2 row["Flight type"] != null && !(row["Flight type"].ToString().ToLower() == "non-handled")
Exclude various cancelled workflow states e.g OTC, Fuel Uplift
1 2 3 4 5 6 7 8 9 10 11 12 ((Func<bool>)(() => { var statesExcluded = new System.Collections.Generic.List<string>(); statesExcluded.Add("otc order | cancelled"); statesExcluded.Add("fuel uplift only | Cancelled"); string state = ((string)row["OrderState"]).ToLower(); bool showOrder = true; if (statesExcluded.IndexOf(state) >= 0) showOrder = false; return showOrder; } ))();
Show only movements for a certain handling station
1 2 row["Handling ICAO"] != null && (row["Handling ICAO"].ToString() == "EDDM")
Exclude movements with 0 pax count
1 2 row["Pax Count"] != null && (row["Pax Count"].ToString().ToLower() != "0")
Exclude movements from a given list of countries e.g Schengen area
1 2 3 4 5 6 7 8 9 10 ((Func<bool>)(() => { // Schengen countries var countriesGroup = new string[] { "Austria", "Belgium", "Czech Republic", "Denmark", "Estonia", "Finland", "France", "Germany", "Greece", "Hungary", "Iceland", "Italy", "Latvia", "Lithuania", "Luxembourg", "Malta", "Netherlands", "Norway", "Poland", "Portugal", "Portugal | Acores", "Slovakia", "Slovenia", "Spain", "Sweden", "Switzerland", }; var fromToCountry = (row["From/To country name"] as string) ?? ""; var isSchengenCountry = countriesGroup.Any(c => c == fromToCountry); bool showInReport = !isSchengenCountry; return showInReport; }))();

Orders query

Show canceled orders only
1 2 3 4 5 6 7 8 ((Func<bool>)(() => { bool showOrder = false; var registrationCode = (row["RegistrationCode"] as string) ?? ""; var orderState = (row["OrderState"] as string)?.ToLower() ?? ""; if ( registrationCode != "" && (orderState == "cancelled" || orderState== "cancelled (home operator)")) showOrder = true; return showOrder; } ))();
Exclude quote orders
1 2 row["OrderState"] != null && (row["OrderState"].ToString().ToLower() != "quote")
Only include orders in a certain state, for example, orders that are invoiced.

OrderState must be written in lower case.

1 2 3 4 5 6 7 8 9 10 11 ((Func<bool>)(() => { bool showOrder = false; if (row["OrderState"] != null && ((string)row["OrderState"]).ToLower().Contains("invoiced")) showOrder = true; return showOrder; } ))();
Exclude orders in a certain state, for example, orders that are invoiced.

OrderState must be written in lower case.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 ((Func<bool>)(() => { var statesExcluded = new System.Collections.Generic.List<string>(); statesExcluded.Add("x"); statesExcluded.Add("y"); statesExcluded.Add("z"); string state = ((string)row["OrderState"]).ToLower(); bool showOrder = true; if (statesExcluded.IndexOf(state) >= 0) showOrder = false; return showOrder; } ))();
Only return orders in a specified time frame

Where the DateTime format is YYYY, MM, DD, HH, MM, SS

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 ((Func<bool>)(() => { bool showOrder = false; DateTime? orderDate = (DateTime?)row["MostActualArrivalDateTimeLT"]; if (orderDate.HasValue) { DateTime? from = new DateTime(2019, 12, 01, 0, 0, 0); DateTime? to = new DateTime(2019, 12, 31, 0, 0, 0); showOrder = from <= orderDate && orderDate <= to; } return showOrder; }))();
Show aircraft that are still on the ground (no ATD) - filtering on front office order type (to exclude sub-orders)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 ((Func<bool>)(() => { bool showOrder = false; DateTime? hasATD = (DateTime?)row["AtdDateTime"]; DateTime? hasATA = (DateTime?)row["AtaDateTime"]; if (row["OrderState"] != null && hasATD == null && hasATA != null && (row["FrontOfficeWorkflowName"].ToString().ToLower() == "parking order" || row["FrontOfficeWorkflowName"].ToString().ToLower() == "handling order")) showOrder = true; return showOrder; } ))();

Parking history query

Include certain debtor and parking areas/positions only
1 2 3 4 row["Debtor Name"] != null && (row["Debtor Name"].ToString().ToLower() == "valljet (c04000541)") && row["Parking Area"] != null && (row["Parking Area"].ToString().ToLower() == "aston hangar" || row["Parking Area"].ToString().ToLower() == "astonsky parking")

Parking Planning query

Include certain parking positions only
1 2 3 4 5 6 row["Position"] != null && (row["Position"].ToString().ToLower() == "e31" || row["Position"].ToString().ToLower() == "e32" || row["Position"].ToString().ToLower() == "e33" || row["Position"].ToString().ToLower() == "e34" || row["Position"].ToString().ToLower() == "e35")

Payments query

Include payments in a certain state (online payments)
1 row["PaymentState"] != null && (row["PaymentState"].ToString().ToLower() == "batchsettled")
Include certain forms of payment only
1 2 3 4 row["Payment FOP"] != null && (row["Payment FOP"].ToString().ToLower() == "cc amex" || row["Payment FOP"].ToString().ToLower() == "cc mastercard" || row["Payment FOP"].ToString().ToLower() == "cc visa")

Pax Crew query

Include primary orders only for pax crew
1 2 3 4 5 6 7 8 ((Func<bool>)(() => { bool includeInReport = true; if (row["Order Key"] != null) { string orderKey = row["Order Key"].ToString(); bool hasSecondaryNumber = orderKey.Count(s => s == '-') > 1; includeInReport = !hasSecondaryNumber; } return includeInReport; }))();
Include arrival pax crew only
1 ((bool)row["Is arrival"] == true)
Include departure pax crew only
1 ((bool)row["Is arrival"] == false)

Price Agreements query

Include price agreements for certain products
1 2 3 4 5 6 row["Product code"] != null && (row["Product code"].ToString().ToLower() == "fuel jet-a1 (posted price)" || row["Product code"].ToString().ToLower() == "fuel jet-a1 discount" || row["Product code"].ToString().ToLower() == "fuel avgas (posted price)" || row["Product code"].ToString().ToLower() == "fuel avgas discount" || row["Product code"].ToString().ToLower() == "defuelling" )

Product data summary query

Include products that are active only
1 (bool)row["Is active"] == true

ReceiptLine query

For ReceiptLine report query: includes only services where the LineItemDescription contains a given text(s) e.g "mineral oil"
1 2 3 4 5 6 7 8 9 10 11 12 13 14 ((Func<bool>)(() => { bool showOrder = false; if (row["LineItemDescription"] != null && ((string)row["LineItemDescription"]).ToLower().Contains("mineral oil") || ((string)row["LineItemDescription"]).ToLower().Contains("jet a1 uplift | ticket") || ((string)row["LineItemDescription"]).ToLower().Contains("jet a1 defuel | ticket") || ((string)row["LineItemDescription"]).ToLower().Contains("avgas uplift | ticket")) showOrder = true; return showOrder; } ))();

ReceiptPaymentLine query

Hide aborted online payments from the list of payments
1 2 3 4 5 6 ((Func<bool>)(() => { bool showOrder = true; if (row["Payment receipt"] != null && ((string)row["Payment receipt"]).ToLower().Contains("aborted")) showOrder = false; return showOrder; } ))();

Role authorizations query

Exclude 'NA' role and show only entity commands
1 2 row["Role code"] != "NA" && row["Command type"].ToString().ToLower() == "entity command"

Running stock query

Exclude ‘Stock location header’, ‘End balance’, ‘Start balance’ from running stock
1 2 3 row["Row type display name"].ToString() != "Stock location header" && row["Row type display name"].ToString() != "End balance" && row["Row type display name"].ToString() != "Start balance"
Include running stock mutations that affect physical stock only
1 row["AffectsPhysicalStock"].ToString().ToLower() =="true"

Services query

Include services that have a quantity that is not 0
1 ((decimal?)row["Quantity"]).HasValue && ((decimal?)row["Quantity"]).Value != 0
Include only services that have a supplier order number
1 !string.IsNullOrEmpty(row["Supplier order number"].ToString())
Include services that are in a registered or completed service workflow state
1 2 3 4 5 6 7 8 9 10 ((Func<bool>)(() => { var state = (string)row["Order State"]; if (state == null) return false; else { state = state.ToLower(); return state == "registered" || state == "approved" || state == "completed"; } } ))();
Include services that have a delivery date within the next 5 hours from now
1 2 3 4 5 6 7 row["Product"] != null && (row["Product"].ToString().ToLower() == "aasi - trans cdg)" || row["Product"].ToString().ToLower() == "aasi - trans lbg" || row["Product"].ToString().ToLower() == "aasi - crew transfer" || row["Product"].ToString().ToLower() == "aasi - pax tranfer" || row["Product"].ToString().ToLower() == "aasi - limo booking") && (row["Delivery DateTime LT"] != null && ((DateTime)row["Delivery DateTime LT"]).Date == DateTime.UtcNow.AddHours(+5).Date)
Exclude services that have a supplier invoice
1 row["SupplierInvoiceID"] != null
Include services that have a certain VAT invoice code
1 2 row["VatInvoiceCode"] != null && (row["VatInvoiceCode"].ToString().ToLower() == "rev")
Filters only on services where the Amount Incl. VAT is equal to zero
1 2 3 4 5 6 ((Func<bool>)(() => { var amountInclVat = (decimal?)row["Amount Incl. VAT"]; return amountInclVat.HasValue && amountInclVat.Value == 0M; } ))();
Selects all rows where the Product column contains PRODUCT CODE 1, PRODUCT CODE 2, or PRODUCT CODE 3.

All product codes must be between quotation marks " ". All product codes must be written in lower case

1 2 3 4 row["Product"] != null  && (row["Product"].ToString().ToLower() == "product code 1"  || row["Product"].ToString().ToLower() == "product code 2"  || row["Product"].ToString().ToLower() == "product code 3")
Include certain services/products and exclude services with amount 0.

To Include multiple products based on product code whilst excluding services with zero price. Note: All product codes must be written in lower case.

1 2 3 4 5 6 7 8 ((Func<bool>)(() => { var amountInclVat = (decimal?)row["Amount Incl. VAT"]; var product = row["Product"]?.ToString()?.ToLower(); return amountInclVat.HasValue && amountInclVat.Value != 0M && product != null && (product == "jet a retail (usa)" || product == "jet a cost plus (usa)" || product == "jet a fixed price (usa)"); } ))();
Exclude certain services/products.

To exclude multiple products that contain similar text (for example an FBO Location prefix) only filling in that text will suffice to exclude all the products that contain that text. All product codes must be written in lower case.

1 2 3 4 row["Product"] != null && !(row["Product"].ToString().ToLower() == "product code 1" || row["Product"].ToString().ToLower() == "product code 2" || row["Product"].ToString().ToLower() == "product code 3")

Stock mutations query

Include mutations where excise tax is applicable
1 2 row["Is excise applicable"] != null && (row["Is excise applicable"].ToString().ToLower() == "true")
Include uplift or purchase mutations only
1 2 3 row["Stock Mutation Type Code"] != null && (row["Stock Mutation Type Code"].ToString().ToLower() == "u" || row["Stock Mutation Type Code"].ToString().ToLower() == "p")
Include uplift or defuel mutations only for jet fuel for a list of debtors
1 2 3 4 5 6 7 8 9 10 row["Stock mutation type name"] != null && (row["Stock mutation type name"].ToString().ToLower() == "uplift" || row["Stock mutation type name"].ToString().ToLower() == "defuel") && (row["Sku"].ToString().ToLower() == "jet a1 - splo") && (row["Debtor short name"].ToString().ToLower() != "total (660727)") && (row["Debtor short name"].ToString().ToLower() != "bp rmo building / afd. air bp (680098)") && (row["Debtor short name"].ToString().ToLower() != "klm e&m - klm fleet fuel (777001)") && (row["Debtor short name"].ToString().ToLower() != "transavia fuel (777007)") && (row["Debtor short name"].ToString().ToLower() != "klm e&m motorshop (777005)") && (row["Debtor short name"].ToString().ToLower() != "klm (610094)")
Include uplift mutations that are throughput only
1 2 3 row["Stock mutation type code"] != null && row["Stock mutation type code"].ToString().ToLower() == "u" && row["Is throughput"].ToString().ToLower() == "false"
Include non-quote stock mutations only
1 2 3 4 5 6 7 8 9 ((Func<bool>)(() => { var statesExcluded = new System.Collections.Generic.List<string>(); statesExcluded.Add("quote"); string state = ((string)row["OrderState"]).ToLower(); bool showOrder = true; if (statesExcluded.IndexOf(state) >= 0) showOrder = false; return showOrder; } ))();
Include stock mutations that affect physical stock only
1 row["AffectsPhysicalStock"].ToString().ToLower() =="true"

User roles query

Include active users only
1 row["Is active"].ToString().ToLower() == "true"

Custom value

Include only Flights with a specific Order Custom value (E.g Custom Value 'Terminal Location', set to GAT
1 2 row["Terminal location"] != null && (row["Terminal location"].ToString().ToLower() == "gat")
Exclude flights with Custom Value Terminal Location|GAT & Flight Type "Non-Handled"
1 2 3 4 row["Terminal location"] != null && (row["Terminal location"].ToString().ToLower() == "gat") && row["Flight type"] != null && !(row["Flight type"].ToString().ToLower() == "non-handled")
Include Orders with a particular Custom Value set to blank (Custom Value 'Airport Fee - Direct Bill')
1 row["Airport Fee - Direct Bill"] == null
Include aircraft registrations that have Apron / Hangar contract custom value
1 2 3 4 5 row["Apron / Hangar Contract"] != null && (row["Apron / Hangar Contract"].ToString().ToLower() == "yes - apron & hangar parking contract" || row["Apron / Hangar Contract"].ToString().ToLower() == "yes - hangar parking only" || row["Apron / Hangar Contract"].ToString().ToLower() == "yes - apron parking only" || row["Apron / Hangar Contract"].ToString().ToLower() == "expired contract")
Include Orders that have 0 prior visits to the FBO location

See How to show a Prior Visit Count on Orders (per Registration)

1 2 3 4 row["Prior Visits"] != null && (row["Prior Visits"].ToString().ToLower() == "0") && row["Front office workflow name"] != null && (row["Front office workflow name"].ToString().ToLower() == "handling order")