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()
dbr.hidecolumns
- Hide the number of columns at the end of the result setdbr.hidecolumn
- Hide individual columns which can be anywhere in the querydbr.showcolumn
- Show individual columns that were automatically hiddendbr.rowstyle
- Report row's CSS style. A column in the query contains the CSS style for the rowdbr.rowclass
- Report row's CSS class. A column in the query contains the CSS-classname for the rowdbr.colstyle
- Report column's style, where a style is a combination of printf formatting/CSS-styledbr.colclass
- Report column's CSS class. Adds a CSS-class to a specified columndbr.cellstyle
- Adds CSS-style to individual cells based on the style in another column. This allows value-based formattingdbr.cellclass
- Adds CSS-class to individual cells based on the style in another column. This allows value-based formattingdbr.cellformat
- Report cells' printf format. Format column with formatting from another column. Allows formatting cells differently in the same column in different rowsdbr.hideheader
- Hide the headers in result setdbr.headerstyle
- Header's styledbr.header.colstyle
- Header columnm's styledbr.summary.colstyle
- Summary columnm's styledbr.header.colclass
- Header-column's CSS classdbr.footerstyle
- Footer's styledbr.footer.colclass
- Footer column's CSS class'dbr.footer.colstyle
- Footer's style for specific columndbr.footer.cellclass
- Set footer's CSS class based on data on another footer columndbr.footer.cellstyle
- Set footer's CSS style based on data on another footer columndbr.resultclass
- Add custom CSS class to the result tabledbr.html5data
- Put a column to another column's data-xxx attribute instead of a columndbr.sum_prefix
- Override the default sum-prefixdbr.min_prefix
- Override the default min-prefixdbr.max_prefix
- Override the default max-prefixdbr.avg_prefix
- Override the default avg-prefixdbr.count_prefix
- Override the default count-prefix
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'
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;
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:
[color: green]%.1f
[background-color: yellow;]
%d
[overflow:hidden; white-space: nowrap; text-overflow:ellipsis; width: 200px;max-width: 200px;]
%0.1f;-;[color:red](%0.1N);
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:
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:
color: white; background-color: red;
font-size: 18px;
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
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 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
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
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;
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')
.
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;