Data Warehouse Report Server User Guide
This guide is for the Data Warehouse Report Server (CCC Data 2.5.0) released to production on August 16, 2023.
In This User Guide
Note: All hyperlinks in this document open in the same window.
Part 1: Overview
The information provided in this document serves as a "Quick Start" guide for users of the CCC Data Warehouse Report Server (“DW Report Server”, “Report Server”, “DWRS”), including:
Getting started with the DW Report Server; requesting authorized access and implementation support
User account set up and authentication processes
Running preconfigured reports that pull data from the CCC Data Warehouse (currently limited to the LGBTQ report template)
Querying available data sources, running ad hoc reports, scheduling and exporting results
Creating custom Ad Hoc Views from which multiple reports can be run based on the following data sources:
CCCApply Standard & Noncredit Applications
CCCApply LGBTQ Data (AB 620)
CCCApply International Application
California Colleges Promise Grant Application
Multiple Measures Placement Service (MMPS)
COCI - Chancellor’s Office Curriculum Inventory
C-ID - Course Identification System
Note: If you are interested in direct (ODBC/JDBC) access to the CCC Data Warehouse via a site-to-site VPN, please contact your Enabling Services College Relationship Manager (CRM) to initiate the request for this access.
About the DW Report Server
The DW Report Server is a secure, user-friendly reporting tool that creates pixel-perfect reports, charts & dashboards using secure CCC Data Warehouse data.
The CCC Data Warehouse is a structured source of master data that can be used to generate the data marts and reports and analytics that end-users need.
The DW Report Server is powered by JasperReports, a Java-based open-source reporting tool. JasperReports, connected to the CCC Data Warehouse, allows for advanced reporting usage and provides extensive help that you can access from the Help link.
User Authorization & Authentication
Access to the DW Report Server is restricted to authorized users to ensure the privacy and security of the data within the CCC Data Warehouse. Users must request access via one of the appropriate processes below and upon approval will be granted an account based on their user type.
Requesting Access
College and District Institutional Researchers: An identified lead IRPE Researcher at each college or district will be established as the authorized user and “gatekeeper” for their institution (organized by MIS Code) upon confirmation from their college or district Vice President. These “gatekeepers” can then request access for additional researchers at their institution by submitting an email request to ES Support Services or by contacting their College Relationship Manager (CRM) with the information below.
Chancellor's Office & Other External Stakeholders: All requests for access to the Data Warehouse Report Server from the CCC Chancellor's Office or other external stakeholder organization are to be directed to the CCC Data Warehouse Product Manager (Mark Cohen mcohen@ccctechcenter.org) who must obtain approval from the Vice Chancellor for Digital Innovation & Infrastructure at the CCC Chancellor’s Office.
Support & FAQs
Support is available to authorized Report Server users in our 24/7 online support community CCCTechnology.info - where college and district staff can share ideas, ask questions, and report bugs and issues for all the CCC Technology Center online applications.
Our experienced support engineers monitor all incoming posts and comments, Monday - Friday, 7:00am - 4:00pm PST. Depending on the nature of the issue or comment, support engineers will respond within 24 hours to critical issues regarding production applications.
Part 2: User Accounts & Access
This section includes:
Getting Started
Establishing an authorized DWRS user account is a two-part workflow between a CCCTC Administrator and the approved user. An Enabling Services Implementation Configuration Engineer (ICE) will create and configure the account in our internal system and then work with the user to complete the final steps and log in, depending on the user type. This section of the document outlines the roles and responsibilities - and setup tasks - for the CCCTC Administrator and the approved DWRS user.
The first steps in getting started are:
Contact the CCCTC Enabling Services team to Request Access and initiate the implementation process.
Schedule a call with a CCCTC Administrator to facilitate implementation & configuration services.
User Accounts
There are two types of authorized user accounts:
SSO Users: Colleges and Districts using their institution’s IDP for single sign-on. (CCCTC does not manage your institution’s two-factor authentication (2FA) mechanism, if implemented.)
Non-SSO Users: CCCCO, CVCOEI, and other authorized external users will authenticate directly to the DWRS using 2FA to secure and protect our systemwide student data.
User Types & Access Levels
User Type | Authentication | Authorized Access Level | |
College User | SSO User | SSO access via college/district IdP | Single college access to available data sets based on College MIS Code |
District User | SSO User | SSO access via college/district IdP | Multi-college access to available data sets based on District MIS Code |
Chancellor's Office User | Non-SSO User | Direct access (non-IdP) via two-factor authentication (2FA) | Access to all data sets across all colleges |
CVCOEI User | Non-SSO User | Direct access (non-IdP) via two-factor authentication (2FA) | Access to CVCOEI report but no actual data (can see tables and columns in AdHoc view, but no data populates) |
CCCTC Administrator | Non-SSO User | Direct access (non-IdP) via two-factor authentication (2FA) | Access to the internal system administration console to facilitate account creation & configuration. |
Set Up & Log In Processes
The process for account setup and log in will be different depending on user type, access level, and the mechanism used to authenticate. Working with the CCCTC Enabling Services team to set up and configure your account, college & district users (SSO Users) are provided a custom URL configured to your institution MIS code and internal IdP link. Non-SSO Users will be directed to the DWRS URL to complete additional setup & verification steps to facilitate authentication.
Prerequisites for All Users
Before a user can log in to the DWRS the following conditions must be met:
The authorized user has followed the Getting Started tips above and has coordinated with CCCTC Enabling Services to begin the account setup and configuration process based on user type and access level.
CCCTC Enabling Services has added and configured your account with the appropriate roles and attributes, and has sent your account credentials via secure method.
SSO Users Only: You have bookmarked your unique college or district DWRS URL and know your institution’s IdP single sign-on credentials (managed locally by your college or district IT department).
Non-SSO Users Only: You have received your temporary password from a CCCTC Enabling Services Administrator, installed the authentication software, and have completed the “First-Time User Log In” process required to initiate two-factor authentication.
SSO Users: Account Setup & Log In Process
College & District Users
The User coordinates with the CCCTC Enabling Services team to ensure their account is configured with the required roles, attributes, and IDP link.
The User is provided the appropriate DWRS URL for their IDP and logs in.
The User is authenticated locally through their institution’s IDP (SSO) and logs in to the DWRS.
Non-SSO Users: Account Setup & Log In Process
All Other Users
The User coordinates with the CCCTC Enabling Services team to get their temporary password and first-time log in information via secure method (i.e., Privnote).
The User downloads and installs compatible authentication software on their desktop or mobile device.
The User completes the “First-time User Log In” process, which enables entry of account credentials, one-time QR code verification, and authentication to the DWRS.
Optional: After logging in, the User may update their temporary password using the Forgot Password? link to reset their password.
Receiving Your Secure Account Credentials
For security purposes, non-SSO users will receive account credentials and a temporary password in one or two separate emails from an CCCTC Enabling Services Administrator. The email(s) will include a link to a secure Privnote message sent to the user’s institution email address (.edu).
Implementing the Authentication Software
All Non-SSO users are required to use a compatible authentication app to log in to the Data Warehouse Report Server. Prior to the first log in, logging in to the DWRS for the first time, users must have a compatible authentication app installed on their desktop or mobile device that facilitates Time-based One-Time Password TOTP functionality such as Google Authenticator, Chrome Authenticator, or Authy (which you could download to a phone or computer). Below are several examples of compatible authentication software apps that can be used; however there are many free, user-friendly Authenticators available at no cost that can be downloaded and installed quickly and easily to your desktop and/or mobile device.
Chrome Authenticator for Desktop
Authenticator for Mac and Windows Chrome browsers is available free from the Chrome Web Store. This app installs very quickly to your desktop (recommended) as well as your phone, and includes a lot of great features that promote security and usability. NOTE: The entire download and install process takes less than one minute. You may need local IT authorization and assistance to install it if policy requires this.
Google Authenticator App for Mobile Device
Another user-friendly option, especially for users who want to use their mobile device for authentication, is the Google Authenticator; a free app available from the Google Play Store.
The First-Time User Log In Process
Non-SSO Users Only: The first time a new non-SSO user attempts to log in to the DWRS they must establish the connection between their user account credentials, a shared secret key (QR Code), and the authentication software to enable the two-factor (2FA) log in authentication.
Prerequisites:
The User has the DWRS direct access URL: (https://dw-reports.ccctechcenter.org/jasperserver-pro/login.html).
The User has received their account credentials from a CCCTC Enabling Services Administrator.
The User has installed and configured compatible authentication software on their desktop and/or mobile device.
Verified User Log In Process
For Non-SSO Users Only: After the user completes the one-time “First-Time User Log In” process, the user is “verified” and will not have to repeat the Scan the QR Code Page step again UNLESS they need to re-install their authentication software and/or generate a new QR code. For all subsequent s, the verified user needs only to enter their account credentials and the verification code retrieved from their authenticator app.
Step 1: Enter Username or Email
Navigate to the Data Warehouse Report Server URL (https://dw-reports.ccctechcenter.org/jasperserver-pro/login.html).
Enter your username or institution’s email address in the input field.
Click the “Sign In” button.
Step 2: Enter Password
Enter your password in the Password input field or click the Forgot Password? link to reset your password.
Click the “Sign In” button.
Step 3: Enter Verification Code
Immediately upon landing on the “One-time code” screen, copy the verification code from your Authenticator app within 30 seconds of making the request (after entering password).
Enter the verification code in to the “One-time code” field.
Click the “Sign in” button to complete the process and access the DWRS.
Password Reset Process
To change or update your account password:
Navigate to the Data Warehouse Report Server at https://dw-reports.ccctechcenter.org/jasperserver-pro/login.html.
Click the “Forgot Password?” link which is available on every screen.
Enter your email address that was used to create your account.
Click Submit.
A confirmation message appears on the screen directing you to retrieve an email.
4. Open the Reset Password email, click the embedded link to go to the Update Password page.
Enter your new password in the New Password box. Confirm your new password, then click Submit to return to the Sign In page.
Email Address Update Process
DWRS user accounts require a unique email address that identifies the user in the CCCTC secure identity management system. To change or update the email address associated with your account, please contact the CCCTC Support team to update the email address manually in the system. With verbal confirmation that the change has been made, the user should return to the Data Warehouse Report Server Sign In page to repeat the sign in process.
User Type | Authentication | Update Email Address | Update Password | |
College User | SSO User | SSO access via college/district IdP | Contact your college/district IT Dept | |
District User | SSO User | SSO access via college/district IdP | Contact your college/district IT Dept | |
Chancellor's Office User | Non-SSO User | Direct access (non-IdP) via two-factor authentication (2FA) | Use the self-serve Forgot Password? process | |
CVCOEI User | Non-SSO User | Direct access (non-IdP) via two-factor authentication (2FA) | Use the self-serve Forgot Password? process | |
CCCTC Administrator | Non-SSO User | Direct access (non-IdP) via two-factor authentication (2FA) | Use the self-serve Forgot Password? process |
Appendix A: Data Warehouse Report Server URLs
Part 3: Using the Report Server
The CCC Data Warehouse Report Server is a branded version of TIBCO JasperReports (v.7.8.0), with role-based access to integrated CCC data for authorized CCC researchers using multi-factor authentication.
In this section:
Benefits of the Report Server
Centrally-managed reporting server for CCC Data Warehouse objects
Role based access permissions to all data objects/domains by organization MIS code
Create custom ad hoc reports and charts using filters, groups and measures
Schedule interactive reports and charts to be delivered to one or more email addresses and/or output to external network location
Export custom or public reports and charts in a variety of output formats
Schedule reports or charts to run daily, weekly, or monthly using relative date filter - and save/attach in a variety of output formats
Ability to make on the fly modifications to ad hoc “Views” for real-time data analysis.
Introduction to the Report Server
The Getting Started Page
The first thing that displays when you sign in to the Report Server is the Getting Started (Home) page. From the Getting Started (Home) page, you can quickly access the most frequently used features of the server.
The Getting Started (Home) page displays the following items:
Getting Started Page Sections | Allows you to: |
Header bar Menu Items |
On the right side of the header bar, you can:
|
Getting Started Column |
|
Core Workflow Icons |
|
The Library Page
The Library page offers a more focused view of the Repository objects. It displays only the data and resources that the user is authorized to view and work with.
Click Library to view your Library list, allowing you to:
run and schedule reports
open ad hoc Views and generate Reports from them
run and edit dashboards
run OLAP Views
All of these functions are available by right-clicking the item you want to work with and selecting an action from the context menu.
The Repository Page
The Repository is the server’s internal storage for folders, reports, analysis views, and related files. The Repository is organized as a structure of folders containing public and private resources and templates, as well as Organization folders, much like a file system.
To… | You… | Allows you to… |
---|---|---|
Display the Repository page | select View > Repository from the header menu (from any page) | view and access the contents you are authorized to view in the Repository. |
Browse the Repository | click to expand a folder name (Public or Private folder) in the Folders panel | view and access the reports, themes, and other content files stored on the server that you have permission to view. |
Search the entire Repository | select View > Search Results from the header men (from any page) | display the Search Results page and search the entire Repository using intuitive search criteria, such as who modified a resource and when, to find pinpoint resources. |
List of resources in the Repository in a district organization's folder.
Repository Folders
The file system in the Repository has a tree structure which displays folders containing the resources that you have permission to view and access. Contents can be displayed by expanding the available folder icons in the Folders panel. There are two primary types of folders displayed to users: Public and Organization folders.
Public Folders
The Public Folder that sits at the root of the Repository contains public template reports, views and dashboards that each college can run based on their college data. As template reports and views are added, each data set, such as CCCApply Standard applications, International applications, etc. will have a separate folder set containing ready-to-run reports.
Organization Folders (Colleges / Districts)
Each CCC college organization has a college folder nested under its district folder. Organization folders are used for saving and organizing your ad hoc Views, reports, and other content resources.
Repository: The Folder Panel tree showing various Organizations and Public folders.
Using Repository Resources
After finding a resource in the Repository, naturally you want to do something with it. Your options are:
Click the name of a report to run and view it.
Right-click the name of a resource to access other operations on the context menu, for example Edit or Open. Items appear on the context menu according to your permissions.
Click anywhere in the row except the resource name to select a resource. Ctrl-click anywhere in the rows to select multiple resources. Use the context menu or buttons above the results list: Run, Edit, Open, Copy, Cut (move), or Delete. If the button is unavailable, the resource doesn’t support the operation or you don’t have permission for the operation. For example, the Open button is available when you select a dashboard or an Ad Hoc report if you have permission to write to it.
You might also need permission to access the folder or dependent file, such as an image, of a resource. For example, to schedule a report, you need to have read/write/delete permission on the folder where server saves the report output. For more information about permissions, see the JasperReports Server Administrator Guide.
Searching the Repository
You can search the entire Repository, subject to your permissions, or narrow your search using filters. The Filters provided for searching the Repository restrict a search by name, who changed the resource, type of resource, date of the resource, and schedule.
To search the entire Repository, select View > Search Results from the header menu. The Search Results page appears with the Filters panel on the left side.
The Search Results Page
The Search Results page displays results of searches and filters.
On the Search Results page, use either the Filters panel or the Search field to find resources.
Select one of these filters: All available, Modified by me, or Viewed by me.
Click the X icon in the search field to clear the search term if there is one.
The search results will display a list of files that your user account has permission to view.
Double-click a resource in the list to view it, or right-click a resource to see what functions are available from the context menu.
Running Reports & The Report Viewer
The Report Server makes it easy to run reports. When you run a report, it opens in the interactive Report Viewer. With the Viewer, you can personalize and refine the displayed report data. If the report has input controls (filters), you run the report with one set of data and then another.
In this section:
Overview of the Report Viewer
Running Reports
Formatting Options in a Table Report
Saving & Exporting a Report
Overview of the Report Viewer
The Report Viewer allows you to view a report, export content to various output formats, and apply formatting, sorting, and filters to control how the data is displayed. This section describes the functions available in the Report Viewer.
Running a Report
To open and run a report in the Report Viewer:
Log in to the Data Warehouse Report Server.
Locate your report file in the Library or a folder in the Repository.
Repository: Click View > Repository from the main menu.
Library: Click Library link from the main menu.
Click the report name and then click the Run button in the tool bar. Alternately, right-click the report name and select Run from the menu.
The report will open in the Report Viewer.
Running or Creating a Simple Report
You can view and work on a report in the Report Viewer in a number of ways:
Running an instance of an existing report
Creating a new report from an existing Ad Hoc view
Running a Simple Report
This section describes how to run a tabular report that lists account data.
To run a report:
Log into the Report Server with your authorized user account.
On the Home page, click View list in the Reports block.
The search results appear, listing your own files and other files that your user account has permission to view.
To run a report, click the name of a report in the repository. For example, click DW LGBTQ Report. The report appears in the Report Viewer.
Output of the DW LGBTQ Report.
Creating a Report
You can create a report directly from the Report Server Home page. This method allows you to select an existing Ad Hoc view and generate a report from it, without going through the Ad Hoc Editor.
To create a report from the Home page:
On the Home page, click Create in the Reports block. The Create Report wizard opens.
Select the Ad Hoc view you want to use as the basis for your report.
Select a report template. To use a template other than the default, select Custom Report Template, click Browse and select the desired template. See Report Templates for more information.
Click OK. If asked, enter the input controls needed. See Using Input Controls.
You can now begin working with your report.
Report Templates
When you create a report, the Create Report wizard displays layout options for generating and exporting the report:
Default Report Template applies basic layout options to your report. This is usually the Actual Size template.
Custom Report Template allows you to browse to an existing template. JasperReports Server includes a number of templates are available by default, including:
A4 Landscape
A4 Portrait
Actual Size
Letter Landscape
Letter Portrait
Report Generator allows you to create a highly customized report design. This option is not often enabled. See your JasperReports Server administrator for more information.
Most commonly, you will choose Default Report Template.
Using Report Templates for PDF
If you are exporting your report to PDF, choose your option based on the size of the output.
For most PDF exports, you can use Actual Size, which supports a maximum size of 14400px by 14400px.
For reports with an output height exceeding 14400 px, use a paginated report template that is wide enough for your report. For example, if you have a long report with width less than 842px, you can use the paginated A4 Landscape theme.
Reports with output width exceeding 14400 px will be truncated in PDF. Redesign your report or use a different export format.
Navigating a Report
If your report has multiple pages, you can use the pagination controls to move through the report quickly.
To navigate the published report:
Use at the top of the Report Viewer to navigate to the previous page.
Use to navigate to the next page.
Use to go to the end of the report.
Use to go to the beginning of the report.
If you know the number of the page you want to view, enter the page number in the Current Page indicator box.
Formatting Options in a Table Report
Reports that contain table components are enabled for user interactivity. Table components are defined in Jaspersoft Studio or from Ad Hoc Views. When a table is enabled for interactivity, column formatting, filtering, and sorting are managed from a menu displayed by clicking the column you want to apply changes to.
To use column formatting, click on the column heading to display the icons described above.
Using Column Filters
To restrict the data that appears in your report, a column filter can be applied limiting the results displayed based on the data type.
To add a column filter:
Click the column heading of the data you want to filter and select the filter (funnel) icon from the context menu.
The Filter Column dialog box appears.
Select the “Show only rows where…” radio button and select the appropriate operator from the menu list.
Click OK to see your report results based on the applied filter.
To remove the column filter:
Repeat the actions to add a column filter, until the Filter Column dialog box appears.
Click the “Show all rows” radio button.
Click OK.
Saving & Exporting a Report
You can save a report or export a report in a variety of file formats, including PDF, Excel, CSV, DOCX, RTF, XLS, and more.
To save or export your report, use the actions in the table below.
Click the... | To... |
Save icon and select Save | save the report to the default DW Reports folder |
Save icon and select Save As | display the Save As dialog box where you can select a different folder to save your report: |
Export icon | select an export format option to download the report to your computer's default download location. |
Below is a screen image of a report exported in PDF format.
Running a Report with Input Controls or Filters
Some reports will display automatically in the Report Viewer, while others may prompt you for input parameters to refine your report results first via the Input Controls dialog box.
An input control are graphical widgets that filter the data that appears in a report. The perfect input control limits the data to what you want to see—and nothing more. When you run a report based on a Domain Topic that defines a filter, the server can render the filter as an input control. The Data Warehouse Report Server interface uses "input controls," "filters," and "options" interchangeably.
Default input controls are defined when the original Ad Hoc View-based report is run; thus will overwrite any changes made to them each time the report is run. For instance, suppose you run a report, update the input controls, then save the report. At a later date, you run the report from the Ad Hoc View source again. That new report will replace the report you ran earlier, and your input control changes will be lost.
To avoid this, save a version of the report with your selected data preloaded. That way, when subsequent reports are run from the same source, they will not overwrite your report.
Simple Input Controls
Using input controls, you run a report with one set of data and then another. When saved, an instance of the report with alternate input controls is called a Report Version, and is labeled as such in the repository.
Multi-Select Input Controls
A single report can be created and run with multiple different input controls, allowing for a greater level of data granularity.
Saving Input Control Values
Selected input control values can be saved and used at another time. Both the original report and a copy of it can be saved. The Report Server saves a version of the report with the selected values as a child of the original report. This new version of the report appears as a child of the original report in the repository,
Scheduling Reports
You can schedule a report to run at a specific date and time, including a recurring time frame (every Friday at 9 am. for example). The image below is an example of how you begin this process.
When you right-click a report Name, a pop-up menu displays and you can click the Schedule... option to open a Scheduled Jobs window that allows you to configure your scheduled report.
Part 4: Creating Custom Ad Hoc Views & Reports
This section explains the process of creating ad hoc Views and custom reports using CCC Data Warehouse data.
Working with the Ad Hoc Editor
The Ad Hoc Editor is the interactive designer for creating and editing an ad hoc View, where you can explore and analyze data from a topic, domain, or other data source. Ad hoc Views are used to create content for various types of Reports, including tables, crosstabs, and charts. After you create an ad hoc View, you - and any other authorized users with the proper permissions - can save it as a Report, then further refine the displayed information and personalize the look of the report in the Report Viewer.
Getting to the Ad Hoc Editor
The Ad Hoc Editor is the interactive design interface used for creating and editing Ad Hoc Views. There are two ways to access the Ad Hoc Editor:
Select Create > Ad Hoc View from the main menu.
Open an existing Ad Hoc View.
Either of these actions will launch the Ad Hoc Editor.
Layout Panels
The Ad Hoc Editor contains the following panels, from left to right:
Data Source Selection panel contains the data fields, dimensions, and measures available in the source Domain, Topic, or OLAP connection.
Ad Hoc View panel is the central design panel.
Filters panel (optional) defines a subset of data to retrieve from the data source.
The image above shows the Ad Hoc Editor user interface and the data fields, filters, and groups selected for a table report format.
The Data Source Selection Panel
The Data Source Selection panel (left column) contains the folders of available fields in the Topic or Domain chosen by the user to build the report. If you are using a Domain, fields may appear in nested sets. Available data fields may be divided into two sections in the panel, Fields and Measures. You can use the search field in each section to locate a specific field or measure.
To hide this panel, click < in the top left corner; this is helpful when arranging content in a large Ad Hoc view. Click the same icon on the minimized panel to expand it.
The Ad Hoc View Panel
The Ad Hoc View panel (center column) is where your custom report View structure displays as you drag data source items into the Columns and Rows fields. Other tools you can use here include the Ad Hoc Editor Tool bar, the Layout Band (columns and rows fields), and the Canvas Options selector (gears) icon.
The Filters Panel
The Filters panel (right column) displays any fields you select to be used as filters of your report data, and allows you to refine the definition of those filters.
When a filter is added to a new ad hoc View, the Filters panel will display (right column) and the user has options to set the input controls for that filter. The operator can be specified for each filter, based on the type of data field.
Ad Hoc Sources: Topics & Domains
The following repository objects provide a prepared connection to a data source for Ad Hoc view creation:
Topics: JRMXL files created externally and uploaded to JasperReports Server as a basis for Ad Hoc views.
Domains: Virtual views of a data source that present the data in business terms, allow for localization, and provide data-level security.
You can also open and edit an existing Ad Hoc view to create a new Ad Hoc view.
Ad Hoc View Types
The design and content tasks for creating a new report are determined by the Ad Hoc View Type that is selected. By default, all new ad hoc Views are set to the Crosstab format type.
The Ad Hoc Editor allows you to select from three different view types:
Table: Used to view detailed values in the database and to summarize the values in columns.
Crosstab: Used to aggregate data across multiple dimensions.
Chart: Used to compare one or more measures across multiple sets of related fields.
Changing the Ad Hoc View Type
The format of your ad hoc View type can be changed at any time while in the Ad Hoc Editor.
Steps to change from a Crosstab to a Table format:
Click the chart icon in the tool bar in the Ad Hoc Editor to display the “Select Visualization Type” dialog box.
Click on the Table icon - or your preferred type - to highlight your selection.
Click the “Apply and Close” button to save your change.
Working with Tables
This section explains how to populate, edit, and format your table type report view, including:
Using Data Fields in Tables
Using Groups
Adding Summaries
Column & Header Labels
Adding a Title
Using Data Fields in Tables
Insert data into your table-type View by adding data fields. All available fields are listed in the Data Source Selection panel (left column) of the Ad Hoc Editor. The available fields are divided into two sections in the panel:
Fields, which can be added to the table as columns or groups.
Measures, which are specialized fields that contain data values.
To… | Do this… |
---|---|
To add fields and measures as columns in your table-type ad hoc View |
|
To remove a field or measure from a table View: | In the Layout Band, click the x next to the field or measure’s name. |
To re-order the fields (columns) in your table View: | In the Columns row in the Layout Band, click and hold a field tab and drag it right or left to a new position |
Using Groups
The Group feature allows you to display detailed groups of data rows in a table View. For example, if you want to create a table View/Report that will list all the CCCApply applications submitted to your college separated by the three residency status values, you would add a group based on the “Residency Status” field - even if the field is not currently being used as a column in your table View. The applications would then be displayed as table rows and grouped into the corresponding residency status values.
The Group feature can be used with any data field included in the data source (domain or topic) selected when creating your new ad hoc View.
You can also use multiple fields to make more specific nested groups. For example, by adding a group based on the applicant’s “State” field to the table described above, the applications would be arranged by State within the residency status groups. Under the “Residency Status = 3” group header row, new header rows would appear for the different states (i.e., Alabama, Alaska, Arizona, Arkansas, and so on) with the applications submitted by students from each state appearing under their respective state names. All groups, as well as data field columns, can be sorted ascending or descending at any time - relative to their respective groupings.
Only fields can be applied to a table as a group; measures cannot be used.
Data is grouped in the table according to the order they have defined. You can change the order by dragging the groups into position if needed.
To… | Do this… |
---|---|
Create a group |
|
Remove a group | In the Layout Band, click the x next to the field’s name in the Groups box. |
Move a grouping order up or down in a table | In the Layout Band, drag the name of the group you want to move into its new position. |
Displaying Summaries
You can display summary data for any column in your table. Summary data may be in the form of various functions, such as:
Sum
Count
Distinct Count
Average
For example, in a table with a list of applications grouped by Residency status and State, you can display the number of applications submitted from each state for each residency status group, using the Summary function.
To… | Do this…. |
---|---|
To add a summary to a specific column |
|
To remove a summary from a specific column: |
|
Column and Header Labels
You can edit a column or header label directly in the Ad Hoc Editor.
To… | Do this… |
---|---|
Edit a column or header label |
|
Delete a column or header label: |
|
Re-apply a label: |
|
Adding a Title
You can add and edit a title to your ad hoc view using the following steps:
Above the table, click the text Click to add a title.
Enter the new table title in the text entry box.
Changing the Data Format
You can change the formatting for columns containing numeric data, such as dates and monetary amounts. The format is applied to all rows as well as the group- and view-level summaries. By default, non-integer fields use the -1,234.56 data format; integers use -1234.
To change the data format for a column:
In the Ad Hoc view, right-click the column header.
Select Change Data Format from the context menu.
Select the format you want to use. These options vary, depending on the type of numeric data contained in the column.
The data in the column now appears in the new format.
Changing the Data Source
You may need to select a new data source for your table. This is a simple task, but you should keep in mind that all view data and formatting are lost when you select a new Topic, Domain, or OLAP connection. Any changes to the view are also lost if you navigate to another page using the browser navigation buttons, the main menu, or the Search field. To preserve changes, accept the current Topic or click Cancel.
To change the table’s data source:
At the top of the Data Source Selection panel, click and select Change Source.
Select a different Topic, Domain, or OLAP connection.
Click Table to apply the new data source.
OR Click Cancel to return to the editor without changing the Topic.
Controlling the Data Set
You can control the data displayed in the table using the Canvas Options selector. Click to select the option you want to apply to your table.
Your options are:
Detailed Data displays table detail only. For instance, in a table listing CCCApply applications submitted to all colleges in a district for a given month, the total apps submitted to each college that month is displayed.
Totals Data displays the table totals only. In the table described above, only the total number of applications submitted that month is displayed.
Details and Totals displays both the individual applications submitted, as well as the totals at the bottom of the columns.
Show/Hide Duplicate Rows displays only the distinct values in your table if you choose to hide the duplicate rows.
Showing Distinct Values
Tables sometimes contain duplicate values in multiple rows, making it difficult to find relevant data. You can choose to show only distinct values in your tables by choosing to hide duplicate rows, making the table shorter and easier to read. To show