Applying styles

myDBR offers commands for formatting the individual report elements if the default formatting is not sufficient.

Styles can alter all the visual elements of the data: formatting, color (background, foreground), font, etc. Styles can be applied to several elements in the result set: individual rows, columns, header, and the footer. myDBR supports global styles that can be shared between reports. Styles can also be defined dynamically in a report. In order to keep the report's look and feel consistent, it is recommended that styles from parameters are to be used.

Commands ending with "class" take a CSS class as a parameter. One can define a CSS class with dbr.css-command or by adding the class definition in the user/userstyle.css -file where the definition is available to every report.

Common use cases for formatting are:

myDBR has global styles that can be applied to elements via the mydbr_style function. See the definition of global of Styles at Preferences.

mydbr_style is a database function that accesses the style-table from the myDBR. When using the function in MS SQL Server, Sybase ASE or SQL Anywhere, please use the syntax: dbo.mydbr_style()

Commands

dbr.hidecolumns - Hide the number of columns at the end of the result set
dbr.hidecolumn - Hide individual columns which can be anywhere in the query
dbr.showcolumn - Show individual columns that were automatically hidden
dbr.rowstyle - Report row's CSS style. A column in the query contains the CSS style for the row
dbr.rowclass - Report row's CSS class. A column in the query contains the CSS-classname for the row
dbr.colstyle - Report column's style, where a style is a combination of printf formatting/CSS-style
dbr.colclass - Report column's CSS class. Adds a CSS-class to a specified column
dbr.cellstyle - Adds CSS-style to individual cells based on the style in another column. This allows value-based formatting
dbr.cellclass - Adds CSS-class to individual cells based on the style in another column. This allows value-based formatting
dbr.cellformat - Report cells' printf format. Format column with formatting from another column. Allows formatting cells differently in the same column in different rows
dbr.hideheader - Hide the headers in result set
dbr.headerstyle - Header's style
dbr.header.colstyle - Header columnm's style
dbr.summary.colstyle - Summary columnm's style
dbr.header.colclass - Header-column's CSS class
dbr.footerstyle - Footer's style
dbr.footer.colclass - Footer column's CSS class'
dbr.footer.colstyle - Footer's style for specific column
dbr.footer.cellclass - Set footer's CSS class based on data on another footer column
dbr.footer.cellstyle - Set footer's CSS style based on data on another footer column
dbr.resultclass - Add custom CSS class to the result table
dbr.html5data - Put a column to another column's data-xxx attribute instead of a column
dbr.sum_prefix - Override the default sum-prefix
dbr.min_prefix - Override the default min-prefix
dbr.max_prefix - Override the default max-prefix
dbr.avg_prefix - Override the default avg-prefix
dbr.count_prefix - Override the default count-prefix

Syntax

select 'dbr.hidecolumns', ColumnReference / nbr of columns from right to hide
select 'dbr.hidecolumn', ColumnReference [, ColumnReference...]
select 'dbr.showcolumn', ColumnReference [, ColumnReference...]
select 'dbr.rowstyle', ColumnReference
select 'dbr.rowclass', ColumnReference
select 'dbr.colstyle', ColumnReference, {ColumnReference2...}, columnstyle
select 'dbr.colclass', ColumnReference, {ColumnReference...}, css_classname
select 'dbr.cellstyle', ColumnReference, {ColumnReference...}, CSSStyle_ColumnReference
select 'dbr.cellclass', ColumnReference, {ColumnReference...}, CSSclass_ColumnReference
select 'dbr.cellformat', ColumnReference, {ColumnReference...}, ColumnFormat_ColumnReference
select 'dbr.hideheader'
select 'dbr.headerstyle', rowstyle
select 'dbr.header.colstyle', css_style
select 'dbr.summary.colstyle', css_style
select 'dbr.header.colclass', ColumnReference, css_classname
select 'dbr.footerstyle', rowstyle
select 'dbr.footer.colclass', ColumnReference, {ColumnReference...}, css_classname
select 'dbr.footer.colstyle', ColumnReference, {ColumnReference...}, columnstyle
select 'dbr.footer.cellclass', ColumnReference, {ColumnReference...}, CSSclass_ColumnReference
select 'dbr.footer.cellstyle', ColumnReference, {ColumnReference...}, CSSstyle_ColumnReference
select 'dbr.resultclass', 'myclass'
select 'dbr.html5data', 'ColumnReferenceTo', 'ColumnReferenceFrom', 'xxx'
select 'dbr.sum_prefix', 'Summe'
select 'dbr.sum_prefix', 'Minimum'
select 'dbr.sum_prefix', 'Maximum'
select 'dbr.sum_prefix', 'Mittel'
select 'dbr.count_prefix', 'Anzahl'

Column hiding

Hidden columns are used to include ID values that may not necessarily be shown to the user, but are used for report linking / other functionality or hidden columns are used for calculation purposes. Columns can be hidden using dbr.hidecolumns and dbr.hidecolumn commands. The dbr.hidecolumns command hides the number of columns from the end where ColumnReference marks the first column to be hidden. dbr.hidecolumn command can be used to hide individual commands.

/* 
  Show only the customer name for the user, but link the report on customer ID  
  We will hide 1 column from the end
*/
select 'dbr.report', 'sp_DBR_MoreCustomerInfo', 'ID=ID';
select 'dbr.hidecolumns', 'ID';

select Name, ID
from mydb.Customer;

Define a column style

On tabular reports, you may have a need to format a column differently from the default style. You can apply a style to the command by using the dbr.colstyle - command. The command takes one parameter, which is the style definition. myDBR offers global styles that can be shared between the reports. These styles can be accessed using the built-in mydbr_style-function. Alternatively, the user can simply use ad-hoc/dynamic styles as a parameter. It is recommended that you use global styles when you do not need dynamic styling.

The column style can be used to format an image when fetching an image blob from the database.

The column style can also contain a calculation format (with PHP syntax). This comes handy for example in cases where you have the number of rows with decimals and you want to show rounded numbers to the user, but still, want to maintain high accuracy when calculating summary values.

The column style has a format consisting of:

Positive value style [; Zero value style [; Negative value style] ]

The format of each of these (positive, zero and negative) styles is:

[CSS style definition] printf arguments for the value

You can omit the CSS or printf part of the style if you do not need one.

The column style when fetching image blobs directly from the database is:

[image CSS-style]image

In the example, we have defined a style called 'AlternateColorDecimal' in Preferences. The style is defined as:

[color: green]%.2f; ;[color: red;]%.2f

Positive numbers are in green with 2 decimals, zero is set to space (=blank) and negative numbers are in green with 2 decimals:

/* 
  Apply a predefined style for the column Total
*/
select 'dbr.colstyle', 'Total', mydbr_style('AlternateColorDecimal');

select Name, Total
from mydb.Customer;

And the result shows numbers formatted accordingly:

Other examples:

  • A decimal number with just one decimal in green:
          [color: green]%.1f
        
  • Text on a yellow background
          [background-color: yellow;]
        
  • Integer number without a thousand separator (by default the thousand separator from Environment settings is used)
          %d
        
  • Limiting column width (200px) and preventing line wrapping without losing the information for export. Will put an ellipsis on the end of the column if the column is too wide
          [overflow:hidden; white-space: nowrap; text-overflow:ellipsis; width: 200px;max-width: 200px;]
        
  • An extension to printf-format (%0.0N for zero decimals, %0.2N for two decimals), myDBR allows formatting negative numbers as positive allowing accountant style formatting (optionally with parentheses). The following format would show the negative numbers as positive, in red wrapped in parentheses. Sorting will still be done with actual values.
          %0.1f;-;[color:red](%0.1N);
        

Summary calculation with column style

The summary calculation is always made using full decimal numbers even when the row values are rounded to be less accurate.

Showing full accuracy in the summary column

/* 
  Apply a style for the column to display the value in one decimal. The summary calculation is done using full accuracy
*/
select 'dbr.colstyle', 'Rounded', '%.1f';
select 'dbr.sum', 'Original', 'Rounded';

select item_value as 'Original', item_value as 'Rounded'
from mydb.mydata;

And the result shows numbers formatted accordingly:

Define a row style

You can also define a style for an individual row. This is not as common as column styling but can be used when individual rows need different formatting. Since a row can contain multiple columns with different data types, row styles cannot format the content, just the appearance. Styling the rows also differs in the way that the style is defined in the data itself while dbr.rowstyle -command refers to the column containing the style.

/* 
  Some rows are drawn with white-on-red
*/
select 'dbr.rowstyle', 'rowstyle';

select 'First row' as 'Col 1', '' as 'Col 2[rowstyle]'
union all
select 'Second row', mydbr_style('WhiteOnRed')
union all
select 'Third row', '';

Will produce a row with set attributes. Usually, the style column is placed at the end of the selected list and it is hidden.

Row style is simply a CSS-format string.

CSS style definition

Some example styles:

  • White text on a red background:
          color: white; background-color: red;
        
  • Big font
          font-size: 18px;
        

Define a cell style or class

With the cell style, you are able to define a style for each cell in the report. Similarly one can use CSS-class to define the style

/* 
  Color red cells whose value is less than 14. Other cells will be green
*/
select 'dbr.crosstab','Month';
select 'dbr.hidecolumns', 'style';
select 'dbr.cellstyle', 'value', 'style';

select name, 
       Month, 
       value as '[value]', 
       if (value<14,'color:red','color:green') as 'style'
from mydb.mydata;

Same as previous, now using the dbr.cellclass with predefined classes redclass and greenclass

/* 
  Color red cells whose value is less than 14. Other cells will be green
*/
select 'dbr.css', '.redclass {color:red;} .greenclass {color:green;}';
select 'dbr.crosstab','Month';
select 'dbr.hidecolumns', 'class';
select 'dbr.cellclass', 'value', 'class';

select name, 
       Month, 
       value as '[value]', 
       if (value<14,'redclass','greenclass') as 'class'
from mydb.mydata;

Both will have similar output

Hiding the header from a result table

If you want to display only the data, but not the column header in the tabular report, this can be done with the dbr.hideheader command. The command takes no parameters.

/* Don't show the header */

select 'dbr.hideheader';

select Year, Title, Inventory, Items
from mydb.Exampledata
order by Year, Title;

Will produce the result set without the header

Header and footer styles

Header styles can be defined using a css style or css class (dbr.header.colclass- and dbr.headerstyle-commands).

Footer can have a constant style shared among all footers (subtotals and totals). These can be set using commands dbr.footer.colstyle and dbr.footer.colclass. You can also format footer columns based on footer values using dbr.footer.cellclass and dbr.footer.cellstyle.

/* Turn the header's background to yellow */

select 'dbr.headerstyle', 'background-color: black;';

select Year, Title, Inventory, Items
from mydb.Exampledata
order by Year, Title;

Will produce the result set with a black header

Custom CSS class for the result tables

If you wish to add a custom style to the result table you can add a CSS style for it. Define the style in userstyle.css (or embed it into a report using 'dbr.html:').

We'll make a custom box for the invoice header. We'll use pageview to output the data and we'll create a custom CSS for the object. First, we'll create the style in userstyle.css overriding the default styles.

table.invoicebox {
	border: 1px solid black;
}

table.invoicebox tr {
	border: none;
}

table.invoicebox td {
	background-color: #EFEFEF;
}
/* Turn the headers background to yellow */

select 'dbr.pageview';
select 'dbr.resultclass', 'invoicebox';

select  nbr as 'Invoice number',
	    invoice_date as 'Date',
		interest as 'Interest';
from invoices
where nbr = 1

Will produce the desired result

Formatting columns and footers based on data

Format the data cell and footer column format based on data.

-- Define the styles
select 'dbr.css', '.text_green {color:green}';
select 'dbr.css', '.text_red {color:red}';

-- Format data cells and the footer alike
select 'dbr.cellclass', 'Diff', 'color';
select 'dbr.footer.cellclass', 'Diff', 'color';

select 'dbr.sum', 'Value', 'Budget';
select 'dbr.calc', 'Diff', '[Value] - [Budget]';

-- Calculate the CSS class to be used
select 'dbr.calc', 'color', "[Value]>=[Budget] ? 'text_green' : 'text_red'";

select Date, Value, Budget, null as Diff, null as 'color'
from mydata;

Using html5 data-* attributes

With the dbr.html5data-command one can turn a column into html5 data attribute. This can be used if JavaScript is used to access the data.

/* Turn the header's background to yellow */

select 'dbr.html5data', 'Title', 'data_code', 'code';

select Title, data_code
from mydb.Exampledata;

Will produce a table cell with 'data-code' attribute.

  <td data-code="a3-2x" class="cell">180</td>

The data can be accessed with pure JavaScrpt element.dataset.code or using jQuery $(element).data('code').

Fetching images from the database

We'll set the image a CSS style 'width:150px'. The constant 'image' tells myDBR to determine the image format from the incoming image data stream.

select 'dbr.colstyle', 'image_data', '[width:150px]image';

select image_data
from images;

select item_value as 'Original', item_value as 'Rounded'
from mydb.mydata;