Defining report parameters

  Previous topic Next topic JavaScript is required for the print function Mail us feedback on this topic! 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 the 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:

 

Attribute name: UserID
Attribute type: Integer number
Attribute default value: 53
Field caption: Created by user
Field type: User Selector

 

Click OK.

 

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 the report with a filter like this:

 

devguide_clip0697

 

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

 

Continued in the example section of the Saving a report chapter.

 

Page url: http://devnet.kentico.com/docs/devguide/index.html?defining_report_parameters.htm