Kentico CMS 6.0 Developer's Guide

Defining report parameters

Defining report parameters

Previous topic Next topic Mail us feedback on this topic!  

Defining report parameters

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

Reports may be filtered using parameters. You can define custom parameters on the Parameters tab of the Report properties dialog.

 

Context Parameters

 

In your queries, you can use parameters that provide information about the current context when the report is viewed, such as current user, current site, etc. Here's a list of all available context variables:

 

@CMSContextCurrentUserID

@CMSContextCurrentUserName

@CMSContextCurrentUserDisplayName

@CMSContextCurrentSiteID

@CMSContextCurrentSiteName

@CMSContextCurrentSiteDisplayName

@CMSContextCurrentDomain

@CMSContextCurrentTime

@CMSContextCurrentURL

@CMSContextCurrentNodeID

@CMSContextCurrentCulture

@CMSContextCurrentDocumentID

@CMSContextCurrentAliasPath

@CMSContextCurrentDocumentName

@CMSContextCurrentDocumentNamePath

 

For example, if you want to display a list of all expired documents of the current website, you can use a query like this:

 

SELECT DocumentNamePath AS [Document path]

FROM View_CMS_Tree_Joined

WHERE DocumentPublishTo < @CMSContextCurrentTime AND NodeSiteID = @CMSContextCurrentSiteID

 

Displaying Parameter Values in the Report

 

If you need to display the parameter values in the report, you can place the following macro expression in the report text:

 

{%parametername%}

 

For example:

 

List of documents expired on or before {%CMSContextCurrentTime%}

 

displays:

 

List of documents expired on or before 8/12/2007 12:06:49 PM

 

You can use this syntax for both custom report parameters and context parameters.

 

Example:

 

1. Switch to the Parameters tab, click New attribute (AddWebPart) and enter the following values:

 

Column name: UserID

Attribute type: Integer number

Attribute default value: 53

Field caption: Created by user

Form control type: Selector

Form control: User selector

 

devguide_clip0004

 

Click Save Save field.

 

2. Now we need to add this parameter to our queries. For the purposes of this example, we will modify only the table query. Switch to the General tab, select the Pages by page template table and click Edit. Now modify the table SQL query like this:

 

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 DocumentCreatedByUserID = @UserID
ORDER BY PageTemplateDisplayName

 

As you can see we added the parameter to the WHERE condition of the query. All parameters that you define can be used in the query using the @<parametername> expression. Click OK and go to the View tab. You will see that the report now has a filter like this:

 

devguide_clip1237

 

The table now only displays template names of documents that were created by the user specified in the filter.

 

Continued in the Saving a report topic.