Example - creating a simple report

This example demonstrates how to create a report that displays a table, graph and value, and uses a reporting parameter:

Creating a report category

  1. Open the Reporting application.
  2. Select the root of the reporting tree (the All reports category by default).
  3. Click next to the New report button and select New category.
  4. Type Custom reports as the Category display name.
  5. You can leave the code name as (automatic).
  6. Click Save.

Creating a new report

  1. In the Reporting application (with the Custom reports category selected), click New report.
  2. Type Pages by type as the Report display name.
  3. Click Save.
    • Now you can edit the layout of the report and insert tables, graphs and values.
    • The General tab of the report editing interface opens.
  4. Type Pages by type into the Layout text area.
  5. Select the text and use the editor to set the Format to Heading 1.
    Adding the heading into the report’s layout
  6. Click Save.

Creating a new table

  1. On the report’s General tab, click New in the Tables section below the layout editor.
  2. Enter the following values:
    • Display name: Pages by type

    • Query:

      
      
      
        SELECT ClassDisplayName AS [Page type], NodeAliasPath AS [Page], DocumentCulture AS [Culture]
         FROM View_CMS_Tree_Joined
         ORDER BY ClassDisplayName
      
      
        
    • Is stored procedure: no (cleared)

    • SkinID: ReportGridAnalytics

    • Enable paging: yes (selected)

    • Page size: 10

    • Page mode: Page numbers

  3. Click Save & Close.
  4. Place the cursor in the layout editor on a new line under the heading.
  5. Select the table from the list in the Tables section.
  6. Click Insert.
    • The system adds a string like %%control:ReportTable?PagesByType.PagesByType%% into the text area.
  7. Click Save.

Switch to the View tab to see the report table.

Viewing the new report table

Creating a new graph

  1. Switch back to the report’s General tab.
  2. Click New in the Graphs section below the layout editor.
  3. Enter the following values:
    • Display name: Favorite page types

    • Query:

      
      
      
        SELECT TOP 5 ClassDisplayName AS [Page type], COUNT(ClassDisplayName) AS [Usage]
         FROM View_CMS_Tree_Joined
         GROUP BY ClassDisplayName
         ORDER BY COUNT(ClassDisplayName) DESC
      
      
        
    • Graph type: Pie chart

    • Drawing style: Pie

    • Title: Favorite page types

    • Series -> Display item value: no (cleared)

  4. Click Save & Close.
  5. Place the cursor in the layout editor on a new line under the table.
  6. Select the graph from the list in the Graphs section.
  7. Click Insert.
    • The system adds a string like %%control:ReportGraph?PagesByType.FavoritePageTypes%% into the text area.
  8. Click Save to save the changes.

 If you now switch to the View tab, you can see the report graph.

Viewing the new report graph

Creating new values

  1. Switch back to the report’s General tab.
  2. Click New in the Values section below the layout editor.
  3. Enter the following values:
    • Display name: Total pages

    • Query:

      
      
      
        SELECT COUNT(DocumentID)
         FROM View_CMS_Tree_Joined
      
      
      
        
    • Is stored procedure: no (cleared)

    • Formatting string: Total number of pages: {0}

  4. Click Save & Close.
  5. Place the cursor in the layout editor under the graph.
  6. Select the new value from the list in the Values section.
  7. Click Insert.
    • The system adds a string like %%control:ReportValue?PagesByType.TotalNumberOfPages%% into the text area.
  8. Click Save.

 If you switch to the View tab, you can see the text of the value.

Defining report parameters

  1. Switch to the Parameters tab.
  2. Click New field and enter the following values:
    • Field name: UserID
    • Data type: Integer number
    • Default value: 53
    • Field caption: Created by user
    • Form control: User selector
  3. Click Save.

Now you need to add the parameter to your queries. For the purposes of this example, modify only the table query.

  1. Switch to the General tab.

  2. Select Pages by type in the Table list.

  3. Click next to the New button and select Edit.

  4. Modify the table SQL query like this:

    
    
    
     SELECT ClassDisplayName AS [Page type], NodeAliasPath AS [Page], DocumentCulture AS [Culture]
      FROM View_CMS_Tree_Joined
      WHERE DocumentCreatedByUserID = @UserID
      ORDER BY ClassDisplayName
    
    
     

    This adds the parameter to the WHERE condition of the query. All parameters that you define can be used in the query using the @<ParameterFieldName> expression.

  5. Click Save & Close.

Now switch to the View tab. You can see that the report has a filter:

Viewing the filter that allows you to set value of the report’s parameter

The table now only displays the types for pages that were created by the user specified in the filter.