I recently helped a customer with their Drillbridge installation/configuration for PBCS that had an interesting time period conversion issue I wanted to write about.

Drillbridge helps convert a given POV into a SQL query, webpage link, MDX query, or whatever you want (such as with a custom plugin). Out of the box, Drillbridge contains a number of commonly-used convenience functions for easily converting months to numbers (as well as other functions). You can do this in SQL too but it seems to almost always be a little “cleaner” to let Drillbridge do it for you, especially when it comes to upper-level drill-through.

Interestingly enough, a client has an interesting but not incredibly uncommon fiscal calendar where February is actually period 1,  March is 2, and so on. In this calendar, January is actually period 12. But the Drillbridge calendar conversion functions usually return the common month numbers. What to do? Just adjust the expression a little to check for January specifically, otherwise convert the month and subtract one. For example:

SELECT 1 WHERE FROM DUAL WHERE
PERIOD IN ({{"name":"Period","expression":"#Period == 'Jan' ? 12 : #monthAbbreviationToDigit(#Period) - 1","drillToBottom":true,"sampleValue":"Q1","quoteMembers":false,"suppressParentheses":true,"overflow":"","overflowAt":0,"flags":""}})

There are a few variant methods to handle this, but this one is pretty straightforward and clean. This token actually also handles upper level drill (such as from member Q1, Q2, and so on), so the query predicate to use is a SQL IN clause, to accommodate multiple values.

Now when we drill on member January, we get this test query:

SELECT 1 WHERE FROM DUAL WHERE
PERIOD IN (12)

And if we drill on Q1, for example, we get this:

SELECT 1 WHERE FROM DUAL WHERE
PERIOD IN (1, 2, 3)

You might have been expecting to see 12, 1, 2 there but it’s actually right since Q1 contains February, March, and April – so everything is mapping as expected.

I’ve been pretty happy over the years with how the original Drillbridge expression/token concept has been able to accommodate some tricky use cases, although this one is relatively straightforward. It’s also nice to be able to write a bit of a “pure” query that doesn’t have to join against a calendar table just to get the right dates. This is just one of the things that makes Drillbridge, in my opinion, a true turnkey drill-through solution.