In This User Guide

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

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

• User Accounts

• Set Up & Log In Processes

• Appendix A: Data Warehouse Report Server URLs

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

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 the “First-Time User Log In” process 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.

Expand here to see instructions for downloading and installing the Google Authenticator App.

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

Open

The First-Time User Log In Process

Non-SSO Users Only: The first time a new non-SSO user attempts to log in to the DWRS they must establish the connection between their user account credentials, a shared secret key (QR Code), and the authentication software to enable the two-factor (2FA) log in authentication.

Prerequisites:

• The User has the DWRS direct access URL: (https://dw-reports.ccctechcenter.org/jasperserver-pro/login.html).

• The User has received their account credentials from a CCCTC Enabling Services Administrator.

• The User has installed and configured compatible authentication software on their desktop and/or mobile device.
  
  

Verified User Log In Process

For Non-SSO Users Only: After the user completes the one-time “First-Time User Log In” process, the user is “verified” and will not have to repeat the Scan the QR Code Page step again UNLESS they need to re-install their authentication software and/or generate a new QR code. For all subsequent s, the verified user needs only to enter their account credentials and the verification code retrieved from their authenticator app.

Step 1: Enter Username or Email

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.

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

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

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:

• Introduction to the Report Server

• Running Reports and the Report Viewer

• Scheduling Reports

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:

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.

^{List of resources in the Repository in a district organization's folder.}

Repository Folders

The file system in the Repository has a tree structure which displays folders containing the resources that you have permission to view and access. Contents can be displayed by expanding the available folder icons in the Folders panel. There are two primary types of folders displayed to users: Public and Organization folders.

Public Folders

The Public Folder that sits at the root of the Repository contains public template reports, views and dashboards that each college can run based on their college data. As template reports and views are added, each data set, such as CCCApply Standard applications, International applications, etc. will have a separate folder set containing ready-to-run reports.

Organization Folders (Colleges / Districts)

Each CCC college organization has a college folder nested under its district folder. Organization folders are used for saving and organizing your ad hoc Views, reports, and other content resources.

^{Repository: The Folder Panel tree showing various Organizations and Public folders.}

Using Repository Resources

After finding a resource in the Repository, naturally you want to do something with it. Your options are:

• Click the name of a report to run and view it.

• Right-click the name of a resource to access other operations on the context menu, for example Edit or Open. Items appear on the context menu according to your permissions.

• Click anywhere in the row except the resource name to select a resource. Ctrl-click anywhere in the rows to select multiple resources. Use the context menu or buttons above the results list: Run, Edit, Open, Copy, Cut (move), or Delete. If the button is unavailable, the resource doesn’t support the operation or you don’t have permission for the operation. For example, the Open button is available when you select a dashboard or an Ad Hoc report if you have permission to write to it.

You might also need permission to access the folder or dependent file, such as an image, of a resource. For example, to schedule a report, you need to have read/write/delete permission on the folder where server saves the report output. For more information about permissions, see the JasperReports Server Administrator Guide.

Searching the Repository

You can search the entire Repository, subject to your permissions, or narrow your search using filters.  The Filters provided for searching the Repository restrict a search by name, who changed the resource, type of resource, date of the resource, and schedule.

To search the entire Repository, select View > Search Results from the header menu. The Search Results page appears with the Filters panel on the left side.

The Search Results Page

The Search Results page displays results of searches and filters.

On the Search Results page, use either the Filters panel or the Search field to find resources.

• Select one of these filters: All available, Modified by me, or Viewed by me.

• Click the X  icon in the search field to clear the search term if there is one.

The search results will display a list of files that your user account has permission to view.

Double-click a resource in the list to view it, or right-click a resource to see what functions are available from the context menu.

Running Reports & The Report Viewer

The Report Server makes it easy to run reports. When you run a report, it opens in the interactive Report Viewer. With the Viewer, you can personalize and refine the displayed report data. If the report has input controls (filters), you run the report with one set of data and then another.

In this section:

• Overview of the Report Viewer

• Running Reports

• Formatting Options in a Table Report

• Saving & Exporting a Report

Overview of the Report Viewer

The Report Viewer allows you to view a report, export content to various output formats, and apply formatting, sorting, and filters to control how the data is displayed. This section describes the functions available in the Report Viewer.

Running a Report

To open and run a report in the Report Viewer:

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.

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

• Working with Tables

• Creating New Ad Hoc Reports

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.

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.

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.

Column and Header Labels

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

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.

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.

Ad Hoc View Best Practices

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

_{Back to Top}

Creating New Ad Hoc Reports

There are two ways to create a new or customize a report:

• Copy and Customize an Existing Report View

• Create a New Ad Hoc Report View from Scratch

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.

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

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.

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.

Create New Ad Hoc View 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.

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…”.

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.

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.

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.

Support Using the Report Server

Contact College Support (staffsupportccctc@openccc.zendesk.com) to report technical issues with the Data Warehouse Report Server or visit our online community support site CCCTechnology.info.

_{Back to Top}

Part 5: Report Templates

The Data Warehouse Report Server is a structured source of master data that can be used to generate the reports, templates, data marts, and analytics that end-users need.

The template reports in the Data Warehouse Report Server repository include the following kinds of reports along with their report Views:

• ready-to-use standard reports specific to single application data instances

• ready-to-use CCC Data Warehouse reports specific to one of the following application data sources:

  • Canvas

  • CCCApply Standard application

  • CCCApply International application

  • California College Promise Grant application

  • MyPath

Report Overviews:

• DW LGBTQ Report

DW LGBTQ Report

The Data Warehouse (DW) LGBTQ Report is a ready-to-use report produced from the sensitive CCCApply data available only in the CCC Data Warehouse.

This guide serves as both an overview and a "quick start" guide specifically for accessing and customizing the DW LGBTQ Report template.

You can:

• run the preconfigured LGBTQ report that pulls CCCApply data from the CCC Data Warehouse

• create a custom LGBTQ report "View" from which you can customize the LGBTQ report data

Running the DW LGBTQ Report

To run the ready-to-use DW LGBTQ Report template in Public folder in the Repository, use the steps below.

1. Log in to the Data Warehouse Report Server with your authorized user account.

2. Click View → Repository to display the Repository folders in the navigation panel on the left side of the screen.

3. From the navigation panel, open the Public folder → Data Warehouse folder → DW Reports folder to display the available reports for the Data Warehouse, including the DW_LGBTQ_Report.

4. Click the DW_LGBTQ_Report link to run the report.

Below is an example of the DW_LGBTQ_Report report that displays in the Report Viewer.

Using the Input Controls to Filter Your Report Data

If you want to narrow the report's results to a different time range from the default, click the Options icon to display the Input Controls dialog box and enter date/time fields to specify a new date range.

Click Apply to apply the new date range. The report will run and display results within the new date/time range you specified. The Input Controls dialog box continues to display allowing you to change the values if needed. Click OK to close dialog box.

The table below outlines the available actions you can take in the Input Controls dialog box.

Save your report by clicking the Save/Save As... icon in the toolbar.

To Export your report, click the Export icon from the toolbar and choose from a variety of file formats.

To export as a PDF, the report will open in a new browser tab which can be downloaded as a PDF file.

Customizing the LGBTQ Report Template

The process to create and save a custom version of the LGBTQ report template in the Repository, or any report template in the Data Warehouse Report Server, will depend on what you want changed and whether the change is temporary (one-off) or if you want to make long-term changes to the format, the data source, the data elements, and/or different or additional Input Controls (filters).

From the Repository:

Step 1: Locate and select the corresponding Ad Hoc View template of the report you want to customize from the Public > Data Warehouse > DW Views folder.

Step 2: Right-click on the Ad Hoc View file to Copy - or click on the “Copy” link in the folder toolbar - and Paste the copy into your Organization folder(s).

Step 3: Locate your copy of the Ad Hoc View and double-click to open it in the Ad Hoc Editor.

In the Ad Hoc Editor

Step 4: With your copy of the Ad Hoc View open in the Ad Hoc Editor, follow the information provided in Part 4: Creating Custom Ad Hoc Views & Reports in this guide.

Once you’ve selected the fields you want to be visible in the Ad Hoc Editor - which can be used as data field columns, measures, groups, and/or input control filters, click OK.

In the example above, you will now see “birthdate” as an available data field in the Fields column and can be used in your report.

Step 4: Save your changes using the Save As > Save Ad Hoc View and Create Report function.

Step 4A: Click the Save icon in the Ad Hoc Editor tool bar.

Step 4B: In the dropdown menu, select Save Ad Hoc View and Create Report to open the Save As dialog menu.

Step 4C: In the Save Ad Hoc View and Create Report dialog box, enter the names of your View and Report, and designate the folders where each will be saved.

In the Save Ad Hoc View and Create Report dialog box, you will name and save both resources, ad hoc View in the Data View fields the new ad hoc View in the fields on the left side of the dialog box, then enter the name and select the folder for the corresponding Report in the right side dialog fields. The View provides the report type, format, data source and elements and filters (input controls) for the report you will run.

_{Back to Top}