AESuniversity Ad hoc Reporting

1. AESuniversityAd hoc Reporting

2. Ad hoc Reports What are ad hoc reports? Why would you use ad hoc reports? Creating an ad hoc report from a query Building a new query Saving an ad hoc report query

3. ad hoc for the special purpose or end presently under consideration concerned or dealing with a specific subject, purpose, or end - dictionary.com

4. What is an Ad hoc Report? • A report that is completely customizable • The type of format (list, chart, graph) • Items to include • How to group the information • Parameters

5. Why use ad hoc reports • There may not be a management or application report that gives the information you need • May need very specific information • You can check large amounts of data • Send the report to Excel to do further calculations or turn into graphs

6. Ad hoc reports choices • Two main choices • Type of report – this determines what the report will look like • Table to use – this determines what items you can select to be part of the report

7. Types of ad hoc reports • 50 Fields List – most information of all types • Fields List – good to view data that do not work with sums or counts • Group and List/Group and List Sum – can get counts and sums, only shows 5 – 6 items • Pie Charts – good to show relationships within one data set • Cylinder Chart – compares items in data set • Matrix Charts – can show more complex relationships

8. 50 Fields List • Most information of all types, useful for checking data • Best to export to excel

9. Fields List • Useful for items that do not need sums or counts • Use for items too big to fit into columns – like Comments

10. Group List Sum / Group & List • Creates Count, Sum and Average for each Group and for the total • Calculations are for the item selected first • Can only fit 5 – 6 items across on the page • Group & List does not include the calculations, just the data

11. Pie Charts • Shows size of items on one data series, proportional to the sum of the items • Useful if there is only one data series to view • Limit it to ten or less categories • Item selected first and as Grouping will be graphed

12. Cylinder Charts • Useful for illustrating comparisons among items • Can help clarify relationships and highlight patterns • Item selected first in Grouping determines the X-Axis field

13. Matrix charts • Matrix charts show data arranged in a grid or matrix format • Four types of Matrix charts: • Listing lists just the data • Count provides counts of occurrences of the data • Distinct Count counts distinct occurrences • Sum provides a sum of the data

14. Matrix List Charts • First item selected on SELECT tab is the data that will be displayed in the grid • Up to 4 other items can be selected and will be shown in the row • Matrix Count would show the number of services • Grouping here = Program, Service

15. Matrix Count Charts • Service Unit was selected first, so it shows in the Grid • Grouping is Program, Program Component, Service

16. Creating an ad hoc report • Select type of report • Select Query or Build new Query • Add final information • Title • Display & Number • Dates • Show Report or Export to Excel 1 2 3 4

17. Run report from saved Query • Running a report from a saved Query • Must know the type of report • 50 fields, Matrix, etc. • Must know name of saved Query • May need to edit Query

18. Build a new ad hoc query • Ad hoc query builder popup window tabs • FROM: select table which determines available items (required) • SELECT: available items list (required) • WHERE: filters items searched • SORT: determines order of matches • GROUPING: determines categories of data (required)

19. FROM tab Must select ONE Table Select a Table (or View) The Table determines the items available for the report Each Table has different items Only one Table may be used at a time

20. SELECT Tab • Select items to appear on report • Items listed alphabetically • Use >> to select items • Items will appear in order selected • Use UP & DOWN to change order • DISTINCT counts unique combinations Must select at least one Column item

21. WHERE Tab • Filter items here • Select item in Column dropdown • Operator is how to to match: • =, LIKE, >, < • Value is what to match • Use AND or OR if adding second row Not required

22. SORT Tab • Determine order items will appear on report • Use >> to select • Checkbox reverses order • Use UP & DOWN to change order Not required

23. REPORT GROUPING Tab • Items selected here determine how report is grouped • Each Group starts a new section of report • Up to 5 items may be selected Must select at least 1 and up to 5 items

24. Finish Report Parameters • Enter a Title • Display determines group to display – ALL, TOP or BOTTOM • For Number: • If ALL, use 0 • Top or Bottom, enter number • Select a Date Field and Date range if needed 1 3 2 4

25. Run Report • SHOW REPORT opens in Internet browser window • Can still export to Excel from window • EXPORT TO EXCEL opens in Excel

26. Save Report Query • SAVE AS saves the Query, not the report • Click SAVE AS button • Select GROUP who has permission to report • Enter REPORT NAME • Make the name descriptive and useful • Make it unique • Select CATEGORY • Enter useful Description • Click OK