Creating a new Table

A table 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 tables:

Display name

The name of the table shown in the user interface.

Code name

Name used in your code.

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.

Skin ID

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

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.

Page 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

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

%%control:ReportTable?<report code name>.<table code name>%%

This is done automatically when the Insert button is used.

Writing queries for tables

The queries you write for tables are standard SQL queries that pull data from the Kentico CMS database. You can find the description of Kentico CMS database tables and views in Kentico CMS Database Reference that is a part of the standard installation.

For information about documents, you can use the View_CMS_Tree_Joined table that returns published versions of all documents.

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

Example:

1. Click Add in the Tables section on the right. Enter the following values:

•	Display name: Pages by Page Template

•	Code name: PagesByPageTemplate

•

Query:

SELECT PageTemplateDisplayName as [Template Name], DocumentNamePath as [Document]

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

ORDER BY PageTemplateDisplayName

•	Is stored procedure: no

•	SkinID: leave empty

•	Enable paging: enabled

•	Page size: 10

•	Page mode: Page numbers

Click OK.

2. Now place the cursor in the layout editor on a new line under the title, choose the table in the Tables section and click Insert. A string like %%control:ReportTable?PagesByPageTemplate.PagesByPageTemplate%% will be added to the text area.

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

devguide_clip0694

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

Page url: http://devnet.kentico.com/docs/5_5r2/devguide/index.html?creating_a_new_table.htm