Data Warehouse Report Server - User Guide

CCC Data Warehouse Report Server v7.8 Last Update: May 2021

In This User Guide

 

Part 1: Overview

The information provided in this document serves as a "Quick Start" guide for the CCC Data Warehouse Report Server (“DW Report Server”). With the DW Report Server you can:

  • Run preconfigured reports that pull data from the CCC Data Warehouse, currently limited to the LGBTQ report.

  • Query available data sources, run ad-hoc reports, and download results.

  • Create an Ad Hoc View from which you can create Reports 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.

Access to Canvas Data is currently only available by direct access connection. For information regarding the Canvas DW Direct Connect service, please contact your Enabling Services College Relationship Manager (CRM) to initiate the request for this access.

 

About the Data Warehouse 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 Jasper Reports, a Java-based open-source reporting tool. Jasper Reports, connected to the CCC Data Warehouse, allows for advanced reporting usage and provides extensive help that you can access from the Help link.

About TIBCO JasperReports Server
TIBCO JasperReports Server is a comprehensive Business Intelligence (BI) tool that provides robust static and interactive reporting, report server, and data analysis capabilities. A comprehensive Help system is available and can be accessed from any screen in the upper right toolbar. The Help system provides high-level descriptions of feature functionality, and step-by-step instructions for how to extract the data and insights you need from your data.

 

User Authorization & Authentication

The new Data Warehouse Report Server (CCC Data release v.2.1) requires an authorized user account with two-factor authentication to ensure the privacy and security of the Data Warehouse Report Server.

Requesting Access to the Report Server

College and District Institutional Researchers: The identified lead IRPE Researcher at each college or district will be established as the authorized user and “gatekeeper” for their organization (organized by MIS Code) upon confirmation from their college or district Vice President. These “gatekeepers” can then request access for additional researchers at their organization by submitting an email request to ES Support Services or contacting their College Relationship Manager (CRM) with the information below.

Process for Gatekeepers to Request Additional Users
Authorized gatekeepers may submit an email request with the new user’s name and email address (.edu) to ES Support Services at staffsupportccctc@openccc.zendesk.com.

Chancellors Office & Other External Stakeholders: All requests for access to the Data Warehouse Report Server from the Chancellors Office or other external stakeholder, are to be directed to Mark Cohen, Product Manager, CCC Data, mcohen@ccctechcenter.org who will 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 Account Setup & Login Process

This part of the User Guide provides information about:

 

The Report Server User Account

The new Report Server User Account is designed with two-factor authentication (2FA) to secure and protect our college and systemwide student data. Two-Factor Authentication is a security system that authenticates a user’s identify by requiring two or more pieces of evidence (credentials + verification code) before granting access to a service or system.

IMPORTANT: Beginning with the production launch of DW Report Server (CCC Data v.2.1), all new and existing authorized DW Report Server users are required to set up new user accounts with two-factor authorization in order to gain access to data through the DW Report Server. All previous accounts have been de-provisioned and removed. For more information, please contact your Enabling Services College Relationship Manager (CRM) or contact Support Services.

 

User Types & Access Levels

Authorized access correspond directly to the user’s college or district 3-digit MIS Code, as shown in the table below.

User Type

Authorized Access Level

College User

Single college access to available data sets based on College MIS Code

District User

Multi-college access to available data sets based on District MIS Code

Chancellor's Office User

Access to all data sets across all colleges

 

 

The Account Creation & User Setup Process

The account creation and user setup process is a two-part workflow between a CCCTC system Administrator and the authorized user. Below is a breakdown of the two-part setup process for the Administrator and the User.

The Administrator account creation process is summarized as follows:

  • The authorized CCC Tech Center Administrator logs in to Manage > Users and adds new user account to the user’s Organization (MIS Code)

  • The Administrator securely transmits the account credentials and password to the User’s institution email (.edu) using Privnote.

The User authentication setup and first time login process is summarized as follows:

  • The User engages with the Enabling Services implementation team and receives account credentials and user setup information

  • The User installs a compatible authentication app on their desktop or mobile device

  • The User completes the “first-time login” process (the one-time scan of the QR code with authenticator app happens and captures Verification code required for login)

  • The User is automatically authenticated and authorized to login to the DW Report Server.

 

 

Receiving Your Secure Login Credentials

For security purposes, the User will receive their account credentials and password from the CCCTC Administrator in two separate emails to their institution email address (.edu).

The User will receive:

Note: The unique password will not be passed to the authorized user until they have commenced the implementation process with the CCCTC Enabling Services team for added security.

 

 

Implementing the Authentication Software

Before the user can log in to the Report Server for the first time, they must have a compatible authentication app installed that implements Time-based One-Time Password TOTP functionality such as Google Authenticator, Chrome Authenticator, or Authy (which you could download to a phone or computer). There are many free, user-friendly Authenticator apps available at no cost that can be downloaded and installed quickly and easily onto your desktop and/or mobile device.

Suggestion: Although any six-digit TOTP compatible authentication software should work for the Report Server User Account, the tools that we’ve found to be most user friendly are: Chrome Authenticator for desktop use and Google Authenticator for mobile devices. Both are free downloads and both can also be used together.

 

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.

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.

WARNING: If you have already installed Chrome Authenticator (Authenticator.cc) don’t re-install this application as it will overwrite you previous download of this same application.

NEXT: Start the “First-Time User Login” process below to initiate the authentication process and login.

 

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.

NEXT: Start the “First-Time User Login” process below to initiate the authentication process and login.

NOTE: The authenticator tools recommended above are just suggestions. If the user already has a compatible authentication tool it may be used instead.

 

The First-Time User LogIn Process

The first time a new user logs into the Data Warehouse Report Server they need to establish the connection between their account credentials, a shared secret key (QR Code) and the authentication software in order enable the multi-factor login authentication. This is a one time process that is completed in the steps below.

 

The prerequisites for initiating the first-time login process:

  • The user has the Data Warehouse Report Server URL (http://dw-reports.ccctechcenter.org/)

  • The user has received their unique password and account credentials from the CCCTC Administrator

  • The user has installed and configured a compatible authentication application on their desktop and/or mobile device

 

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

  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 (http://dw-reports.ccctechcenter.org/) and land on the Log In page.

  3. Enter the account credentials provided to you into the following fields:

  • Organization (MIS Code): Enter your 3-digit MIS Code.

  • User ID: Enter your institutional email address in the User ID field (.edu or .org is required).

  • Password: Enter the unique password provided to you by the Administrator (sent separately via Privnote). This password is generated by the system Administrator during the account creation process. This unique password cannot be changed or reset by the user.

Complete the Log In page by clicking the “Login” Button: After entering your user credentials, click the “Login” button to continue to the QR Code Scan page.

Forgot Your Password? The unique password configured by the Administrator for the user account cannot be changed or reset by the user. If you’ve forgotten your account password - or if your account has been compromised and you need a replacement password, please contact ES Staff Support to request assistance at staffsupportccctc@openccc.zendesk.com.

 

Step 2: Scan the QR Code Page

The Scan the QR Code page is displayed once during the “first-time user login” 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 “Login” to go to the next page.

  4. Click the “Login” button to return to the Log In Page.

NOTE: This step requires the user to use their authentication app - previously installed on their desktop or mobile device - to scan the onscreen barcode. The “secret” key identified by the barcode will add the required attribute to the User’s account.

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.

Tip: After scanning the QR code and confirming the verification code is captured by the authentication software, snap a screen shot of the code and store in a secure place. For security purposes, don’t store the verification code with the URL or other credentials to the DWRS.

 

Step 3: Repeat Log In Page

After scanning and capturing the six-digit verification code in authenticator app, you have established the connection between the authenticator application and your secure Report Server User Account.

Now you must return to the Log In page to re-enter your user credentials and verification code.

  1. Back on the Log In page, re-enter your account credentials in the input fields: Organization (MIS Code), User ID (email address), and Password.

  2. Click the “Login” button, again.

Validation Error Note: A validation error message will display to all users when they are completing the first-time user login” process. This system message is indicating that the user attempted to login without entering a verification code. As you know, you haven’t been asked for a verification code yet (that is coming in Step 4). Please disregard this error message and continue with the login process.

 

Step 4: Verification Code Page

After re-entering the account credentials a second time, the user is now presented with the Verification Code page to enter the six-digit verification code that was scanned and stored in the Authenticator app. .

  1. Enter your six digit Verification Code

    1. Click on your Authenticator app and retrieve the time-limited verification code.

    2. Paste the code in the “Verification Code” field.

  2. Click the “Login” button on the Verification Code page to complete the login process.


Verified User Login Process

The “first-time login” process is a one-time process. Once the new user has completed the first-time login process, they do not have to repeat steps 2 - 4 UNLESS they need to re-install their authentication software and generate a new QR code.

After the first time login and verification process, user login requires the user to enter their credentials and the verification code retrieved from the authenticator app.

Step 1: Log in Page

  1. Navigate to the Report Server URL (http://dw-reports.ccctechcenter.org/) and land on the Log In page.

  2. Enter your account credentials and password in the input fields.

    1. Organization (MIS Code): Enter your 3-digit MIS Code.

    2. User ID: Enter your institutional email address in the User ID field (.edu, or org) (Required)

    3. Password: Enter your unique password.








Step 2: Verification Code Page

  1. The User refers to their authenticator app to retrieve their new Verification code within 30 seconds of making the request (clicking on Login button on Log In screen).

  2. Verification Code: The user enters the code from the Authenticator App into the Verification Code field.

  3. Click the “Login” button on the Verification Code page to complete the login process and access the DW Report Server.

 

 

WARNING: Your unique Password is generated by the CCCTC Administrator at the time of account creation. The password cannot be changed or reset by the user. To get a new password, please contact ES Support Services.


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.

Note: This scope of this User Guide encompasses details for accessing and running reports, views and dashboards related to CCC Data Warehouse data. For the purposes of this DW Report Server User Guide, it is assumed that you are accessing reports based on CCC Data Warehouse data specifically.

 

In this section:

 

What are the benefits of using 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.

Note: The Getting Started image above may contain additional icons for features visible only to users with Administrator privileges.

 

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

 

Note: It is expected that most DW Report Server users will use the Ad Hoc Views, Reports, and Dashboards resources. These topics are covered in this guide while details for other Report Server tool functionality is available in the JasperReports Server v7 online help documentation, which is accessible via the Help link in the top-right side of the header bar menu.

 

 

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.

Helpful Hint: The server remembers your settings on the Search Results page, so the most commonly needed resources remain visible when you return to the page.

 


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

Find more detailed information about Running Reports and the Report Viewer section of the JasperReports Server v7 User Guide.

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

  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.

IMPORTANT: There is a 200K row limit on all reports that are run. When building a Table report, ensure there is one or more filters configured to limit the report data to display under 200,000 rows.



Running Reports with Input Controls (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.

 

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.

Add Column Filters to Your Report: To add an additional filter to a report after it’s run, click the column heading of the data you want to filter and select the funnel icon.

 

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.

After running and/or exporting a report, click View → Repository to return to the Repository screen to view the list of college and public reports.

 

Dependent Reports

When you create a report from an Ad Hoc view, the report is considered “dependent” on that view.

When you save an Ad Hoc view, some, but not all, of its changes appear in its dependent reports. For example, if you open an Ad Hoc view with a table and adjust the data level for its columns, the column changes will show up in previous reports created from that view.

In cases where changes to an Ad Hoc view could cause errors in dependent reports, you should save the updated view with a different file name and create a new report.

 

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.

See the online help topic under the Reports workflow View tutorial link. At around the 3:35 mark the tutorial begins to demonstrate scheduling a 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.

 

Understanding Ad Hoc “Views”

A report View is a kind of "report designer" that defines the data source, type of report, and the fields, groups and filters that will be applied when the report is generated.

Every report is first created (designed) as an ad hoc View in the Ad Hoc Editor, and then saved as a report.

Once an ad hoc View is created and saved as a report, it can be:

  • run immediately

  • run in the background

  • scheduled (at any interval you define, sent by email to designated recipients, etc.)

  • included on a dashboard

 

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 Ad Hoc Editor provides analysis options (such as slice, pivot, and filter) to help you recognize trends and outliers in your data. You can drill into specific details or analyze your data at a very high level.

Reminder: In order to create a report you must first create an ad hoc report View and save it as a report.

 

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.

NOTE: For filters based on date fields, you can configure a "relative date filter" that refreshes data to a specified date range by using key words and a number. For example, if you want to run a report that displays student data that is only from the previous week, you would set a filter that looks like this: "WEEK-1". For details, see the online help's "Using Filters" topic.

Tip: See the View tutorial link for the Ad Hoc Views workflow on the Report Server's Home page to access a video with further details.

 

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.

By default, all new ad hoc Views are set to the standard Crosstab type. To create a Table report, you must change the format type to Table in the “Select Visualization Type” dialog box.

Note: This document only covers the Table type in detail. For more discussion on crosstab and chart type formats, see JasperReports Server v7 User Guide: Working with Standard Crosstabs.

 

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.

 

 

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

 

By default, the format type for all new ad hoc Views is set to the Crosstab type. To build a Table report, change the format type to Table in the “Select Visualization Type” dialog box.

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.

You can also add a group to the table by right-clicking a field and selecting Add as 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…

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.

If space is at a premium, you can remove labels from the view. When you delete a label, it still appears when you look at the view in the Ad Hoc Editor, but does not appear when you run the report.

For more report formatting tips, such as managing column sizes and adding spacing to your table-type report, see Working With Tables section of the JasperReports Server v7 User Guide.

 

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 only the distinct values in a table, click on the Grid Detail Selector  and choose Hide Duplicate Rows.

When you choose to hide duplicate rows, all columns will be sorted in ascending order based on their distinct values by default, but columns explicitly sorted in ascending or descending order by the user will have higher priority. Sorting by hidden fields will have no effect.

 

Understanding & Using Filters (Input Controls)

When a report is created with built-in filters (also known as Input Controls), the Input Controls dialog box will display when the report is run.

You can take the following actions in the Input Controls dialog box:

  • accept the default field values and click Apply to run the report

  • click in the field and edit the value(s) and then click Apply to run the report

  • click the calendar icon to the right of each date input field to display a calendar where you click and select a date and then click Apply to run the report

After the report runs with input values, the Input Controls dialog box continues to display, which allows you to change the inputs, if needed. Use the table below to decide what action to take in the Input Controls dialog box.

Click this button in the Input Controls dialog box...

To...

Apply

run the report but leave the Input Controls dialog box open

OK

run the report and auto-close the Input Controls dialog box

Reset

set the field values back to their default after you've edited them; the
Input Controls dialog box remains open

Cancel

close the Input Controls dialog box; click View → Repository to return to the list of data warehouse reports

Save

to display the Save Values dialog box to save a specific value setting so you can use it easily the next time you run the report The next time you run the report, a drop-down lists displays in the upper left corner so you can select your saved value(s).

Relative Date Filters: To filter information in your View based on a date range relative to the current system date, see Creating Filters with Relative Dates.

 

Creating Filters with Relative Dates

You can filter information in your view based on a date range relative to the current system date. You can accomplish this using date-based filters, and entering a text expression describing the relative date or date span you want to display, using the format <Keyword>+/-<Number> where:

  • Keyword indicates the time span you want to use. Options include: DAY, WEEK, MONTH, QUARTER, SEMI, and YEAR. An option used by itself (without +/-<Number>) gives the current value for that option.

  • or - indicates whether the time span occurs before or after the chosen date.

  • Number indicates the number of the above-mentioned time spans you want to include in the filter.

For example, if you want to see all of the CC Promise Grant Applications submitted during the prior week, your expression would be: WEEK-1.

 

Steps to create a relative date filter:

Following the instructions in Using Filters, create a filter based on a date field. The filter appears in the Filters panel.

  • In the filter’s first text entry box, enter an expression describing the relative date or date span you want to display.

  • In the filter’s second text entry box, enter the date you want to base your filter on.

For instance, if you want to display all the CCCApply applications submitted for one month prior to the current date, enter MONTH-1 in the first text input box, and enter today's date (or click “Now”) in the second input box.

When you right-click a group member in a Crosstab and select Keep Only or Exclude, you create complex filters. When you create a filter against an inner group, the filter that appears may be created as a complex filter. You can't edit a complex filter, but you can remove it. Complex filters also appear in the Ad Hoc Editor if a Data Chooser filter was created and locked

Best Practice Suggestion: When you run a report with a date/time filter, ensure you enter the correct start and end times along with your day, week, month, or year dates to ensure your intended date filter is capturing every second of your expected data results.

 

 

Ad Hoc View Best Practices

Use the following "best practices" as outlined below when working with Views and their associated reports.

General Best Practice

Example

View/Report Organization

  • Keep your Views organized in folders that have the word "View" in their name to make the Views easier to find

  • Keep your reports organized in folders that have the word "Reports" in their name to make the reports easier to find

  • Use the same name for the View as you do for its corresponding Report but include "View" or "Report" in its name to further help with organization

When Creating a View by Copying an Existing View

  • Only copy a report's View if you need to edit or customize it; otherwise, always run a Public report from the Public folder in the Repository

  • When copying or customizing an existing View, add the word "Report" or "View" onto the end of the new name to keep the report/View distinction clear

  • If you want to keep the original View when making changes, use the Save As button to save the View with a different name and then make your changes (and use the Description field in the Save As dialog box to add information)

  • Public reports and their Views cannot be edited or deleted (though in some reports you may have the option to filter out certain data from the displayed results)

  • Remember: If you change a View, you will need to create/run a report from it in order to see the changes in report form

 


Creating New Ad Hoc Reports

There are two ways to create a new custom report:

This section may reference features and functionality covered in the Working with the Ad Hoc Editor and the Working with Tables sections. Please refer to these sections as needed.

 

Copy and Customize An Existing Report View

A quick and easy way to create a new, custom report is by copying an existing View, making changes to it, and saving it with a new name. This process will save you a lot of time and allow you to focus on customizations. Use the steps below to copy an existing file and create a new one.

Important: To make a change to a saved Report, you must make the change to its original View in the Ad Hoc Editor.


Copy Steps:

  1. Log in to the Data Warehouse Report Server.

  2. Locate the View file that you want to copy from a folder in the Repository, or in the Library, and click on it.

  3. Copy: With the file name highlighted, copy it using one of the following methods:

    1. Click the Copy button in the tool bar.

    2. Right-click the file name and select Copy from the context menu.

  4. Paste the copy into a folder available to you in the Repository (one of your college or district organization folders).

You can now customize your new View copy as desired, and save it with a new name. See more Save & Export options below.

 

Customize & Save View Steps:

  1. Locate the new file you just copied, and click on it. Click the Open button. The View will open in the Ad Hoc Editor.

  2. If desired, you can customize the new View you just copied or saved by using the functional controls in the Ad Hoc Editor. Customization options include:

    • Add or remove data Columns to your format

    • Add or remove Groups to your format

    • Add or remove Filters to further analyze your data

    • Reorder the columns by dragging the tabs in the Columns or Groups rows.

    • See the Working with Tables section above for help using the functionality in the Ad Hoc Editor.

  3. When you are ready to save your new View, hover over the Save icon in the tool bar to display the menu options available.

  4. Select Save Ad Hoc View As...

  5. When the Save As dialog box displays:

    1. Edit the Data View Name (required).

    2. Edit the Data view Description field (optional).

    3. Scroll through the list of available Organizations folders to select which folder you want to save your college's report View.

    4. Click Save.

 

Save Ad Hoc View and Create Report Steps:

To create a Report from your new ad hoc View copy, click the Save icon again but select the option to Save Ad Hoc View and Create Report from the menu to display the Save Ad Hoc View and Create Report dialog box.

When you are ready to create a Report from your ad hoc View, there is an additional step required.

  1. Hover over the Save icon in the tool bar to display the menu options available.

  2. Select Save Ad Hoc View and Create Report from the menu options.

  3. When the dialog box displays, you must save both the View file and the Report file in the same Save As dialog box.

    1. Add or edit the Data View Name (required).

    2. Add or edit the Data View Description field (optional).

    3. Scroll through the list of available Organization folders and select the folder to save your View file.

  4. Repeat steps 3a - 3c above to save your new Report Name (required), Report Description (optional), and choose your folder using the Create Report dialog fields.

  5. Click Save.

Review the section The Ad Hoc Editor above to revisit the components and functionality of the Ad Hoc Editor

Get Help: See the Video Tutorials available on the Getting Started (Home) page.

 

Dependent Reports

When you create a report from an Ad Hoc view, the report is considered “dependent” on that view.

When you save an Ad Hoc view, some, but not all, of its changes appear in its dependent reports. For example, if you open an Ad Hoc view with a table and adjust the data level for its columns, the column changes will show up in previous reports created from that view.

In cases where changes to an Ad Hoc view could cause errors in dependent reports, you should save the updated view with a different file name and create a new report.

 

 

Create a New Ad Hoc Report View from Scratch

To create your own custom report you must first create an ad hoc View for it with a data source, a format type, one or more data fields and filters. This can be done by following the steps and best practices provided below.

You create these views by simply dragging and dropping elements. You can add and summarize fields, define groups, label and title the report, and format data for each field. The effects of your changes are evident immediately, and you can adjust the display to highlight the most relevant and compelling aspects of your data.

Reminder: In order to create a new ad hoc report, you must first design the report in the Ad Hoc Editor and save it as a View. Then it can be saved and run as a report.

 

Steps:

  1. Log in to the Data Warehouse Report Server.

  2. From the main menu, select Create > Ad Hoc View to display the Select Data dialog box.

Alternatively you can click the Ad Hoc Views workflow icon on the Getting Started/Home page.

 

3. Select the Data Source: From the Select Data dialog box, scroll to find the Topic or data source you want to use for your report View. Highlight the data source and click “Choose data…”.

Data Warehouse-related data sources have a "DW" in the name of the source and "Topic" as a part of their name.

NOTE: Hover over the data source name to see its description and path information.


4. Choose Your Data Fields: On the Choose Data screen, you are able to bring in all the data fields from your selected data source, or you can select individual fields that you want to include in your new ad hoc view. Most users will choose all data fields.

A. Click the right-facing double arrows icon in the center column to select fields from the data source.

  • To choose individual data fields from the Source column, click the single arrows in the center column.

  • To choose all data fields from the Source column, click the double arrows.

  • To remove fields from the Selected Fields column, use the single arrows in the center column.

B. Click “OK”.

C. The Ad Hoc Editor displays.

 

Before you begin adding fields, measures and other customization to your new ad hoc View, select a view type from the “Select Visualization Type” dialog box.

 

5. Change the Ad Hoc View Type: By default, the Ad Hoc Editor is set for a Crosstab type view. To create a Table report, select the Table icon from the “Select Visualization Type” dialog box.

A. Click on the Select Visualization Type icon in the main menu. The “Select Visualization Type” dialog box will display.

B. Highlight the “Table” icon.

C. Click the Apply and Close button.

 

6. Add Data Fields and Groups: In the Ad Hoc Editor, the data fields from your data source will appear in the Data Selection Panel (left column). See the Using Data Fields in Tables section for steps.

A. Click on a data and hold field; drag it into the canvas area (Ad Hoc View Panel). The data will display in a column in the canvas area.

B. Repeat step A until you have added all the data fields desired in your table view.

7. Add Filters (optional). See the Understanding & Using Filters section for steps.

8. Add a Summary (optional). See the Displaying Summaries section for steps.

9. Edit Column Header Labels (optional). See the Columns & Header Labels section for steps.

10. Add a Title. See the Adding a Title section for steps.

11. Save Your View and Create Report. See the Save Ad Hoc View and Create Report section for steps.

12. Run and review your report. Locate your report file where you saved it (Repository) or in the Library, and run your new report to ensure it captures all the data and customizations needed.

Recommendation: When saving your ad hoc view and report, it’s very helpful to make the names and descriptions the same, or similar, in order to manage future changes.

Review the Running Reports & The Report Viewer section for a refresher on using report filters & conditional formatting, and steps to Saving & Exporting Your Report.

 


Support Using the Report Server

Contact College Support to report technical issues with the Data Warehouse Report Server - or visit our online community support site, CCCTechnology.info.