Defining report parameters

The reports may be filtered using custom parameters. You can define the parameters on the Parameters tab in the Report properties dialog.

 

Click New attribute 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. 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 and edit 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 filter like this:

 

clip0734

 

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 web site, you can use 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 document expired on or before {%CMSContextCurrentTime%}:

 

displays:

 

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

 

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