Technical Desk - Report
Writer Basics (2)
 One
of the most powerful tools in the Commence solution is the Report
Writer. Report Writer allows you to easily produce attractive reports, which are also
capable of providing additional summary information in a number of different
ways.
Last quarter we introduced the Report Writer in Technical
Desk - Report Writer Basics (1). In this issue of CRMtimes, we
are continuing on from that article, to provide further information
about how you can group your data, then provide useful summary
information using the Report Writer tools.
Just a reminder, that Commence and Advanced
User Systems only support the features which are documented in the
Commence User Manual. In some places in this article, we will describe
undocumented features which you can benefit from, however these may
not be supported if they are not in the Commence User manual.
 Sample Data
For
this article, we will use some simple data; a number of contact names,
the state they live in, and some numeric amount.
Grouping
Grouping your data in a report, is essentially what we
have always known as sorting in Commence. In fact there are two parts
to grouping, and the first of those is to sort the view just as you
have always done in Commence.
This is done from Views > Sort View and then
choosing the field you want to sort on. If you are not sure how to do
this, refer to the section in the Commence User Manual,
"Customising Views, Sorting a View".
Remember that you must save the Report Writer view
(Views > Save View) then refresh the data with the Refresh
button before you will see the changes take effect.
 Now that you have the data sorted and the Report
Writer view has been refreshed, the report will show the data in
sorted order.
You will often want to add a heading for each
grouping; in this case the grouping will be based on the State. Go
into the Report Designer (if not already open, Customize > Design View, then Design
Tab > Launch Designer),
Click the right mouse button in the report area and
choose Insert > Group Header / Footer.
Now the Report Designer will show the new group in the
report area. You need to set the field that is going to be
used as the criteria to decide if there is a new group heading
to put in the report. With the Group selected, go to the properties
window and enter "State" as the DataField property.
Now we want to create the heading text for each state.
In the Toolbox, click on the label and drag onto the group heading
edit areas. Modify the properties as you wish, setting the caption,
font etc. Now you need to drag on the Commence data field from the
Fields pane, and position in the group edit area, then set
any properties such as font. The Report Designer will now look
something like:
Remember that you must specify the DataField that the
GroupHeader maps to, as highlighted above. This step is critical to
get the report to appear correctly.
 When
you save the report, it will be regenerated in Commence, and will
appear with the new groupings and headings.
Adding Summary Information for Groups
Now that we have the report grouped according to some
criteria, we will often want to draw out summary information for each
group. For example, we might want to know the total of all
Amounts for each State.
Report Writer provides powerful summary capabilities,
and can calculate a number of parameters such as:
-
Total
-
Average
-
Count
-
Min
-
Max
-
Variance
-
Standard Deviation
Totalling the Data for a Group
Determining the Total for a Group is perhaps the most
common requirement. You need to decide where the Total is to be
displayed, most often in the GroupFooter. Drag a TextBox from the
ToolBox and position where you want the Total displayed.
Now you need to define to Report Writer which field is
to be Totalled (there could be many numeric fields in your database).
With the TextBox selected, set the DataField property to
"Amount".
You also need to set the following properties
More information about how these setting can be used
is provided later.
The Report Designer screen should like
 The
report that will be produced will now have the total Amount shown for
each State.
As we now have a heading for each State, we are
showing redundant information with the State against each person, so
this field has been removed from the report.
Averaging the Data for a Group
 Its
now easy to change the summary or add other summaries. To get an
average of the Amounts, you simply change the SummaryFunc property to
0 - ddSFAvg.
Obtaining a Count for each Group
 To
report a count for each Group, change the SummaryFunc property to 0 -
ddSFCount.
Other Report Writer Summary Properties
Report Writer provides many other tools to help manage
summary reporting.
SummaryRunning
SummaryRunning calculates running totals that
accumulate with each printing and reset at the specified level.
SummaryGroup
SummaryGroup calculates a subtotal summary for the
indicated group. It defines the group section level from which a
subtotal should be calculated. It is usually the matching group header
section associated with the section where the summary field is placed.
SummaryType
SummaryType specifies where the field's calculations
will be done.
The summary types are:
-
GrandTotal – Calculates the summary expression
once for all detail records in the report.
-
PageTotal – Calculates the summary expression
once for each page.
-
SubTotal – Calculates the summary expression
once per SummaryGroup section.
Spend some time with Report Writer
Use this simple example as a guide for reports that
you can develop. Spend some time becoming familiar with the tools that
Report Writer provides - before long you will be creating elegant and
complex reports quickly and efficiently. Report Writer is arguably one
of the best enhancements ever added to Commence.
In a future issue of CRMtimes, we will look how you can use the
Scripting feature in Report Writer.
|
