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:

  1. Contact the CCCTC Enabling Services team to Request Access and initiate the implementation process.

  2. 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:

  1. 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.

  2. CCCTC Enabling Services has added and configured your account with the appropriate roles and attributes, and has sent your account credentials via secure method.

  3. 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).

  4. Non-SSO Users Only: You have received your temporary password from a CCCTC Enabling Services Administrator, installed the authentication software, and have completed theFirst-Time User Log Inprocess required to initiate two-factor authentication.

 

SSO Users: Account Setup & Log In Process

College & District Users

  1. The User coordinates with the CCCTC Enabling Services team to ensure their account is configured with the required roles, attributes, and IDP link.

  2. The User is provided the appropriate DWRS URL for their IDP and logs in.

  3. 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

  1. 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).

  2. The User downloads and installs compatible authentication software on their desktop or mobile device.

  3. 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.

  4. 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.

 

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.

To Install the Chrome Authenticator:

  1. Navigate to the Chrome Web Store and search for Authenticator by authenicator.cc

  2. Click on “Add to Chrome” button to initiate the download and follow very simple instructions to install.

  3. When the Authenticator app has been installed, a shortcut will appear in your Extensions toolbar in your browser.

 

 

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.

Google Authenticator is compatible with multiple different mobile devices, operating systems, and browsers. The process for downloading and installing can be found here.

 

 

 

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:

Step 1: Enter Your Account Credentials on the Log In Pages

  1. Ensure you have one of the compatible authenticator apps installed on your desktop or mobile device.

  2. Navigate to the Data Warehouse Report Server URL for non-SSO users (https://dw-reports.ccctechcenter.org/jasperserver-pro/login.html); land on the log in page.

  3. Enter your Username or Email Address (institutional email address) in the Username or email input field (.edu or .org is required) on the “Sign in to your account” screen. Click “Sign In” to proceed to the next screen.

  4. Enter the temporary Password provided to you by the CCCTC Enabling Services Administrator (sent separately via Privnote). Click the “Sign in” button to continue to the QR Code Scan screen.

 

 

 

Step 2: Scan the QR Code Page

The Scan the QR Code page is displayed once during the “first-time user log in” process. The user must have the authentication software installed on their desktop or mobile device in order to scan the QR code that appears on the page. The code is captured by the authenticator app which establishes the connection between the account (shared secret key hidden in the barcode) and the authenticator app to complete the initial two-factor authentication process.

  1. With your Authenticator App installed, scan the QR code that appears in the center of the page.

    1. Scanning the QR Code with Chrome Authenticator (from desktop).

    2. Scanning the QR Code with Google Authenticator (using a mobile device).

  2. As the authenticator scans the barcode (QR Code), a six-digit verification code will appear/register in your authenticator app.

  3. As soon as you see the six-digit code appear in your authenticator app, copy the code and then click “Submit” to go to the next page.

 

 

Using Chrome Authenticator to Scan the QR Code

  1. Once the Chrome Authenticator app has been installed, a shortcut will appear in your Extensions toolbar in your browser.

  2. Click the Authenticator icon in your Extensions toolbar to open the tool.

  3. Click the scan icon and move your mouse over the QR barcode in the center of the page.

  4. Click + hold the mouse to drag the scan functional across the bar code.

  5. The secret key hidden within the barcode will register as a six digit code in the Authenticator registered to your Report Server account.

Using Google Authenticator to Scan the QR Code

  1. Once the Google Authenticator app has been installed on your mobile device, an icon will appear on your phone wall or homepage.

  2. Click to open the Google Authenticator app

  3. Point the camera on your mobile device at the barcode on the screen and snap a picture.

  4. The secret key hidden within the barcode will register as a six digit code in the GA app registered to your DW Report Server account.

 

 

 

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

  1. Navigate to the Data Warehouse Report Server URL (https://dw-reports.ccctechcenter.org/jasperserver-pro/login.html).

  2. Enter your username or institution’s email address in the input field.

  3. Click the “Sign In” button.

 

Step 2: Enter Password

  1. Enter your password in the Password input field or click the Forgot Password? link to reset your password.

  2. Click the “Sign In” button.

 

 

 

 

 

 

 

 

 

 

 

Step 3: Enter Verification Code

  1. 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).

  2. Enter the verification code in to the “One-time code” field.

  3. Click the “Sign in” button to complete the process and access the DWRS.

 

 

 

 

Password Reset Process

To change or update your account password:

  1. Navigate to the Data Warehouse Report Server at https://dw-reports.ccctechcenter.org/jasperserver-pro/login.html.

  2. Click the “Forgot Password?” link which is available on every screen.

  1. Enter your email address that was used to create your account.

  2. 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 CCCTC Support

Contact your college/district IT Dept

District User

SSO User

SSO access via college/district IdP

Contact CCCTC Support

Contact your college/district IT Dept

Chancellor's Office User

Non-SSO User

Direct access (non-IdP) via two-factor authentication (2FA)

Contact CCCTC Support

Use the self-serve Forgot Password? process

CVCOEI User

Non-SSO User

Direct access (non-IdP) via two-factor authentication (2FA)

Contact CCCTC Support

Use the self-serve Forgot Password? process

CCCTC Administrator

Non-SSO User

Direct access (non-IdP) via two-factor authentication (2FA)

Contact CCCTC Support

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

  • click the Home icon from anywhere in the application to return to the Getting Started (Home) page

  • click the Library link to view all available reports, views, and content resources

  • click the View drop-down list to view Search Results, report Repositories, Schedules for scheduled report jobs, system Messages, or UI Samples for report design

  • click the Manage drop-down list is for managing Report Server access via organization, users, roles, and server settings; this option may only be available to CCC Tech Center staff

  • click the Create drop-down list to select short cuts to creating the items that also display in the Core Workflows section

     

On the right side of the header bar, you can:

  • click the Help link to view the JasperReports Server v7 online help

  • click Log out to safely sign out of the Report Server

  • enter text queries in the Search server field

Getting Started Column

  • view and access the most frequently used features in the server, including :

    • view and click items in the Recently Viewed Items and Popular Resources

    • link to Video Tutorials for each core workflow

Core Workflow Icons

  • multiple blocks that link to the core workflows, that may include some or all of the following options:

    • Ad Hoc Views – Select or create a visualization for basic reporting and analysis

    • Reports – Create an interactive report from an Ad Hoc view, or select an existing report

    • Dashboards – Combine related visualizations into a single-page layout, or select from existing layouts

    • Data Sources – Select or define a connection to a database or other data source

    • Domains– Add structure to your data source for use in a visualization

 

 

 

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…

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: RunEditOpenCopyCut (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:

  1. Log in to the Data Warehouse Report Server.

  2. Locate your report file in the Library or a folder in the Repository.

    1. Repository: Click View > Repository from the main menu.

    2. Library: Click Library link from the main menu.

  3. 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.

  4. 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:

  1. Log into the Report Server with your authorized user account.

  2. 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:

  1. On the Home page, click Create in the Reports block. The Create Report wizard opens.

  2. Select the Ad Hoc view you want to use as the basis for your report.

  3. 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.

  4. 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:

  1. Click the column heading of the data you want to filter and select the filter (funnel) icon from the context menu.

  2. The Filter Column dialog box appears.

  3. Select the “Show only rows where…” radio button and select the appropriate operator from the menu list.

  4. 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.

 

Back to Top


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:

  1. Select Create > Ad Hoc View from the main menu.

  2. 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:

  1. Click the chart icon in the tool bar in the Ad Hoc Editor to display the “Select Visualization Type” dialog box.

  2. Click on the Table icon - or your preferred type - to highlight your selection.

  3. Click the “Apply and Close” button to save your change.

 

Back to Top

 

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…

Do this…

To add fields and measures as columns in your table-type ad hoc View

  1. In the Data Source Selection panel, click to select the field or measure you want to add to the table. Use Ctrl-click to select multiple items.

  2. Drag the selected item into the Columns box in the Layout band row.

  3. The field is now added to the View as a column in the table.

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…

To…

Do this…

Create a group

  1. In the Data Source Selection panel, click to select the field you want to add to the table as a group.

  2. Drag the field to the Groups box in the Layout Band.

  3. The Ad Hoc view refreshes and displays the data grouped under a new header row.

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…

Do this….

To add a summary to a specific column

  1. In the table, right-click the column you want to calculate a summary for, and select Add Summary.

  2. The summary total is added to the bottom of that data column, or in the heading of a group of rows if a group has been included in the table

To remove a summary from a specific column:

  1. In the table, right-click the column with the summary you want to remove, and select Remove Summary.

  2. The summary information is removed from the table.


Column and Header Labels

You can edit a column or header label directly in the Ad Hoc Editor.

To…

Do this…

To…

Do this…

Edit a column or header label

  1. On the Ad Hoc view panel, right-click the column or group header you want to rename.

  2. Select Edit Label from the context menu. The Edit Label window opens.

  3. In the text entry box, delete the existing name and enter the new name.

  4. Click Submit.

Delete a column or header label:

  1. On the Ad Hoc view, right-click the column or header label you want to remove.

  2. Select Delete Label from the context menu.

Re-apply a label:

  1. Right-click the column or header label you want to replace.

  2. Select Add Label from the context menu. The Edit Label window opens.

  3. Enter the label name, if needed.

  4. Click Submit.

 

Adding a Title

You can add and edit a title to your ad hoc view using the following steps:

  1. Above the table, click the text Click to add a title.

  2. 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:

  1. In the Ad Hoc view, right-click the column header.

  2. Select Change Data Format from the context menu.

  3. 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:

  1. At the top of the Data Source Selection panel, click  and select Change Source.

  2. Select a different Topic, Domain, or OLAP connection.

  3. 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