Data Viewer

Classic View: Ad Hoc Reporting > Data Viewer

Search Term: Data Viewer

The Data Viewer is a user-friendly report building tool which allows users to drag and drop fields into a real-time view of the report as it is being built.

Data Viewer Tool

Calendar Selection

Select a saved Student Report or a saved Course/Section Report. Select calendars by the active year, by the school name or by year. If a calendar is selected in the Campus Toolbar, that calendar is already selected.

Calendars cannot be selected if the query is for Census/Staff Data Types.

Only calendars to which the user is assigned calendar rights are available for selection.

Data Viewer Calendar Selection

Create a New Report

The Data Viewer allows users to drag and drop fields into a real-time view of the report as it is being built. Each report must have a unique name in order to save correctly. To create a new report, select the New button. The Data Viewer - New Item editor appears.

New Item Editor

Select Items to Include on the Report

Select the Report radio button and choose the Report Focus. The report focus determines which fields are made available when building the report and helps you understand which data is being reported for fields which may be shared between staff and students. Users can select a Report Focus of Student, Census/Staff or Course/Section.

Once Report and Report Focus have been selected, hit the OK button. 

Add Fields to the Report

Select fields from the Field Selection area on the left and drag and drop them into the Report Preview window. You can double-click a field to append it to the right-most column of the Report Preview. The Report Preview will refresh each time a field is added to show a real-time view of how the report will look as well as the first ten records of reported data (see Image 3).

Configure Filtering of Data Elements

Report data can be filtered using the Filtering editor (Image 4). This editor allows you to apply operators to fields added to the Report Preview.  

Fields can be added to the Filtering editor by selecting the Add New Filter button or by dragging and dropping the field from the Field Selection section to the Filtering editor.

See the Operator Options and Rules section for more information about each available operator.

Filtering Report Data

In the example above, data is being filtered to only report students who are currently active (Active Today = TRUE) and have a last name which sounds like Aarons (Last Name SOUNDS LIKE Aarons). The Report Preview window will display.

Change a column's name by double-clicking the column name box. Rearrange the column order by clicking on a column name and dragging the column left or right.

To access column options such as sort order, grouping and deleting the column, select the small triangle to the right of the column name.

Sorting and Modifying Column Names/Order

Operator Options and Rules

The following table describes each available operator:

Operator

Results

Example

= (Equals)

Returns exact match of value.

student.grade = 3
     
Only students in grade 3 are returned.

< > (Does not equal)

Returns results not equal to the value.

student.gender < > M
   
Students who have a Gender = F on the Demographics tab or who do not have a value entered in the Grade field are returned.
 
This operator allows NULL values.

> (Greater than)

Returns results that are greater than the entered numeric value.

student.age > 16
     
All students older than 16 years of age are returned.

> = (Greater than or equal to)

Returns results that are greater than or equal to the entered numeric value.

student.age >= 16
   
All students 16 years of age and older are returned.

< (Less than)

Returns results that are less than the entered value.

student.age < 16
   
All students under the age of 16 are returned.

< = (Less than or equal to)

Returns results that are less than or equal to the entered numeric value.

student.age <= 16
   
All students 16 years of age and younger are returned.

IN

Includes value.

student.grade IN 9,10
   
All students in 9th and 10 grade are returned.

 

When using this format, do not put spaces after the comma

NOT IN

Excludes value.

student.grade NOT IN 11,12
   
All students not in 11th or 12th grade are returned.
 
This operator allows NULL values.

 

When using this format, do not put spaces after the comma

BETWEEN

Filters data between two specified values. Works with numbers, dates and strings.
     
If a date field is selected, the following options are available:

  • DATE - Returns data based on the specified date range (where the starting date is sub-option 1 and the ending date is sub-option 2).
  • TODAY - Filters data based on dates that occur from a specific date through today or vice versa.
  • TOMORROW - Filters data based on dates that occur from a specific date through tomorrow or vice versa.
  • YESTERDAY - Filters data based on dates that occur from a specific date through yesterday or vice versa.
  • DAYS BEFORE - Filters data based on the number of days (sub-option 1) prior to sub-option 2 through sub-option 2.
  • MONTHS BEFORE - Filters data based on the number of months (sub-option 1) prior to sub-option 2 through sub-option 2.
  • DAYS AFTER - Filters data based on sub-option 1 through the number of days (sub-option 2) after the sub-option 1 date.
  • MONTHS AFTER - Filters data based on sub-option 1 through the number of months (sub-option 2) after the sub-option 1 date.

For BETWEEN: student.stateID BETWEEN 00001 THROUGH 100000.
 
All students with a State ID between 00001 - 100000 are returned.

For DATE: student.birthDate BETWEEN DATE 10151995 THROUGH DATE 10152010.
         
All students with a birth date between 10/15/1995 - 10/15/2010 are returned.
   
For TODAY: student.startDate BETWEEN TODAY THROUGH TODAY.
   
All students who began an enrollment in the school today (current date) are returned.
   
For YESTERDAY: student.startDate BETWEEN YESTERDAY THROUGH DATE 10152010.
   
All students who began an enrollment in the school yesterday through 10/15/2010 are returned.
   
For DAYS BEFORE: student.startDate BETWEEN DAYS BEFORE 4 THROUGH YESTERDAY.
   
All students who began an enrollment in the school 4 days before yesterday through yesterday are returned.
   
For MONTHS BEFORE: student.startDate BETWEEN MONTHS BEFORE 5 THROUGH TODAY.
   
All students who began an enrollment in the school 5 months prior to today through today are returned.
     
For DAYS AFTER: student.startDate BETWEEN DATE 10152010 THROUGH DAYS AFTER 5.
 
All student who began an enrollment in the school on 10/15/2010 through 10/20/2010 (5 days after) are returned.
   
For MONTHS AFTER: student.startDate BETWEEN DATE 10152010 THROUGH MONTHS AFTER 5.
   
All student who began an enrollment in the school on 10/15/2010 through 3/15/2011 (5 months after) are returned.

IS CURRENT USER

Returns the current user's ID.

For learningPlan.planManagerPersonID IS CURRENT USER

The current user's ID is reported along with data only applicable to him/her.

LIKE

Searches for test string in field.

course LIKE hist
   
All courses like History 101 are returned.

NOT LIKE

Searches for test string and filters data that is not like the user-defined value.

course NOT LIKE hist
   
All courses not like Hist are returned.
 
This operator allows NULL values.

SOUNDS LIKE

Uses a database function to return names with similar sound patterns.

student.lastName SOUNDS LIKE Ball
     
Names such as "Ball," "Bell" and "Boll" are returned.

CONTAINS

Searches for strings that include the same data entered by the user in the field. Any string that does not contain the user-defined value is filtered out. Any wildcard characters entered are treated as standard SQL wildcards.

student.birthCountry CONTAINS Cana
 
All students with a Birth Country that contains "Cana" are returned.

STARTS WITH

Searches for strings that begin with the same data entered by the user in the field. Any string that does not contain the user-defined value is filtered out. Any wildcard characters entered are treated as standard SQL wildcards.

student.birthCountry STARTS WITH Mexi
   
All students with a Birth Country that begins with "Mexi" are returned.

ENDS WITH

Searches for strings that end with the same data entered by the user in the field. Any string that does not contain the user-defined value is filtered out. Any wildcard characters entered are treated as standard SQL wildcards.

student.birthCountry ENDS WITH many
   
All students with a Birth Country that ends with "many" are returned.

IS NULL

Returns fields that are completely NULL (0 is considered a value).

student.stateID IS NULL
   
All students who do not have a state ID are returned.

IS NOT NULL

Returns all fields that are not NULL (0 is considered a value).

student.ssn IS NOT NULL
   
All students who do not have a stateID are returned.

IS TODAY

Returns result dates as the current date.

start.date IS TODAY
   
Entries where the start.date is the current date are returned.

IS YESTERDAY

Returns result dates as of yesterday's date.

start.date IS YESTERDAY
   
Results for one day previous to the current date are returned.

IS TOMORROW

Returns result dates as of tomorrow's date.

end.date IS TOMORROW
   
Results for one day after the current date are returned.

IN THE MONTH

Returns all database field data for the month entered.
   
This operator allows both numbered dates and spelled out dates (i.e., 10 or October). This operator also allows for both upper and lower case letters. If spelling out a month, users must entered at least the first three characters (i.e., Oct for October).

employment.districtStartDate IN THE MONTH October
 
All employees who have a district employment Start Date within the month of October are returned. This operator does not look at the Year or Calendar selected in the Campus toolbar. All historical and current district employment records with a Start Date in October are returned.

=TRUE

Returns checkbox values of "true" (checkbox is marked)

enrollment.stateExclude = TRUE
   
All students who have the State Exclude checkbox marked on their enrollment records are returned.

=FALSE

Returns checkbox values of "false" (checkbox is not marked)

enrollment.stateExclude = FALSE
     
All students who do not have the State Exclude checkbox marked on their enrollment records are returned.

In addition to the options above, wildcard searching is also available. The following is a list of options:

Wildcard or Pattern

SQL Meaning

Standard Examples

%

0 or more characters

Entering the word Man returns the same results when entering Man%.

%son finds names that end in -son: Johnson, Manson, Jason-Benson, etc.

_ (underscore)

One character

Olson_Zierke and Olson Sierke returns the same results.

L__ (with two underscores) does not look only for 3-character names that start with L, but _L_e_ will find names where L is the first and e the third character (e.g. Lee, Luewenhook).

If the three underscores are entered at the end of a name, like Dan___, results will list names with three additional letters (Daniel).

[token]

A range of possible characters

L[ae] finds names that start with La or Le.

,James

No SQL wildcard

Searches for first name equal to or beginning with James.

Gonzales-Uribe

Compound name

Finds that last name.
This will return compound names regardless of whether they are linked by a space or hyphen.

Gonzales Uribe or
Gonzales_uribe or
Gonzales%uribe

A compound name with a space.

Will find the name with or without a space or hyphen.

Try wildcards if there is a space between the compound name.

Users can also use the following combinations when using the Like operator:

Wildcard or Pattern

SQL Meaning

Standard Examples

%

0 or more characters

L% finds names that start with L

L finds names that contain an L

LAN finds names containing LAN (Blanko, Landesburg, Blankenship, etc.)

_ (underscore)

One character

L__ (two underscores) finds Lee and Lor, not Luewenhook.

[token]

A range of possible characters

L[ae]% finds names that start with La or Le.

^

Negation of token

L[Query Wizard^ae] finds names that do not start with La or Le.

Rules for Operators by Data Type

The following table describes all rules for allowing or disallowing operators by data type where:

= Allowed, N = Not Allowed and D = Depends on Field.


Number

Float

String

Date

Text

Bit

>

Y

Y

Y

Y

Y

N

>=

Y

Y

Y

Y

Y

N

<

Y

Y

Y

Y

Y

N

<=

Y

Y

Y

Y

Y

N

< >

Y

Y

Y

Y

Y

N

=

Y

Y

Y

Y

Y

N

IS NULL

D

D

D

D

D

N

IS NOT NULL

D

D

D

D

D

N

BETWEEN

Y

Y

Y

Y

Y

N

IS TODAY

N

N

N

Y

N

N

IS YESTERDAY

N

N

N

Y

N

N

IS TOMORROW

N

N

N

Y

N

N

IN

Y

Y

Y

Y

Y

N

NOT IN

Y

Y

Y

Y

Y

N

LIKE

N

N

Y

N

N

N

STARTS WITH

N

N

Y

N

N

N

ENDS WITH

N

N

Y

N

N

N

CONTAINS

N

N

Y

N

N

N

SOUNDS LIKE

N

N

Y

N

N

N

=TRUE

N

N

N

N

N

Y

=FALSE

N

N

N

N

N

Y

Manage and Store Data Viewer Reports

Copy Reports

To copy an existing report, select the report from the My Reports window and click the Copy button. The copied report will appear within the My Reports window prefixed by the words "Copy of". In the example below, the student sped report was copied to have a student Copy of sped report.

Copy a Report

Edit an Existing Report

To edit an existing report, select the report from the My Reports window and click the Edit button.

Edit a Report

Apply an Ad hoc Filter to an Existing Report

An Ad hoc filter can be applied to an existing report to further filter report data. To apply a filter, select the report from the My Reports window and select the filter from the Ad Hoc Filter dropdown list.

  1. Select a report from the My Reports window.
  2. Select a filter from the Ad Hoc Filter dropdown list. Filters are created using the Filter Designer tool.
  3. Select the Report Output Format. Options include PDF (default), HTML or CSV.
  4. Click the Generate Report button. The report will appear in the designated format with data filtered based on the filter selected.

Ad Hoc Filter with an Existing Report

Delete a Report

To delete a report, select the report from the My Reports window and select the Delete button.

Delete a Report

Create a Folder

Reports can be organized into folders, allowing for easier access to related reports as well as providing important report context. 

Creating a New Folder

To create a report folder:

  1. Select the New icon. The Data Viewer - New Item editor will appear.
  2. Click the Folder radio button.
  3. Enter the Folder Name.
  4. Select the Parent Folder (if necessary). This option allows you to decide if the folder should be separate from all other folders (No Parent) or live within an existing folder.
  5. Select the OK button. The folder will be added to the My Reports window or within the selected Parent Folder (see Image 16).

New Created Folder

Edit Existing Folders

To edit a folder, select the folder from the My Reports window and select the Edit button. Select a new Parent Folder and/or enter a new Folder Name and select the Save icon. 

Editing an Existing Folder

Add/Remove Reports into Folders

Reports can be moved in and out of folders by clicking and dragging the report into the folder or dragged out of folder. The folder receiving the moving report will highlight in yellow.

To remove a report from living within a folder, click and drag the report name to the bottom of the My Reports window. The area displaying reports not within folders will highlight in yellow.

Generate Data Viewer Reports

Once the Name has been entered, all fields have been added to the Report Preview and any Filtering options have been defined, save the report by selecting the Save icon. 

To generate the report, click the Print button. The report will appear in PDF format.

Selecting the Print button will also save the report.

Saving and/or Printing the Report

Example of a Printed Report

To generate a report, select the report from the My Reports window and select the Generate Report button.

Generating a Report