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. 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
-
Turn on the Enable_paging option on the report administration page
-
Enter a row-counting query for the report
-
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.
:doctype:book = 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
:doctype:book = 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
.
Header & Footer
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).