Creating reports

To watch the activity in the system and on websites, you need to create reports. Then you can add components to the report to display the collected data, such as graphs, tables and values.

  1. Open the Reporting application.
  2. Select a category in the tree.
  3. Click New report.
  4. Type in the Report display name.
  5. Click Save.
    • The General tab of the report editing interface opens.

      General tab properties

      Report display name

      Sets the name of the report displayed in the administration interface.

      Report code name

      A unique name that serves as an identifier of the report, for example in the API or URLs.

      Report category

      Shows the category under which the report belongs. You can move the report to a different category by selecting one from the drop-down.

      Connection string

      Sets the database connection string used by the report’s components (graphs, tables and values) when loading data. You can override this value for individual components by editing their details.

      Only users who have the Set connection string permission for the Reporting module are allowed to change this property’s value.

      The system retrieves the list of connection strings from the <connectionStrings> section of the application’s web.config file. The (default) option represents the CMSConnectionString added by the application’s initial database installer.

      You can check the Inherit box to load the value from the Default report connection string setting configured in Settings -> Security & Membership.

      You can use reporting connection strings for the following scenarios:

      Allow public users to see this report

      Indicates if the report should be visible by public users if it is published on the website using a reporting web part.

      Enable subscription

      If enabled, users with the Subscribe permission for the Reporting module are allowed to subscribe to the report and its components (graphs, tables or values).

      This setting is also available when configuring the details of individual components. Subscribing to specific components is only possible if both settings are enabled.

  6. Define the Layout of the report using the WYSIWYG editor. To retrieve and display information from the Kentico database, add the following objects into the report’s layout:

Additionally, macro expressions are supported in the report layout.

You can view the output of the report on the View tab.

Localizing strings in reports

If you need to create a single report in multiple languages, follow the instructions given in Working with resource strings.

Creating tables in reports

Tables allow you to retrieve data from the Kentico database using an SQL query.

  1. In the Reporting application, edit a report on the General tab.

  2. Click New in the Tables section below the layout editor.

  3. Define the properties of the new table:

    Report table properties

    Default

    Display name

    The name of the table shown in the user interface.

    Code name

    Name used in your code.

    Enable export

    If enabled, users who view the table are 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 table in the report, which opens a menu with possible export actions.

    Enable subscription

    If enabled, users will be able to subscribe to the currently edited report table. To allow subscriptions, it is also necessary to have the Enable subscription box checked on the General tab of the given report.

    Query

    Query

    Here you can add the SQL query used to retrieve data to be displayed by the table.

    Is stored procedure

    Indicates if the query is a stored procedure or not.

    Connection string

    Sets the database connection string used by the table’s query.

    Only users who have the Set connection string permission for the Reporting module are allowed to change this value.

    The system loads the list of connection strings from the <connectionStrings> section of the application’s web.config file. The (default) option represents the CMSConnectionString added by the application’s initial database installer.

    You can check the Inherit box to load the Connection string value set for the parent report.

    No record text

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

    Skin

    Skin ID

    ID of the .NET skin (stored in the .skin files in ~/AppThemes/<theme name>) which will be used for the table.

    Paging

    Enable paging

    If enabled, paging will be enabled when the report table is displayed. The paging can be configured by the two properties below.

    Page size

    Number of table rows per page.

    Paging mode

    Type of paging controls displayed below the table. The following options are available:

    • Previous-next buttons - displays buttons leading to the previous and next page
    • Page numbers - displays page numbers leading to the corresponding pages
    • Previous-next-first-last buttons - displays buttons leading to the first, last, previous and next page
    • Page numbers-first-last buttons - displays page numbers leading to the corresponding pages and buttons leading to the first and last page
  4. Click Save & Close.

  5. Place the cursor in the layout editor where you want to put the table.

  6. Select the defined table from the list in the Tables section.

  7. Click Insert.

    • Tables are entered into the report layout editor as an expression in the following format: %%control:ReportTable?<report code name>.<table code name>%%.
  8. Click Save.

Example of a report table

Writing queries for tables

The queries you write for tables are standard SQL queries that pull data from the Kentico database. For information about pages, you can use the View_CMS_Tree_Joined table that returns published versions of all pages.

Table column names

The table column names use the same names as the column names from the returned data set. If you need to use user friendly names, you can use the following syntax in the query:




SELECT PageTemplateDisplayName AS [Template Name], ...


Creating graphs in reports

Graphs allow you to retrieve data from the Kentico database and display it in various types of visual formats.

  1. In the Reporting application, edit a report on the General tab.

  2. Click New in the Graphs section below the layout editor.

  3. Define the properties of the new graph:

    Report graph properties:

    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 are 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 opens a context menu with possible export actions.

    Enable subscription

    If enabled, users will be able to subscribe to the currently edited report graph. To allow subscriptions, it is also necessary to have the Enable subscription box checked on the General tab of the given report.

    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.

    Connection string

    Sets the database connection string used by the graph’s query.

    Only users who have the Set connection string permission for the Reporting module are allowed to change this value.

    The system loads the list of connection strings from the <connectionStrings> section of the application’s web.config file. The (default) option represents the CMSConnectionString added by the application’s initial database installer.

    You can check the Inherit box to load the Connection string value set for the parent report.

    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
    • Cylinder - uses cylinders

    Bar stacked chart:

    • Bar - uses rectangular bars
    • Cylinder - uses cylinders
    • Area - uses a line chart with the space under the lines filled with the respective color

    Pie Chart:

    • Pie - uses a standard circular chart divided into sectors
    • Doughnut - uses a circular chart with a blank center

    Line chart:

    • Line - uses straight lines to connect values
    • SpLine - uses smooth curved lines

    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 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.

  4. Click Save & Close.

  5. Place the cursor in the layout editor where you want to put the graph.

  6. Select the defined graph from the list in the Graphs section.

  7. Click Insert.

    • Graphs are entered into the report layout editor as an expression in the following format: %%control:ReportGraph?<report code name>.<graph code name>%%.
  8. Click Save.

Example of a report graph

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.

Creating HTML graphs in reports

In addition to the image-based graphs, data can be visually represented in HTML graphs. Graphs of this type are composed purely out of HTML code (table and DIV elements). As a result, they can be dynamically scaled according to the amount of data that needs to be displayed, unlike an image with a predefined size.

HTML graphs always use a horizontal bar layout, which can easily be extended to display any number of items. In most cases where scaling is not an issue, it is recommended to use standard graphs, since they offer more customization options and graphical flexibility.

Like other reporting tools, HTML graphs retrieve the data to be displayed using queries. The queries must return at least two columns: the first column is used for items and the others for their values. If more than two columns are specified, the values of these additional columns are displayed below each other as differently colored bars.

Example of an HTML graph

By default, data is displayed in descending order, i.e. with the newest items at the top of the graph. To create a HTML graph:

  1. In the Reporting application, edit a report on the General tab.

  2. Click New in the HTML graphs section below the layout editor.

  3. Define the properties of the new HTML graph:

    HTML graph properties

    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 are 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 opens a context menu with possible export actions.

    Enable subscription

    If enabled, users will be able to subscribe to the currently edited HTML graph. To allow subscriptions, it is also necessary to have the Enable subscription box checked on the General tab of the given report.

    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.

    Connection string

    Sets the database connection string used by the graph’s query.

    Only users who have the Set connection string permission for the Reporting module are allowed to change this value.

    The system loads the list of connection strings from the <connectionStrings> section of the application’s web.config file. The (default) option represents the CMSConnectionString added by the application’s initial database installer.

    You can check the Inherit box to load the Connection string value set for the parent report.

    No record text

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

    Title

    Title

    Sets the title of the graph.

    Legend

    Legend title

    Sets the text caption of the legend.

    Display legend

    Indicates if the legend should be displayed.

    Series

    Item name 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.

    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 Standard date and time format specifiers without quotes.

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

    Item value format

    Sets the format of the text displaying the values of series items on the Y axis of the graph.

    This field supports all types of Kentico macro expressions. The following macros should be used most frequently:

    • {%ser%} - resolves into the name of the current item’s series, i.e. the type of the value.
    • {%xval%} - resolves into the X axis value of the current item.
    • {%yval%} - resolves into the Y axis value of the current item.
    • {%pval%} - resolves into the percentage that the value of the current item represents out of the sum of all values in the graph. If there are multiple types of Y axis values, they are all included in the total sum.

    Examples:

    • {% ser %} = {% yval %} - displays the name of the series and its value (e.g. Visits = 287).
    • {% Format(ToDouble(pval), “{0:0.0}%”) %} - displays the item’s percentage value rounded to one decimal place (e.g. 5.1%).

    Item tooltip

    Determines the content and format of the tooltip that is displayed when hovering over a series item in the graph. The macro expressions described in the Item value format property can also be used in this field.

    Item link

    Causes the series items in the graph to serve as links to the specified URL when clicked. The macro expressions described in the Item value format property can also be used in this field.

  4. Click Save & Close.

  5. Place the cursor in the layout editor where you want to put the HTML graph.

  6. Select the defined HTML graph from the list in the HTML graphs section.

  7. Click Insert.

    • HTML graphs are entered into the report layout editor as an expression in the following format: %%control:ReportHtmlGraph?<report code name>.<graph code name>%%.
  8. Click Save.

HTML graphs on the live site

Because of the way they are constructed, HTML graphs are only displayed correctly in the administration interface. The necessary styles will not be applied on the live site.

Therefore, it is recommended to avoid publishing reports containing HTML graphs on your website (you can find more information about this process in Displaying reports on websites).

Creating values in reports

A value is an object that you can place into the layout of a report, which can be used to display a single scalar value returned by a query in a specified string format.

  1. In the Reporting application, edit a report on the General tab.

  2. Click New in the Values section below the layout editor.

  3. Define the properties of the new value:

    Report value properties

    Default

    Display name

    The name of the item in the list

    Code name

    Name used in your code

    Enable subscription

    If enabled, users will be able to subscribe to the currently edited report value. To allow subscriptions, it is also necessary to have the Enable subscription box checked on the General tab of the given report.

    Query

    Query

    Here you can add the SQL query used to retrieve data to be displayed by the value.

    Is stored procedure

    Indicates if the query is a stored procedure or not.

    Connection string

    Sets the database connection string used by the value’s query.

    Only users who have the Set connection string permission for the Reporting module are allowed to change this value.

    The system loads the list of connection strings from the <connectionStrings> section of the application’s web.config file. The (default) option represents the CMSConnectionString added by the application’s initial database installer.

    You can check the Inherit box to load the Connection string value set for the parent report.

    Format

    Formatting string

    You can format the displayed value using standard .NET expressions. For example:

    • {0} - displays the value
    • {0:F1} - displays the value as a floating point number with one digit displayed after the decimal point
  4. Click Save & Close.

  5. Place the cursor in the layout editor where you want to put the value.

  6. Select the defined value from the drop-down list in the Values section.

  7. Click Insert.

    • Values are entered into the report layout editor as an expression in the following format: %%control:ReportValue?<report code name>.<value code name>%%.
  8. Click Save.

Writing queries for scalar value

The queries for scalar values may return any number of columns and rows, but the only value that will be displayed is the value in the first column of the first row of the result set.

See Example - creating a simple report for an example on how to create a report with a table, graph and a value.