Ad Hoc Report Query Step-by-Step

e

1

Start from the HMIS or HMIS Low Volume page

From your HMIS module - * Click on the “Report” module

Initial Ad Hoc Inventory Search

1) Click on Ad Hoc Report / Inventory 2) Select the drop down

box for Category. 3) Click Search. If you

selected “All” – all of the reports available to you will be displayed in the grid

*

1

Pag

e

2

Checking the boxes on the far left side of the grid and clicking on “Save Favorite” will populate those reports in the “Favorite” Category for quicker access. The favorite category is the default category.

4) Select the report you wish to view by clicking on the Report Name.

5) Go to “Query Builder” to change any of the current parameters like date range and “Drill Down” or SQL

6) Select “Show Report” 4

5

e

3

7) Export to Excel, Word or PDF format if you want to save it to a local file -or- Print if report is satisfactory

Pag

e

4

Format Type – 50fieldslist - for most Ad Hoc Reports

Build a simple Ad Hoc Report –

Start with a basic question:

How many people served by my program have Earned Income as an income source?

e

5

- Name your report

1

2 3

Pag

e

6

In the Tree Node, click on ‘View Builder‘ – select the appropriate view for your data. You can refer to the Excel spreadsheet: ‘Ad Hoc View Details’ for a comprehensive explanation of available views.

e

7

Select the Fields (column headers) you want to appear on your report by highlighting them in the grid on the left and using the arrow

to move the field into the grid on the right. “Distinct” is checked by default. Leave it checked unless you absolutely do not want to have a distinct count.

Use up and down arrows (on the right) to move fields into order.

SAVE

Special Note:

You MUST

SAVE

Save

Pag

e

8

On the Tree Node - Click on: Query Builder

A summary of your report, based on the Fields you have selected, will appear in the grid. This information is “unfiltered” and will give you ALL sources of income for this Household. To select only the ‘Earned Income’ you will need to set some parameters using the ‘Where Statement’.

Click on the and add ‘Income Source’ - ‘IsEqualTo’ - ‘Earned Income’

e

9

Click on the to ‘Check Query’. The set parameter will appear below the ‘Where Statement’

Click on the to ‘Apply Query’ - note that the sample information has changed to match your parameters for ‘Earned Income’ only.

Pag

e

10

Category – defaults to ‘Other’ Drill Down – will give details of your information

Select a Field to ‘Group’ by - by dragging and dropping any of the column headers - in this example we chose

ID - note that in the grid – the information has shrunk down to only show the ID column. You can ‘Sort’ (but

do not have to) by clicking on the ‘Grouped by’ field selected. An up or down arrow will appear at the top or bottom of the box to indicate ‘Ascending’ –or- ‘Descending’

You can 1) ‘Show Report’ to preview how the report will look

2) SAVE if you have the query you expected and want to keep it

1 2

e

11

Click on ‘Show Report’ (-or- ‘Print’) - ‘Show Report’ will allow you to view your report. It will also allow you to export to Excel, Word or PDF formats.

Special Note:

You MUST SAVE your Query parameters before leaving the page. If you do not Save – your parameters will be gone and you will have to do it again.

Below is an example of the report of ‘Earned Income’ using the set parameters from above. You can view the entire SQL statement in the header of the report

Pag

e

12

1. Report Name = How to find it in the inventory of available reports (important) 2. Title = Words at top of report when it is finally displayed/run/exported

3. You can’t “SAVE” anything about the report from the inventory page

4. “Save Favorite” (on the inventory page) just puts this report in the category “Favorite” – it does not save the parameters you adjust on the inventory page

5. The View Builder page is the combination of the “FROM” tab and the “SELECT” tab from V4. 6. Distinct defaults as checked (GREAT NEWS) – leave it

7. The Query Builder page is the combination of the “WHERE”, “SORT” and “GROUPBY” tabs from V4. 8. The real work will occur in the back and forth between the ‘View Builder’ and the ‘Query Builder’ pages

– but the trick is: you must Save before you leave one page to go to the next. The order I like to use for creating new reports and then working on them:

Last Hint:

Save your excel files locally before you open them. The days of being able to open excel file after excel file until you get the right one are gone.

View

Builder

•New

•Name

•From (View List) •Select (Fields) •Save

Query

Builder

Toggle

etc.)