______JCS Data Warehouse Standard Queries

Using standard queries in the JCS Data Warehouse
(Hyperion/BRio Version 8)

This document is available online at

http://inside.courts.wa.gov/index.cfm?fa=controller.showPage&folder=manuals&file=brioManuals

Introduction 4

What is the Data Warehouse? 4

What are JCS Data Warehouse Standard Queries? 4

Processing Standard Queries and Using the Results 5

Standard Data Warehouse vs Reports-Only Data Warehouse 5

Processing Standard Queries 5

Open the Data Warehouse and Find the Query You Want to Process 5

Process the Selected Query 8

Working with Query Results 11

Adjusting Column Size 12

Changing Column Order 12

Hiding a Column 13

Unhiding a Column 13

Sorting Results 13

Pivot Reports and Other Reports 15

Saving Your Own Version of the Query 15

Closing the Query and the Data Warehouse 15

List of Queries 17

What Queries are Included in this List? 17

Queries about Referrals 17

05-*SW-R-01 Juveniles by Responsible Units 18

05-*SW-R-02 Juveniles by Probation Units 18

05-*SW-R-03 Active Referrals by School District 18

05-*SW-R-04 Chemical Dependency Resolved 18

05-*SW-R-05 Chemical Dependency Warrants 18

05-*SW-R-06 Active Over 18 (JVR 001, 008) 18

05-*SW-R-07 Referrals for Selected Reason(s) (JVR014) 18

05-*SW-R-08 Legal Case 18

05-*SW-R-09 Adjudicated Legal Cases (JVR7067) 18

05-*SW-R-10 ARY Petitions Denied_Dismissed 18

05-*SW-R-11 CHINS Petitions Denied_Dismissed 18

05-*SW-R-12 Petitions Filed 18

05-*SW-R-13 Truancy Orders 18

05-*SW-R-14 “Becca” Type Referrals with Legal Cases Filed 18

05-*SW-R-15 Becca Petitions Approved 18

05-*SW-R-16 Diversions 18

05-*SW-R-17 Diversion Agreements 18

05-*SW-R-18 Deferred Dispositions 18

05-*SW-R-19 Referral Transfers 18

05-*SW-R-20 Referrals by Current Status 19

05-*SW-R-21 Dismissed Cases 19

05-*SW-R-22 Dispositions by Type 19

05-*SW-R-23 Referral Reasons by Status 19

05-*SW-R-24 Juveniles by Residence County 19

05-*SW-R-25 Referrals by Type 19

05-*SW-R-26 Dismissed Charges 19

05-*SW-R-27 Referrals by Referring Org 19

05-*SW-R-28 Conditions Past Due 19

05-*SW-R-29 Refused or Rejected Diversions 19

05-*SW-R-30 Offender Legal Cases Filed 19

05-*SW-R-31 Sealed Cases 19

05-*SW-R-32 New Referrals 19

05-*SW-R-33 Extended Jurisdictions 19

05-*SW-R-34 Offender List with all Offenses 19

05-*SW-R-35 Offender Legal Cases with all Reasons 19

05-*SW-R-36 Cdda, Ssoda Referrals 19

05-*SW-R-37 Reasons by Outcome 19

05-*SW-R-38 Reasons by School 19

05-*SW-R-39 NonOffender Referrals 19

05-*SW-R-40 Primary Reason by Sex, Age (JVR 3010, 3011) 20

05-*SW-R-41 All Offenses JVR 3000-3001 20

05-*SW-R-42 ARY, CHINS, Dep Cases JVR 015 20

05-*SW-R-43 JVR7040 Management Summary Part 1 20

05-*SW-R-44 Drug Referrals 20

05-*SW-R-45 Diversion Referrals 20

05-*SW-R-46 Appointments 20

05-*SW-R-47 Referral Cases and Legal Cases 20

05-*SW-R-48 Adjudicated Primary Offense by Sex (JVR005) 20

05-*SW-R-49 Adjudicated JRA Commitments (JVR005) 20

05-*SW-R-50 Diverted Primary Offense by Sex (JVR005) 20

05-*SW-R-51 Probation Violations (JVR016) 20

05-*SW-R-52 JVR7040 Mgmnt Summary Part 2 20

05-*SW-R-53 JVR7040 Mgmnt Summary Part 3 20

05-J31-R-01 Current Active Pick Probation Unit 21

05-J31-R-02 Active Over 18 w Probation Unit 21

05-J32-01 Youth Dismissed Charges 21

05-J32-02 Diversion Days Elapsed 21

05-J32-03 Unique Youth by Race_Sex 21

05-J32-04 Relinquishment Petitions 21

05-J32-05 Manifest Injustice 21

05-J32-06 Decline of Jurisdiction 21

05-J32-07 Option B Youth 21

Queries about Detention 21

05-*SW-D-01 Becca Bed Days 22

05-*SW-D-02 Detainees by Race 22

05-*SW-D-03 Misdemeanants in Detention 22

05-*SW-D-04 Detention by Referring Organization 22

05-*SW-D-05 Detention Held Over 30 Days 22

05-*SW-D-06 Current Days Detained 22

05-*SW-D-07 Days Detained by Date Range 22

05-*SW-D-10 Detention Counts 22

05-*SW-D-11 Days Detained Through End Date 22

05-J31-D-01 Meal Service Form 22

05-J31-D-02 Popsheet 22

05-J31-D-03 Average Daily Population 22

Introduction

What is the Data Warehouse?

The Data Warehouse is a collection of Judicial Information System (JIS) data located on a central computer at the Administrative Office of the Courts (AOC). The JCS data warehouse contains juvenile court information.

The Data Warehouse software (Hyperion--formerly known as Brio) is a Web-based software application that can be used to ask individual questions (queries) about a large amount of data, extract information from the data, and format that information into reports.

What are JCS Data Warehouse Standard Queries?

AOC and court staff used Hyperion/Brio to answer common questions that juvenile courts have about their data. These questions and their results are stored in the data warehouse as standard queries.

Examples:

·  What referrals in my court are currently in diversion?

·  I need a list of juveniles by school districts.

Data Warehouse users from any juvenile court can customize these standard queries to provide answers about their court.

Processing Standard Queries and Using the Results

Standard Data Warehouse vs Reports-Only Data Warehouse

There are two formats for the Data Warehouse:

·  The Standard Data Warehouse allows the user to build queries and reports as well as process, view, and print them.

·  The Reports-Only Data Warehouse allows the user to process, view, and print reports but not to build them.

Instructions in this document cover the Standard Data Warehouse. Instructions for the Reports-Only Data Warehouse are in the Reports-Only Data Warehouse documentation on the Brio Manuals page: http://inside.courts.wa.gov/index.cfm?fa=controller.showPage&folder=manuals&file=brioManuals

Processing Standard Queries

Open the Data Warehouse and Find the Query You Want to Process

1. / Double click the Data Warehouse icon on your PC desktop.

Result:
A login window displays.

2. / Fill out the Login window.
·  / At User ID, type the ID you use on the AOC screen or opening screen for ACORDS (also called RACF ID).
Example: J02ACXN
·  / At Password, type your AOC screen or application password (also called RACF password).
Password Note:
Until further notice, use the password assigned by Customer Services when the Data Warehouse software was installed. When full Data Warehouse security is implemented, the RACF password will be required.
·  / Choose Login.

Result:
The Washington Courts Data Warehouse page displays.

3. / Click:
·  Referral Statewide for Referral queries
·  Detention Statewide for Detention queries
·  Court Queries for a list of folders that contain queries for specific courts.

Result:
A list of queries displays.

Note:
You can use queries that “belong” to other courts. They are a valuable resource. Some court queries are included in this document’s List of Queries. Be sure to change the court code to your court’s ID (see below under Process the Selected Query).

4. / Next to the desired query, click bqy.

Important:
For the best view, click bqy instead of clicking the name of the query.

If bqy displays twice, choose the bqy for the standard Data Warehouse unless you use the Reports-Only Data Warehouse.

5. / A Data Warehouse query screen displays.

Process the Selected Query

1. / Make sure the toolbar displays. If it does not display, choose View from the menu, and then choose Toolbars/Standard.
2. / On the toolbar choose the Process button to process the query.
3. / Log on if prompted (see above, step 2).
4. / If prompted, set a court code limit in the Limit Dialog box.
·  Highlight your court’s court code.
·  Choose OK.

Notes:
·  If a list of court codes does not display, type the appropriate value and choose OK (Example: J08).
·  If this is a Detention query, you may be prompted for a detention facility instead of a court code. Highlight the correct facility and choose OK.
5. / If prompted for a date range, enter the date range as follows:
·  Make sure the Between operator displays (see illustration below).
·  Type the first date and the last date of the desired date range, separated by a comma.
·  Click the green check mark (see illustration).

·  Make sure the two dates you typed are highlighted.
·  Choose OK.

6. / If prompted for other limits, respond as appropriate. There may be directions inside the Limit dialog box.
Example:

Result:
When the query finishes processing a Results screen displays. See next page for an example.

Results Screen

Working with Query Results

Adjusting Column Size

Changing Column Order

Hiding and Unhiding a Column

Sorting Results

For additional information see the Data Warehouse Quick Start Guide (Hyperion/Brio 8) at http://inside.courts.wa.gov/index.cfm?fa=controller.showPage&folder=manuals&file=brioManuals

Adjusting Column Size

1. / Click to highlight a column.
2. / Use the mouse to drag the border and make the column narrower or wider.
Result:
The column width is adjusted and the column heading text wraps to fit.
Note:
If a column is too narrow, numbers and dates display as ######.

Changing Column Order

Click the gray column header bar above the column title and drag it to the desired position. The entire column moves to the new position.

Example:
To move Case Date Filed after Case Type.

·  Click the gray column header bar above Case Date Filed.

·  With the left mouse button depressed drag the Case Date Filed column to the right until it is positioned after the Case Type Code column.

·  Release the mouse button.

Result:

·  The Case Date Filed column is “dropped” into place after Case Type Code.

Hiding a Column

·  Highlight the desired column.

·  Right click the mouse to display a speed menu.

·  Choose Hide Column.

Unhiding a Column

·  On the Results screen, right click the mouse to display a speed menu.

·  Choose Unhide Column.

·  In the Unhide Column window that displays, highlight the name of the desired column.

·  Choose OK to close the Unhide column window and restore the column.

Sorting Results

The rows in the Results section can be sorted by one or more columns. A single column sort is called a simple sort. A multiple column sort is called a complex sort.

Getting Ready:

·  Make sure the Sort Line displays on the Results screen.

·  If the Sort Line does not display, Click Sort(0) on the Results Bar.

Simple Sorting

1. / Click the desired column to highlight that column.
2. / On the toolbar click for ascending sort or for descending sort.
Result:
The selected item is placed on the Sort Line.
The rows are sorted in either ascending or descending order as specified.
3. / To remove a sort, highlight the item in the Sort Line. Then right click the mouse and choose Remove from the speed menu that displays.

Complex Sorting

1. / Make sure the Sort Line displays (see above).
2. / For each item to be sorted, drag the gray column header bar from the top of the column to the Sort Line.
Note:
The order of items on the Sort Line determines the sort order.
3. / Click Sort Now on the Sort Line (if present).
Important:
Because of system defaults, the Data Warehouse software often sorts the data into requested order automatically. On these occasions, Sort Now does not display and Step 3 can be skipped.
Result:
The complete results are sorted based on the columns selected. In the illustration on the previous page, the rows sort by filing date and then by case type.
4. / To remove a sort, highlight the item in the Sort Line, right click the mouse and choose Remove from the speed menu that displays.

Pivot Reports and Other Reports

Sometimes the standard query results display in an alternate report format that presents the data more clearly. The available formats are Report and Pivot. To display a report, click the item in the dark grey Sections frame on the left side of the screen.

Saving Your Own Version of the Query

See the following sections of the Data Warehouse Quick Start Guide:

·  Getting Ready to Save a Query (First Time Only)

·  Saving and Processing a Query

·  Opening and Changing a Query

It is recommended that once you have finalized a query that you plan to reuse, you should request that it be published under Court Queries, so that it will be updated when the data models are updated.

Closing the Query and the Data Warehouse

·  To process another standard query, click in the upper right corner to close the query window.

·  When prompted to save the query, choose No.

·  Return to the section on Opening the Data Warehouse and start with step 3.

·  To close the Data Warehouse, click in the upper right corner of the Washington Courts Data Warehouse (home) page window to close it.

List of Queries

What Queries are Included in this List?

This query list includes all statewide queries plus a number of court queries that may be of general interest. If you create (or find) a court query that is not listed here but would be useful for other courts, contact Customer Services.

Queries about Referrals

Query Name / Description

05-*SW-R-01 Juveniles by Responsible Units

/ Active Referrals and Juveniles by Responsible Unit and Official.

05-*SW-R-02 Juveniles by Probation Units

/ List of juveniles by Probation Unit and Officer.

05-*SW-R-03 Active Referrals by School District

/ List of juveniles and their active referrals. List is by School and School District.

05-*SW-R-04 Chemical Dependency Resolved

/ Lists juveniles in the Chemical Dependency alternative probation unit that have resolved referrals. See also Query 36.

05-*SW-R-05 Chemical Dependency Warrants

/ Lists referrals & juveniles in the Chemical Dependency Unit who have warrants authorized. First query is by Responsible Unit/Official; 2nd query by Probation Unit/Officer.

05-*SW-R-06 Active Over 18 (JVR 001, 008)

/ 1st query lists active referrals with juveniles over 18. 2nd query lists all active referrals, showing uveniles & ages. 3rd query prompts for birthdate; then shows active referrals for juvs born before that date. Use for JVR 001, 008.

05-*SW-R-07 Referrals for Selected Reason(s) (JVR014)

/ Lists and counts juveniles and referrals for selected reasons. Lists and counts by School and Zip Code. Third query lists and counts by offense. Can be used in place of JVR014.

05-*SW-R-08 Legal Case

/ List juveniles and legal case numbers for specified law numbers in the local superior court.

05-*SW-R-09 Adjudicated Legal Cases (JVR7067)