Kentico CMS 6.0 Developer's Guide

Creating a new Graph

Creating a new Graph

Previous topic Next topic Mail us feedback on this topic!  

Creating a new Graph

Previous topic Next topic JavaScript is required for the print function Mail us feedback on this topic!  

A graph can be placed into the layout of a report and can be used to display data retrieved from the Kentico CMS database by a query.

 

The following properties can be used to configure graphs:

 

Default:

 

Display name

Display name of the graph shown in the user interface.

Code name

Code name of the graph.

Enable export

If enabled, users who view the graph will be able to export the displayed data to external files using the Microsoft Excel (XLSX), CSV or XML format. The data export feature may be accessed by right‑clicking the graph in the report, which will open a context menu with possible export actions.

 

Query:

 

Query

Database query that extracts the data that will be displayed in the graph. It must return at least two columns: first one for categories, the other columns are used for values.

Is stored procedure

Indicates if the specified query is a stored procedure.

No record text

Text to be displayed if the query doesn't return any data.

 

Chart type:

 

Graph type

The following graph types are available:

 

Bar chart - bar graph, accepts multiple values and displays them next to each other.

 

Bar stacked chart - bar graph, accepts multiple values and displays them on top of each other.

 

Pie chart - pie graph, accepts only one column for values.

 

Line chart - line graph, accepts multiple values and displays them as separate lines.

Drawing style

The following chart styles are available:

 

Bar chart:

 

Bar - uses rectangular column bars

devguide_clip1316
 

 

Cylinder - uses cylinders

devguide_clip1313

 

Bar stacked chart:

 

Bar - uses rectangular bars

devguide_clip1317

 

Cylinder - uses cylinders

devguide_clip1325

 

Area - uses a line chart with the space under the lines filled with the respective color

devguide_clip1318

 

Pie Chart:

 

Pie - uses a standard circular chart divided into sectors

devguide_clip1314

 

Doughnut - uses a circular chart with a blank center

devguide_clip1315

 

Line chart:

 

Line - uses straight lines to connect values

devguide_clip1319

 

SpLine - uses smooth curved lines

devguide_clip1320

 

Overlay

If enabled, charts with multiple values display them behind each other with the lower values in the front. Only available for Bar charts displayed in 3D.

100% stacked

If enabled, values are displayed as a percentage of their category's column. Only available for Bar stacked charts.

Orientation

Determines if bars are displayed horizontally or vertically. Only available for Bar and Bar stacked charts with the Bar drawing style.

Drawing design

Determines the aesthetic design of Pie charts.

Label style

Determines the style of 'pie piece' descriptions for Pie charts.

Doughnut radius

Determines the width of Doughnut style Pie charts. Larger numbers decrease the size of the center. Only available for Pie charts with the Doughnut drawing style.

Collect pie slices

Items that represent a smaller percentage of the Pie chart than the value entered here will be added together and displayed as a single item labeled Others. This ensures that pie charts remain legible, even if there are many items with very small values.

Show as 3D

If enabled, charts are displayed in 3D.

Rotate X

Rotates the chart around its X axis. Accepts values from -90 to 90. Only available if Show as 3D is enabled.

Rotate Y

Rotates the chart around its Y axis. Accepts values from -180 to 180. Only available if Show as 3D is enabled.

Width

Determines the width of the chart image.

Height

Determines the height of the chart image.

Show Grid

Shows a thin dotted line grid in the graph chart, Not available for Pie charts.

 

Title:

 

Title

Title of the chart.

Title font

Determines font properties of the chart title.

Title color

Determines the color of the chart title. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used.

Title position

Determines the position of the chart title.

 

Legend:

 

Background color

Determines the background color of the legend. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used.

Border color

Determines the border color of the legend. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used.

Border size

Determines the size of the legend border.

Border style

Determines the style of the legend border.

Position

Determines the position of the legend in the chart.

Legend inside

If enabled, the legend is displayed inside the chart area.

Fixed legend

Allows a custom description to be set for the value in the legend that will be used instead of the name of the source column. This field is only usable for charts that display one type of series, i.e. each item has a single value. It also cannot be used for Pie charts.

 

It is possible to enter static text or use a macro that resolves into the currently selected value of a report parameter in format {%<parameter name>%}.

 

For example, if the report has a parameter named CampaignName that allows users to display the statistics of a selected marketing campaign, {%CampaignName%} could be entered into this field, and the legend value description would automatically contain the name of the currently displayed campaign.

Legend title

Sets the text caption of the legend.

 

X-axis:

 

X axis title

Title of the horizontal axis in the chart.

Title color

Determines the color of the X axis title. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used.

X axis angle

Determines the declination angle of X axis descriptions. Setting this parameter to 90 causes upright descriptions. Accepts values from -90 to 90.

X axis format

Can be used to specify the format of item descriptions on the X axis that are in numerical or date‑time format.

 

Numeric formatting:

 

Numbers can be formatted using .NET Custom numeric format strings enclosed in curly brackets.

 

Examples:

 

{Item #} - X axis descriptions will be displayed as Item 1, Item 2 etc.

{0.00} - numeric X axis descriptions will be displayed with precision of two decimal places.

 

Date and time formatting:

 

The format can be set using single‑letter .NET Standard date and time format specifiers without quotes.

 

In addition, any custom formatting can be defined using expressions enclosed in curly brackets. For example, {yyyy - MM - dd - hh:mm} would specify a date and time format like:
2010 - 08 - 19 - 12:30

Title font

Determines font properties of the X axis title.

Position

Determines the position of the X axis title.

Axis label font

Determines font properties of X axis descriptions.

X axis interval

Sets the interval between X axis descriptions.

Use X axis sorting

If enabled, values are connected in the order they appear in on the X axis, otherwise they are connected in the order they have in the returned dataset. Only used by Line charts and Bar Stacked charts with the Area drawing style.

 

Y-axis:

 

Y axis title

Title of the vertical axis in the chart.

Title color

Determines the color of the Y axis title. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used.

Y axis angle

Determines the declination angle of Y axis descriptions. Setting this parameter to 90 causes upright descriptions. Accepts values from -90 to 90.

Y axis format

Can be used to specify the format of value descriptions on the Y axis that are in numerical or date-time format. The same formatting options can be used as in the X axis format field described above.

Use X axis settings

If enabled, X axis settings are used for the Title font, Position and Axis label font properties.

Title font

Determines font properties of the Y axis title.

Position

Determines the position of the Y axis title.

Axis label font

Determines font properties of Y axis descriptions.

 

Series:

 

Primary background color

Determines the primary color of series items in the chart. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used. Not available for Line charts.

Secondary background color

Determines the secondary color of series items in the chart. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used. Not available for Line charts.

Gradient

Gradient of the colors of the series items in the chart. Transitions from Primary to Secondary background colors. Not used when displayed in 3D. Not available for Line charts.

Border color

Determines the border color for series items in the chart. Not available for Line charts.

Border size

Determines the border size for series items in the chart. Not available for Line charts.

Border style

Determines the border style for series items in the chart. Not available for Line charts.

Display item value

If enabled, values are displayed above series items.

Item value format

Sets the format of the text displaying the values of series items in the chart. This overrides the Display item value property.

 

Standard MS chart keywords can be placed into this field, such as for example:
 

#VALX - displays the current value of the X axis.

#VALY - displays the current value of the Y axis.

#AXISLABEL - displays the current X axis label.

#INDEX - displays a number determined by the order of the series item on the X axis, starting from 0.

#SER - displays the name of the current series, i.e. the type of the value.
 

If the current value is numerical, the displayed format can be modified by adding the following parameters after the keyword:

 

{P} - displays the number as a percentage.

{C} - displays the number as a monetary amount in the currency of the current language culture. specified in the browser; please be aware that this does not convert the value, it only influences the format.

{F} - displays the number with a floating point, this is the default parameter.

{E} - displays the number in exponential format.

 

The number of digits after the decimal point can be specified within the curly brackets.
 

For example #VALY{F2} displays Y axis values with a floating point and a precision of 2 decimal places.

Line color

Determines the line color used in Line charts. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used.

Line size

Determines the line size used in Line charts.

Line style

Determines the style used in Line charts. Not used when displayed in 3D.

Symbols

Determines the symbols used for values in Line charts.

Item tooltip

Determines the content and format of the tooltip that is displayed when hovering over a series item in the chart. This field supports both Kentico CMS macro expressions and standard MS chart keywords as described in the Item value format property.

Item link

Causes the series items in the chart to serve as links to the specified URL when clicked. The same macro expressions and keywords as described in the Item tooltip property can be used here as well.

Values as percent

If checked, graphs with multiple types of series (several values per item) will convert item values into a percentage out of the sum of all values for that item.

 

For example, if an item has two values, 3 and 9, they would be converted to 25 and 75 respectively.

 

When using this setting, it is necessary to set the Chart Area -> Scale max property to at least 100 to ensure that all types of data are displayed correctly.

 

Not available for Pie charts, since these already display one type of value as a percentage.

 

Chart area:

 

Primary background color

Determines the primary background color of the chart area. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used.

Secondary background color

Determines the secondary background color of the chart area. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used.

Gradient

Gradient of the chart area background colors. Transitions from Primary to Secondary background colors.

Border color

Determines the border color of the chart area. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used.

Border size

Determines the size of the chart area border.

Border style

Determines the style of the chart area border.

Scale min

Sets the minimum Y axis value that is required for an X axis category to be displayed. Not used by Pie charts.

Scale max

Sets the maximum value that is displayed on the Y axis. Not used by Pie charts.

Ten powers

If large values are present in the chart, they are divided by appropriate ten powers and the division ratio is displayed with the y-axis title. Not used by Pie charts.

Reverse Y axis

If enabled, the vertical axis is reversed. Not used by Pie charts.

Border skin style

Determines the skin of the chart area border.

 

Plot area:

 

Primary background color

Determines the primary background color of the plot area. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used.

Secondary background color

Determines the secondary background color of the plot area. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used.

Gradient

Gradient of the plot area background colors. Transitions from Primary to Secondary background colors.

Border color

Determines the border color of the plot area. Accepts standard HTML color names and hexadecimal color codes or the color selector can be used.

Border size

Determines the size of the plot area border.

Border style

Determines the style of the plot area border.

 

Graphs are entered into the report layout editor as an expression in the following format:

 

%%control:ReportGraph?<report code name>.<graph code name>%%

 

This is done automatically when the Insert button is used.

 

 

 

Writing queries for pie charts

 

The queries for pie chart graphs must return two columns: the item categories and their values. The graph automatically calculates the displayed size of the given category.

 

Writing queries for bar graphs

 

The queries for bar chart graphs must return at least two columns: the item categories and their values. If you specify more than two columns, the additional columns will be displayed side-by-side (Bar charts), in front of each other (Bar charts with the Overlay setting enabled), on top of each other (Bar stacked charts) or they will divide one column by percentage (Bar stacked charts with the 100% stacked setting enabled).

 

Writing queries for line charts

 

The queries for line chart graphs must return at least two columns: the item categories and their values. If you specify more than two columns, the additional columns will be displayed as separate lines.

 

 

Example:

 

1. Switch back to the General tab. Click New in the Graphs section below the layout editor. Enter the following values:

 

Display name: Favorite Page Templates

Code name: FavoritePageTemplates

Query:
 

SELECT TOP 5 PageTemplateDisplayName AS [Template Name], COUNT(PageTemplateDisplayName) AS [Usage]
FROM View_CMS_Tree_Joined
LEFT JOIN CMS_PageTemplate
ON CMS_PageTemplate.PageTemplateID = View_CMS_Tree_Joined.DocumentPageTemplateID
WHERE PageTemplateDisplayName IS NOT NULL AND PageTemplateIsReusable = 1
GROUP BY PageTemplateDisplayName
ORDER BY COUNT(PageTemplateDisplayName) DESC

 

Graph type: Pie chart

Title: Favorite page templates

Series -> Display item value: Disabled (unchecked)

 

Click OK.

 

2. Now place the cursor in the layout editor on a new line under the table, select the graph from the drop-down list in the Graphs section and click Insert. A string like %%control:ReportGraph?PagesByPageTemplate.MostFavoritePageTemplates%% will be added to the text area.

 

Click Save to save the changes and switch to the View tab. You will see a graph similar to this in the report:

 

devguide_clip1210

 

Continued in the example section of the Creating a new Value topic.