Custom Report Example - Activity and Skill Totals Report

The following example will show you how to group records and give a summary total of the number of records in each group. The query produces a list of activities and skills and counts the number of individuals for each one.

Result:

Steps:

1. To begin, start by navigating to the Activities & Skills module, select the Other Output option and then finally Custom Reports. When asked whether to run reports for “Activities” or “Offices Served” select Activities.
   1. If you are using a version prior to 11, there will be no other output option; the Custom Reports option will be directly under the Activities & Skills module.
      
2. The next step will be creating a new query and a title to go with it. This step will be different depending on whether you have created a custom report in the past or not.
   1. If you have NOT created a custom report in the past, the system will immediately prompt you for a report title. Simply enter the desired title and click OK; this will bring up the query builder for the next step.
   2. If you HAVE created a custom report in the past, you will be brought to an existing page of all your custom reports. Simply click the Add button and enter the desired title and click OK; this will bring up the query builder for the next step.
      
      
3. Now we will need to create the filter for the query.
   • From the "Step 1: Pick a field" tab, select Activity Number.
   • From the "Step 2: Pick an Operator" tab, select Greater Than.
   • From the “Step 3: Enter a Value” tab, enter 0.
   • Once satisfied with the query, select done near the bottom right to move onto the next step.
     
     
     
4. At this point, you will be prompted: Do you want to run this query now? Click Yes. Here we will need to define the output fields. Do so by simply double clicking on the desired fields; this will move them to the included section. Select Activity Number, Activity Description and Profile Last Name from the “fields to pick from” section. Optionally, you could select other fields here as well.
   
   
   
5. Now we are going to define two Groups. First, start by selecting the Activity number from the list of fields to include. Then click Group at the bottom to see the Select Group Field Type dialog box. Select the first option, “Group records first by this field (GRP 1)” and click done.
   
   
   
6. Next, select Profile Last Name and click Group again and choose “Count number of records in each group (CNT)”. This option counts the number of records in each Activity Number group. Click done and then done again to finalize the output.
   
   
   
7. Now we need to define the sort order as the final step before running our new query. Select the Edit Sort Order button in the Query Overview window. From here, select the Activity Number field and click done.
8. Finally, click on Run Query. Optionally change the output direction to screen preview and click start report to generate the results.