Here’s another quick tip for setting up Drillbridge tokens. I recently created a Drillbridge report that needs to map a member from the Cost Centers dimension over to multiple columns in a relational database table. For example, a cost center may have the format 111-222-333-44, and the code 222 corresponds to a column in a table, 333 corresponds to a column in a table, and 44 corresponds to a column in a table (these are all in the same table, by the way).

Because the incoming member on the POV comes across as a Java string, we can perform normal Java string operations on it. In this case we can easily extract the “222” by doing the following (assuming the cost center dimension is named “Cost Centers”:

#Cost_Centers.substring(4, 7)

Recall that in Java, string character offsets start at 0, and that the substring we extract does not include the character at the ending offset (this is all spelled out nicely in the Java String documentation). So effectively, in the above function we are saying “give me the substring of the #Cost_Centers variable from characters 4 through 7 (but not including 7).

A problem with this approach is that members with fewer characters could potentially cause a StringIndexOutOfBounds Java exception. For example, a level one member or parent to our cost center might be “Other”. Therefore when the substring method is called on “Other”, we are asking Java to give us characters that do not exist, and an exception is thrown.

I thought for awhile on what the best way to handle this is. There are a lot of ways I could go in terms of the code, but I decided that the best approach is one that won’t really affect the Drillbridge code much at all right now.

The decision to use a full-fledged expression language as the basis of Drillbridge token expressions has turned out to be quite fortuitous, and one of the things it affords us inside of Drillbridge expressions is the ability to write complex scripts that can handle this situation for us. One such way to guard against member names that are too short is to use the ternary operator.

Many programming languages support the ternary operator. It’s a compact way of representing an “if-else” construct. For example, consider this simple code:

if (stopped) {
    return "Red";
} else {
    return "Green";
}

In this case, the “stopped” variable is a boolean variable that is always one of either true or false. If the variable is true, our function returns the text “Red” and if the variable is false, our function returns the value “Green” (assume that the if block is contained within a function that returns a String value.

This construct is so prevalent in computer programming that many languages support a way to compactly write this, using the ternary operator:

String colorText = stopped ? "Red" : "Green";

In the above example, the variable stopped gets evaluated and if true, the whole expression will evaluate to “Red” and if false, it’s “Green”. Then the String variable named colorText will get the value.

We can use this exact same construct inside of a Drillbridge token expression, thanks to the powerful expression language being used. Now consider this enhanced query example:

SELECT * FROM TRANSACTIONS
WHERE
    SEGMENT2 = '{{
        "name":"Cost Centers", "expression" : "#Cost_Centers.length() >= 13 ? #Cost_Centers.substring(4, 7) : '~~ Cost Center name not long enough to parse ~~'", "sampleValue":"111-222-333-44"
    }}'

Now we are checking the value of an expression first – checking the length of the incoming cost center. If it is at least 13 characters, then the return value of of the expression is safe to be figured out using the substring method. If the member fails the length test, then we return an arbitrary string, which in this case we have determined cannot match anything at all in the database, and therefore return nothing.

This little trick can be used in situations where we need to parse out sub-strings from member names but might have some members that don’t fit the format we need. Also, thanks to Drillbridge’s inventive handling processing of multiple members with the drill-to-bottom feature, we can also use this to effectively filter out members from a list of members, such that we keep the ones we want and discard any others that we don’t need (for example, it’s not uncommon to have “non-conforming” member names for input members).