Filtering an Adhoc report
1.1 Ad hoc Reports
Custom Ad Hoc Reporting allows you to create reports in the system by pulling data from fields within the application. There are no limits on the number of reports that you can create.
Accessing The Ad Hoc Reports Section:
- Step 1: Home > (Select - Application Name) > Manage Reports
- Step 2: Once you select the application you will be directed to the Manage Applications, select the Manage Reports menu option.
- Step 3: This will direct you to the Reports dashboard.
- Step 4: Once you select Ad Hoc Reports the following screen is displayed: (Note: if you do not have any reports created the listing will be blank)
Ad Hoc Reports Listing Overview:
Allows you to export a listing of all the custom reports that are in the Report List to an Excel spreadsheet.
- Report Name - Name the report has been given when created. Clicking on this column header will sort the Report Name listing alphabetically.
- Report Description - Brief description of the report’s purpose. Clicking on this column header will sort the Report Description listing alphabetically.
- Visibility - ALL or ME - This will let you know who can see the report. Whether it is Visible to Me, for reports that only you want to be able to view, or Visible to All, to allow all the Admin to be able to view.
- Updated On – This shows the date and time, the report was last updated with current data. This date will change when the report has been edited and when the Update Reporting Database button has been pushed.
1.1.1 Creating a Custom Ad Hoc Report
- Step 1: From Ad Hoc Reports listing, click on the Create New Report button.
- Step 2: The following screen is displayed:
- Step 3: Name the Report
- Report Name – Enter the name you want to give to the report.
- Report Description – Enter the report description, describing the purpose of the report.
- Step 4: Report Visibility – set access for who can view the report
- Visible to Me Only – only the person that created the report can view it.
- Visible to All – allows anyone with a role type that can view reports to be able to view.
- Step 5: Report Includes – Select the Data Areas that contain the various fields within your application that you need to pull into the report. Select each item by putting a checkmark in each area you want to include. You can select one or all of the selections.
It is best practice to include the Application Data in every report so that you can pull on the status of your applicants.
Application Data will allow you to include the following in a report:
|Section Data||Section Data will allow you to select the sections in your application and the questions in that section. Place a check in the appropriate sections you wish to pull data on.|
|Score Card Information||Score Card Information allows you to pull all values from the question fields within the Evaluation Tasks that you set up as a part of your review process. You can also select the specific task and get the scores based on that one specific task.|
Award Data will allow you to include the following in a report:
- Step 6: Click Save and Next to proceed to the next section, clicking Cancel will cancel the report and return you to the report listing.
- Step 7: The following screen will appear will you will Select the Items to be included in your report. In the Select, an Item selects the section(s) in each Data Area that you want to include.
- Step 8: Select An Item - Click on the dropdown menu. The following Data Area list will appear based on what data elements you previously asked to be included in Step 5 above.
You will select the Section within that Data Area that contains the field, not the actual field yet. The data area will appear in the listing with brackets “[ ]” and then each individual section within that data area will be listed below it. You can only select one section at a time.
- Step 9: Once you select a section in the Select An Item, the Unselected Column will populate with all the available fields in that section.
- Step 10: Select each Individual Field Item/Question that you want as a column in your report by putting a check in the box to the left of the field you want to include in your report. You can select one or all in that section.
- Step 11: Select the arrow pointing to the right to move that selection over to the next column. To move a selection back place a check in the box and click on the arrow pointing to the left.
- Step 12: Repeat steps 8 to 11 for each section you want to include items for in your report. Continue to select items from the drop-down until you have selected all of the data field columns for your report.
Your screen will look similar to this:
- NOTE: You will always want to include the Application Data – Status as a column in your report. When running a report all status levels are included and will need to be filtered out in the next step to Add Conditions, or once you run your report it can be exported to excel and filtered in excel.
- Step 13: If you need to move an item’s order, select the box next to the item you want to move, and click the up/down button until it is moved to the desired location. You can only move one item at a time, therefore, only that selection can be checked to move it.
- NOTE: Each item you pull to the right is the actual column that will be displayed in your report. Each row of your report will be each individual application showing how those question/item fields were answered within the application process. See below example of a generated report showing how each select field is a column.
- Step 14: Once you have selected all your columns you can click on the Save and Next button to go to the next page and set the filters for your report.
- Previous will bring you back to the previous screen
- Cancel will cancel this report and return you to the list of reports
- Step 15: The next screen is where you will enter the filters for your report.
After you add fields to your report you can add filters to the report.
Filters allow you to filter a group of applicants to only include those applicants in your report that meet a certain condition based on how they answered a specific question or based on their status within the application process.
For example: If an Admin wishes to see a report of only female applicants, you can apply a condition filter to only show applicants that answered Female to the Gender Question.
- Step 16: To add a New Condition Filter to a report select the Add New Condition button.
- Step 17: The following screen will appear. You will create a line for each filter.
- Step 18: Report Item: Select the Report Data Item that contains the Section Name you want to pull.
- Step 19: Section Name: Select the Section Name that contains the Field Name.
- Step 20: Field Name: Select the Field Name that contains the data you want to set a filter value on.
- Step 21: Filter: This is the condition of the field value you want to select. Select one of the following types:
- Contains: Include if the value field contains this value/answer within the value
- Equals: Include if the value equals this value/answer. (or is exactly this value)
- Not Equal To: Include if the value does not equal this value/answer.
- Is Empty: Include if the value field does not contain any information, the field is blank
- Is Not Empty: Include if the value field contains any information.
- Starts With: Include if the value/answer starts with this value.
- In: Include if the value/answer is in this listing of values.
- Min/Max Value: For numeric values only, enter the minimum to maximum range the value/answer should fall within, in order to include.
- Greater Than: For numeric values only, enter the value in which all value/answers greater than shall be included.
- Less Than: For numeric values only, enter the value in which all values/answers less than shall be selected.
- Filter Value: Enter the exact Value/Answer you want the condition to be set on.
- Step 22: Group: When entering multiple lines of filters, you will need to group those filters into specific groups to pull data effectively.
- Step 23: And/Or: Each time you add a condition and choose and/or in the last field a new search filter row will open up below for you to add your next filter.
When entering multiple lines of condition filters you will need to explain how each line of filters is to be related to the pulling of data. To add additional lines of filter conditions, you will need to simply choose either the “And” or the “Or” depending on how the current line relates to the next line of conditional filtering. On your last line of filters leave this blank, failing to do so will create an additional line of empty conditions and cause the report to not pull data. If you accidentally add this additional line, you must delete it by clicking the “Delete” next to that line, prior to clicking finish and running your report.
Example 1: Filter Female applicants AND Filter Submitted Status – it will only pull applicants that answer Female for Gender and have a Status of Submitted. Both those condition filters must be met for the applicant to pull into the report.
Example 2: If you use “OR” then the condition filter will pull on either or and therefore will give you all applicants that have submitted despite being female or not. In reports “or is mostly used within the same group if pulling on the same question and multiple choices for that question are being filtered.
You want all female applicants that are pending submission that attended King George High School or Massaponax High School:
- Step 24: Once you have finished entering all your filters for your report, select the Finish button.
The Finish will generate your report and bring you back to the report listing.
Previous: This takes you back to the previous step to add columns to your report.
Cancel: Cancels the report and returns you to the report listing.
1.1.2Additional Examples of Setting up Grouped Conditions in Ad Hoc reports
Criteria for this funding source equals:
- Must be a Graduating High School Senior
- Residing in either Fulton County or Miami County
- Attending North Miami High School, Rochester High School, Tippecanoe Valley High School, Private School or Home School
Criteria for this funding source equals:
- Must be a Graduating High School Senior
- Attending Peru High School
- GPA 8.00 or higher