Paging

This section discusses paging in Drillbridge versions 1.3.3 and later. Earlier versions of Drillbridge included a paging feature that has now been deprecated. Documentation for old-style paging can be found here, although you are encouraged not to use this old-style of paging and it will only work on Drillbridge versions earlier than 1.3.3.

As of Drillbridge 1.3.3, paging has made a comeback. Paging is implemented in a server-side manner. This means that the client will load the current results from the query, and if the next page/previous page button is clicked, a new SQL request will be made to the relational database in order to retrieve the relevant results. This is in contrast to earlier versions of Drillbridge that just pull back the entire result set. Implementing server-side paging is straightforward but requires a few steps.

Setting up paging

There are three steps needed to implement paging

  1. Turn on the "Enable paging" option on the report administration page

  2. Enter a row-counting query for the report

  3. Rework the main query to include the two paging tokens

Row counting query

In order to implement paging, Drillbridge requires that the exact number of rows for a query be known. This allows the Drillbridge interface to determine how many pages of data there are. Rather than attempt to guess how many rows there are in the result set, Drillbridge reports with paging enabled require what’s known as a "row counting query". The row counting query is syntactically very similar to the main query, but typically is modified to be a variant of the main query using COUNT() instead of the normal items in the SELECT portion of the query. The SELECT COUNT() query is executed on the initial drill request for a report with paging enabled. The value is then cached for future pages so that the user doesn’t have to wait for the query to run (the SELECT COUNT(*) query may take some time to execute).

Rework the main query to include the two paging tokens

Reports with paging enabled must include two additional tokens in order to work: %%OFFSET%% and %%LIMIT%%. The offset token is the row offset to start with. This will be 0, 10, 20, or some multiple of the current page size. The limit token is the number of rows to return. A simplistic implementation of the offset/limit in a MySQL query would look like this:

SELECT * FROM Transactions LIMIT %%OFFSET%%, %%LIMIT%%

Note that the LIMIT syntax is part of MySQL. Other database variants such as Microsoft SQL Server and Oracle have different implementations for getting certain rows. Lastly, do note that the MySQL LIMIT syntax, as indicated above, is simplistic: it can have performance issues in larger result sets because it has to scan all of the rows prior to the offset row. There are advanced ways of writing the query (that would still utilize the offset/limit tokens) that can perform better.

Smart Formatting

Drillbridge reports have a Smart Formatting feature that can be turned on and off. If turned off, the data in a Drillbridge table does not receive any formatting beyond having its value pulled from the database (this is the default option).

If Smart Formatting is turned on, Drillbridge will inspect the types of data (the columns) that are returned from the SQL query and attempt to format them to make their presentation nicer. This formatting applies to the following types of columns:

  • Date (DATE)

  • Time (TIME)

  • Timestamp (TIMESTAMP)

  • Integer (BIGINT, INTEGER, SMALLINT)

  • Decimals (DECIMAL, NUMERIC, DOUBLE, FLOAT)

Most notably, Drillbridge will not attempt to do any formatting to text-based columns such as the following:

  • Character (VARCHAR, NVARCHAR, CHAR, NCHAR)

  • Text (TEXT, CLOB)

  • Other types (BLOB, TINYINT, BIT)

Additionally, Drillbridge will take into consideration the locale of the user and try to format according to that. The locale is determined by the Accept-Language header submitted by the web browser.

Date Formatting

Dates are formatted using the "SHORT" formatting type in Java, taking the current locale into consideration. For example, in English the date August 5th, 2014 is formatted as 8/5/14. In French this is 05/08/14.

Time Formatting

Standard formatting in English (US) would look like: 3:53 PM. In French this would appear as 15:53.

Timestamp Formatting

Timestamp formatting is a combination of Date and Time formatting (the date format has a space and the formatted time appended to it), such as 05/08/14 15:53.

Integer & Decimal Formatting

The precision and scale attributes of a numerical column will be inspected in order to determine how they should be formatted. Let’s consider a column with a type of DECIMAL(13, 2). In SQL parlance this means that the values can have 13 digits and 2 of those are to the right of the decimal. This can be used to represent typical currency values.

We’ll say that the database value for a particular row is 1234567.1234. With Smart Formatting turned on, with an English web browser, this will output as:

1,234,567.12

In a French locale, the output is:

1 234 567,12

Note the different grouping (thousands) and decimal separators.

Circumventing Formatting

Drillbridge will not attempt to do any formatting on columns that have a character-based type. If you need to use Smart Formatting but want to exclude a column from getting formatted, then find a way to cast or otherwise convert it to a string. For example, if you have a year value stored in an integer-based column that represents the year and turn Smart Formatting on in the French locale, the output would be:

2 014

Note that a space is the thousands or grouping separator in the French locale. For U.S. English, Smart Formatting would convert the above value to the following:

2,014

Either way, it’s unlikely that this is the desired result. So to use Smart Formatting in the report but not have it apply to this column, we just need to cast it to a character using something like the following:

SELECT TO_CHAR(AMOUNT) FROM DUAL

When Drillbridge interprets the SQL results, AMOUNT will appear as a VARCHAR instead of some integer type, and spaces/commas will not be added even with Smart Formatting turned on.

Other Quirks

Note that using ROUND() to round to a certain number of decimals does not affect the scale that Drillbridge analyzes from the column. For example, if you have currency values stored in a column with a definition of DECIMAL(13, 4) such that there are four places stored after the decimal but want Drillbridge to just show two, then you would want to cast the value rather than round it.

SELECT CAST(AMOUNT AS DECIMAL(13, 2)) AS AMOUNT FROM DUAL

Custom Stylesheet/HTML Tips

Drillbridge allows custom styling in a few different ways. It is possible to specify a global stylesheet that affects all reports, as well as report-specific options including stylesheets, header, and footer.

Global Stylesheet

The global stylesheet server setting will be added to every report that is run from the server. This allows for easily customizing the appearance of every report.

CSS "Hooks"

Drillbridge report data tables are generated with specific CSS classes in order to allow the table, rows, and specific columns to be easily formatted.

The single data table for the report data has a CSS class of drillbridge-table.

Each row in the data table has a class of dr. Odd rows in the table will additionally have a class of odd. This makes it easy to do custom styling for alternating rows.

Each column in the data table has a class of dc as well as a class of dc-X where X is the column index. Note that column indices start at 1. So in a 10 column table the classes would be dc-1, dc-2, ..., dc-10.

Each report can have a custom header and footer. You can include arbitrary HTML in these blocks. This custom HTML/text will be placed inside a <div> block at the top and bottom of the page. The header contents will be inside the following block:

<div class="header">
    Your custom HTML
</div>

Similarly, the footer text will be placed in this block near the end of the page:

<div class="footer">
    Your custom HTML
</div>

You can use custom image tags and other external items so long as they are hosted somewhere on the network that is accessible via the typical web-browser session (i.e., Drillbridge itself cannot host custom images or files).