Data Tools on RSA.ED.GOV

1. Ad Hoc Query on http://rsa.ed.gov Data Tools on RSA.ED.GOV AD HOC QUERY

2. Ad Hoc Query on http://rsa.ed.gov Old v. New Ad Hoc Query • Used criteria in URL (potential security flaw) • Forced users to select individual columns • Export to Excel not stable • Slow • No sorting, and users sometimes got “ORDER BY” bug when editing query • Limited to one table for queries – forms using multiple tables had multiple ad hoc query screens. • Hides criteria and SQL in the form • Allows users to select columns by group* • Excel export is stable – also to Word • Faster • Sort is allowed • Allows us to join together multi-table form data – RSA-15, RSA-911, Payback, SGAT forms – into one query seamlessly. * This was the #1 most often requested enhancement

3. Ad Hoc Query on http://rsa.ed.gov The Design Approach The #1 job of Ad Hoc Query is to get data out of the system as easily as possible. • K.I.S.S. • Keep It Simple, Spartan • Remediate flaws in prior Ad Hoc • Support Excel and let Microsoft do the heavy work of complex filtering / sorting / formatting for now • Enhance when time permits

4. Ad Hoc Query on http://rsa.ed.gov Running Ad Hoc Query • From the home page, click on AD HOC QUERY

5. Ad Hoc Query on http://rsa.ed.gov Running Ad Hoc Query • Annual Review Report • FFR • RSA-113 • RSA-15 • RSA-2 • RSA-227 • RSA-509 • RSA-572 • RSA-661 • RSA-662 (AFP) • RSA-664 (AT State Plan) • RSA-692 • RSA-704 Pt.1 • RSA-704 Pt.2 • RSA-7-OB • RSA-911 • Standards and Indicators • Telework

6. Ad Hoc Query on http://rsa.ed.gov Running Ad Hoc Query: Instructions Read the on-line Instructions for updated tips & tricks.

7. Ad Hoc Query on http://rsa.ed.gov Running Ad Hoc Query: Step 1 Note that in this particular case, we allow you to set stateand/or fiscal year. You could leave both blank (when blank, all values are returned). Note that dataset is required and may not be left blank.

8. Ad Hoc Query on http://rsa.ed.gov Running Ad Hoc Query: Step 2 This varies form to form. Some forms have an “All” option. At this point, you can click Run Query (or go on to options Steps 3 and 4).

9. Ad Hoc Query on http://rsa.ed.gov Running Ad Hoc Query: Results Note that in this particular case, we allow you to set stateand/or fiscal year. You could leave both blank (when blank, all values are returned). Note that dataset is required and may not be left blank.

10. Ad Hoc Query on http://rsa.ed.gov Filtering Results in Excel Note that in this particular case, we allow you to set stateand/or fiscl year. You could leave both blank (when blank, all values are returned). Note that dataset is required and may not be left blank.

11. Ad Hoc Query on http://rsa.ed.gov Filtering in Ad Hoc Query: Step 3 Filtering enhancements coming MONDAY! • Comma-delimited list • Wild card “%”

12. Ad Hoc Query on http://rsa.ed.gov Sorting Results in Excel Note that in this particular case, we allow you to set stateand/or fiscl year. You could leave both blank (when blank, all values are returned). Note that dataset is required and may not be left blank.

13. Ad Hoc Query on http://rsa.ed.gov Sorting in Ad Hoc Query: Step 4 Limited to four items…

14. Ad Hoc Query on http://rsa.ed.gov TIP After doing a query, if you realize you forgot to select something and hit “Start over”, the system removes all of the previously selected and set criteria. Hitting the back button retains your selections.

15. Ad Hoc Query on http://rsa.ed.gov A common question . . . Q: It is unclear when setting additional criteria what format is required to correctly run the query. Example, State Name spelling or abbreviation, Final report Y/Yes/or /yes A: AHQ uses the values as shown (numbers include commas, etc.) Export to Excel and use Excel’s excellent auto-filter option. It populates a dropdown with all the possible values, and lets you check off the one or ones you want.

16. Ad Hoc Query on http://rsa.ed.gov A reminder. . . Q: Often when running reports, we need to know if something is not equal to a field. For example we may need to know if it is <>, >=, or <=. For reports we often need to know criteria for all agencies where column B is greater than 0 or where it is not 0 A: Export to Excel and use Excel’s excellent auto-filter option. It recognizes number columns and gives you a wide range of options.

17. Ad Hoc Query on http://rsa.ed.gov I wish it would… Q: Having groups creates a some extra fields or extra data that may not be needed. It would be better to be able to select line items needed in the query Q: When we need one or two fields from Step 2, it is not easily available and the end result is a “lot” of formatting A: Being able to select a group of columns with one click is the single most requested item for Ad Hoc. Other users are ecstatic over this. And it’s “paper friendly” There’s a real need to keep the column groups. So - why not both? For now, use Excel. Delete columns you don’t want. Or hide them. Or use a pivot table. All are very easy to do in Excel.

18. Ad Hoc Query on http://rsa.ed.gov Vision for the future of Ad Hoc Query • People should be able to query by a topic area – shouldn’t have to be familiar with RSA forms • AHQ for a program – with tabs for Performance Data, Financial Data, etc. allowing system to merge data from 113, 911, 2, 425, State Plan onto one query

19. Ad Hoc Query on http://rsa.ed.gov Formula Grant Calculator • Level of explanation • Choose FY/CR • Choose state • (optional) choose comparison states

20. Ad Hoc Query on http://rsa.ed.gov Formula Grant Calculator • Level of explanation • Choose FY/CR • Choose state • (optional) choose comparison states

21. Ad Hoc Query on http://rsa.ed.gov Formula Grant Calculator

22. Ad Hoc Query on http://rsa.ed.gov Match Calculator Enter either the Federal amount to find the state’s required match, or the state amount.