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 saved 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 Displays the most information of all report 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-Program, Caption

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 and Load or Build new Query • Add final information • Title • Display & Number • Dates • Show Report or Export to Excel 1 2 2 3 4 4

17. Run report from saved Query • Running a report from a saved Query • Know the type of report • 50 fields, Matrix, etc. • Know the name of the saved Query • May edit Query if needed

18. Use Saved Query • Select Type of report • Select Category and Report Query • Click LOAD • Add final information • Title • Display & Number • Dates • Show Report or Export to Excel 1 2 3 4 5 5

19. 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)

20. 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

21. 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

22. 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

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

24. 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

25. 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

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

27. 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