aesuniversity ad hoc reporting

Adsystech, Inc.

AESuniversityAd hoc Reporting

Adsystech, Inc.

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

Adsystech, Inc.

• for the special purpose or end presently under consideration

• concerned or dealing with a specific subject, purpose, or end

- dictionary.com

ad hoc

Adsystech, Inc.

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

Adsystech, Inc.

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

Adsystech, Inc.

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

Adsystech, Inc.

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

Adsystech, Inc.

50 Fields List• Displays the most

information of all report types

• Useful for checking data

• Best to export to excel

Adsystech, Inc.

Fields List

• Useful for items that do not need sums or counts

• Use for items too big to fit into columns – like Comments

Adsystech, Inc.

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

Adsystech, Inc.

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

Adsystech, Inc.

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

Adsystech, Inc.

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

Adsystech, Inc.

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

Adsystech, Inc.

Matrix Count Charts

• Service Unit was selected first, so it shows in the Grid

• Grouping is Program, Program Component, Service

Adsystech, Inc.

Creating an ad hoc report

1. Select type of report

2. Select Query and Load or Build new Query

3. Add final information– Title– Display & Number– Dates

4. Show Report or Export to Excel

1

2

3

4

2

4

Adsystech, Inc.

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

Adsystech, Inc.

Use Saved Query

1. Select Type of report

2. Select Category and Report Query

3. Click LOAD

4. Add final information– Title– Display & Number– Dates

5. Show Report or Export to Excel

12

3

4

55

Adsystech, Inc.

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)

Adsystech, Inc.

FROM tab

• 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 timeMust select ONE Table

Adsystech, Inc.

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

Adsystech, Inc.

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 rowNot required

Adsystech, Inc.

SORT Tab

• Determine order items will appear on report

• Use >> to select • Checkbox

reverses order•Use UP & DOWN to change order

Not required

Adsystech, Inc.

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

Adsystech, Inc.

Finish Report Parameters

1. Enter a Title

2. Display determines group to display – ALL, TOP or BOTTOM

3. For Number:• If ALL, use 0

• Top or Bottom, enter number

4. Select a Date Field and Date Range if needed

1

2 34

Adsystech, Inc.

Run Report

• SHOW REPORT opens in Internet browser window– Can still export to Excel from window

• EXPORT TO EXCEL opens in Excel

Adsystech, Inc.

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