______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.
· / 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.
· 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).
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 / Description05-*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)