Charts

Commands

dbr.chart - Create a chart from the following result set
dbr.chart.options - Assign special options for the chart
dbr.chart.color - Define own colors

Syntax

select 'dbr.chart', chart_type, [ chart_name [, xSize, ySize ] ]
select 'dbr.chart.options', option [, value]
select 'dbr.chart.color', hex ARGB color [, hex ARGB color]

Explanation

myDBR allows you to create charts based on the result set. myDBR supports multiple charting modules with distinct capabilities:

  • ChartDirector (from Advanced Software Engineering) offers true picture based charting with advanced options. ChartDirector is a commercial product requiring a license. The benefit of using ChartDirector over FusionCharts is that the charts are generated as a picture (PNG) instead of relying on the client-side Flash-plugin. Better support for printing is also a benefit of ChartDirector-based charts.
  • With Graphviz support myDBR is able to draw complex network and hierarchal reports. The charts generated with Graphviz-module can also output the source code for the chart to be processed with external charting applications supporting Graphviz's underlying charting language.
  • FusionCharts Free (from InfoSoft Global) offers Flash-chart capabilities. This module comes with myDBR as a chart package to be used in the Community evaluation version.

Charts can be divided into four categories:

  • XY-charts (XY), where the data set consist of X-value (category) and Y-value (data).
  • Multi series-charts (MS), where the data set consist of X-value (category) and multiple data values (Y's).
  • Meter-chart (Meter), where the data set consist of a single meter value.
  • Network-charts (Network), where the data set consist of parent-child / node-node pairs.
  • Other charts

Chart types:

Chart type Command Type Example Flash Charts Image Charts Graphviz
Column Column XY Yes Yes  
3D Column Column3d XY Yes Yes  
Multi-Series column MSColumn MS Yes Yes  
3D Multi-Series column MSColumn3D MS Yes Yes  
Stacked Column StackedColumn MS Yes Yes  
3D Stacked Column StackedColumn3D MS Yes Yes  
Stacked Bar StackedBar MS Yes Yes  
3D Stacked Bar StackedBar3D MS   Yes  
Stacked Area StackedArea MS Yes Yes  
3D Stacked Area StackedArea3D MS   Yes  
Line Line XY Yes Yes  
3D line Line3D XY   Yes  
Multi-Series Line MSLine MS Yes Yes  
Multi-Series Line Double Y MSLineDY MS   Yes  
Multi-Series Line Double Y 3D MSLineDY3D MS   Yes  
Spline Spline XY   Yes  
Multi-Series Spline MSSpline MS   Yes  
Stepline Stepline XY   Yes  
Multi-Series Stepline MSStepline MS   Yes  
Pie Pie XY Yes Yes  
3D Pie Pie3D XY Yes Yes  
Bar Bar XY Yes Yes  
3D Bar Bar3D XY   Yes  
Multi-Series Bar MSBar MS Yes Yes  
Area Area XY Yes Yes  
3D Area Area3D XY   Yes  
Bubble Bubble XY   Yes  
3D Bubble Bubble3D XY   Yes  
Multi-Series Area MSArea MS Yes Yes  
Percent Column Percentcolumn MS   Yes  
Percent Bar Percentbar MS   Yes  
Percent Area Percentarea MS   Yes  
Donut Donut (or Doughnut) XY Yes Yes  
3D Donut Donut3D (or Doughnut3D) XY   Yes  
Scatter Scatter XY   Yes  
Pyramid Pyramid XY   Yes  
Pyramid 3D Pyramid3D XY   Yes  
Cone Cone XY   Yes  
Cone 3D Cone3D XY   Yes  
Funnel Funnel XY   Yes  
Funnel 3D Funnel3D XY   Yes  
Multi-Series Column Double Y MSColumnDY MS   Yes  
Multi-Series Column Double Y 3D MSColumnDY3D MS   Yes  
Multi-Series Column Line Double Y MSColumnLineDY MS Yes Yes  
Multi-Series Column Line Double Y 3D MSColumnLineDY3D MS Yes Yes  
Multi-Series Column Spline Double Y MSColumnSplineDY MS   Yes  
Multi-Series Column Spline Double Y 3D MSColumnSplineDY3D MS   Yes  
Meter Meter Meter   Yes  
Linear meter Linearmeter Meter   Yes  
Semicircle Semicircle Meter   Yes  
Rectangular meter Rectangularmeter Meter   Yes  
Donut Percent Donutpercent (or Doughnutpercent) Meter   Yes  
Semi-Donut Percent Semidonutpercent Meter   Yes  
Hierarchy Hierarchy Network     Yes
Network Network Network     Yes
Gantt Gantt Gantt   Yes  
Polar Area PolarArea MS   Yes  
Polar Line PolarLine MS   Yes  
Polar Spline PolarSpline MS   Yes  
Polar Spline Area PolarSplineArea MS   Yes  
Polar Scatter PolarScatter MS   Yes  
Radar Area RadarArea MS   Yes  
Radar Line RadarLine MS   Yes  
Radar Spline RadarSpline MS   Yes  
Radar Spline Area RadarSplineArea MS   Yes  
Boxwhisker Boxwhisker Other   Yes  
Floatingbox Floatingbox Other   Yes  
Scatter3D Scatter3D Other   Yes  
MSScatter3D MSScatter3D Other   Yes  

How to create a chart?

To create a chart, you need to select the chart type and then fetch the appropriate data for the chart. Here is an example of a sample pie chart generation.

select 'dbr.chart', 'Pie', 'Sample Pie chart';

select category, total_sales
from mydb.sales_by_film_category;

Selecting data for the chart

Depending on the chart type selected, the data must be selected accordingly:

XY chart

For XY-charts, use the following data selection:

select X, Y
from mydb.Data;

Multi-series chart

For multi-series charts (MS), use the following data selection:

select Category (x), Series (label), Value (y)
from mydb.Data;

Scatter chart

For a scatter chart, use the following data selection:

select Series, x, y
from mydb.Data;

Boxwhisker chart

For a boxwhisker chart, use the following data selection:

select Series, bottom, top, [min, [max, [middle]]]
from mydb.Data;

Scatter3D chart

For a Scatter3D chart, use the following data selection:

select select x, y, z
from mydb.Data;

MSScatter3D chart

For a MSScatter3D chart, use the following data selection:

select select Series, x, y, z
from mydb.Data;

Meter chart

For meter charts, simply select the data value.

select 'dbr.chart', 'meter';

select 	78;

Network chart

For network charts there are two options:

Option 1 where the chart is drawn based on single values/node.

select node1, node2 [, edge_style]
from mydb.Data;

Option 2 where node contains both an ID value and the visible value to the user. This can be used when making charts to be exported and you want to keep the ID's.

select node1_id, node2_id, node1_name, node2_name [, edge_style]
from mydb.Data;

Network charts are generated using Graphviz technology and Graphviz formatting can be applied to the charts using the 'dbr.chart.options'-command. The option can be separately set to nodes (is a parent) and leaves (is only a child). The options are set as a comma-separated list and can contain all the formatting supported by Graphviz.

select 'dbr.chart.gv.style', 'node', 'shape=house,fillcolor=#CCCCFF,style=filled';

Gantt chart

Gantt chart has a label, starttime and endtime and optional category

select 'dbr.chart', 'gantt';

select 	label, starttime, endtime [, category]
from data;

Setting own chart colors

Commands

dbr.chart.color - Set chart colors

Syntax

select 'dbr.chart.color', hex ARGB color, [, hex ARGB color]

Default colors are defined in the defaults.php-file. If you wish to change the default colors, you can edit the file to contain your colors. Alternatively, you can use the 'dbr.chart.color'-command to override the default colors. If you wish to override the default colors and use the same definition in multiple reports, the easiest way to do this is to place the command in a separate stored procedure and call the procedure from your report.

We'll first define the procedure containing the colors (you can call 'dbr.chart.color' multiple times):

create procedure sp_ADBR_MyColors()
begin

select 'dbr.chart.color', '0x300000cc', '0x4066aaee', '0x40ffbb00', '0x40ee6622';

end

And then use it in a report:

call sp_ADBR_MyColors(); 

select 'dbr.chart', 'Cone3d', 'Cone3d';

select 'Funds', 156
union
select 'Bonds', 123 
union
select 'Stocks', 211
union
select 'Cash', 179;

And the chart will use the colors you defined:

Chart options

With chart options, you can affect the chart's visual appearance. In case of flash- and image-based charts, myDBR offers the most common options as standardized commands making the shift from one chart module to another easy. In addition to the common options, myDBR allows passing native commands to each chart module for additional formatting. With access to native commands, you are able to access individual features of each chart module without myDBR acting as a gatekeeper.

Common chart options

Commands

dbr.chart.options - Specify chart options
dbr.chart.gv.option - Specify GraphViz chart options

Syntax

select 'dbr.chart.options', option {, value{, value}}
select 'dbr.chart.gv.option', option

Image and Flash chart options:

  • chart_transpose - converts Category and Series in multi-series charts
  • axis, x [, y [, y2]] - sets x and y axis name as defined in parameter (select 'dbr.chart.options','axis', 'x-axis', 'y-axis', 'y-axis2')
  • imagechart - makes myDBR use ChartDirector PNG-based charts
  • flashchart - makes myDBR use FusionCharts Flash-based charts
  • showvalues, 0 | 1 | 'data' | 'total' | 'percent' - shows (1) or hides (0) values in a chart. 'data' shows values in indivudual data points, 'total' shows values in aggregate, 'percent' shows percentage instead of the value
  • alternatecolor, color1, color2 - set's chart canvas colors ( color1 and color2 )
  • scale, min, max - set's y-axis min and max values (select 'dbr.chart.options', 'scale', 300, 700)
  • scale2, min, max - set's 2nd y-axis min and max values (select 'dbr.chart.options', 'scale', 300, 700)
  • scale_x, min, max - set's x-axis min and max values (select 'dbr.chart.options', 'scale_x', 0, 100)
  • rotateNames, degree - rotates the x-axis names to the degree specified (free rotate for image chart, flash charts only rotates 90 degrees)
  • numberSuffix, 'suffix'- sets visible suffix for values (use dbr.colstyle for image charts)
  • showLegend, 0 - hides legend from multi-series chart

Image chart / Graphviz only:

  • font [,font] [,font_size] - Set chart font/font size for all chart elements (axis, title, lables etc) all at once
  • shading, shading_value - Pie chart shading value (LocalGradientShading, GlobalGradientShading, ConcaveShading, RoundedEdgeShading, RadialShading, FlatShading and RingShading). See more info from ChartDirector documentation.
  • marker series, [marker, [size, [edge_color]]] - Specify a marker type and size used in chart (scatter/line). If the marker_series is null same marker type is used in all series.
  • format, imageformat - Defines the image format (PNG, SVG, GIF, JPG, WMP, BMP, SVGZ)
  • 3d, depth - Defines 3D depth in pixels (-1=auto, 0=2D, >1 depth in pixels)
  • autoscale_x [ topExtension [, bottomExtension [, zeroAffinity ]]] - Autoscale the chart x-axis to see differences better
  • autoscale_y [ topExtension [, bottomExtension [, zeroAffinity ]]] - Autoscale the chart y-axis to see differences better
  • chart.scale scale_percentage - Scale the chart to given percentage of the screen. SVG-charts scale best.
  • datetimescale, 1 [, lower_limit, upper_limit [, major_tick_inc, [minor_tick_inc]]] - Treats first column as a datetime and uses datetime scaling on X-axis, optionally setting scaling, major and minor ticks. The lower_limit and upper_limit are datetimes or times and major_tick_inc and minor_tick_inc are seconds.
  • timevalue, 1 - Converts the seconds the in data column to a time value allowing times >24h to be displayed in the chart
  • cartesian - use cartesian coordinate system in the chart
  • axis_noscale 1/0 - Do not sync x- and y-axis on cartesian charts
  • numberscale - keep x-axis number scale the same
  • background_color, color - Set chart background color
  • edge_color, color - Set chart's edge color
  • plot_edge_color, color - Set plot's edge color
  • grid_color_horizontal, color - Set chart's horizontal grid color
  • grid_color_vertical, color - Set chart's vertical grid color
  • raised_effect, raised - Set edge raise in pixels
  • title_font, font - Set title font
  • title_font_size, size - Set title font size
  • title_color, color - Set title color
  • title.wrap, character_count[, cut] - Wraps title text to defined character length. Word-wrap when cut is not defined. if cut==1, cut long words
  • axis_font, font - Set axis font
  • axis_font_size, size - Set axis font size
  • axis_color, color - Set axis color
  • fix_y_axis, 1 - Set y-axis alignment to right to fix SVG chart text spacing when font spacing is not correct in ChartDirector
  • x_axis_max_width, width - Set maximum length of x axis in a XY-chart
  • y_axis_max_width, width - Set maximum length of x axis in a XY-chart
  • z_axis_max_width, width - Set maximum length of x axis in a XY-chart
  • x2_axis.show, 1 - Show the second x-axis
  • x_axis_margin, margin - Add extra margin at the ends of the x-axis
  • x2_axis_margin, margin - Add extra margin at the ends of the second x-axis
  • x_axis_color, color - Set x-axis color
  • y_axis_color, color - Set y-axis color
  • x2_axis_color, color - Set x2-axis color
  • y_axis_color, color - Set y-axis color
  • z_axis_color, color - Set z-axis color
  • y2_axis_color, color - Set y2-axis font color
  • x_axis_font_color, color - Set x-axis font color
  • y_axis_font_color, color - Set y-axis font color
  • x2_axis_font_color, color - Set x2-axis font color
  • y_axis_font_color, color - Set y-axis color
  • y2_axis_font_color, color - Set y2-axis color
  • z_axis_font_color, color - Set z-axis color
  • y_axis_label_style, colstyle - Set style for y-axis labels
  • y2_axis_label_style, colstyle - Set style for y2-axis labels
  • x_axis_tick_middle, 1 | 0 - Define if tick-marks are placed in middle of title in x-axis
  • padding_top, padding - Set top padding in pixels
  • padding_left, padding - Set left padding in pixels
  • padding_bottom, padding - Set bottom padding in pixels
  • padding_right, padding - Set right padding in pixels
  • title.align, 'left' | 'chart' - Align chart title to left of chart / middle of chart instead of the default middle of plot area
  • hide 'all | ('bg', 'yaxis', 'xaxis', 'grid') - Hide parts of the chart: all (show just the chart, bg=plot area background, x-axis, y-axis, grid)
  • radius, radius - Set pie/funnel radius in pixels
  • label_font, font - Set label font
  • label_font_size, size - Set label font size
  • label_font_color, color - Set label color
  • label_color, label, color - Allows you to define a color based on a label name (XY-charts) or based on the series name (multi-series charts)
  • label.wrap, character_count[, cut] - Wraps label text to defined character length. Word-wrap when cut is not defined. if cut==1, cut long words
  • legend.columns, column_nbr - Sets number of legends to use
  • legend_font, font - Set legend font
  • legend_font_size, size - Set legend font size
  • legend_color, color - Set legend color
  • legend.position, 'left' | 'right | 'top' | 'bottom' - Set legend position
  • legend.style, 'background_color', 'edge_color', 'rounded_border[TL]', ['rounded_borderTR', 'rounded_borderBR', 'rounded_borderBL'] - Set legend style
  • legend_reverse, 1 - Reverse the legend order
  • line_width, width [, label] - Line width in line chart. Optionally selecting the label
  • line.style[ series_name,] 'dash' | 'dot' | 'dotdash' | 'altdash' - Line style. series_name is required in multiseries chart.
  • bargap, 0..1 - Controls the gap between columns. Value is percentage <=1 (negative value overlaps).
  • subbargap, 0..1 - Controls the gap between individual columns in multiseries column chart. Value is percentage <=1 (negative value overlaps).
  • width_per_item, width - When chart width is set to 'auto', this option determines the width for each item
  • height_per_item, width - When chart height is set to 'auto', this option determines the height_per_item for each item
  • export_as_png, 1/0 - When doing export on svg chart, the option will determine if the chart is drawn as png (some wkhtmltopddf versions may render some charts poorly). Global option in defaults.php exists for this.
  • softlightning, 1/0 - Determines if softlightning is used on charts.
  • series, 'series1' [, 'series2' [, 'series3'..,]] - Defines the series order in multiseries chart
  • category_order, 'category1' [, 'category2' [, 'category3'..,]] - Defines the category display order in multiseries charts.
  • category_sort, 'string', 'numeric' | 'natural' - Set category display sorting order
  • mark, value, color, width, [, 'text' [, text_angle]] - Add a mark line to a XY-chart
  • target, color, width in px, height (0-1) - Add a target mark for bar and column charts (3rd data column)
  • series_axis, series_name, 1 | 2 - Choose the axis for the series in double Y-axis charts.
  • InnerRing, size - Meter-chart inner ring proportional size
  • OuterRing, size - Meter-chart outer ring proportional size
  • skip_null_values, 0 - By default, report's NULL values in charts are treated as missing values. If this option is turned on, they are treated as zeros
  • allow_gaps, 1 - If the options is turned on, missing values (or values containing NULL) are drawn into a line chart allowing gaps to appear to the line(s). This option turns the markers in the line on by default in order to show values that appear between two missing values. See 'marker'-option how to specify the marker.
  • tooltip, '[ColumnReference] [ColumnReference]' - Define the mouse tooltip content with the ability to refer to data columns
  • tooltip_animate, 1/0 - Allows disabling tooltip animation
  • xy_plot_fill, 1/0 - Use full chart area for the plot area and hide axis
  • trendline, line_color[, title, line_width [, SeriesName], [regrssion_type [,regression_degree]]] - Adds a trendline to the chart (line, column, bar)
  • gantt.scale, days - Scale of the chart. Use 30 for month
  • gantt.start, date - If specified, marks the start date for chart. If not, first date in data is used
  • gantt.end, date - If specified, marks the end date for a chart. If not, the last date in data is used
  • gantt.baseline, label, start, end - Sets the baseline for a label
  • gantt.baseline_text, text - Baseline text, if not specified 'Baseline' is used if baselines are defined
  • gantt.baseline_pattern.border, color - Baseline marker's border color
  • gantt.baseline_pattern.color, color - Baseline marker's background color
  • gantt.baseline_pattern.width, px - Baseline marker's width
  • gantt.baseline_pattern.height, px - Baseline marker's height
  • gantt.dateline, day - Extra date line
  • gantt.label.height, px - Height of the gantt labels
  • gantt.single_label, 1 - Use just one row / label
  • gantt.start_of_day_format, chartdirector_date_format - ChartDirector date format for day gantt
  • gantt.start_of_hour_format, chartdirector_time_format - ChartDirector time format for hour gantt
  • gantt.start_of_month_format, chartdirector_date_format - ChartDirector date format for day gantt when month changes
  • scale, minimum, maximum, step - Y-Axis scale, If 'auto', scales x and y-axis equally in bubble-chart
  • scale2, minimum, maximum, step - 2nd Y-Axis scale
  • equal_scale_dy, 1/0 - Control scaling of Y-axis on DoubleY charts
  • bubble.scale, 'x'|'y'|'pixel' - Individual bubble size relative to y-axis, x-axis or pixels.
  • bubble.transparency, 30 - Bubble transparency to show overlapping bubbles
  • meter.text, text - Optional text shown in a meter-chart.
  • meter.text.font, font - Text font (default is arial)
  • meter.text.size, size - Text size (default is 8)
  • meter.text.color, color - Text color (default is black)
  • linearmeter.orientation, 'horizontal'|'vertical' - Linearmeter orientation. Default is horizontal.
  • linearmeter.zone, from, to, color - By default, the zones are 0-33 green, 33-66 yellow and 66-100 red. With this option you can define your own zones.
  • linearmeter.height, height - Linearmeter height in pixels, default is 20 pixels.
  • linearmeter.width, width - Same as linearmeter height
  • linearmeter.text, text - Optional text shown in linearmeter.
  • linearmeter.text.font, font - Text font (default is arial)
  • linearmeter.text.size, size - Text size (default is 8)
  • linearmeter.text.color, color - Text color (default is black)
  • linearmeter.pointer.color, color - Pointer color
  • meter.theme, theme - Select the meter theme ('black', 'white', 'default')
  • meter.title.font, font - Set meter title font
  • meter.title.size, size - Set meter title font size
  • meter.title.color, color - Set meter title font color ('0xFF0000')
  • meter.text, text - Set the meter text
  • meter.text.font, font - Set meter text font
  • meter.text.size, size - Set meter text font size
  • meter.text.color, color - Set meter text font color ('0xFF0000')
  • meter.label.font, font - Set meter label font
  • meter.label.size, size - Set meter label font size
  • meter.label.color, color - Set meter label font color ('0xFF0000')
  • meter.pointer.color, color - Set meter pointer color ('0xFF0000')
  • meter.color.type, 'step' | 'gradient' - Select if the meter colors are individual (steps) or gradient (the default)
  • meter.color.step, step, color - Define step colors
  • meter.color.gradient, step, color - Define gradient colors
  • polar.grid, axis_color, axis_width, polar_color, polar_width, - Define chart colors for polarscatter-chart
  • zone, from_value, to_value - Set background color for zones, range of values
  • donut.size size - Donut thickness
  • donut.orientation 1/-1 Donut rotation clockwise / counterclockwise
  • donut.text text - Donut text
  • donut.font.size - font size
  • donut.font.name - font name
  • donut.font.color - font color
  • donutpercent.orientation 1/-1 - DonutPercent chart orientation. 1=clockwise, -1 anticlockwise
  • donutpercent.size size - Donut thickness
  • donutpercent.text text - Text inside the donut
  • donutpercent.font.name font - Font for the text inside the donut
  • donutpercent.font.size size - Font size for the text inside the donut
  • donutpercent.font.color color - Font color for the text inside the donut
  • semidonutpercent.size size - Donut thickness
  • semidonutpercent.text text - Text inside the donut
  • semidonutpercent.font.name font - Font for the text inside the donut
  • semidonutpercent.font.size size - Font size for the text inside the donut
  • semidonutpercent.font.color color - Font color for the text inside the donut
  • scatter3d.x_axis_labels label - Replace x-axis numbers with a text label
  • scatter3d.y_axis_labels label - Replace y-axis numbers with a text label
  • scatter3d.z_axis_labels label - Replace z-axis numbers with a text label
  • scatter3d.drop_line Series | null, color | null, 'dash' | 'dot' | 'dotdash' | 'altdash' - Define marker and the drop line type for the data point
  • scatter3d.plot_area center_x, center_y, xwidth, ydepth, zheight - Manually define the plot area
  • scatter3d.view_angle elevation, rotation - Set orientation of the chart in degrees
  • scatter3d.wall_color xy_color, yz_color, zx_color, border_color - Define charts wall colors
  • scatter3d.z-legend 1 - Use single gradient legend for MSScatter3d chart instead of separate series legends

Where:

topExtension
The top portion of the axis that no data point should fall into. For example, a value of 0.1 means no data value will fall within the top 20% of the axis. The topExtension must be between 0 to 1. Default is 0.1.
bottomExtension
The bottom portion of the axis that no data point should fall into. For example, a value of 0.1 means no data value will fall within the bottom 20% of the axis. The bottomExtension must be between 0 to 1.
zeroAffinity
ChartDirector will determine that it is "unreasonable" to use 0 as the axis starting point if the data fluctuation (the difference between the maximum and minimum data values) is too small compared with the data value. ChartDirector tests the "too small" condition using the formula: maxDataValue * zeroAffinity < minDataValue. The default value is 0.5
regrssion_type
Sets the regression type to be used. Available regression types are: LinearRegression (default), ExponentialRegression, ExponentialRegression, LogarithmicRegression or PolynomialRegression. In case of PolynomialRegression one must define the regression_degree
regression_degree
PolynomialRegression's degree.
Donut vs Doughnut
For compatibility reasons, you can use both 'donut.*' or 'doughnut.*' syntaxes.

GraphViz options

  • diagram - pass diagram level parameters on to the chart (see example).
  • node 'format in comma-separated list' - sets node characteristics as Graphviz commands in Network chart
  • chart_scale, 1 - Adds a UI element where user can scale Graphviz charts

FusionCharts options

For FusionCharts charts, myDBR passes any native <graph>-level formatting FusionCharts command directly to FusionCharts, so you can use any of the module's formatting. The full list of options is available in FusionChart's documentation under the "Chart XML Reference"-section.

select 'dbr.chart', 'Line';
select 'dbr.chart.options', 'bgColor', '0xDDDDDD';      // Gray background color
select 'dbr.chart.options', 'axis', 'X-axis', 'Y-axis'; // x-axis name
select 'dbr.chart.options', 'yAxisMinValue', 8;         // minimum value for y
select 'dbr.chart.options', 'yAxisMaxValue', 22;        // maximum value for y
select 'dbr.chart.options', 'showAnchors', 0;           // do not show the anchors

select x, y
from mydb.mydata;

Image chart options

Formatting labels

Image chart's labels can be formatted using normal 'dbr.colstyle' command (CSS is not supported). 'dbr.colstyle' also accepts ChartDirector's parameter substitution and formatting and ChartDirector Mark Up Language with dbr.colstyle. The style-string can contain:

  1. printf-format for the value
  2. Any free text
  3. ColumnReferences to get value from another column
  4. ChartDirector's Parameter Substitution Strings and Mark Up Language

In the example, the style string has a column reference ('[name]'), a value printf ('%.2f') and a ChartDirector's parameter substitution string ({percent|0}).

select 'dbr.chart', 'pie';
select 'dbr.colstyle', 'value', '[name]: US$ %.2fK ({percent|0}%)';

select 'First' as 'name', 1.1 as 'value'
union
select 'Second', 2.2
union
select 'Third', 3.3;

Multiple options

The example show multiple options to be applied to the chart

declare v_s varchar(80);
declare v_w varchar(80);
declare v_color1 varchar(10);
declare v_color2 varchar(10);

set v_s = 'Sociology Doctorates Awarded (US)';
set v_w = 'Worldwide non-commercial space launches';

set v_color1 = '0xA9001D';
set v_color2 = '0x000000';

select 'dbr.title', '';
select 'dbr.chart', 'mssplineDY', concat('<*size=16,color=A9001D*>', v_w, '\n<*color=AAAAAA*>correlates with\n<*color=000000*>', v_s), 1200, 500;
select 'dbr.chart.options', 'font', 12;

select 'dbr.chart.options', 'scale', 30, 60, 10;
select 'dbr.chart.options', 'scale2', 500, 700, 100;
select 'dbr.chart.options', 'rotateNames', 45;

select 'dbr.chart.color', v_color1, v_color2;
select 'dbr.chart.options', 'marker', v_w, 'DiamondShape', 12, v_color1;
select 'dbr.chart.options', 'marker', v_s, 'CircleShape', 12, v_color2;
select 'dbr.chart.options', 'x2_axis.show', 1;

select 'dbr.chart.options', 'x_axis_margin', 20;
select 'dbr.chart.options', 'x2_axis_margin', 20;

select 'dbr.chart.options', 'y_axis_font_color', v_color1;
select 'dbr.chart.options', 'x2_axis_font_color', v_color1;

select 'dbr.chart.options', 'y_axis_label_style', '%d Launches';
select 'dbr.chart.options', 'y2_axis_label_style', '%d Degrees Awarded';

select 'dbr.chart.options', 'axis', '', v_w, v_s;

select 'dbr.chart.options', 'legend', 1;
select 'dbr.chart.options', 'legend.position', 'bottom';
select 'dbr.chart.options', 'legend.columns', 1;

select 'dbr.chart.options', 'tooltip', '[sw]: [v]';

select y, sw, v
from mydb.mydata;

ChartDirector native options

ChartDirector offers a variety of options for chart formatting. ChartDirector is implemented using PHP class directives, and for native commands, myDBR offers direct access to the myDBR's ChartDirector's chart-object ($c), to the layer-object ($layer) and to trend layers array ($trend_layers). Commands are passed as PHP-code snippets using 'dbr.chart.options', 'chartdirector'-command.

select 'dbr.chart.options', 'chartdirector', '$c->chartdirectorcommand';

In the example, we'll change the y-axis to logarithmic and set minimum and maximum values, display only every second x-axis label, set the border and background to gray and finally display a drop shadow for the chart.

select 'dbr.chart', 'Line', 'Chart';

select 'dbr.chart.options','rotateNames', 90;
select 'dbr.chart.options','showvalues', 0;
select 'dbr.chart.options','chartdirector', '$c->yAxis->setLogScale(300,700);$c->xAxis->setLabelStep(2);';
select 'dbr.chart.options','chartdirector', '$c->setBorder(0xEEEEEE);$c->setBackground(0xeeeeee);$c->setDropShadow();';

select Name, sum(Items)
from mydb.TestTable
where year(InsertDate)=2008
group by Name;

For a full description of ChartDirector's class library, see the ChartDirector documentation.

If you have multiple charts and wish to keep the same colors between different charts you can define colors based on labels (or series). If you use the same color for the same labels in many places, it is easier to create a helper procedure so your definitions can be found in one place.

You can also leave out the optional color definition. If one is not set, the color is defined from the normal color set.


select 'dbr.chart.options', 'label_color', 'Q1', '0x006F00';
select 'dbr.chart.options', 'label_color', 'Q2', '0xFF66CC';
select 'dbr.chart.options', 'label_color', 'Q3', '0x9900FF';
select 'dbr.chart.options', 'label_color', 'Q4', '0x99FFCC';

select 'dbr.chart', 'mscolumn';

select Name, Quarter, sum(Items)
from mydb.TestTable
group by Name, Quarter;

select 'dbr.chart.options', 'label_color', 'Q1', '0x006F00';
select 'dbr.chart.options', 'label_color', 'Q2', '0xFF66CC';
select 'dbr.chart.options', 'label_color', 'Q3', '0x9900FF';
select 'dbr.chart.options', 'label_color', 'Q4', '0x99FFCC';

select 'dbr.chart', 'Column';

select Quarter, sum(Items)
from mydb.TestTable
group by Quarter;

Markers

For scatter charts you can choose the marker for each category.

select 'dbr.chart.options', 'marker', 'category_name', ['Marker'[, size]];

Markers available starting from left bottom:

  • CircleShape
  • GlassSphereShape
  • GlassSphere2Shape
  • SolidSphereShape
  • SquareShape
  • DiamondShape
  • TriangleShape
  • RightTriangleShape
  • LeftTriangleShape
  • InvertedTriangleShape
  • StarShape(3)
  • StarShape(4)
  • StarShape(5)
  • StarShape(6)
  • StarShape(7)
  • StarShape(8)
  • StarShape(9)
  • StarShape(10)
  • PolygonShape(5)
  • Polygon2Shape(5)
  • PolygonShape(6)
  • Polygon2Shape(6)
  • CrossShape(0.1)
  • CrossShape(0.2)
  • CrossShape(0.3)
  • CrossShape(0.4)
  • CrossShape(0.5)
  • CrossShape(0.6)
  • CrossShape(0.7)
  • Cross2Shape(0.1)
  • Cross2Shape(0.2)
  • Cross2Shape(0.3)
  • Cross2Shape(0.4)
  • Cross2Shape(0.5)
  • Cross2Shape(0.6)
  • Cross2Shape(0.7)

Some markers have parameters which alter the marker appearance.

select 'dbr.chart', 'scatter';
select 'dbr.chart.options', 'marker', 'Amrak', 'TriangleShape', 12;
select 'dbr.chart.options', 'marker', 'Baxtor', 'InvertedTriangleShape';
select 'dbr.chart.options', 'marker', 'Detac', 'SquareShape';


select category, x, y
from mydb.mydata
order by category;

Chart value formatting

You can use the normal 'dbr.colstyle'-command to format the image chart values.

Simple trendline

A trendline can be added to XY and scatter charts using trendline-options.

select 'dbr.chart', 'Column', 'Trendline';	

select 'dbr.chart.options', 'trendline', '0x008000' , 'Trendline', 5;
/* Add a legend to the trendline */
select 'dbr.chart.options', 'chartdirector','$legendObj = $c->addLegend(50, 8, false, "", 8)->setBackground(Transparent);';

select supplier, sum(items)
from mydata
group by supplier;

Hiding chart elements

You can hide parts of the chart with 'hide'-option. In the example, we'll hide the plot background, both axis and the grid to make a cleaner looking chart with label showing the value.

select 'dbr.chart', 'bar', 'Matching type', 300, 'auto';
select 'dbr.colstyle', 2, '%.1f %';

-- Hide background, both axis and the grid
select 'dbr.chart.options', 'hide', 'bg,yaxis,xaxis,grid';
-- Title aligned to center of chart (instead of plot area)
select 'dbr.chart.options', 'title.align', 'chart';
select 'dbr.chart.options', 'height_per_item', 30;

select 'Automatically matched', 79.2
union
select 'Manually matched', 11.9
union
select 'Unmatched', 8.9;

The result is shown right. The same chart without the options is shown in left.

Curve fitting with ConfidenceBand and PredictionBand

The chartdirector-option has variable $trend_layers which can be used to add functionality to a trendline. In the example, ConfidenceBand and PredictionBand are added.

select 'dbr.chart', 'scatter', 'Scatter chart with trendline, confidence and prediction band';
select 'dbr.chart.options', 'marker', 'Server AAA', 'TriangleShape', 16;
select 'dbr.chart.options', 'marker', 'Server BBB', 'InvertedTriangleShape', 26;

select 'dbr.chart.options', 'trendline', '0x008000', 'AAA Trend', 4, 'Server AAA';
select 'dbr.chart.options', 'trendline', '0x1EC41B', 'BBB Trend', 4, 'Server BBB';

select 'dbr.chart.options', 'chartdirector', 
'$trend_layers[0]->addConfidenceBand(0.5, 0x806666ff);
$trend_layers[0]->addPredictionBand(0.5, 0x8066ff66);
$trend_layers[0]->setRegressionType(PolynomialRegression(2));';

select server, x, y
from mydata;

Sparkline charts

Sparkline charts are often used to present trends and variations, examples of this include stock prices and temperature.

Sparkline charts in myDBR

Sparkline charts in myDBR use Gareth Watts' jQuery Sparklines-plugin for Splunk Inc. All features present in the plugin are available in myDBR.

To use the sparklines the data column needs a comma-separated list of values in a column. In the example, this is done using MySQL's group_concat-function. Then a CSS class is added to the column so that a jQuery call can reference the column. With a dbr.javascript-command the plugin is called with parameters. The 'onload' parameter for dbr.javascript makes the jQuery plugin execute after the DOM tree has been created.

select 'dbr.hidecolumns', 1;
select 'dbr.colclass', 'Spark', 'spark';

select 'Sales', group_concat(s) as 'Spark', 'pie'
from (
select quarter(InsertDate) as q, sum(ItemsSold) as s
from TestTable
where year(InsertDate)=2008
group by 1
) sub;

select 'dbr.javascript', "$('.spark').sparkline('html', { type: 'pie', height: '1.0em' });", 'onload';

Chart examples

Basic XY chart

In the example, we'll generate a simple pie chart. As for any other XY-chart we need to provide X and Y values for the chart.

select 'dbr.chart', 'Pie', 'Sample Pie chart';

select category, total_sales
from mydb.sales_by_film_category;

A multi-series column chart

In multi-series charts, we have multiple Y-values. In the following example, we'll select data for each quarter for selected years and make a multi-series column chart out of the data.

select 'dbr.chart', 'mscolumn';
select 'dbr.chart.options', 'showValues', 1;

select production_year, production_quarter, value 
from mydata;

Transposing a multi-series column chart

Multi series charts can easily be transposed so that the categories and the series will swap places. We'll use the same query as in the previous example and we'll transpose the chart prior showing it. This is done with a chat option 'chart_transpose'.

select 'dbr.chart', 'mscolumn';
select 'dbr.chart.options', 'showValues', 1;
select 'dbr.chart.options', 'chart_transpose';

select production_year, production_quarter, value 
from mydata;

Hierarchical chart

Hierarchical and diagram charts will produce a network chart. In a hierarchical chart, there is a parent-child connection, while in diagram-charts there is just a connection between the nodes. Both charts are generated the same way, the only difference being the chart type.

select 'dbr.chart', 'Hierarchy';

select 'Corporate', 'Sales'
union
select 'Corporate', 'Production'
union
select 'Corporate', 'Management'
union
select 'Sales', 'Northern Sales'
union
select 'Sales', 'Southern Sales'
union
select 'Production', 'Top part production'
union
select 'Production', 'Bottom part production';

Network chart options

The example is a query against the MySQL's sakila-demo database's system tables in order to draw a quick ER-diagram. The chart is a Hierarchy-chart and with the diagram-option the chart is set to Left-to-Right (see Graphviz documentation for more info):

select 'dbr.chart', 'Hierarchy';
select 'dbr.chart.gv.style', 'graph', 'rankdir=LR';
select 'dbr.chart.gv.style', 'node', 'shape=box,fillcolor=#CCCCFF,style=filled';


select distinct referenced_table_name, table_name
from information_schema.key_column_usage
where table_schema='sakila' and referenced_table_name is not null
order by referenced_table_name;

Boxwhisker chart

The boxwhisker chart consists of the bottom and top values. Optionally you can add min, max, and middle values

select 'dbr.chart', 'Hierarchy';
select 'dbr.chart.gv.style', 'graph', 'rankdir=LR';
select 'dbr.chart.gv.style', 'node', 'shape=box,fillcolor=#CCCCFF,style=filled';

select label, box_min, box_max, minim, maxim, middle
from mydb.data;

Floatingbox

A floating box chart is a variation of boxwhisker chart which draws independent boxes for series/label with min-max

select 'dbr.chart', 'floatingbox', 'floatingbox';

select series, label, box_min, box_max
from mydb.data;

3D Bubble chart

A sample bubble chart with options

declare vTechA varchar(30);
declare vTechB varchar(30);
declare vTechC varchar(30);

select 'dbr.chart', 'bubble3d', '3D Bubble chart', 612, 345;
select 'dbr.chart.options', 'bubble.scale', 'y';

select 'dbr.chart.color', '0x00FF00', '0xFF0000','0x0000FF';

set vTechA = 'Techology A';
set vTechB = 'Techology B';
set vTechC = 'Techology C';

select 150 as 'x', 12 as 'y', 20 as 'r', vTechA as 'name', 'x1' as 'rn'
union
select 30, 60, 50, vTechA, 'x2'
union
select 300, 25, 50, vTechA, 'x3'
union
select 240, 125, 165, vTechA, 'x4'
union
select 50, 35, 30, vTechB, 'x5'
union
select 100, 50, 55, vTechB, 'x6'
union
select 130, 175, 95, vTechC, 'x7';

Chart shading

With the 'shading' option you can use CharDirector's chart shading feature

sselect 'dbr.chart', 'Pie3D', 'Shading Pie chart';
select 'dbr.chart.options', 'shading', 'ConcaveShading';
select 'dbr.chart.options', 'pie_label_format', '0';

select category, total_sales
from mydb.sales_by_film_category;

Donut chart with options

A donut-chart (or you can use alias 'doughnut'), with options

select 'dbr.chart', 'donut', '', 550, 350;

/* Set the donut thickness */
select 'dbr.chart.options', 'donut.size', 20;
/* Text in the middle */
select 'dbr.chart.options', 'donut.text', 'Income';
/* Font size of the text in the middle */
select 'dbr.chart.options', 'donut.font.size', 24;
/* Label font sizes */
select 'dbr.chart.options', 'label_font_size', 12;

select 'dbr.chart.color', '0x194996', '0x5F77BC', '0x9AA7DA', '0xE6E8F7', '0xF7F7FF';
select 'dbr.colstyle', 'Percentage', '{value} %\n{label}';

select Sector, Percentage
from mydb.income;

Scatter3D chart

A basic chatter chart includes x, y, and z data.

select 'dbr.chart', 'scatter3d';

select x, y, z
from scatter_3d;

MSScatter3D chart with options

A scatter3d chart has multiple options so you can finetune the chart to suit your needs.

/* A scatter chart with multiple options */
select 'dbr.chart', 'msscatter3d', '', 760, 440;

select 'dbr.chart.options', 'axis', 'Date', 'Importance', 'Effect';
select 'dbr.chart.options', 'marker', 'Unsuccessful', 'Cross2Shape()', 15;
select 'dbr.chart.options', 'scatter3d.y_axis_labels', "Low", "Medium", "High";

select 'dbr.chart.color', '0x00ff00', '0x0000cc';

select 'dbr.chart.options', 'scatter3d.plot_area', 385, 210, 400, 280, 240;
select 'dbr.chart.options', 'scatter3d.view_angle', 15, 25;

select 'dbr.chart.options', 'axis_font' ,'arialbd.ttf';
select 'dbr.chart.options', 'axis_font_size' , 10;

select 'dbr.chart.options', 'label_font_size', 8;

select 'dbr.chart.options', 'scatter3d.drop_line', 'Successful', null, 'dot';
select 'dbr.chart.options', 'scatter3d.drop_line', 'Unsuccessful', '0x0000cc', 'altdash';

select label, x, y, z
from msscatter_3d;

Basic Gantt-chart

In the most basic Gantt chart, we have a labels with start and end dates

select 'dbr.chart', 'gantt', 'Gantt';

select label, cast(start as date), cast(end as date)
from (
  select 'Market Research' as 'label', '2017-03-16' as 'start', '2017-03-30' as 'end', 'Market' as 'stage'
  union
  select 'Market Research', '2017-06-02', '2017-06-20', 'Market'
  union
  select 'Define Specifications', '2017-03-30', '2017-04-13', 'Specification'
  union
  select 'Overall Archiecture', '2017-04-13', '2017-04-27', 'Specification'
  union
  select 'Project Planning', '2017-04-20', '2017-05-04', 'Detail Specification'
  union
  select 'Detail Design', '2017-04-27', '2017-05-11', 'Final Specification'
  union
  select 'Software Development', '2017-05-04', '2017-06-03', 'Development'
  union
  select 'Test Plan', '2017-05-25', '2017-06-03', 'Development'
  union
  select 'Testing and QA', '2017-06-01', '2017-06-22', 'Development'
  union
  select 'User Documentation', '2017-06-03', '2017-06-22', 'Development'
 ) as q;

Gantt-chart within a day

If your Gantt-chart data covers only a day, the x-axis shows hours. You can set the scale with the gantt.start and gantt.end options-

select 'dbr.chart', 'gantt', 'Gantt hours', 600, 150;
select 'dbr.chart.options', 'gantt.scale', 1;
select 'dbr.chart.options','gantt.start', '2000-01-01 06:00:00';
select 'dbr.chart.options','gantt.end', '2000-01-01 21:00:00';

select label, cast(concat('2000-01-01 ',start) as datetime), cast(concat('2000-01-01 ',end) as datetime)
from (
  select 'Breakfast' as 'label', '07:00' as 'start', '07:20' as 'end'
  union
  select 'Lunch', '11:00', '10:30'
  union
  select 'Dinner', '17:45', '18:30'
 ) as q;

Gantt-chart with options

Gantt-chart has many options which you can use to configure the Gantt-chart.

select 'dbr.chart', 'gantt', 'Gantt with options', 800, 'auto';

select 'dbr.chart.options', 'category_order', 'Extra', 'Detail Specification';
select 'dbr.chart.options', 'category_order', 'Development';
select 'dbr.chart.options','gantt.baseline_text', 'Baseline text'; 
select 'dbr.chart.options','gantt.baseline_pattern.border', '0x000000'; /* Show no border for baseline bars */
select 'dbr.chart.options','gantt.baseline_pattern.color', '0x1B32C4'; /* Show baseline bars in dark blue */
select 'dbr.chart.options', 'gantt.start_of_day_format', '-{value|dd}'; /* Show dates as "Monthname dd" */
select 'dbr.chart.options','gantt.baseline', 'Market Research', '2017-03-12', '2017-03-20' /* Add baseline bars */
union
select 'dbr.chart.options','gantt.baseline', 'Overall Archiecture', '2017-04-08', '2017-04-26';
select 'dbr.chart.options', 'gantt.dateline', '2017-05-09'; /* Add vertical bar */
select 'dbr.chart.options', 'gantt.scale', 30; /* x-axis per month */
select 'dbr.chart.options','height_per_item', 30;

select label, cast(start as date), cast(end as date), stage
from (
  select 'Market Research' as 'label', '2017-03-16' as 'start', '2017-03-30' as 'end', 'Market' as 'stage'
  union
  select 'Market Research', '2017-06-02', '2017-06-20', 'Market'
  union
  select 'Define Specifications', '2017-03-30', '2017-04-13', 'Specification'
  union
  select 'Overall Archiecture', '2017-04-13', '2017-04-27', 'Specification'
  union
  select 'Project Planning', '2017-04-20', '2017-05-04', 'Detail Specification'
  union
  select 'Detail Design', '2017-04-27', '2017-05-11', 'Final Specification'
  union
  select 'Software Development', '2017-05-04', '2017-06-03', 'Development'
  union
  select 'Test Plan', '2017-05-25', '2017-06-03', 'Development'
  union
  select 'Testing and QA', '2017-06-01', '2017-06-22', 'Development'
  union
  select 'User Documentation', '2017-06-03', '2017-06-22', 'Development'
 ) as q;

Meter charts

myDBR offers various types of meter charts (a chart that have show one value in a scale): circle meter, semicircle, rectangular and linear meter. All share the same logic and options.

Formatting options for meter charts

You can format various parts of meter charts

  • Choose between step and gradient coloring
  • Define coloring scale (start and steps)
  • Define number scale
  • Add texts
  • Choose between themes
  • Set font, font size and font colors for both title and label
  • Choose pointer color

Setting steps and gradient colors

You can choose between step and gradient colors.

Using step colors and custom color scales

select 'dbr.chart','meter';
/* Use step colors */
select 'dbr.chart.options', 'meter.color.type', 'step';

/* First step represents where the coloring starts, subsequent steps define next step and used color */
select 'dbr.chart.options', 'meter.color.step', step, color
from (
  select 10 as 'step', null as 'color'
  union
  select 40, '0xFFFF00'
  union
  select 70, '0x00FF00'
  union
  select 100, '0xFF0000'
) as q;

select 60;

Using gradient colors and custom color scales

select 'dbr.chart','meter';
/* Use of gradient colors is done by default, you can also define the color type to gradient*/
select 'dbr.chart.options', 'meter.color.type', 'gradient';

/* Use custom scale min, max, step */
select 'dbr.chart.options', 'scale', 20, 180, 20;

/* Choose white theme */
select 'dbr.chart.options', 'meter.theme', 'white';

/* Set gradient steps */
select 'dbr.chart.options', 'meter.color.gradient', step, color
from (
select 20 as 'step', '0xd3d3d3' as 'color'
union
select 40, '0xd3d3d3'
union
select 100, '0xa9a9a9'
union
select 180, '0x000000'
) as q;


select 60;

Choosing meter theme

Meter themes (defined in defaults.php: default, white, black) allow defining various aspects of a chart

Using the black theme and setting text

select 'dbr.chart','rectangularmeter';
select 'dbr.chart.options', 'meter.text', 'CPU';
select 'dbr.chart.options', 'meter.theme', 'black';
select 'dbr.chart.options', 'scale', 20, 200, 20;

select 'dbr.chart','meter';

select 40;

Vertical linearmeter

You can set the linearmeter to be vertical

select 'dbr.chart','linearmeter';
select 'dbr.chart.options', 'linearmeter.orientation', 'vertical';

select 76;

Hierarchical and diagram charts - using Graphviz

With myDBR's Graphviz-support one can easily create hierarchical and diagram charts and the associated Graphviz-code. Basic charting can be done without any Graphviz-knowledge. In order to fully exploit Graphviz's capabilities, please consult the original Graphviz documentation.

Graphviz-charts have chart type of 'hierarchy' or 'network' which you select by dbr.chart-command.

Commands

dbr.chart.gv.style - Defines the default style for the object
dbr.chart.gv.node - Defines the individual node style
dbr.chart.gv.cluster - Defines the individual cluster style
dbr.chart.gv.engine - Selects the layout engine
dbr.chart.gv.renderer_formatter - Selects the renderer and optional formatter for the engine overriding the default ones, for example cairo:cairo

Syntax

select 'dbr.chart.gv.style', 'graph' | 'node' | 'edge' | 'cluster', style_definition
select 'dbr.chart.gv.node', nodeID, name, style_definition, [clusterID]
select 'dbr.chart.gv.cluster', clusterID, name, style_definition
select 'dbr.chart.gv.engine', engine
select 'dbr.chart.gv.renderer_formatter', renderer[:formatter]

myDBR's Graphviz basics

Nodes represent the connected elements in Graphviz. If no node definitions are given, the chart with it's nodes is drawn with default Graphviz formatting.

In Graphviz charts there are two options for the data query:

Option 1 where the chart is drawn based on single values / node.

select node1, node2 [, edge_style]
from mydb.Data;

Option 2 where node contains both ID value and the visible value to the user. This can be used, when making charts to being exported and you want to keep the IDs.

select node1_id, node2_id, node1_name, node2_name [, edge_style]
from mydb.Data;

Here is a simple example of a hierarchical chart.

select 'dbr.chart', 'Hierarchy';

select 'Corporate', 'Sales'
union
select 'Corporate', 'Production'
union
select 'Corporate', 'Management'
union
select 'Sales', 'Northern Sales'
union
select 'Sales', 'Southern Sales'
union
select 'Production', 'Top part production'
union
select 'Production', 'Bottom part production'
union
select 'Extra', null;

Graph

Formatting needed for the Graphviz graph can be passed through the dbr.chart.gv.style-command.

select 'dbr.chart.gv.style', 'graph', style_definition;

select 'dbr.chart', 'Hierarchy';
select 'dbr.chart.gv.style', 'graph', 'rankdir=LR,splines=false';

select 'Corporate', 'Sales'
union
select 'Corporate', 'Production'
union
select 'Corporate', 'Management'
union
select 'Sales', 'Northern Sales'
union
select 'Sales', 'Southern Sales'
union
select 'Production', 'Top part production'
union
select 'Production', 'Bottom part production';

Node

The nodes can be formatted all with the same formatting or use specific formatting for individual nodes. In the next example, we'll change the default formatting and format selected nodes individually.

select 'dbr.chart.gv.node', nodeID, name, style_definition[, clusterID]

The style_definition is a comma-separated list of Graphviz-attributes.

select 'dbr.chart', 'Hierarchy';

/* This will change the default node formatting */

select 'dbr.chart.gv.style', 'node', 'shape=box,fillcolor=#CCCCFF,style=filled';
/* This will change formatting from individual nodes */

select 'dbr.chart.gv.node', 'Sales', 'Sales', 'shape=box3d,fillcolor=red,style=filled';
select 'dbr.chart.gv.node', 'Production', 'Production', 'shape=box3d,fillcolor=red,color=white,style=filled';

select 'Corporate', 'Sales'
union
select 'Corporate', 'Production'
union
select 'Corporate', 'Management'
union
select 'Sales', 'Northern Sales'
union
select 'Sales', 'Southern Sales'
union
select 'Production', 'Top part production'
union
select 'Production', 'Bottom part production';

Subgraph/cluster

Adding clusters to the chart can be done by using 'dbr.chart.gv.cluster' command. In addition to the cluster itself, the nodes attached to the cluster also needs to be defined.

select 'dbr.chart.gv.cluster', clusterID, name, style_definition

select 'dbr.chart', 'Hierarchy';
select 'dbr.chart.gv.style', 'graph', 'rankdir=TB,splines=true,ranksep=0.8'; 
select 'dbr.chart.gv.style', 'node', 'shape=box,fillcolor=#CCCCFF,style=filled,fontsize=10';

select 'dbr.chart.gv.node', 'Sales', 'Sales', 'shape=box,fillcolor=red,fontcolor=white,style="rounded,filled",fontsize=10', 1;
select 'dbr.chart.gv.node', 'Production', 'Production', 'shape=box,fillcolor=red,fontcolor=white,style="rounded,filled",fontsize=10', 1;

select 'dbr.chart.gv.cluster', 1, 'Organization', 'style=filled,fillcolor=lightgrey,labelloc=b,fontsize=12';

select 'Corporate', 'Sales'
union
select 'Corporate', 'Production'
union
select 'Corporate', 'Management'
union
select 'Sales', 'Northern Sales'
union
select 'Sales', 'Southern Sales'
union
select 'Production', 'Top part production'
union
select 'Production', 'Bottom part production';

Edge

Passing formatting information to edges can be done simply adding the style information to the node-query.

select 'dbr.chart', 'Hierarchy';

select 'Corporate', 'Sales', 'color=red,arrowhead=empty'
union
select 'Corporate', 'Production', ''
union
select 'Corporate', 'Management', ''
union
select 'Sales', 'Northern Sales', ''
union
select 'Sales', 'Southern Sales', ''
union
select 'Production', 'Top part production', ''
union
select 'Production', 'Bottom part production', '';

Layout engine

myDBR allows you to select the layout engine used by using the command dbr.chart.gv.engine. See the Graphviz documentation for more info on layout engines and how to choose the most suitable one for your chart.

select 'dbr.chart.gv.engine', engine

Possible engines:

  • dot - makes 'hierarchical' or layered drawings of directed graphs (default engine)
  • neato - makes 'spring model' layouts (Kamada-Kawai algorithm)
  • fdp - makes 'spring model' layouts (Fruchterman-Reingold algorithm)
  • twopi - makes radial layout after Graham Wills 97
  • circo - makes circular layout, after Six and Tollis 99, Kauffman and Wiese 02

Example of engine usage:

declare vShape varchar(100)

/* Make it a network chart */

select 'dbr.chart', 'network';

/* Use the neato engine */

select 'dbr.chart.gv.engine', 'neato';

/* Prepare the shape, we'll get the color from the data */

select 'shape=octagon,style=filled,fillcolor=' into vShape;

/* Set individual node's color, no label */

select 'dbr.chart.gv.node', id, null, concat(vShape, color)
from mydb.nodes;

/* Get the chart data */

select parent, child
from mydb.node_connections;
circo engine neato engine
select 'dbr.chart.gv.engine', 'circo'; select 'dbr.chart.gv.engine', 'neato';

Example: Same chart drawn with different layout engines.

Notes: Report linking and Graphviz-chart size

Report linking (clicking a node launches another report) is also available for Graphviz charts. The report's parameters must always be bound to the first column.

select 'dbr.chart', 'network';
select 'dbr.report', 'sp_DBR_NodeZoom','vNodeID=1';

select parent, child
from mydb.node_connections;

If you want to scale the size of a Graphviz-chart, you can use both 'dbr.chart' -commands x,y parameters or native Graphviz scaling (see size-attribute). If both are applied, myDBR uses the size-attribute.

select 'dbr.chart', 'network';
select 'dbr.chart.gv.style', 'graph', 'size="4,3"';
select 'dbr.report', 'sp_DBR_NodeZoom','vNodeID=1';

select parent, child
from mydb.node_connections;