Version 5.2
Report Writer Guide
Chancery SMS
®
November 2004
CSL - 12333
The Chancery SMS Documentation Team:
Joanna Denford, Ben Hechter, Sarah Hewson, Karin Jensen, Sharon Russell
© 2004 Chancery Software Ltd.
Information in this document is subject to change and does not represent a commitment on
the part of Chancery Software Ltd. For up-to-date information, call Customer Service at
1-800-999-9931.
Chancery SMS, Chancery Student Management Solutions, Win School, Mac School, eClass,
and Open District are registered trademarks of Chancery Software Ltd.
Microsoft, Windows, and Internet Explorer are registered trademarks of Microsoft
Corporation. SQL Server and .NET are trademarks of Microsoft Corporation.
Oracle is a registered trademark of Oracle Corporation.
Apple, Mac, and Macintosh are registered trademarks of Apple Computer, Inc.
Crystal Reports is a registered trademark of Business Objects SA.
Contents
i
C
ONTENTS
Report Writing ............................................................................. 1
Permission to Create Custom Reports .................................................................................. 1
What You Should Know ......................................................................................................... 1
Support Services Available .................................................................................................... 2
Documentation Included .................................................................................................. 2
Providing Feedback ......................................................................................................... 3
Getting Training ................................................................................................................ 3
Getting Support ................................................................................................................4
Consultation Services ...................................................................................................... 4
Database Fundamentals ............................................................... 5
Reporting Restrictions ............................................................................................................ 5
Comparing Database Models ................................................................................................ 5
Data Access Layer ................................................................................................................. 7
Naming Conventions .............................................................................................................. 8
Views ................................................................................................................................ 8
Intersection Views ............................................................................................................ 9
Simple Fields .................................................................................................................... 9
Dynamic Properties .......................................................................................................... 9
ID Fields ......................................................................................................................... 10
Reference ID Fields ....................................................................................................... 10
Dynamic Reference ID Fields ........................................................................................ 10
Owner Object ID Fields .................................................................................................. 11
Base Object ID Fields .................................................................................................... 11
Query ID Fields .............................................................................................................. 11
Report Design ............................................................................ 13
Mapping ............................................................................................................................... 13
Root View ....................................................................................................................... 13
Object Hierarchy ............................................................................................................ 14
Data Ownership ............................................................................................................. 14
Filters ................................................................................................................................... 15
Role-Level Security ........................................................................................................ 15
Chancery SMS Filters .................................................................................................... 15
Crystal Reports Filters .................................................................................................... 16
Dynamic Properties ........................................................................................................ 16
Rules .................................................................................................................................... 16
Report Building Help ............................................................................................................ 17
Report Extensibility .............................................................................................................. 18
Contents
ii
Linking ...................................................................................... 21
Report Validation ................................................................................................................. 21
Joins ..................................................................................................................................... 21
Inner Joins ...................................................................................................................... 21
Left Outer Joins .............................................................................................................. 22
Right Outer Joins ........................................................................................................... 22
Link Types ...................................................................................................................... 22
Connections ......................................................................................................................... 23
Directional Links ............................................................................................................. 23
Aliased Views ................................................................................................................. 23
Intersection Views and Reference IDs ........................................................................... 23
Query ID Connections .................................................................................................... 25
Linking Views ....................................................................................................................... 25
Report Building .......................................................................... 29
Adding Fields and Sorting .................................................................................................... 29
Adding Groups and Summaries ........................................................................................... 30
Adding Filters ....................................................................................................................... 33
Adding Subreports ............................................................................................................... 34
Adding Print Options ............................................................................................................ 35
Formatting ............................................................................................................................ 39
Adding Page Numbers, Dates, and Other Special Fields .............................................. 39
Changing Date and Time Formats ................................................................................. 39
Adding Text ....................................................................................................................40
Revising and Formatting Text Objects ........................................................................... 40
Moving, Resizing, or Deleting Fields and Text Objects .................................................. 41
Positioning Elements on the Page ................................................................................. 41
Uploading Reports ..................................................................... 43
Uploading Reports ............................................................................................................... 43
Step 1: Run SQL Script, If Necessary ............................................................................ 43
Step 2: Upload Report File ............................................................................................. 44
Step 3: Set Report Filters ............................................................................................... 46
Step 4: Test Run the Report .......................................................................................... 48
Addendum: Database Tables ...................................................... 51
Database Tables .................................................................................................................. 51
Naming Conventions ...................................................................................................... 51
Connections ................................................................................................................... 53
Adding Custom Reports (Database Tables) ........................................................................ 54
Step 1: Preparation ........................................................................................................ 54
Step 2: Create the Report .............................................................................................. 54
Step 3: Add the Report to Chancery SMS ..................................................................... 54
Contents
iii
Step 4: Test Run the Report in Chancery SMS ............................................................. 55
Modifying Custom Reports (Database Tables) .................................................................... 57
Step 1: Download the Report ......................................................................................... 57
Step 2: Customize the Report ........................................................................................ 58
Step 3: Add the Modified Report to Chancery SMS ....................................................... 58
Reverting to an Original Report ........................................................................................... 59
Modifying the Permissions of a Report ................................................................................ 60
Deleting Custom Reports ..................................................................................................... 60
Running Database Table Reports at the School Level ........................................................ 61
Working with Crystal Reports ............................................................................................... 62
Creating Custom Reports (Database Tables) ................................................................ 62
Step 1: Start a Report and Connect It to the Data .................................................. 63
Step 2: Select Tables with the Data You Want ....................................................... 64
Step 3: Link the Tables ........................................................................................... 64
Step 4: Add Fields to the Report ............................................................................. 65
Step 5: Group Information and Add Summaries ..................................................... 66
Step 6: Add Filters ................................................................................................... 67
Step 7: Complete the Formatting ............................................................................ 68
Modifying Custom Reports (Database Tables) .............................................................. 68
Addendum: XML Schema ............................................................ 71
XML Schema ....................................................................................................................... 71
Naming Conventions ...................................................................................................... 71
Connections ................................................................................................................... 72
Adding Custom Reports (XML Schema) .............................................................................. 73
Step 1: Preparation ........................................................................................................ 73
Step 2: Create the Report .............................................................................................. 74
Step 3: Add the Report to Chancery SMS ..................................................................... 74
Step 4: Test Run the Report in Chancery SMS ............................................................. 75
Modifying Custom Reports (XML Schema) .......................................................................... 75
Step 1: Download the Report ......................................................................................... 76
Step 2: Customize the Report ........................................................................................ 76
Step 3: Add the Modified Report to Chancery SMS ....................................................... 77
Reverting to an Original Report ........................................................................................... 77
Modifying the Permissions of a Report ................................................................................ 78
Deleting Custom Reports ..................................................................................................... 79
Working with Crystal Reports ............................................................................................... 79
Creating Custom Reports (XML Schema) ...................................................................... 80
Step 1: Start a Report and Connect It to the XML Schema .................................... 80
Step 2: Select Tables with the Data You Want ....................................................... 81
Step 3: Link the Views ............................................................................................. 82
Step 4: Add Fields to the Report ............................................................................. 83
Step 5: Group Information and Add Summaries ..................................................... 83
Step 6: Add Filters ................................................................................................... 84
Step 7: Complete the Formatting ............................................................................ 84
Chancery SMS Report Writer Guide
Report Writing
Permission to Create Custom Reports
1
R
EPORT
W
RITING
Welcome to Chancery SMS
®
from Chancery Software. Chancery SMS is a
software tool for managing student and family information in your school
district.
Chancery SMS contains extensive functionality for creating custom reports to
meet the specific needs of your district. This guide provides information and
instructions required by advanced users in your district office to write these
reports.
Permission to Create Custom Reports
To create a custom report in Crystal Reports using the data access views, you
need:
• a user ID and password for the Chancery SMS data access report writing
database
• the name of the Chancery SMS data access report writing database (ends
with _DA)
• the name of the server that the Chancery SMS data access reporting
writing database is on
If you do not have this information, contact the district administrator.
To upload a custom reports to Chancery SMS, you need the Create Custom
Report permission set to Yes.
What You Should Know
Before you create custom reports, you should have a thorough understanding
of the various tools, applications, and requirements used in the report writing
process:
• Chancery SMS: You should have a working knowledge of Chancery SMS
and its features. The reporting framework is based on a logical
representation of the database, meaning that you must understand the way
that various entities in Chancery SMS relate to each other in order to find
and select the appropriate types of data for users. For example, when
creating a discipline report, you must understand what an incident is, and
how it relates to an infraction and an action. In addition, you should
understand the rationale behind whether data is owned by the district or
by individual schools.
Report Writing
Support Services Available
Chancery SMS Report Writer Guide
2
• Relational databases/SQL: A basic understanding of relational databases
is required to understand the Chancery SMS reporting framework. SQL is
also required if your reports are complex enough that the report
extensibility functionality is required.
• Crystal Reports: You should be comfortable working Crystal Reports,
which is the third-party application used to create custom reports.
Support Services Available
Chancery offers a full suite of documentation, online Help, and technical
support to help you with your day-to-day tasks.
Documentation Included
The following documentation is included with Chancery SMS:
See For
Chancery SMS
Guides
The following guides are available in PDF format on the Installation
CD and at our Support web site:
• School User Guide: For teachers and school secretaries, the School
User Guide covers the basics of using Chancery SMS at the school
level, including how to take attendance, enter grades, and work
with student data.
• School Setup Guide: For school administrators, the School Setup
Guide provides instructions on establishing school-wide features,
such as scheduling, setting up grading information, and
configuring user access.
• District Administration Guide: For district administrators, the
District Administration Guide provides instructions on
maintaining the centralized Chancery SMS solution, including
setting up your district, adding schools to the district,
standardizing data across the district using setup lists, working
with custom data, and importing key student demographic data.
• District User Guide: For district office users, such as data entry
clerks, the District User Guide covers the basics of using Chancery
SMS at a district level, including working with student data and
maintaining setup lists.
• Grading Setup Guide: For school administrators, the Grading
Setup Guide is a feature-area specific guide that describes the
process of setting up grading properties in detail.
Depending on the school, school staff, such as teachers, attendance
clerks, and vice principals, might use Win School
®
and Mac School
®
to manage school-owned information. See the Win School and Mac
School guides for those procedures.
Chancery SMS Report Writer Guide
Report Writing
Support Services Available
3
Providing Feedback
To improve the quality of your documentation, we welcome your comments
regarding this guide and the online Help. Please email your comments to
Chancery’s Technical Communications Department at
user_docs@chancery.com.
Getting Training
This guide is intended to give you the basic information you need to create
reports. To learn more about Crystal Reports, Chancery offers additional
training courses on Introductory, Intermediate, and Advanced Report
Writing.
Choose from on site training, regional workshops, live online training, or
Chancery University. We can also create a customized training program to
meet your district’s needs.
Phone us toll free at 1-800-999-9931 or see the Training area of our Support
web site at
http://support.chancery.com for an up-to-date list of scheduled
courses.
Chancery SMS
Specialty
Guides
Additional guides are available in PDF format on the Installation CD
and at our Support web site to supplement the standard guides:
• Customization Guide: For district administrators, the
Customization Guide covers how to create custom student pages,
custom panels for existing pages, customization packages, custom
reports, and custom setup lists in Chancery SMS.
• Report Writer Guide: For district administrators and report
writing contractors, the Report Writer Guide covers how to create
custom reports using the Chancery SMS reporting framework and
Crystal Reports.
• Interoperability Guide: For district administrators, the
Interoperability Guide covers how to integrate third-party
applications with Chancery SMS using SIF and Import/Export
functionality.
Chancery SMS
Online Help
Help for the page you’re currently working on is always available by
accessing the Help menu from the task bar.
Chancery SMS
Data Dictionary
The data dictionary contains lists of tables and fields in the district
database. It is recommended for the report developer who uses a
reporting tool such as Crystal Reports
®
to retrieve additional
information from the district database.
Release Notes Release Notes are summaries of the new features and changes in this
release.
Installation
Documentation
The Installation Guide provides technical details of installing the
entire Chancery SMS system for district administrators.
Installment Notes are detailed instructions of upgrading from a prior
version.
See For
Report Writing
Support Services Available
Chancery SMS Report Writer Guide
4
Getting Support
The following support programs are available by subscription only:
For more information on Technical Support Programs, phone Chancery
Customer Service at 1-800-999-9931.
Consultation Services
Performing complex, infrequent technology tasks can be a challenge for busy
School Districts. Save time and effort by having Chancery experts take care of
the activities that place demands on your district’s schedule and resources.
Chancery offers contract services for:
• Scheduling
• Data conversion
• Custom report development
• Template creation
• Program and need assessment
• Upgrade planning
• Project management
• Training and technology requirement evaluation
• And much, much more.
Consultation with a Chancery expert is an indispensable investment in your
district’s success.
One-on-one
Technical Support
Talk to a technical support analyst to help solve problems and
answer questions when software doesn’t produce the expected
results. Analysts can use the telephone or Internet access to
troubleshoot your issues.
Monday to Friday, 5 am to 5 pm Pacific Time
Phone: 1-800-688-9939
Fax: 1-800-446-5650
Email:
techsupp@chancery.com
Access to our web
site
http://support.chancery.com
In Chancery SMS, from the Help menu, click Support Website.
Chancery’s web site includes a searchable KnowledgeBase with
frequently asked questions, tips, and troubleshooting instructions,
as well as support forums for users to share experience and
knowledge.
Software updates Releases of new versions with improved functions and software
updates. Updates are provided free of charge for three months
from your date of purchase and are included in the subscription
to Chancery Support Programs.
Chancery SMS Report Writer Guide
Database Fundamentals
Reporting Restrictions
5
D
ATABASE
F
UNDAMENTALS
You can create new reports or customize existing reports using Crystal
Reports version 9.0 and add your reports to Chancery SMS so that district
and school users can run the reports as required.
Reporting Restrictions
The district database is the central location for all reporting data. Any data
held in Win School or Mac School is transferred to common tables in
Chancery SMS using the District Replicator. School data held in Chancery
SMS is stored in specific tables and has to be mapped to the common tables
containing the Win School and Mac School data.
Only districts with schools that maintain their school data in Chancery SMS
can access school reports. Districts with schools that maintain their school
data in Win School or Mac School can use Crystal Reports to create and run
district-level reports or use the Report Manager module to create and run
school-level reports.
Also note that you cannot customize reports on the Macintosh. You can run
reports, but you cannot use Crystal Reports to modify built-in reports or to
create new custom reports.
Comparing Database Models
In the context of reporting, there are two ways of looking at the database: the
physical model and the logical model.
Database Fundamentals
Comparing Database Models
Chancery SMS Report Writer Guide
6
The physical model of the database represents how the data is actually
structured. The database itself is a collection of tables, each of which contains
records, which are in turn made up of fields.
When you build a report on the physical structure of the database, the
connections between the report and the data are rigid. This means that any
changes that are made to the tables – such as deleting a field – can adversely
affect your report.
By contrast, the logical model of the database is made up of metadata – data
about data. This metadata is a description of the physical tables in the
database, with information grouped in a logical way. This metadata removes
complex joins between tables, simplifies field selection, and applies the
business logic required to interpret information like attendance data and to
prevent users from viewing information from other schools.
Where the physical structure of the database is made up of tables, records, and
fields, the logical structure is made up of schemas, entities, and properties.
The following outlines the relationships between elements in the physical and
logical models of the database:
Physical Logical Definitions
Table Schema A table is a collection of records in the database.
A schema is a way of looking at a particular table or
collection of tables in the database, based on a logical
grouping of information.
Record Entity A record is a collection of related fields that describe
a particular object, such as a student, a homeroom,
or a grade level. In a database table, a record is a row
of information.
An entity is a collection of properties that describe
an object in a schema in a similar way.
Field Property A field is a particular type of information that is
shared by records in a table. The fields each describe
some aspect of the object. In a database table, a field
is a column.
A property is a particular type of information that is
shared by entities in a schema. There are several
types of properties:
• Simple properties relate directly to a field in the
database
• Reference properties connect schemas together
in a one-to-one relationship.
• Complex properties connect schemas together in
a one-to-many relationship as collections or
parent/child collections.
• Simple dynamic properties are formulas that
return values for information not contained in a
particular field.
• Reference dynamic properties are formulas that
return entire records.
Chancery SMS Report Writer Guide
Database Fundamentals
Data Access Layer
7
Referencing the metadata associated with the tables in the logical model
creates a flexible link between the report and the data, making it possible for
changes to be made to the tables without losing the integrity of the reports
that use them.
Data Access Layer
The physical structure of the database is a collection of SQL objects, and so it
can be directly interpreted by any number of third-party applications,
including Crystal Reports, which is the reporting tool that Chancery SMS
supports. However, the logical structure of the database exists within
Chancery SMS, and was created using unique programming syntax. In order
for Crystal Reports to interpret the metadata, the logical structure must be
converted into a recognizable format.
The Data Access Layer is an additional layer in the structure of Chancery SMS
that interprets the existing metadata so that it can be converted to SQL. The
Data Access Layer also includes an extension to the metadata that
encompasses dynamic properties, which are calculated fields that interpret
other information in the database using the imbedded business logic.
Attached to the Data Access Layer is a collection of dynamically-generated
SQL views of all of the tables in the database for which metadata exists. All of
these views are then accessed in Crystal Reports through the data access
database, which is used specifically for report writing. The data access
database protects the tables in the actual database from corruption.
Database Fundamentals
Naming Conventions
Chancery SMS Report Writer Guide
8
These views are created by the Data Access View Generator, which is a task
that runs as part of the Task Manager, a feature in Chancery SMS that allows
you to schedule system maintenance tasks to run automatically at regular
intervals or when certain conditions arise. Whenever changes are made to the
metadata, such as additions through Customization Builder, the Data Access
View Generator automatically updates the views to incorporate these changes.
When you upload the report to Chancery SMS, it carries the views you
referenced over into the reporting filters. This means that you can use any of
the fields that are part of the views as filters for your report – all without the
need to write code or set up complicated parameters in Crystal Reports.
All of the tables available in Chancery SMS are available as views in the Data
Access Layer, meaning that you can use the instructions in the following
chapters to report on any type of data you want. In previous releases, some
types of data were only available through the legacy methods of creating
reports, which involved connecting to the tables directly or accessing them
through the XML Schema, a subset of available metadata. Although these
methods are still supported (see the Appendices for details), the
recommended method is to create your reports using the Data Access Layer.
Naming Conventions
In order to be able to build reports from these data access views, you need to
understand how the different components in the Data Access Layer are
derived from the logical model, which is reflected in the naming convention.
This naming convention is critical to understanding how to connect related
information in a meaningful way.
Views
Views are derived from schemas in the logical model. Each view contains a
selection of fields in a related area that may come from a single table or a
collection of tables.
All views start with
the prefix DA_.
This is the name of the schema in the metadata. It can be
several words, but has no spaces or punctuation.
DA_School
Chancery SMS Report Writer Guide
Database Fundamentals
Naming Conventions
9
Intersection Views
When views are related to each other in a one-to-many relationship, such as
the link between a school and all of the students enrolled at that school, the
Data Access Layer creates an “intersection view”. The only purpose of this
view is to link the two views together. This is the equivalent of a complex
property in the logical model.
Simple Fields
In each view, there are fields that correspond directly to fields in the database,
also known in the logical model as simple properties. They have no prefixes or
suffixes, and no special formatting except that there are no spaces between
words. Any fields available in a view can be used in your reports. Examples of
fields include LastName, Gender, and Description.
Dynamic Properties
There is also a sub-category of fields that deserves special attention.
Occasionally, you will need to report on information for which there is no
correlating field in the database. To address this issue, new fields called
dynamic properties have been added to the data access views. Dynamic
properties are formulas that have been created to combine fields in the
database in meaningful ways.
Dynamic properties are identified by a prefix of D#, followed by a
description of the information they contain. They return the information the
formula calculates as a field that can be added to your reports like any other
field.
For example, there is no actual field in the database that allows you to include
the full name of a student. However, there is a dynamic property in the
DA_SchoolStudent view called D#FullName, which can be added to your
report to serve the same function. This simple dynamic property combines
the values from several fields in the database at the time that the report is run
and returns the appropriate value without using a formula in Crystal Reports
to do the concatenation.
The first section is the name
of the schema.
All intersection views
start with the prefix DA_.
The intersection is
indicated with _I_.
The second section is the name
of the property in the schema.
DA_School_I_Students
Database Fundamentals
Naming Conventions
Chancery SMS Report Writer Guide
10
ID Fields
Each view has a specific field that uniquely identifies that view, called its
primary ID. These fields cannot be used as fields in your report; they are for
identification purposes only.
Reference ID Fields
When a view is related to another view in a one-to-one relationship, such as
the link between a student’s demographics and the student’s homeroom, a
reference ID field is included that allows the two views to be linked. For this
type of link, the reference ID in one view links to the primary ID in the other
view. This is the same as the reference property in the logical model.
Dynamic Reference ID Fields
As with a regular reference ID, a dynamic reference ID is used when views are
related in a one-to-one relationship. However, a dynamic reference ID is used
when the record to be retrieved from the second view needs to be determined
at the time that the report is run, such as the effective grade level of a student
as of a particular date.
The name of the ID is the same
as the name of the schema.
All ID fields end with ID.
SchoolStudentID
The first section is the name of the
property for the reference schema.
The intersection is
indicated with #.
The second section is the name
of the reference schema.
All ID fields end with ID.
Homeroom#HomeroomID
The first section is the name of the
property for the reference schema.
The intersection is
indicated with #.
The second section is the name
of the reference schema.
All ID fields end with ID.
D#EffectiveGrade#GradeID
D# indicates that this is a
dynamic property.
Chancery SMS Report Writer Guide
Database Fundamentals
Naming Conventions
11
Owner Object ID Fields
When there is a one-to-one relationship where you can access the a view from
one of the views it owns, such as the link from a class back to the subject to
which it belongs, an owner object ID is also created in the second view to
form the return link. Here, the owner object ID in the view links to the
primary ID in the view that owns it.
Base Object ID Fields
When a view inherits information from another view, such as the
demographics information a student’s school record inherits from his or her
district record, a base object ID is created in the view that inherits the data to
allow you to link the two views. The base object ID in the view links to the
primary ID in the view that it is based on.
Query ID Fields
Every view in the database also has an ID field that allows queries to be
generated. All query IDs are called QUID.
The first section indicates that
this is an owner object ID.
The intersection is
indicated with #.
The second section is the
name of the owner schema.
All ID fields end with ID.
The intersection is
indicated with #.
OwnerObject#SubjectID
The first section indicates
that this is a base object ID.
The second section is the
name of the base schema.
All ID fields end with ID.
The intersection is
indicated with #.
BaseObject#DistrictStudentID
Database Fundamentals
Naming Conventions
Chancery SMS Report Writer Guide
12
Chancery SMS Report Writer Guide
Report Design
Mapping
13
R
EPORT
D
ESIGN
Designing your report – determining what data to display, what views to
include, what filters to use, etc. – is the most difficult part of the report
writing process. Unfortunately, it’s also the part of the process that lends itself
the least to documentation. Most of designing your reports is going to be a
question of trial and error. However, the sections that follow are intended to
give you a head start on the process.
Mapping
If your report is very complex, it’s a good idea to create a diagram of how you
intend to link the various views together before you start building the report.
Checking your logic ahead of time will save you a lot of frustration in the long
run. As you work through the mapping process, pay particular attention to
selecting the correct root view, determining your object hierarchy, and
defining the appropriate data ownership.
Root View
The first step of the process is to determine the view that the entire report
will be based on. This is known as the root view. All of the links between
views move outward from this root view.
The root view for a report varies depending on the type of information you
wish to have available in your report. When selecting your root view, ask
yourself the following questions:
• Which view provides the greatest amount of fields required for my report
canvas?
• Which view provides the greatest number of links to the other views
required for the report.
• Which view is the logical owner of all of the other views included in the
report?
The answers to these questions will not necessarily be the same view, but they
will provide a jumping-off point for further investigation.
For the example, if you are creating a school report that contains mostly
Student Demographics information, the view with the greatest amount of
fields required for the report canvas is probably the School Student view. This
is also the view that provides the greatest number of links to other required
views. However, the most logical owner of the other views, at the highest
level, is actually the School view (you can tell that the School Student view is
owned by the School view by the names). If you intend to include information
Report Design
Mapping
Chancery SMS Report Writer Guide
14
about the School itself, such as the school name or address, you might
consider using the School view as your root view. You can still link from
School to School Student, plus you can link from School to School Info for
that additional information.
For information on how to find out which views contain which fields and
connections to other views, see “Report Building Help” on page 17.
Object Hierarchy
Another factor that influences how you map out your report design is the
concept of object hierarchy. In the Database Fundamentals chapter, you read
about intersection views. These views are used to connect objects that have a
one-to-many relationship, which means that one object (the parent object) can
have a collection of other objects (child collection) related to it.
You also read about Owner Object IDs, which can be used to link a child
object back to its parents. However, because this is a reference link, rather
than an intersection, the same data will be returned as a one-to-one
relationship, rather than a one-to-many relationship.
When you connect objects, the best practice is to start with the parent object
and end with the child collection. The reason for this is that, when the report
is generated, the data is filtered as it moves from one object to another. If you
start with the largest objects first, the filtering process is much more efficient,
thereby increasing system performance.
To use the same example of School and School Student, the school itself is
higher in the hierarchy because it has a collection of students associated with
it. If you use the School view first when connecting your views, when the
report is generated, it will be filtered first on Schools, reducing the number of
student records to be returned. If you use the School Student view first, the
report will pull all student records, and then sort and filter them based on the
school to which they belong.
Data Ownership
When mapping out your report, you must also consider who owns the data
you want to include in your report. For example, core demographic
information about students is owned at the district-level. However, there is
additional information that relates to a student’s demographic at a particular
school (homeroom, grade level, etc.) that the district does not have access to.
If you are creating a report that requires information stored at the district
level, look for views that reflect that data ownership, such as the District
Student view. Conversely, if your report focuses on information owned by the
individual schools, choose school-related views.
Chancery SMS Report Writer Guide
Report Design
Filters
15
Occasionally, you may need to include both District-only and School-only
information in the same report. In these cases, you can link this disparate
information either by linking two views on the Base Object ID or Owner
Object ID (for example, District Student is the base object of School Student)
or creating a sub-report for one of the types of data and linking them on a
shared field, such as the student number. See “Adding Subreports” on
page 34.
Filters
Another aspect of report design you need to consider before building your
report is how you will filter the information so that the report returns
precisely the data you want. There are several characteristics of the reporting
framework that you need to take into consideration: role-level security;
flexible filters; built-in filters; and filtering on dynamic properties.
Role-Level Security
Role-level security is a security feature in Chancery SMS that limits what
information users can view in a report based on the roles to which they are
assigned.
For example, a school administrator and a school teacher can have permission
to run the same report – this permission is granted when the report is
uploaded to Chancery SMS, providing the ability to view, edit, or delete the
report.
However, with role-level security in place, the report looks different for each
of these users. The school administrator can view data for the school(s) to
which he or she is assigned. The teacher’s version of the report displays
information only for students in that teacher’s classes.
When you are creating custom reports, keep these security measures in mind.
Using role-level security can eliminate the need for other filters.
Chancery SMS Filters
The reporting framework is set up in such a way that all of the views you link
together to build your reports are uploaded with the report to Chancery SMS
and are available for filtering. You can specify default filters when you upload
the report; users can also choose their own filters at run-time. See “Step 3: Set
Report Filters” on page 46.
For example, if you create a Student Demographics report using the School
and School Student views, both of those views – including all of the fields that
are contained in the tables referenced by those views – are available when you
Report Design
Rules
Chancery SMS Report Writer Guide
16
run the report. From a design perspective, this means that, if you want to filter
on a particular field in the Chancery SMS database, you must ensure the view
that references that field is included when you build the report, regardless of
whether you include any fields from that view in the actual report canvas.
Crystal Reports Filters
The flexible nature of reports in Chancery SMS means that, while you can
specify what the default filters are for a particular report, you cannot prevent
users from changing those filters at run-time. If you wish to ensure that a
particular filter is always used for a report, you must insert that filter in Crystal
Reports, where it becomes “hard-coded”. See “Adding Filters” on page 33.
Dynamic Properties
In the Database Fundamentals chapter, you read about dynamic properties
and dynamic reference IDs, which are used to include values and records that
are calculated at runtime. Because of the complexity of these calculations,
dynamic properties and views linked using dynamic reference IDs cannot be
used to filter a report from within Chancery SMS; in other words, the
dynamic properties and dynamically-linked views are not carried over into
Chancery SMS at upload time, as the other fields and views are.
If you want to filter your report on a dynamic property, you must add the
filter in Crystal Reports. See “Adding Filters” on page 33.
Like other filter in Crystal Reports, these filters are “hard-coded”, so you
cannot change them at run-time. Depending on how you want to filter the
report, you may need to create multiple versions of the same report to cover
all of the options. For example, if you wanted to filter a report on a student’s
active status, which is a dynamic property in the School Student view, you
would have to create one version of the report for each status you want to
filter on.
Rules
The following rules also need to be taken into account when you are
designing your report:
• A report may contain one or more subreports. A subreport must not have
any subreports.
• A report may use only DA_ views, but not any other type of views, tables,
or stored procedures (exceptions are allowed for report extensibility; see
“Report Extensibility” on page 18).
• A report must have at least one view.
Chancery SMS Report Writer Guide
Report Design
Report Building Help
17
• A report may use any number of fields.
• All report links must be directional (one view is the source and the other
is the destination).
• All view links must use either left outer joins or inner joins. Right outer
joins are not allowed.
• One and only one view must have only outgoing links and no incoming
links (the root view).
• View links must not form any cyclic path (a path that starts at one view
and leads back to the same view). If necessary, you can create a copy of a
view that you have already used and create an alias for it. This allows you
to continue moving in a linear path.
• Links between views must only be made on valid ID fields.
• The maximum number of links allows from one view to another view is
two (one of for the ID link and one for the QUID link).
Report Building Help
The Data Access Layer makes the whole Chancery SMS database available to
reporting. This significantly increases your ability to create custom reports
that reflect the needs of your district and school.
However, with the sheer volume of information available, it can be difficult to
find the views and fields that contain the specific data you require. To assist
you in this, report building help is provided.
This report building help is an SQL query that allows you to select a schema
(a logical grouping of records, such as SchoolStudent, Address, or Federal
Ethnic Category), and provides the following for each:
• The view for the schema displays below the schema in the View field
(e.g., DA_SchoolStudent).
• The Property column contains all of the properties in that particular
schema in alphabetical order (e.g., LastName). Scroll through the list
to find the property you want to include in your report.
• The Field column indicates which field the property is attached to
(e.g., LastName). There may be key differences between the name of
the property and the name of the related field.
• The Help column provides instructions for how to include the
property for a particular schema in a report (e.g., To access the
[EthnicCategory] property of the [SchoolStudent] schema, join the
[DA_SchoolStudent] view with the [DA_EthnicCategory] view on the
fields [EthnicCategory#EthnicCategoryID] and [EthnicCategoryID]
Report Design
Report Extensibility
Chancery SMS Report Writer Guide
18
respectively.). The instructions indicate which views need to be
connected to the view for that schema using which reference IDs and
intersection views. If the Help field is empty for a property, the
property is a field in the view and can be added directly to the report.
TO VIEW THE SCHEMA HELP
1 In the control bar, under Admin, click School Setup.
2 On the School Setup page, under Customization, click Customize
Reports.
3 On the School Report List page, from the Actions menu, click Report
Schema Help.
4 On the Schema Help page, in Schema dropdown, select the schema that
represents the group of records you want to view.
Report Extensibility
In spite of the flexibility of the reporting framework and the many options
available in Crystal Reports, there may be certain complex functions that you
cannot accomplish.
The reporting framework has the ability to be extended through the use of
SQL transactions that can be created to perform complicated processes when
the report is generated.
Chancery SMS Report Writer Guide
Report Design
Report Extensibility
19
However, this report extensibility feature can result in issues with the database
if they are not created properly. If, during the report design process, you
determine that you require access to report extensibility, you can contact
Customer Services at 1-800-999-9931 to have the report (or components of
it) custom designed for you.
Report Design
Report Extensibility
Chancery SMS Report Writer Guide
20
Chancery SMS Report Writer Guide
Linking
Report Validation
21
L
INKING
Once you have determined the specifications for a report, the process of
building the report begins with creating connections between the various
views the report is based on.
Report Validation
When a report created using the data access views is uploaded to Chancery
SMS, it goes through a validation process that checks that the linking in the
report has been done correctly.
In the event that the report contains an illegal link, Chancery SMS displays a
system message indicating the nature of the error.
For this reason, it is extremely important that you give careful consideration
to how you join views when you create your report.
Joins
There are three types of joins available in Crystal Reports:
• Inner Joins
• Left Outer Joins
• Right Outer Joins
Inner Joins
An inner or equal join returns information only when there is a match
between two views. If a record in the left view has a matching record in the
right view, the information is returned in the report. If there is no matching
record in the right view, no information is returned for that record.
Linking
Joins
Chancery SMS Report Writer Guide
22
Inner joins are useful when you wish to return only those records for which a
specific condition is true, such as only those schools that have enrolled
students, or when you know that a match is guaranteed, such that fact that all
students must have a physical address.
The inner join is the default join in Crystal Reports.
Left Outer Joins
A left outer join returns all records in the left view, regardless of whether there
is a corresponding record in the right view. If there is a matching record in the
right view, that record is returned; if not, the value is blank.
Left outer joins are useful when you want to view all records of a specific type,
with their associated characteristics, such as viewing all buildings in the district
and their associated rooms, even if rooms have not yet been created. You can
use this type of report to check for completeness of your data.
Right Outer Joins
Using a right outer join returns information for all records in the right view,
regardless of whether there is a corresponding record in the left view.
Right outer joins are illegal for reports being uploaded to Chancery SMS, and
will not pass the validation process. If you need a right outer join to return the
data you require, you need to re-assess the mapping of your report.
Link Types
In most cases, you will be linking tables on an ID field, which means that only
equal values are relevant. However, in the rare case that you wish to link on a
different type of field, such as when you add a subreport to your report
canvas, you may wish to refine the nature of your joins by selecting a link type.
Specify the link type from the
Link Options dialog.
Chancery SMS Report Writer Guide
Linking
Connections
23
By default, all joins are equal. However, if you are linking on a field that
contains a numeric value, you may specify a link type that selects only those
records that are greater than or less than the value in the left view.
Connections
In addition to the join types in Crystal Reports, there are other conventions
related to connecting views that are required for reports being uploading to
Chancery SMS.
Directional Links
All of the connections in your report must move in a single direction outward
from your root view.
When connecting views, you should organize them in Crystal Reports so that
they flow logically from left to right. Any joins that do not move in this
direction will cause the report to be rejected when it undergoes the validation
process at upload time.
Aliased Views
If necessary, you can add the same view to a report more than once. Crystal
Reports creates an alias for the additional version by adding _1 to the end of
the view’s name. You can change the name of the aliased view to something
more distinctive, if required.
The ability to add aliased views is particularly useful when one view accesses
multiple types of the same information. For example, if you want to show a
student’s physical address and mailing address on the same report, you must
add the view for addresses twice. You then link the student’s record to one
instance of the view on the reference ID for physical address, and to the other
instance on the reference ID for mailing address.
Note
In this case, consider changing the name of both instances of the view to reflect
their respective natures.
Intersection Views and Reference IDs
There are two ways to link views:
• Intersection views are special views created to link views in a one-to-many
relationship. The intersection view contains no actual fields – just the ID
fields of the two views it joins.
Linking
Connections
Chancery SMS Report Writer Guide
24
• Reference IDs are special fields that reference the IDs of other views. The
reference ID is used to link views in a one-to-one relationship.
To illustrate, use the example of a simple report that shows the name and
enrollment status of all of the students in a particular school.
The first step of the process is to determine the view that the entire report
will be based on. This is known as the root view. All of the links between
views move outward from this root view. For the example, the root view is the
view for the school, which will then have a collection of students associated
with it. The name of our root view in this case is DA_School, which has an
ID field of SchoolID.
Because the school must have a collection of students attached to it, the next
step is to look for a view of students related to a particular school and the
appropriate intersection view. In this case, that view is called
DA_SchoolStudent, and the intersection view is called
DA_School_I_Students.
To link your views, connect the ID field of the first view (1) to the same field
in the intersection view (2), then connect the other ID field key from the
intersection view (3) to the related field in the second view (4). The links look
like this:
There is then a one-to-one relationship between a student and his or her
enrollment status. Identify the reference ID in the DA_SchoolStudent, which
is EnrollmentStatus#EnrollmentStatusID. Add the view
Chancery SMS Report Writer Guide
Linking
Linking Views
25
DA_EnrollmentStatus to the report and the link from the reference ID in
DA_SchoolStudent to the ID field, EnrollmentStatusID, in the other view.
Then, you join the QUIDs for both views. The link looks like this:
Query ID Connections
In addition to IDs and reference IDs, each view contains a Query ID, or
QUID, used to uniquely identify the version of a report being run by a
particular user. This ensures that if multiple users are running the report at the
same time, a user will receive the appropriate data in their version of the
report.
In order to ensure that a single ID applies to all views in the report, you must
link the QUIDs of all of the views in the same order as the links between IDs,
intersection views, and reference IDs were made.
Linking Views
The first part of building a report involves connecting to the data, selecting
your views, and linking them logically.
1 Start Crystal Reports 9.
2 In the Welcome to Crystal Reports dialog, click “As a Blank Report”, and
then click OK.
OR
From the File menu, click New.
3 In the Database Expert dialog, under Available Data Sources, click next
to Create New Connection, and then click next to OLE DB (ADO).
Linking
Linking Views
Chancery SMS Report Writer Guide
26
4 In the OLE DB (ADO) dialog, in the Provider list, select “Microsoft
OLE DB Provider for SQL Server”. Click Next.
5 In the next OLE DB (ADO) dialog, in the Connection Information
section, enter the following information:
6 Click Finish.
Field Note
Server Enter the name of the server where the report writing database
resides. This is specific to your district.
User ID Enter the user ID of a user account that permits you to access
the report writing database.
Password Enter the password of a user account that permits you to
access the report writing database.
Database Enter the name of the report writing database, which always
ends with “_DA”. A typical database name is CSL_SMS_DA.
Use the OLE DB (ADO) data
source to create custom
reports.
The title and appearance of
this dialog depends on
whether you are working with
a blank report or one of the
wizards.
Chancery SMS Report Writer Guide
Linking
Linking Views
27
7 In the Database Expert dialog, in the Available Data Sources window,
click next to the database name.
8 Double-click the views to add them to the Selected Tables list.
• Add the root view of the report.
• Add all main views with fields that are required for the report canvas.
• Add all views that are required to link the main views, including
intersection views.
• Add any views that are required more than once as aliased views. To
rename an aliased view, click the view in the Selected Tables list and
press F2.
9 Click OK.
Note
The links that Crystal Reports creates automatically are usually more complex
than most reports require. It is a good idea to get into the habit of clearing
the existing links and setting up your own links as required.
10
On the Links tab, click Clear Links. Click OK to confirm.
11 Link the views by dragging the ID in one view to the corresponding ID in
another view, depending on the type of information you want:
• For one-to-many relationships, click and drag from the ID in the first
view to the corresponding ID in the intersection view. Then, click and
drag from the other ID in the intersection view to the corresponding
ID in the second view.
• For one-to-one relationships, click and drag from the reference ID in
the first view to the corresponding ID in the second view.
• For all views, click and drag to link all QUIDs.
Linking
Linking Views
Chancery SMS Report Writer Guide
28
12 Change the link types, if required:
• Right click on the link and select Link Options from the menu.
• In the Link Options dialog, select the appropriate join types and link
types, as required. Remember that right outer joins are illegal.
• Click OK.
13 Click OK.
14 On the Crystal Reports design page, from the File menu, click Save.
15 In the Save As dialog, navigate to the appropriate location, enter a name
in the File name field, and click Save.
Chancery SMS Report Writer Guide
Report Building
Adding Fields and Sorting
29
R
EPORT
B
UILDING
Once you have designed your report and set up a shell for it by linking the
appropriate views, the rest of the process involves using the features available
in Crystal Reports to build your report.
Adding Fields and Sorting
The first thing you should do when building your report is select the fields
that you want to appear on the report canvas. You can select from any of the
views you included in the linking step of the process.
TO ADD FIELDS:
1
In the Field Explorer window, click to expand Database Fields.
(If Field Explorer is not open, click .)
2 Click and drag the fields you want to include in the report from the Field
Explorer to the Details line on the Design tab. A heading for the field is
automatically added to the Page Header.
3 From the File menu, click Save.
Click and drag fields
to the Details line.
Report Building
Adding Groups and Summaries
Chancery SMS Report Writer Guide
30
TO SORT DATA:
1
From the Report menu, click Record Sort Expert.
2 In the Record Sort Order dialog, under Available Fields, double-click the
fields that you want to sort data by. You can select from the Report Fields
(those included on the report canvas) or open the views and select new
fields.
3 Select a Sort Direction.
4 Click OK to return to the Design tab.
5 From the File menu, click Save.
Adding Groups and Summaries
Grouping data in a report allows you to summarize your data in meaningful
ways. You can group and summarize by any field in any of the views included
in your initial linking, regardless of whether you have included the field in the
report canvas.
TO GROUP DATA:
1
From the Report menu, click Group Expert.
2 In the Group Expert dialog, under Available Fields, double-click the fields
that you want to group data by. You can select from the Report Fields
Chancery SMS Report Writer Guide
Report Building
Adding Groups and Summaries
31
(those included on the report canvas) or open the views and select new
fields.
3 Click Options.
4 In the Change Group Options dialog, specify how you want the data
grouped.
5 Click OK to return to the Group Expert dialog.
Report Building
Adding Groups and Summaries
Chancery SMS Report Writer Guide
32
6 Click OK again to return to the Design tab. The group is added to the
Group Header line.
7 Enter the name of the group.
8 From the File menu, click Save.
TO ADD A SUMMARY:
1
From the Insert menu, click Summary.
2 In the Insert Summary dialog, specify the following:
Field Description
Choose the field to
summarize
Select the field to summarize from the dropdown. You
can select any of the fields used in the report canvas or
any other field in any view linked in the report.
Calculate this summary Select the method used to calculate the summary from
the dropdown.
Summary location Select where you want the summary to appear in the
report from the dropdown.
Chancery SMS Report Writer Guide
Report Building
Adding Filters
33
3 Click OK to return to the Design tab.
4 Enter the name of the summary.
5 From the File menu, click Save.
Adding Filters
Most of the filters for reports created using the data access views are added
when the report is uploaded to Chancery SMS. However, if you wish to
include a filter that cannot be changed by users or a filter based on dynamic
properties, you must set those particular filters when you create the report in
Crystal Reports. All dynamic properties can be identified by the prefix D#.
Note
All dynamic properties can be identified by the prefix D#.
T
O ADD FILTERS:
1
From the Report menu, click Select Expert.
2 In the Select Expert dialog, there are tabs for filtering on any existing
Summary fields. Do one of the following:
• Click one of the existing tabs.
• Click the <New> tab. In the Choose Field dialog, select the field to
filter on and click OK to return to the Select Expert dialog.
Report Building
Adding Subreports
Chancery SMS Report Writer Guide
34
3 For the field to be filtered, select an operator from the first dropdown. If
required, select or enter a value for the second dropdown.
4 Click OK again to return to the Design tab.
5 From the File menu, click Save.
Adding Subreports
If you want to include secondary information in a report, you can insert a
subreport. However, keep in mind that subreports put extra strain on
resources and can significantly affect system performance.
1 Create the subreport using the same techniques you use to create the main
report.
2 In the main report, from the Insert menu, click Subreport.
3 In the Insert Subreport dialog, select Choose a report. Click Browse,
navigate to the subreport file, and click OK to specify the Report File
name.
4 Click the Link tab.
Chancery SMS Report Writer Guide
Report Building
Adding Print Options
35
5 Select a field that the reports share. Use something that is unique to a
record, such as a student ID number.
6 Click OK to return to the Design tab.
7 Click the cursor where you want to locate the subreport. Subreports are
usually placed in the Report header or footer.
8 Change the name of the subreport, if required.
9 From the File menu, click Save.
Adding Print Options
If you want to provide users with options to select at runtime, such as the
ability to print a new page for each student, grade level, or school, or to add a
legend to the bottom of the report, you can add parameters and formulas to
your Crystal Report template that will display as print options in the Report
Options dialog.
Report Building
Adding Print Options
Chancery SMS Report Writer Guide
36
1 Create the appropriate groups, summaries, and sections needed in the
report.
2 In the Field Explorer window, click Parameter Fields, and then click .
3 In the Create Parameter Field dialog, in the Name field, enter the name of
the parameter. In order for the parameter to be rendered correctly when
the report is uploaded to Chancery SMS, you must use the naming
convention “###_ROType_Name” for your parameters. The format is
broken down as follows:
Field Note
### Enter a three-digit number for the print option. This
number is used to determine the sequence in which the
print options appear in the Report Options dialog.
ROType Enter the code that designates the report option type you
want to use for the print option, which will determine
how it will appear in the Report Options dialog. Select
from the following:
• ROC – checkbox. Use this to specify an option that is
either on or off.
• ROR – radio button. Use this to require users to select
one of two or more related options.
• ROV – list of values. Use this to require users to select
one from a dropdown.
• ROL – label. Use this to add text to the print options,
such as a heading to group other options.
• ROT – text box. Use this to require users to enter text
values, such as names, for use in the report.
• ROD – date. Use this to have users specify a single
date for use in formulas in the report. This enables
users to select today’s date or to specify a different
date.
• RODR – date range. Use this to have users specify a
date range.
Chancery SMS Report Writer Guide
Report Building
Adding Print Options
37
4 In the Prompting Text field, enter the display name for the parameter.
5 From the Value Type dropdown, select one of the following values,
depending on what report option type you specified in the Parameter
Name field:
• Boolean – select this for checkboxes (ROC).
• String – select this for radio buttons (ROR), lists of values (ROV),
labels (ROL), and text boxes (ROT).
• Date – select this for dates (ROD) and date ranges (RODR).
6 If applicable, click Set Default Values.
In the “Select or enter value to add” field, enter the values for the options
available to users and click to move it to the Default Values list (For
Boolean parameters, the values are already defined). Use the up and down
arrows to order the values; the highest value is the default selection for the
parameter. Click OK.
7 To save the parameter, click OK.
Once the parameter is saved, you can use it in any formula to create the
required options. For example, the remaining steps outline how to use this
process to print a new page for each new school in a report.
Name Enter a unique name for the parameter.
Field Note
Report Building
Adding Print Options
Chancery SMS Report Writer Guide
38
8 From the Report menu, click Section Expert.
9 In the Section Expert dialog, in the Sections list, click the section to which
the parameter applies.
10 On the Common tab, select the type of option you want to apply based on
the user’s selection. Next to the option, click .
11 In the Formula Workshop dialog, in the left pane, expand the Report
Fields tree to view the parameter field you created at the bottom or the
list.
Chancery SMS Report Writer Guide
Report Building
Formatting
39
12 Double-click the parameter to add it to the formula and construct the rest
of the formula as appropriate.
13 Click Save and close.
14 Click OK.
Formatting
With many reports, you may want to do some basic formatting, such as:
• adding page numbers and dates
• changing date and time formats
• adding and formatting text
• moving, resizing, or deleting fields and text objects
• positioning elements on the page
Adding Page Numbers, Dates, and Other Special Fields
Crystal Reports comes with a predefined set of special fields that you can use
to add document information to your report, such as page numbers, dates, file
author, report title, and page count.
1 In the Field Explorer window, click to expand Special Fields.
(If Field Explorer is not open, click .)
2 Click and drag the special field to the appropriate location on the Design
tab. Special fields are usually added to the Page or Report Footers.
3 From the File menu, click Save.
Changing Date and Time Formats
Date and time fields are inserted in a default format. If you wish to use an
alternative format, you can change the field options.
1 From the File menu, click Options.
2 In the Options dialog, click the Fields tab.
3 Click Date, Time, or Date and Time.
4 In the Format Editor dialog, click the tab for the format you want, click a
Style, and then click OK.
5 Click OK again.
6 From the File menu, click Save.
Report Building
Formatting
Chancery SMS Report Writer Guide
40
Adding Text
You may also want to add text objects to your report, such as a report title.
1 Click to insert a text object.
An empty object frame appears in the upper left corner of the Design tab.
2 Position the text object where you want it to appear in the report, and
click once.
The object frame is placed in the report and is selected for editing.
3 Enter the text.
4 From the File menu, click Save.
Revising and Formatting Text Objects
Text objects in your report can be formatted any way you want. This is useful
for distinguishing the headings for data from the data itself, or for making the
report title more visible.
1 Do any of the following:
2 From the File menu, click Save.
To Do this
Revise the text Double-click the text object and change the text.
Format part of the
text
Double-click the text object and select part of the text. Right-
click the text and choose Text Formatting. In the Text Format
dialog, make the changes you want. Click OK.
Format all of the
text in the object
Right-click the text object and choose Format Text. In the
Format Editor dialog, make the changes you want. Click OK.
Chancery SMS Report Writer Guide
Report Building
Formatting
41
Moving, Resizing, or Deleting Fields and Text Objects
You may want to move, resize, or delete the fields and text objects you have
added to your report.
1 Click the border of the field or text object. If you are currently editing the
text, you must click outside the object before clicking the border.
The pointer changes to .
2 Do one of the following:
3 From the File menu, click Save.
Positioning Elements on the Page
Margins, guidelines, and grids are available to assist you in positioning objects
in your report.
1 Do any of the following:
2 From the File menu, click Save.
To Do this
Move the object Drag it to a new location.
Delete the object Right-click on the object and choose Delete.
Resize the object. Hover over the border until the pointer changes to , and
then drag the handle.
To Do this
Change margins of the page In the margins of the report, right-click and
choose Page Setup. In the Page Setup dialog,
change the margins. Click OK.
Add guidelines, non-printing lines
that you can use to align, move,
and resize objects with precision
On the ruler, click the spot where you want a
guideline.
Show grids, an underlying
network of “lines” for aligning
fields and graphics
In the margins of the report, right-click and
choose Guidelines > Design.
Snap text to grid, automatically
aligning any fields you insert or
resize to the nearest grid
In the margins of the report, right-click and
choose Snap to Grid.
Report Building
Formatting
Chancery SMS Report Writer Guide
42
Chancery SMS Report Writer Guide
Uploading Reports
Uploading Reports
43
U
PLOADING
R
EPORTS
All reports created using the data access views must be uploaded to Chancery
SMS to be run. During this process, the report is validated against the
reporting framework to ensure that the joins between views are valid.
Because of the flexibility of the reporting framework, you can share reports
with other districts and schools by distributing the .rpt files and .sql scripts
used to create them. In a similar fashion, Chancery SMS can send out newly
developed reports without waiting for a new release. The upload process for
all reports created using the data access views is the same, regardless of who
created the report.
Uploading Reports
The process of manually uploading individual reports involves the following
steps:
• Step 1: Run SQL Script, If Necessary
• Step 2: Upload Report File
• Step 3: Set Report Filters
• Step 4: Test Run the Report
Step 1: Run SQL Script, If Necessary
If your report uses report extensibility to perform complex calculations, you
must execute the SQL script provided prior to uploading the report to
Chancery SMS. You must run the SQL scripts for both the main database and
the report writing database (ending in _DA).
1 On the Chancery SMS database server, from the Start menu, click
Programs > Microsoft SQL Server > Query Analyzer.
2 In the Connect to SQL Server dialog, enter the Login Name and
Password for the owner of the Chancery SMS database, and click OK to
launch the SQL Query Analyzer.
3 In the database dropdown, select the Chancery SMS database. For
example, select CSL_SMS. The dropdown is typically set to Master.
.
4 From the File menu, click Open. Navigate to the SQL script that you
need, and click Open.
Uploading Reports
Uploading Reports
Chancery SMS Report Writer Guide
44
5 Click the Execute Query button to run the script on the main
database.
A message displays to indicate that the script ran successfully.
6 In the database dropdown, select the Chancery SMS Data Access (DA)
database. The name of this database matches the name of the main
Chancery SMS database, but ends with the suffix “_DA”. For example, if
the main Chancery SMS is called CSL_SMS, the Data Access database is
called CSL_SMS_DA.
7 Click the Execute Query button to run the script on the _DA database.
8 Close SQL Query Analyzer.
Step 2: Upload Report File
After running any required SQL scripts, you can upload the report files.
Unlike in the previous step, you do not need to be on the machine running
the Chancery SMS database. You upload reports using the Chancery SMS
interface.
1 Log on to Chancery SMS.
2 On the control bar, under Admin, click District Setup or School Setup.
3 On the District Setup page, under Reports, click Customize Reports.
OR
On the School Setup page, under Customization, click Customize
Reports.
Note
The Customize Pre-4.2 Reports link is only for reports that connect directly to
the tables or use the XML schema.
4
On the Customize Reports page, do one of the following:
• If the report has already been added to the Report List, click the name
of the report you want to upload.
• If the report has not yet been added to the Report List, from the
Actions menu, click Add Report.
Chancery SMS Report Writer Guide
Uploading Reports
Uploading Reports
45
5 On the Edit Report page, in the General area, enter or change the
following information, as necessary:
6 In the Category field, do one of the following to group reports together
on the Customize Reports and Report List pages:
• Click Select and select an existing category from the dropdown.
• Click New and enter the name of a new category.
7 In the Report Weight field, do one of the following to ensure that the
report is sent to the appropriate server for processing:
• Click Light to have the report run by a Lightweight Report server.
This type of server manages reports that do not require significant
system resources.
• Click Heavy to have the report run by a Report server. This type of
server manages reports that require significant system resources.
8 In the Report File area, click Browse, navigate to the appropriate report
file, and click Open.
If the report has been created using illegal joins (e.g., if the joins are not
unidirectional, or if views are linked on the incorrect field), the report will
not upload. You will receive a system message outlining the error in the
report. If you receive an error message, you must fix the report in Crystal
Reports and then attempt to upload the file again.
Note
Only the first error the system finds will display; any subsequent errors will
display only after the first issue is resolved.
Field Note
Report Name This is the name that displays in the report list.
Description This is the description of the report that displays in the report list.
Uploading Reports
Uploading Reports
Chancery SMS Report Writer Guide
46
9 If you want to change the default permissions for the report, in the
Permissions area, identify the base roles and roles that can Edit or View
the report.
10 Click OK.
Step 3: Set Report Filters
The Report Filters page allows you to specify filters for your report. The
options available on this page are dynamically generated from the views you
selected when you created your report in Crystal Reports.
In each view, you can select up to five of the available fields to act as filters for
your report. When users run the report, these filters appear on the Reporting
Options page, where they can enter specific values for the default filters or
add other filters as they choose.
Note
Remember that you cannot set filters on dynamic properties in Chancery SMS. If
you require your report to filter on a dynamic property, as in the case of viewing
only active students, you must have added this filter in Crystal Reports prior to
uploading the report to Chancery SMS.
1
On the Customize Reports page, select the checkbox next to the report.
2 From the Actions menu, click Edit Filters.
Click to view the
roles in a base role.
Set the permissions
for a role.
Set the permissions
for a base role.
Chancery SMS Report Writer Guide
Uploading Reports
Uploading Reports
47
The Report Filters dialog displays. By default, all of the panels are
collapsed.
3 Expand the panels that contain the filters you need to set, and then select
the filters from the dropdowns.
4 Click Save, and then click Close.
Uploading Reports
Uploading Reports
Chancery SMS Report Writer Guide
48
Step 4: Test Run the Report
Once you have created a custom report using the new data access views, you
should perform a test run of the report using various filters and user IDs to
ensure that the appropriate data is returned.
When you run the report, all of the views that were used to create the report
are available for filtering. If a field in a view was selected as a filter when the
report was uploaded to Chancery SMS, the panel for that view is open, and
the filter is displayed on the Basic tab. However, you are not required to use
only the filters chosen at design time. If a filter already exists for a view, you
can add more filters for that view on the Detail tab. You can also expand the
panels for other views and add filters for them.
1 On the Customize Reports page, select the checkbox next to the report.
2 From the Actions menu, click Run Report.
3 In the Report Options dialog, in the Filters panel, perform one of the
following:
5 If you have permission to schedule reports, in the Scheduling Options
panel, perform one of the following:
6 In the Output Options panel, select the appropriate type of output from
the available options:
• Adobe Acrobat (*.pdf)
• Crystal Reports (*.rpt)
To Do this
Run the report as
designed
Select an operator and a value for each Search by
dropdown or leave the filters blank.
Add a filter 1 If the view already has a filter, click the Detail tab.
OR
If the view does not already have a filter, expand the
panel for the view.
2 In one of the available dropdowns, select the
appropriate filter, the operator, and the value.
3 Click Add Criteria.
4 Repeat as required.
To Do this
Run the report
immediately
Select Run now.
Run the report once at a
specific time
Select One time, and then enter the date and time for the
scheduled task.
Run the report on a
recurring schedule
Select Recurring, and then enter the start date, start time,
and interval for the scheduled task.
Chancery SMS Report Writer Guide
Uploading Reports
Uploading Reports
49
• Microsoft Excel (*.xls)
• Microsoft Word (*.doc)
• Rich Text (*.rtf)
7 Click Run Report.
The Report Status dialog displays status information to indicate what
stage the report is at as it is being generated. If you require, you can cancel
the report while it is in the queue.
If the report was scheduled to run immediately, it is displayed in a browser
in the specified format.
If the report was scheduled to run at a specific time, the task is sent to the
Task Manager. Once the report has run, it can be downloaded by clicking
the Reports links in the Scheduled Events panel on the home page.
Uploading Reports
Uploading Reports
Chancery SMS Report Writer Guide
50
Chancery SMS Report Writer Guide
Addendum: Database Tables
Database Tables
51
A
DDENDUM
: D
ATABASE
T
ABLES
If desired, you can build your report by connecting directly to the tables in the
Chancery SMS database. This type of report can be customized only by those
with access to district setup.
Database Tables
Reports that are created by connecting directly to tables in the database can
include any available records in Chancery SMS.
Naming Conventions
To build reports using a direct link to the tables, you should have a solid
understanding of the naming conventions for the tables, fields, and keys in the
database.
Note
Table and field names are in ALL CAPS.
T
ABLES
The names of the tables in the database are set up to convey as much
information as possible in a limited amount of characters:
The second section of the name indicates the kind of information the table
contains, abbreviated for quick reference. It is important to note that, while all
of the tables are available for reporting, not all of them are useful for
reporting. Use the following table as a guide:
Abbreviation Description Used for Reporting?
ACT Accounts (user and roles) No
ADM Administration No
ASCII ASCII import No
BIN Bins for report card Yes
The first section indicates ownership.
Most tables start with CSL_ for
Chancery Software Ltd. or CDM_ for
Custom Data Management.
The second section indicates the type
of data. Refer to the table below for
further information.
The last section is a
description of the data.
Words are separated with
underscores.
CSL_STD_DEMO_DISTRICT
Addendum: Database Tables
Database Tables
Chancery SMS Report Writer Guide
52
FIELDS
Each table contains a collection of records, and each record is made up of a
series of fields. A field contains a single piece of information. A field name is
simply a description of the information it contains, abbreviated as required
and with underscores to separate words. Examples include LAST_NAME,
GENDER, and STD_NUMBER.
PRIMARY KEYS
Every table has a primary key, which is a field that uniquely identifies that
table. These keys cannot be used as fields in your report.
FOREIGN KEYS
In addition to the primary key, tables can also contain foreign keys, which are
links from other tables. Like the primary keys, foreign keys cannot be used as
fields in your reports; they are for linking purposes only.
CDM Custom Data Management No
CNT Contacts Yes
CRS Courses Yes
DIS District-wide Yes
DS Directory Service Yes
FAM Family information Yes
HOMEROOM Homeroom Yes
K12P K12 Planet No
RPT Reports No
SCH Schools Yes
SET Setup lists Yes
SMS SMS School Yes
STD Student Yes
STRING Event logs No
TCH Teacher Yes
TRANSCRIPT Transcripts Yes
TTBL Timetable information Yes
WKL Working lists No
Abbreviation Description Used for Reporting?
The first section indicates
that the field is a key.
The primary key always has the
same name as the table.
ID_STD_DEMO_DISTRICT
Chancery SMS Report Writer Guide
Addendum: Database Tables
Database Tables
53
The foreign key has the same naming convention as the primary key, except
that the name of the key is the name of the table to which it links. Foreign
keys in the CSL_STD_DEMO_DISTRICT table, for example, include the
following:
• ID_DIS_ADDRESS (CSL_DIS_ADDRESS)
• ID_SET_GRADE_LEVEL (CSL_SET_GRADE_LEVEL)
• ID_ADM_ENROLLMENT_STATUS
(CSL_ADM_ENROLLMENT_STATUS)
Connections
The tables in the database are independent entities. In a report, you must first
specify the relationships between the relevant tables so that the report knows
what path to take when it extracts information from the database. These
connections are always formed by linking the primary key of one table to one
of the foreign keys of the other table. The link looks like this:
One important thing to keep in mind when creating these links is where data
is stored. Consider the example in the diagram above. A student’s
demographic information is contained in the two tables
CSL_STD_DEMO_DISTRICT and CSL_STD_DEMO_SCHOOL. To
avoid replicating data, most of the fields are kept in the district table. That
means that if you want to report on the student’s demographic information at
the school level, you have to create a connection between these tables in order
for that student’s name, birthdate, gender, etc. to be available in the report.
Addendum: Database Tables
Adding Custom Reports (Database Tables)
Chancery SMS Report Writer Guide
54
Adding Custom Reports (Database Tables)
You can create a new report and add it to Chancery SMS for you or others to
run.
Before you create a custom report, check the list of built-in and custom
reports to see if an existing report meets your needs. It may be easier to
customize an existing report than to create a new report.
Step 1: Preparation
To create a custom database table report, you need:
• a user ID and password for the Chancery SMS database
• the name of the Chancery SMS database
• the name of the server that the Chancery SMS database is on
If you do not have this information, contact the district administrator.
Step 2: Create the Report
1 Start a report and connect it to the database.
2 Select tables with the data you want.
3 Link the tables.
4 Add fields to the report.
5 Group information and add summaries.
6 Add filters.
7 Complete the formatting.
8 Save and close the report.
See “Creating Custom Reports (Database Tables)” on page 62.
Step 3: Add the Report to Chancery SMS
1 Log on as a district administrator.
2 On the control bar, under Admin, click District Setup.
3 On the District Setup page, under Reports, click “Customize Pre-4.2
Reports”.
4 On the Customize District Reports page, in the “To create a new report”
section, click District Report List.
Chancery SMS Report Writer Guide
Addendum: Database Tables
Adding Custom Reports (Database Tables)
55
5 On the District Report List page, from the Actions menu, click Add
Custom Report.
6 On the Edit Report page, in the General area, enter the following
information:
7 In the Report File area, click Browse, navigate to the report that you want
to add, and click Open.
8 In the Permissions area, identify the base roles and roles who can Delete,
Edit, or View the report.
You can specify access for each base role or for the roles within it. Click
to display the roles in the base role.
9 Click OK.
Step 4: Test Run the Report in Chancery SMS
Once you have created a custom report, you should perform a test run of the
report using various filters and user IDs to ensure that the appropriate data is
returned. For district reports, there are two ways to run the report: in Crystal
Reports and in Chancery SMS.
Field Note
Report Name Enter the name of the report. This is the name that displays in the
report list.
Description Enter a description of the report for internal purposes.
Category Select a category from the dropdown, or click New and enter a
new category name. The category groups similar reports together,
in the report list.
Click to view the
roles in a base role.
Set the permissions
for a role.
Set the permissions
for a base role.
Addendum: Database Tables
Adding Custom Reports (Database Tables)
Chancery SMS Report Writer Guide
56
Note
If the report is being used solely at the district level by only a few users, you may
prefer to leave the report in Crystal Reports. Some of the more complex features
in Crystal Reports, such as parameters, do not transfer into Chancery SMS.
Custom reports you are creating for use by a larger number of users should be
uploaded to and run in Chancery SMS.
1
In the control bar, under District, click Reports.
2 On the District Reports page, click the name of the report.
3 In the Report Options dialog, in the Database Connection section, enter
the following information:
4 Click Preview.
When the report is ready, it generates in PDF format in a Web browser
window.
5 Print or save the report, and then close the browser window.
Field Note
Server Enter the name of the server where the database resides. This
is specific to your district.
User ID Enter the user ID of a user account that permits you to access
the database.
Password Enter the password of a user account that permits you to
access the database.
Database Enter the name of the database. A typical database name is
CSL_SMS.
Chancery SMS Report Writer Guide
Addendum: Database Tables
Modifying Custom Reports (Database Tables)
57
Modifying Custom Reports (Database Tables)
If you have an existing report built directly on the database tables in Chancery
SMS that requires some slight modification, you can customize the existing
report, rather than create a new one.
When you customize this type of report, you can add or delete fields, change
the order of columns, create groups and summaries, add formatting – all of
the same features you use when creating a report from scratch.
To customize an existing report, you download the report, modify it using
Crystal Reports, and then add the revised report back into Chancery SMS.
You can repeatedly modify an existing report. For example, after you add a
modified report to Chancery SMS, you can download it and modify it further.
Later, if you want to remove the modified report, you can revert to the
original report. For more general information about Crystal Reports, see
“Working with Crystal Reports” on page 62.
Note
You must always customize an existing report by downloading and modifying it.
You cannot create a new report and add it to Chancery SMS in place of an existing
report.
Step 1: Download the Report
1 In the control bar, under Admin, click District Setup.
2 On the District Setup page, under Reports, click “Customize Pre-4.2
Reports”.
3 On the Customize District Reports page, in the “To modify a built-in
report” section, click District Report List.
4 On the District Report List page, select the report. From the Actions
menu, click Edit Custom Report.
OR
Click the report name.
5 On the Edit Report page, in the Report File section, click Download next
to the report file.
Note
If the built-in report has already been customized, there will be two files from
which to choose, the original built-in report and the customized version.
Ensure that you select the correct version for your purposes.
6
In the File Download dialog, click Save.
Addendum: Database Tables
Modifying Custom Reports (Database Tables)
Chancery SMS Report Writer Guide
58
7 In the Save As dialog, navigate to the folder where you want to save the
file, and then click Save.
If the Download Complete dialog displays, wait until it says “Download
Complete,” and then click Close.
Step 2: Customize the Report
1 Open the report in Crystal Reports.
2 Modify the report.
See “Modifying Custom Reports (Database Tables)” on page 68.
Step 3: Add the Modified Report to Chancery SMS
Now that you have modified the report, add it to Chancery SMS so that it will
be used in place of the original report.
1 In the control bar, under Admin, click District Setup.
2 On the District Setup page, under Reports, click “Customize Pre-4.2
Reports”.
3 On the Customize District Reports page, in the “To modify a built-in
report” section, click District Report List.
4 On the District Report List page, select the report. From the Actions
menu, click Edit Custom Report.
OR
Click the report name.
5 On the Edit Report page, in the Report File area, click Browse, navigate to
the customized report, and click Open.
6 Ensure that “Use report file” is set to Custom so that Chancery SMS uses
your customized report, rather than the original report.
Click Browse to
navigate to
modified report file
.
Click Custom so that Chancery SMS uses the modified report, not the original one.
(Use report file appears only after you browse to a file.)
Chancery SMS Report Writer Guide
Addendum: Database Tables
Reverting to an Original Report
59
7 In the Permissions Area, you can specify permissions for base roles or
roles. Click to display the roles in the base role.
8 Click OK.
Reverting to an Original Report
If you have modified a built-in report and added it to Chancery SMS, you can
later choose to stop using the modified report and return to using the original
report.
When you revert to the original report, the modified report remains on the
Chancery SMS database server unless you remove it. The modified report is
not used in Chancery SMS, but it is available for later use.
1 In the control bar, under Admin, click District Setup.
2 On the District Setup page, under Reports, click “Customize Pre-4.2
Reports”.
3 On the Customize District Reports page, in the “To modify a built-in
report” section, click District Report List.
4 On the District Report List page, select the report. From the Actions
menu, click Edit Custom Report.
OR
Click the report name.
5 On the Edit Report page, under Report File, set “Use report file” to
Built-In rather than Custom.
6 To remove the modified report from the Chancery SMS database server
so that it cannot be used again, click Clear.
7 Click OK.
Click Built-in so that Chancery SMS uses the
original report.
To delete the modified report
for the Chancery SMS
database server, click Clear.
Addendum: Database Tables
Modifying the Permissions of a Report
Chancery SMS Report Writer Guide
60
Modifying the Permissions of a Report
1 In the control bar, under Admin, click District Setup.
2 On the District Setup page, under Reports, click “Customize Pre-4.2
Reports”.
3 On the Customize District Reports page, in the “To modify a built-in
report” section, click District Report List.
4 On the District Report List page, select the report. From the Actions
menu, click Edit Custom Report.
OR
Click the report name.
5 On the Edit Report page, in the Permissions area, identify the base roles
and roles that can Delete, Edit, or View the report.
You can specify access for each base role or for the roles within it. Click
to display the roles in the base role.
6 Click OK.
Deleting Custom Reports
You can delete custom reports from Chancery SMS, assuming that your user
account has Delete permissions for that report.
Deleting a report means that it’s no longer available for users to run, and it is
no longer on the Chancery SMS database server. However, if the .rpt file is
still available, the report can be restored.
1 In the control bar, under Admin, click District Setup.
2 On the District Setup page, under Reports, click “Customize Pre-4.2
Reports”.
Click to view the
roles in a base role.
Set the permissions
for a role.
Set the permissions
for a base role.
Chancery SMS Report Writer Guide
Addendum: Database Tables
Running Database Table Reports at the School Level
61
3 On the Customize District Reports page, in the “To modify a built-in
report” section, click District Report List.
4 On the District Report List page, select the report. From the Actions
menu, click Delete Custom Report.
Running Database Table Reports at the School
Level
Certain data that schools may need to view (such as CDM data) cannot be
accessed in a school-level report. To resolve this situation, there are two
options: running the report at the district level, or providing district-level
access to school users.
OPTION 1: RUN THE REPORT AT THE DISTRICT LEVEL
1 Create the report in Crystal Reports by linking directly to the tables. See
“Creating Custom Reports (Database Tables)” on page 62.
2 When a school requires this information, run the report in Crystal
Reports, setting a filter to reflect the specific school.
3 Forward the report to the school.
OPTION 2: SET UP SCHOOL USERS WITH DISTRICT ACCESS TO REPORTS
1 Create a role within the base district role specific to each school in the
district that you want to be able to run district-level reports. Limit the
scope of each role to a single school. Remove all permissions for the roles.
See “Controlling User Access” in the District Administration Guide.
2 Assign the new roles to the appropriate school users. See “Controlling
User Access” in the District Administration Guide.
3 Create the report in Crystal Reports by linking directly to the tables.
Create one version for each school, and add a filter to each to limit the
results to that particular school. See “Creating Custom Reports (Database
Tables)” on page 62.
4 Upload each report to Chancery SMS. Assign view-only permission to the
role that matches the version of the report. See “Adding Custom Reports
(Database Tables)” on page 54.
5 Provide the user account information and the logon information for the
database to the school users.
Addendum: Database Tables
Working with Crystal Reports
Chancery SMS Report Writer Guide
62
Working with Crystal Reports
With Crystal Reports, you can create many types of reports with varying levels
of complexity.
Rather than repeating all of the information that is available in the Crystal
Reports Online Help, this section summarizes how to create and modify a
custom report based on the database tables.
For more information, consider taking a Chancery training course on
reporting. Call us toll free at 1-800-999-9931 or see the Training area of our
Support web site at http://support.chancery.com for an up-to-date list of
scheduled courses.
Creating Custom Reports (Database Tables)
You can create district reports in Crystal Reports in different ways. Start by
using the Standard Report Wizard so that you become familiar with the steps
involved in creating a new report. Later, you can also choose to create a blank
report and add all of the fields manually, or you can use one of the other
wizards.
Note
Do not use the OLAP Wizard, which is suited to a different kind of database.
To illustrate the process, the following steps outline how to use the Standard
Report Wizard to create a sample report that:
• lists the students in the district, and provides the following details about
each one: first name, last name, student number, and enrollment status.
• groups students by school.
• provides summaries of the total number of students in each school and
the total number of students in the district.
• filters records to show only students who have a registration status of
Registered.
The sample report looks like this:
Chancery SMS Report Writer Guide
Addendum: Database Tables
Working with Crystal Reports
63
Step 1: Start a Report and Connect It to the Data
1 Start Crystal Reports 9.
2 In the Welcome to Crystal Reports dialog, click “Using the Report
Wizard”, and then click OK.
OR
From the File menu, click New.
3 In the Crystal Reports Gallery dialog, in the Choose a Wizard list, select
Standard, and click OK.
4 In the Standard Report Creation Wizard dialog, under Available Data
Sources, click next to Create New Connection, and then click next
to OLE DB (ADO).
5 In the OLE DB (ADO) dialog, in the Provider list, select “Microsoft
OLE DB Provider for SQL Server”. Click Next.
6 In the next OLE DB (ADO) dialog, in the Connection Information
section, enter the following information:
Field Note
Server Enter the name of the server where the database resides. This
is specific to your district.
Use the OLE DB (ADO) data
source to create custom
reports using the database
tables.
The title and appearance of
this dialog depends on
whether you are working with
a blank report or one of the
wizards.
Addendum: Database Tables
Working with Crystal Reports
Chancery SMS Report Writer Guide
64
7 Click Finish.
The Available Data Sources list now shows the database to which you are
connected.
Step 2: Select Tables with the Data You Want
1 Click next to the database name.
2 Click next to Tables.
3 Double-click the tables you want to use for the report.
For the sample report, select the following tables:
4 Click Next.
The dialog displays a Links tab that shows a diagram of the tables you
chose and the fields they share.
Step 3: Link the Tables
Note
The links that Crystal Reports creates automatically are usually more complex
than most reports require. It is a good idea to get into the habit of clearing the
existing links and setting up your own links as required.
User ID Enter the user ID of a user account that permits you to access
the database.
Password Enter the password of a user account that permits you to
access the database.
Database Enter the name of the database. A typical database name is
CSL_SMS.
CSL_ADM_ENROLLMENT _STATUS
CSL_SET_REGISTRATION_STATUS
CSL_SET_SCHOOL_NAME
CSL_STD_DEMO_DISTRICT
CSL_STD_DEMO_SCHOOL
Field Note
Chancery SMS Report Writer Guide
Addendum: Database Tables
Working with Crystal Reports
65
1 Click Clear Links.
2 Arrange the tables so that you can work with them more easily:
• Resize the dialog so that you can see all of the tables.
• Resize each table so that you can see all of the fields in it.
3 Link the tables by dragging a field in one table to the field of the same
name in another table, depending on the type of information you want.
For the sample report, ensure the following links exist:
4 Click Next.
Step 4: Add Fields to the Report
1 On the Fields page, under Available Fields, expand the tables, and double-
click the fields that you want displayed on the report.
Under Fields To Display, the fields are shown in alphabetical order in the
format TableName.FieldName.
Drag this field To this field
ID_STD_DEMO_DISTRICT field in
CSL_STD_DEMO_DISTRICT table
The same field in
CSL_STD_DEMO_SCHOOL table
ID_SET_REGISTRATION_STATUS
field in CSL_STD_DEMO_DISTRICT
table
The same field in
CSL_SET_REGISTRATION_STATUS
table
ID_SET_SCHOOL_NAME field in
CSL_STD_DEMO_DISTRICT table
The same field in
CSL_SET_SCHOOL_NAME table
ID_ADM_ENROLLMENT_STATUS
field in CSL_STD_DEMO_DISTRICT
table
The same field in
CSL_ADM_ENROLLMENT_STATUS
table
Addendum: Database Tables
Working with Crystal Reports
Chancery SMS Report Writer Guide
66
For the sample report, double-click the following fields:
2 Under Fields To Display, change the order of the fields by clicking a field,
and then clicking the arrows to move it up or down.
In the final report, items in the Fields to Display list are displayed as
columns from left to right. The higher an item is in the Fields to Display
list, the further left it appears in the final report.
For the sample report, re-order the fields as follows:
3 Click Next.
Step 5: Group Information and Add Summaries
Grouping data in a report allows you to summarize your data in meaningful
ways.
1 On the Grouping page, under Available Fields, under Report Fields,
double-click the fields that you want to group data by.
From this table Double-click this field
CSL_ADM_ENROLLMENT_STATUS ENROLLMENT_STATUS_DESC
CSL_STD_DEMO_DISTRICT STD_NUMBER
CSL_STD_DEMO_DISTRICT LAST_NAME
CSL_STD_DEMO_DISTRICT FIRST_NAME
CSL_STD_DEMO_DISTRICT.LAST_NAME
CSL_STD_DEMO_DISTRICT.FIRST_NAME
CSL_STD_DEMO_DISTRICT.STD_NUMBER
CSL_ADM_ENROLLMENT_STATUS.ENROLLMENT_STATUS_DESC
Chancery SMS Report Writer Guide
Addendum: Database Tables
Working with Crystal Reports
67
For the sample report, in the CSL_SET _SCHOOL_NAME table,
double-click SCHOOL_NAME_DESC.
2 Click Next.
3 On the Summaries page, under Available Fields, double-click any fields
you want to summarize.
For the sample report, double-click
CSL_STD_DEMO_DISTRICT.STD_NUMBER.
4 Under Summarized Fields, for each summary indicated by , click the
summary, and then select the summary type from the dropdown under
Summarized Fields.
For the sample report, click the summary indicated by . In the
dropdown under Summarized Fields, select Count.
5 Click Next.
6 On the Group Sorting page, sort items as required. For the sample report,
do not use this feature. Click Next.
7 On the Chart page, add a chart if required. For the sample report, do not
use this feature. Click Next.
Step 6: Add Filters
Use data filtering to isolate the results of the report to include only the
information you’re interested in returning. You specify the selection criteria
on the Record Selection page.
1 On the Record Selection page, under Available Fields, double-click a field
that you want to filter data on.
For the sample report, in the CSL_SET_REGISTRATION_STATUS
table, double-click the REGISTRATION_STATUS_DESC field.
2 In the dropdowns under the Filter Fields area, select a comparison
method, and then select a value.
For the sample report, select Is Equal To. In the dropdown displayed
under Is Equal To, select Registered.
3 Click Finished.
A preview of the report is displayed.
Addendum: Database Tables
Working with Crystal Reports
Chancery SMS Report Writer Guide
68
Step 7: Complete the Formatting
1 Click the Design tab.
2 Format the report by:
• adding special fields, such as print date
• adding text objects, such as a title
• formatting text in fields and text objects
• moving, resizing, or deleting fields and text objects
For more information, see “Formatting” on page 39.
3 From the File menu, clear “Save Data with Report”.
4 Save and close the report.
Modifying Custom Reports (Database Tables)
1 Start Crystal Reports 9.
2 In the Welcome to Crystal Reports dialog, click “Open an Existing
Report”, and then click OK.
OR
From the File menu, click Open.
3 In the Open dialog, navigate to the report that you want to modify, and
click Open.
Chancery SMS Report Writer Guide
Addendum: Database Tables
Working with Crystal Reports
69
4 Do any of the following:
5 Save and close the report.
To Do this
Add a table 1 Click the Database Expert.
2 Under Available Data Sources, click next to Create
New Connection, and then click next to
OLE DB (ADO).
3 In the OLE DB (ADO) dialog, in the Provider list,
click “Microsoft OLE DB Provider for SQL Server”.
Leave Data Link File cleared. Click Next.
4 On the Connection Information page, complete the
fields, and then click Finish.
5 Under OLE DB (ADO), under the server name, click
next to the database name.
6 Click next to Tables.
7 Under Available Data Sources, double-click the
appropriate tables.
8 Click Next.
9 On the Link page, ensure the appropriate links exist. If
the links are incorrect or missing, click Clear Links,
and create the links by dragging a field from one table
to the corresponding field in the other table.
10Click Next.
Add a field 1 In the Field Explorer window, click next to Tables.
2 Click and drag the appropriate fields to the Design
window.
Group information and
add summaries
1 From the Report menu, click Group Expert.
2 On the Group Expert page, under Available Fields,
double-click the appropriate field.
3 Click OK.
Add Summaries 1 From the Insert menu, click Summary.
2 On the Insert Summary page, in the “Choose the field
to summarize” field, select the appropriate field.
3 In the Calculate this summary field, select the
summary type.
4 In the Summary location field, select the location for
the summary.
5 Click OK.
Add filters 1 From the Report menu, click Select Expert.
2 On the Select Expert page, under Available Fields, in
the appropriate table, double-click the appropriate
field.
3 Select an operator from the dropdown under the Filter
Fields area, click the operator.
4 In the associated field, enter or select a value.
5 Click OK.
Change the formatting See “Formatting” on page 39.
Addendum: Database Tables
Working with Crystal Reports
Chancery SMS Report Writer Guide
70
Chancery SMS Report Writer Guide
Addendum: XML Schema
XML Schema
71
A
DDENDUM
: XML S
CHEMA
Previously, the XML Schema was used to create most custom reports at the
school-level. The XML Schema is based on the logical model of the database.
This method is still supported, though the data access method is
recommended. This type of report can be customized only by those with
access to school setup.
XML Schema
The major limitation of the XML schema is that it contains only a subset of
the information in the Chancery SMS database. Although there are almost
600 tables in the database, there are fewer than 100 tables in the XML Schema
available for use in reporting.
Naming Conventions
The XML Schema has its own unique naming convention for its components.
TABLES
Tables in the XML Schema are the equivalent of schemas in the logical model.
The table names are simple descriptions of the type of information they
contain, and are formatted in upper and lower case, without punctuation (with
a few exceptions). Examples include ContactPhone, PeriodAttendance, and
SchoolStudent. This information is based on the naming convention of the
schema in the metadata.
Tables may also have sub-tables that encapsulate complicated joins.
FIELDS
Each table has its own collection of fields, based on properties in the logical
model, which can be from a single table in the database or from a group of
tables, depending on how the metadata is set up. Fields are named the same
way as the tables. Examples include Name, DisabilityCode, and
EmailAddress.
For fields that have sub-tables in the background, periods are used to separate
items, as in the case of MailingAddress.Area.Description.
Addendum: XML Schema
XML Schema
Chancery SMS Report Writer Guide
72
PRIMARY KEYS
As with the database tables, each table in the XML schema has a primary key
that uniquely identifies each of its records.
FOREIGN KEYS
Tables can also contain foreign keys that link them to other tables. A foreign
key is the primary key in another table. These relate to reference properties in
the logical schema.
None of these keys can be used as fields in your reports.
Connections
To create a report, you need to connect the tables together. As with the
database tables, these connections are always formed by linking the primary
key of one table to one of the foreign keys of the other table. The link looks
like this:
All keys end with ID.
The primary key always has the
same name as the table.
SchoolStudentID
The first section is the property
of the other schema.
SchoolStudent.SchoolStudentID
The second section is the
name of the other schema.
All keys end with ID.
Property and schema are separated by a period.
Chancery SMS Report Writer Guide
Addendum: XML Schema
Adding Custom Reports (XML Schema)
73
Adding Custom Reports (XML Schema)
You can create a new report and add it to Chancery SMS using the Data
Access Views. This is the method that you should use to create all new
reports.
Step 1: Preparation
To create a custom school report using the XML schema, you must install the
dynamic-link library (DLL) required by Crystal Reports, if you have not
already done so. You must also download the latest version of the schema
from Chancery SMS.
Note
You do not need these files to run reports. Only people who are actually
customizing reports using the XML schema need these files.
T
O INSTALL THE CRYSTAL REPORTS DLL:
1
Visit the Chancery support web site at http://support.chancery.com.
2 Search for “Crystal Reports Update”.
3 In the search results, click the Crystal Reports Update for Chancery SMS
link that matches the version of Chancery SMS that you are using.
4 On the KnowledgeBase Article page, under Download Files, click Update
for Crystal Reports v9.0, and save the file to your computer.
5 Decompress the file using a third-party tool, such as WinZip.
For instructions on using WinZip, see KBase item 9075, “Utilities for
expanding compressed files” on the Chancery support web site.
6 Copy the crdb_adoplus.dll file to the following folder:
C:\Program Files\Common Files\Crystal Decisions\2.0\bin
T
O DOWNLOAD THE XML SCHEMA:
1
In the control bar, under Admin, click School Setup.
2 On the School Setup page, under Reports, click “Customize Pre-4.2
School Reports”.
3 On the Customize School Reports page, In the “To create a new report”
section, under Step 1, click Download schema.
4 In the Save As dialog, navigate to the folder where you want to save the
file, and then click Save.
5 If the Download Complete dialog displays, wait until it says “Download
Complete,” and then click Close.
Addendum: XML Schema
Adding Custom Reports (XML Schema)
Chancery SMS Report Writer Guide
74
Step 2: Create the Report
1 Start a report and connect it to the XML Schema.
2 Select tables with the data you want.
3 Link the tables.
4 Add fields to the report.
5 Group information and add summaries.
6 Add filters.
7 Complete the formatting.
8 Save and close the report.
See “Creating Custom Reports (XML Schema)” on page 80.
Step 3: Add the Report to Chancery SMS
1 Log on as a school administrator.
2 On the control bar, under Admin, click School Setup.
3 On the School Setup page, under Reports, click “Customize Pre-4.2
Reports”.
4 On the Customize School Reports page, in the “To create a new report”
section, click School Report List.
5 On the School Report List page, from the Actions menu, click Add
Custom Report.
6 On the Edit Report page, in the General area, specify the following
information:
7 In the Report File area, click Browse, navigate to the report that you want
to add, and click Open.
Field Note
Report Name Enter the name of the report. This is the name that displays in the
report list.
Description Enter a description of the report for internal purposes.
Category Select a category from the dropdown, or click New and enter a
new category name. The category groups similar reports together,
in the report list.
Chancery SMS Report Writer Guide
Addendum: XML Schema
Modifying Custom Reports (XML Schema)
75
8 In the Permissions area, identify the base roles and roles that can Delete,
Edit, or View the report.
You can specify access for each base role or for the roles within it. Click
to display the roles in the base role.
9 Click OK.
Step 4: Test Run the Report in Chancery SMS
Once you have created a custom report, you should perform a test run of the
report using various filters and user IDs to ensure that the appropriate data is
returned. All school reports are run in Chancery SMS.
1 In the control bar, under School, click Reports.
2 On the School Reports page, click the name of the report.
3 Click Preview.
When the report is ready, it generates in PDF format in a Web browser
window.
4 Print or save the report, and then close the browser window.
Modifying Custom Reports (XML Schema)
To customize any reports that you have already created using the XML
Schema, download the report, modify it using Crystal Reports, and then add
the revised report back into Chancery SMS.
You can repeatedly modify a report. For example, after you add a modified
report to Chancery SMS, you can download it and modify it further.
Later, if you want to remove the modified report, you can revert to the
original report. For more general information about Crystal Reports, see
“Working with Crystal Reports” on page 79.
Click to view the
roles in a base role.
Set the permissions
for a role.
Set the permissions
for a base role.
Addendum: XML Schema
Modifying Custom Reports (XML Schema)
Chancery SMS Report Writer Guide
76
Note
You must always customize an existing report by downloading and modifying it.
You cannot create a new report and add it to Chancery SMS in place of a built-in
report.
Step 1: Download the Report
1 In the control bar, under Admin, click School Setup.
2 On the School Setup page, under Reports, click “Customize Pre-4.2
School Reports”.
3 On the Customize School Reports page, in the “To create a new report”
section, under Step 1, click Download schema.
4 In the Save As dialog, navigate to the folder where you want to save the
file, and then click Save.
5 If the Download Complete dialog displays, wait until it says “Download
Complete,” and then click Close.
6 Under Step 3, click School Report List.
7 On the School Report List page, select the report. From the Actions
menu, click Edit Custom Report.
OR
Click the report name.
8 On the Edit Report page, in the Report File section, click Download next
to the report file.
Note
If the built-in report has already been customized, there will be two files from
which to choose, the original built-in report and the customized version.
Ensure that you select the correct version for your purposes.
9
In the File Download dialog, click Save.
10 In the Save As dialog, navigate to the folder where you want to save the
file, and then click Save.
11 If the Download Complete dialog displays, wait until it says “Download
Complete,” and then click Close.
Step 2: Customize the Report
1 Open the report in Crystal Reports.
2 Modify the report.
See “Modifying Custom School Reports (XML Schema)” on page 85.
Chancery SMS Report Writer Guide
Addendum: XML Schema
Reverting to an Original Report
77
Step 3: Add the Modified Report to Chancery SMS
Now that you have modified the report, add it to Chancery SMS so that it will
be used in place of the original report.
1 In the control bar, under Admin, click School Setup.
2 On the School Setup page, under Reports, click “Customize Pre-4.2
School Reports”.
3 On the Customize District Reports page, in the “To modify a built-in
report” section, click School Report List.
4 On the School Report List page, select the report. From the Actions
menu, click Edit Custom Report.
OR
Click the report name.
5 On the Edit Report page, in the Report File area, click Browse, navigate to
the customized report, and click Open.
6 Ensure that “Use report file” is set to Custom so that Chancery SMS uses
your customized report, rather than the original report.
7 In the Permissions Area, you can specify permissions for the base roles or
roles. Click to display the roles in the base role.
8 Click OK.
Reverting to an Original Report
If you have modified a report using the XML Schema and added it to
Chancery SMS, you can later choose to stop using the modified report and
start using the original report again.
When you revert to the original report, the modified report remains on the
Chancery SMS database server unless you remove it. The modified report is
not used in Chancery SMS, but it is available for later use.
Click Browse to
navigate to
modified report file.
Click Custom so that Chancery SMS uses the modified report, not the original one.
(Use report file appears only after you browse to a file.)
Addendum: XML Schema
Modifying the Permissions of a Report
Chancery SMS Report Writer Guide
78
1 In the control bar, under Admin, click School Setup.
2 On the School Setup page, under Reports, click “Customize Pre-4.2
School Reports”.
3 On the Customize District Reports page, in the “To modify a built-in
report” section, click School Report List.
4 On the School Report List page, select the report. From the Actions
menu, click Edit Custom Report.
OR
Click the report name.
5 On the Edit Report page, under Report File, set “Use report file” to
Built-In rather than Custom.
6 To remove the modified report from the Chancery SMS database server
so that it cannot be used again, click Clear.
7 Click OK.
Modifying the Permissions of a Report
1 In the control bar, under Admin, click School Setup.
2 On the School Setup page, under Reports, click “Customize Pre-4.2
School Reports”.
3 On the Customize District Reports page, in the “To modify a built-in
report” section, click School Report List.
4 On the School Report List page, select the report. From the Actions
menu, click Edit Custom Report.
OR
Click the report name.
Click Built-in so that Chancery SMS uses the
original report.
To delete the modified report
for the Chancery SMS
database server, click Clear.
Chancery SMS Report Writer Guide
Addendum: XML Schema
Deleting Custom Reports
79
5 On the Edit Report page, in the Permissions area, identify the base roles
and roles that can Delete, Edit, or View the report.
You can specify access for each base role or for the roles within it. Click
to display the roles in the base role.
6 Click OK.
Deleting Custom Reports
You can delete custom reports from Chancery SMS, assuming that your user
account has Delete permissions for that report.
Deleting a report means that it’s no longer available for users to run, and it is
no longer on the Chancery SMS database server.
1 In the control bar, under Admin, click School Setup.
2 On the School Setup page, under Reports, click “Customize Pre-4.2
School Reports”.
3 On the Customize District Reports page, in the “To modify a built-in
report” section, click School Report List.
4 On the School Report List page, select the report. From the Actions
menu, click Delete Custom Report.
Working with Crystal Reports
With Crystal Reports, you can create many types of reports with varying levels
of complexity.
Rather than repeating all of the information that is available in the Crystal
Reports Online Help, this section summarizes how to create and modify a
school report using the XML Schema.
Click to view the
roles in a base role.
Set the permissions
for a role.
Set the permissions
for a base role.
Addendum: XML Schema
Working with Crystal Reports
Chancery SMS Report Writer Guide
80
For more information, consider taking a Chancery training course on
reporting. Call us toll free at 1-800-999-9931 or see the Training area of our
Support web site at http://support.chancery.com for an up-to-date list of
scheduled courses.
Creating Custom Reports (XML Schema)
The recommended method for creating new reports is using the data access
views. However, the XML Schema is still supported, if you prefer.
You can create custom reports in Crystal Reports in different ways. Start by
using the Standard Report Wizard so that you get familiar with the steps
involved in creating a new report. Later, you can also choose to create a blank
report and add all of the fields manually, or you can use one of the other
wizards.
Note
Do not use the OLAP Wizard, which is suited to a different kind of database.
To illustrate the process, the following steps outline how to use the Standard
Report Wizard to create a sample report that:
• lists all students in a school
• provides the following details about each one: last name, first name,
birthdate, and gender
• groups the students based on gender
Step 1: Start a Report and Connect It to the XML Schema
1 Start Crystal Reports 9.
2 In the Welcome to Crystal Reports dialog, click “Using the Report
Wizard”, and then click OK.
OR
From the File menu, click New.
3 In the Crystal Reports Gallery dialog, under Choose a Wizard list, select
Standard, and click OK.
4 In the Standard Report Creation Wizard dialog, under Available Data
Sources, click next to Create New Connection, and then click next
to ADO.NET (XML).
Chancery SMS Report Writer Guide
Addendum: XML Schema
Working with Crystal Reports
81
5 In the ADO.NET (XML) dialog, click the browse button to the right of
the XML File Path field.
6 In the Open dialog, navigate to the location of the XML Schema file you
downloaded. Select the file and click Open.
7 Click Finish.
The Available Data Sources list now shows the database to which you are
connected.
Step 2: Select Tables with the Data You Want
1 Double-click the tables you want to use for the report.
For the sample report, select the following tables:
School
SchoolStudent
Use the ADO.NET (XML) data
source to create custom
reports using the XML
Schema.
The title and appearance of
this dialog depends on
whether you are working with
a blank report or one of the
wizards.
Addendum: XML Schema
Working with Crystal Reports
Chancery SMS Report Writer Guide
82
2 Click Next.
The dialog displays a Links tab that shows a diagram of the tables you
chose and the fields they share.
Step 3: Link the Views
Note
The links that Crystal Reports creates automatically are usually more complex
than most reports require. It is a good idea to get into the habit of clearing the
existing links and setting up your own links as required.
1
Click Clear Links. Click OK to confirm.
2 Arrange the tables so that you can work with them more easily:
• Resize the dialog so that you can see all of the tables.
• Resize each table so that you can see all of the fields in it.
3 Link the tables by dragging a field in one table to the field of the same
name in another table, depending on the type of information you want.
For the sample report, ensure the following links exist:
4 Click Next.
Drag this field To this field
SchoolID in School School.SchoolID in SchoolStudents
Chancery SMS Report Writer Guide
Addendum: XML Schema
Working with Crystal Reports
83
Step 4: Add Fields to the Report
1 On the Fields page, under Available Fields, expand the tables, and double-
click the fields that you want displayed on the report.
Under Fields To Display, the fields are shown in alphabetical order in the
format
TableName.FieldName.
For the sample report, from the SchoolStudent table, double-click the
following fields:
• BirthDate
• FirstName
• Gender
• LastName
2 Under Fields To Display, change the order of the fields by clicking a field
and then clicking the arrows to move it up or down.
In the final report, items in the Fields to Display list are displayed as
columns from left to right. The higher an item is in the Fields to Display
list, the further left it appears in the final report.
For the sample report, re-order the fields as follows:
3 Click Next.
Step 5: Group Information and Add Summaries
Grouping data in a report allows you to summarize your data in meaningful
ways.
1 On the Grouping page, under Available Fields, under Report Fields,
double-click the fields that you want to group data by.
SchoolStudent.LastName
SchoolStudent.FirstName
SchoolStudent.BirthDate
SchoolStudent.Gender
Addendum: XML Schema
Working with Crystal Reports
Chancery SMS Report Writer Guide
84
For the sample report, in the SchoolStudent table, double-click Gender.
2 Click Next.
3 On the Summaries page, under Available Fields, double-click any fields
you want to summarize. Under Summarized Fields, for each summary
indicated by , click the summary, and then select the summary type
from the dropdown under Summarized Fields. For the sample report, do
not use this feature.
4 Click Next.
5 On the Group Sorting page, sort items as required. For the sample report,
do not use this feature.
6 Click Next.
7 On the Chart page, add a chart if required. For the sample report, do not
use this feature.
8 Click Next.
Step 6: Add Filters
1 On the Record Selection page, under Available Fields, double-click a field
that you want to filter data on.In the dropdowns under the Filter Fields
area, select a comparison method, and then select a value (if required). For
the sample report, do not use this feature.
2 Click Finished.
Step 7: Complete the Formatting
1 Click the Design tab.
Chancery SMS Report Writer Guide
Addendum: XML Schema
Working with Crystal Reports
85
2 Format the report by:
• adding special fields, such as print date
• adding text objects, such as a title
• formatting text in fields and text objects
• moving, resizing, or deleting fields and text objects
For more information, see “Formatting” on page 39.
3 Save and close the report.
Modifying Custom School Reports (XML Schema)
If you have already created a custom report that you now wish to customize
further, you must first download the XML schema from the Customize
School Reports page in Chancery SMS.
1 Start Crystal Reports 9.
2 In the Welcome to Crystal Reports dialog, click “Open an Existing
Report”, and then click OK.
OR
From the File menu, click Open.
3 In the Open dialog, navigate to the report that you want to modify, and
click Open.
Addendum: XML Schema
Working with Crystal Reports
Chancery SMS Report Writer Guide
86
4 Do any of the following:
5 Save and close the report.
To Do this
Add a view 1 Click the Database Expert.
2 Under Available Data Sources, click next to Create
New Connection, and then click next to
ADO.NET (XML).
If ADO.NET (XML) is not listed, the Crystal Reports
DLL is not installed. See “Step 1: Preparation” on
page 73.
3 In the ADO.NET (XML) dialog, click the browse
button, navigate to the XML file you downloaded and
select it. Leave Use Visual Studio Data Class cleared.
Click Finish.
4 Under ADO.NET (XML), under the server name,
click next to the database name.
5 Click next to Tables.
6 Under Available Data Sources, double-click the
appropriate tables.
7 Click Next.
8 On the Link page, ensure the appropriate links exist. If
the links are incorrect or missing, click Clear Links,
and create the links by dragging a field from one table
to the corresponding field in the other table.
9 Click Next.
Add a field 1 In the Field Explorer window, click next to
Database Fields and open the appropriate table.
2 Click and drag the appropriate fields to the Design
window.
Group information and
add summaries
1 From the Report menu, click Group Expert.
2 On the Group Expert page, under Available Fields,
double-click the appropriate field.
3 Click OK.
Add Summaries 1 From the Insert menu, click Summary.
2 On the Insert Summary page, in the “Choose the field
to summarize” field, select the appropriate field.
3 In the Calculate this summary field, select the
summary type.
4 In the Summary location field, select the location for
the summary.
5 Click OK.
Add Filters 1 From the Report menu, click Select Expert.
2 On the Select Expert page, under Available Fields, in
the appropriate table, double-click the appropriate
field.
3 Select an operator from the dropdown under the Filter
Fields area, click the operator.
4 In the associated field, enter or select a value.
5 Click OK.
Change the formatting See “Formatting” on page 39.
I
NDEX
Index
87
A
aliased views
23
B
base object IDs
11
C
Chancery SMS filters
15, 46
Clear Links
27
connecting
25
connections
23
aliased views
23
directional links
23
intersection views
23
QUIDs
25
reference IDs
23
Crystal report filters
16
D
Data Access Layer
7
Data Access View Generator
8
data ownership
14
database
logical model
6
physical model
6
database models
5
database tables
54
date formats
39
deleting items
41
directional links
23
documentation, included
2
dynamic properties
9
dynamic properties filters
16
dynamic reference IDs
10
F
fields
adding to reports
29
sorting
29
filters
15
adding to reports
33
Chancery SMS filters
15, 46
Crystal Reports filters
16
dynamic properties
16
role-level security
15
G
getting training
3
grids
41
groups
adding to reports
30
I
inner joins
21
intersection views
9, 23
J
joins
21
inner
21
left outer
22
link types
22
right outer
22
L
left outer joins
22
link types
22
M
Mac School compatibility
5
Macintosh, customizing reports
5
margins
41
metadata
6
moving items
41
O
object hierarchy
14
OLE DB (ADO)
25
owner object IDs
11
P
page numbers
adding to reports
39
permissions
1
prerequisites
1
providing feedback
3
Q
Query ID connections
25
Query IDs
11
QUID connections
25
QUIDs
11
R
reference IDs
10, 23
report building help
17
report building rules
16
report extensibility
18, 43
report mapping
13
data ownership
14
object hierarchy
14
root view
13
Index
88
report validation 21, 45
reporting framework
7
resizing items
41
right outer joins
22
role-level security
15
root schema
13
root view
13
rules
16
S
snap to grid
41
software updates
4
special fields
39
subreports
adding to reports
34
summaries
adding to reports
30
support web site
4
T
technical support
4
text objects
adding to reports
40
formatting
40
revising
40
time formats
39
U
uploading reports
43, 44
V
validating reports
21, 45
views
8
W
Win School compatibility
5
X
XML schema
71
