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 administration 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: Retrieving data from a Separated on-line marketing database Restricting the database-level permissions of reporting queries via a connection string with a limited database user
     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 Xperience database, add the following objects into the report’s layout:
   • Tables
   • Graphs
   • HTML graphs
   • Values

Additionally, macro expressions are supported in the report layout.

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

Creating tables in reports

Tables allow you to retrieve data from the Xperience 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 administration 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.

SELECT PageTemplateDisplayName AS [Template Name], ...

Creating graphs in reports

Graphs allow you to retrieve data from the Xperience 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 administration 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{width="100" }- **Cylinder** – uses cylinders{width="100" }**Bar stacked chart**:- **Bar** – uses rectangular bars{width="100" }- **Cylinder** – uses cylinders{width="100" }- **Area** – uses a line chart with the space under the lines filled with the respective color{width="100" }**Pie Chart**:- **Pie** – uses a standard circular chart divided into sectors{width="100" }- **Doughnut** – uses a circular chart with a blank center{width="100" }**Line chart**:- **Line** – uses straight lines to connect values{width="100" }- **SpLine** – uses smooth curved lines{width="100" }
   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 Xperience 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.

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.

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

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

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