Insights

Kentico’s reporting application is a powerful tool that provides visualization of data on website visits, user registration, document activity, and more. A report can easily be created for any data that can be queried from your Kentico or other external database, including custom table data. The reports can be saved, subscribed to, displayed on your site, and exported as Excel, CSV, or XML. In this post I will describe how to generate custom reports and use the many reporting features.

When you navigate to the reporting application you will notice that there are many reports already created out of the box. Some of the reports include page views, amount of time spent on a page, browser types used, traffic sources (direct, referring sites, search engines and keywords, crawler visits), 404s, referring URLs, registered users, exit pages, landing pages, social marketing, conversions, orders, products, sales and more!

To create a custom report select New Category & enter the category name.

Once you have created a new category you can start adding reports to it. Click New Report on the upper left hand corner of the page and enter your report name. In this example I will create a value and table showing the blog posts created within a specific date range, and containing a specific keyword in the title.
To start I will create three parameters for the report. You can create parameters to query from any field in the database. I am using a macro to automatically set the start date 30 days from now.

• Field name: FromDate
• Data type: Date
• Required: True
• Default value: {% DateTime.Today.AddDays(-30) |(user)nmiller|(hash)aaa56a0e32ed0647f3cff86768488415e5ba312cd7d317f8a524ef47b68cfec0%}
• Field Caption: Start Date
• Form control: Calendar

• Field name: ToDate
• Data type: Date
• Required: True
• Default value: {% DateTime.Today |(user)nmiller|(hash)b51665a7e8e1f5d4697746e1eb3438823be74bd10440d697a8787fabbb7dbe1e%}
• Field Caption: End Date
• Form control: Calendar

• Field name: Keyword
• Data type: Text
• Required: False
• Field Caption: Keyword
• Form control: Text box

After the parameters are created, click on the General tab and scroll to the bottom. You can create graphs, tables, and values by entering SQL queries. First I will show you how to create a simple value.
Click the New button to the right of the Values field. A dialog will open where you can enter the name of the query and the query details.

• Display name: Total blog posts
• Enable subscription: true
• Query:

  SELECT COUNT(*)
  FROM CONTENT_BlogPost
  WHERE BlogPostDate >= @FromDate AND BlogPostDate <= @ToDate
  AND (@Keyword IS NULL OR BlogPostTitle LIKE '%'+@Keyword+'%')

• Formatting string: Total posts: {0}

After the query is saved, scroll to the bottom and select the Value that you just created, then click the green Insert button to add it to the layout, then save the layout

To create a table showing more detailed data click on the New button next to the Table field at the bottom of the General tab and complete the fields as suggested below.

• Display name: Blog details
• Enable export: true
• Enable subscription: true
• Query:

  SELECT
  BlogPostID,
  BlogPostTitle,
  BlogPostDate
  FROM
  CONTENT_BlogPost
  WHERE
  BlogPostDate >= @FromDate
  AND
  BlogPostDate <= @ToDate
  AND
  (@Keyword IS NULL OR BlogPostTitle LIKE '%'+@Keyword+'%')

After saving the report value, insert the table into the layout and save it. Click on the View tab to see the report.

To export the data as Excel, CSV, or XML, simply right click on the table and select the format you want to export the data as.

To create or manage subscriptions to the report click on the Subscriptions tab. You can enter an email address, the range of data you want to send, the dates you want the report sent, and the parameters associated with the report. In addition, you can choose to subscribe to the whole report or just a specific piece of the report.

If you want to display the report on your site, Kentico has a report web part that can be dropped into a template and configured to display different reports. The web part also allows users to subscribe to the report and export the data from the page.

For more information on using Kentico’s powerful reporting application, take a look at the documentation:

Creating reports
Defining report parameters
Displaying reports on websites
Subscribing to reports
Configure permissions for report access
Specify connection string for reports