|
Question: How
can I add advanced calculations in my report builder reports using SQL
or RAP?
Answer:
ExpressMaintenance includes the top rated reporting tool, Report
Builder. Using SQL within Report Builder, users can produce
comprehensive reports presenting data in just about any desired format.
Reports often need to include calculated fields as well as advanced
calculations based on logical conditions.
Using
Report Builder For Simple Calculations
Several
standard detail row (record) calculations are readily available in the
Design tab of Report Builder. These include count, sum, minimum,
maximum and average. You can easily add a calculated field within
your report detail, group or footer bands. See the report builder
help for more details. Sub reports are also used to present totals
and aggregate fields.
Using
SQL For Calculations
Because a
report can consist of multiple data queries, SQL can be used to generate
calculated fields as well as aggregate calculations from your data.
SQL includes support for aggregate queries such sum, count, average,
minimum, maximum, etc. In addition, SQL allows you to use logical
operators with "where" statements. To get more information on the
SQL syntax and commands, see the
SQL Learning More Tech
Bulletin - Click Here.
Using
RAP For Advanced Calculations
There are
times when users desire to produce reports with certain logical events
and variables. For example, suppose you want to list all work
scheduled for a range of dates but would like separate counts of the
open and completed work orders in the summary band. This would require the
use of certain "if .. then" logic in the calculation (see
sample below). ReportBuilder
includes a powerful but advanced language for this purpose.
Report
Application Pascal, RAP allows users to create their own
calculations within reports. RAP extends Report
Builder by giving end users the ability to code their own event handlers
and extended calculations. The Calc workspace is an Object Pascal
development environment that is designed for ease of use by
non-developers. The Code Explorer offers varied views of the report code
module; the Code Editor is a syntax-sensitive Pascal editor; the Code
Toolbox serves double duty, providing both a partial list of supported
identifiers as well as a drag & drop code creation facility.
To utilize
the RAP capabilities of Report Builder, you must:
-
Download and
install the very latest release of ExpressMaintenance. Rap was
made available starting in version 5.5.1.27.
-
Turn on the
feature under Utilities / Set Application Properties / Enable RAP
Interface.
-
View the
ExpressMaintenance Application Help including the Report Builder / RAP
Interface topic for complete details on the RAP aspect of Report
Builder.
-
Download the pdf documentation -
What is RAP.
-
Download the pdf documentation -
Learning RAP.
-
Consider
downloading and working through the Report Builder
learning tool - click here.
Note: Express
Technology does not currently support end users with this new tool.
RAP is a powerful extension to Report Builder that we are pleased to
make available to our customers. However, we cannot provide
technical support on RAP at this time.
For a more advanced
example report using RAP, please download the 'PMs Services Analysis
Report' and accompanying pdf file from our reports download page -
click here.
Example
Report
Below is an
example report that uses two variable fields to perform the calculations
desired. In this example, we are listing all work orders scheduled
between a range of dates. However, at the end of the report in the
summary band, we want separate counts of the open and completed work
orders. You can download and import the report used in this
example (Work Orders List By Category) at the Reports Download Page -
Click Here.
1. Design
Tab
Notice that
two calc Variable fields Variable1 and Variable2 have been placed in the
Summary band of the report:
2. Calc
Tab
In the
Calc tab, code is placed on the first variable to perform the if ..
then..else logic. If the Work Order has a completed date of 0
(null), the value of Variable1 is increased by 1, else the value of
Variable2 is increased by 1 indicating the work order is completed.
3. Preview
Tab
When
previewing the report, you can see that the variables have been
calculated and placed in the summary band of the report.
Additional General Information
The following
is additional overview information about RAP taken from the Digital
Metaphors website.
|
The RAP user
interface can be
configured to meet
the needs of your
end users. There are
three basic
configurations
available:
1.
Calculations Dialog
For casual end
users, a simple
Calculations dialog
is accessible from
the speedmenu of the
new TppVariable
component. This
dialog provides a
place for
calculations to be
entered and returned
via a single Result
parameter. The
calculation result
is then displayed in
the variable when
the report prints.
In order to
configure the
user-interface to
provide this level
of functionality,
set the RAPInterface
riNotebookTab
property to False
and the riDialog
property to True.
The figure below
illustrates the
Calculations… option
available when the
end user accesses
the speed menu of a
variable:
When this option is
selected, the
Calculation dialog
is displayed. The
end user can then
create code to
assign the value of
the variable.
2. Calc Tab
with Variables
For more
sophisticated users,
RAP can be
configured to
display as an
additional tab in
the Report
Designer. The new
‘Calc’ tab shows the
bands of the report
in a tree view,
along with the
associated
variables. The end
user can click on
the variables and
code the calculation
in a
syntax-sensitive
edit window.
In order to
configure the
user-interface to
provide this level
of functionality,
set the RAPInterface
riNotebookTab
property to True.
When the report
designer is
displayed, the Calc
tab will be visible,
and the variables
within each band
will be listed.
3. Calc Tab with All
Objects
The highest level of
functionality is
provided by the
‘Event’ view. This
capability shows all
of the report
components in a tree
view. When a
component is
selected, all of the
events for the
component are
displayed. The user
can then select an
event and code an
event handler.
Calc Tab Views
In order to
configure the
user-interface to
provide this level
of functionality,
set the RAPInterface
riNotebookTab
property to True and
set all of the
RAPOptions to True.
When the report
designer is
displayed, the Calc
tab will be visible.
By right-clicking on
the treeview on the
left, a speed menu
will be displayed
that shows the
different views of
calculations
available:
a.
Variables
This view shows
the variables in
each band of the
report.
b.
Events
This view shows
the report and
all of the
objects within
it. An event
handler can be
coded for any
object.
c.
Module
This view shows
the global
declarations,
functions,
procedures, and
events.
-
Download the pdf documentation -
What is RAP.
-
Download the pdf documentation -
Learning RAP.
-
For a more advanced
example report using RAP, please download the 'PMs Services Analysis
Report' and accompanying pdf file from our reports download page -
click here.
|
|
|
|
|
|
|
