Bars, lines and pies, oh my! The Analytics experience allows you to create visually appealing charts representing scholar data. Share how many H-1B scholars filed Premium Processing cases. Or which scholars are starting within the next 15, 30, or 60 days. This is just the beginning of the possibilities with Analytics!
Before getting started, it's helpful to note that the Analytics tool operates differently from classic reporting tools in Terra Dotta. You'll now be focusing first on defining what information you want and need from, for example, your scholars. These data points might be J-1 scholar name, start date, and SEVIS Status. Once you've defined what data you're looking for and determined how you wish to group this data (e.g. first by SEVIS Status, then start date), you'll then have your query. Then you'll be able to apply filters based on the individual needs you have and create your desired report. In this way, it is possible to use one query from which you can create multiple reports based on how you filter.
This article discusses the following topics related to Analytics:
Additionally, this article presents a high-level view of how report building may be experienced. We look forward to adding more updates to this article as Analytics continues to take shape.
Permissions & Access
The Analytics appears in the menu after clicking on the Hamburger Menu icon in the upper left corner of the page, which is accessible by any ISSS Admin. Specific access to Analytics requires users to have the appropriate permission(s).
There are two permission options:
- Report (Create/Edit)
- Query (Create/Edit)
Report (Create/Edit) only allows the user to create and edit reports based on queries shared with them.
Query (Create/Edit) only allows the user to create and edit queries.
If a user does not have either of these permissions, then they will only be able to view the respective queries and reports that are shared with them.
Important Note: The permission to create queries allows that user to see all data within the site. Therefore, the Query Permission should only be granted to those users who need to have access to everything.
Accessing Analytics
Once in Scholar Admin, navigate to Hamburger Menu icon to access the builder.
From the Analytics home screen, access a full list of your queries and reports.
Creating Queries
A maximum of 100 queries is allowed per site. To create a query, click the plus sign in the lower right corner of the screen. From the Query Builder page, you will add a title and, if desired, a description of your query.
To continue, choose your desired product. For Scholar, you will choose "Scholars".
From here you will choose your data source from which to build a query. For Scholar, select the "Scholars" data source.
From step 2 "Fields", you will first choose the fields, or data points, in the left-hand box that you wish to include in your query and then choose how you wish to group them using the right-hand box.
"Fields" and "Group By" options display in alphabetical order. Clicking the box to the left of a category will select all data points within it. Clicking the carrot will open the category, allowing you to choose individual fields within this category.
After choosing the fields required for the query, choose how the query should be grouped. If the end result is a table of data to be exported, a grouping is not required. However, if the goal is to create a chart with the data, such as a bar chart or line graph, a grouping must be selected.
Within the "Fields" section, you can determine how the results should be sorted. You will also see the order in which they're listed in the group by section (denoted by the number to the left of the sort option).
With the "Group By" section, the type can be changed by clicking the option.
Adding a name at any time, as shown in the screenshot below, will remove the red "Title" (showing it's required).
If a numerical value is selected, such as a rating or a numerical response to a question within a form, then the following calculations are also available.
After selecting the fields for the query and how you wish to group the results, you can filter to a particular subset, such as SEVIS status, host department, or start date, by clicking "Add filter". Filters are optional and are a way to limit the data set users can see if you share the query with others. Simply filter to include the information you want others to be able to access.
Simply type to start your filter search. Be sure to click "Apply" to select the appropriate filters. Once your filter is selected, you can choose the desired values. Multiple filters can be applied.
After filtering to the desired subset, this query can be shared with an individual or a group currently in the system. If the query is to be shared with someone not yet in the system, they should be added to the appropriate permission group first. This step in the query building process can be edited after saving it.
Once the query is configured, it can be saved or it can be run to immediately view the results (running will automatically save the query).
If a title has not been entered prior to clicking "Save" or "Save & Run", the system will select a name in order to save the query. This name can be edited.
Clicking "Save" in the "Query name" widget will save or save and run the query, depending on what was initially chosen. "Save" will take the user back to the main "Query" page while "Save & Run" will take the user to the "Query Execute" page, which shows the results in table format. From here user can go to the main query page, edit the query configuration, or create a report using this query.
Creating Reports
Creating Reports
With an existing query, you can create a report. As an admin user, you can create a report in one of the following ways:
- "Query List" Tab: Select Create a Report from the triple dot "More Actions" menu of your query in the "Query List" tab
- "Report List" Tab: Click the "+" icon in the lower right of the page to access the Report Builder.
To create a report from the "Report List" tab:
- Click the "+" plus icon in the lower right corner of the page. This action opens the Create Report interface. To build a report, it is required to name your report, and select an existing query:
- Click “Continue” in the lower right corner to generate your new report.
Adding and Editing Charts
Once generated, the report page will display the report together with a range of options to manage the report and add a chart.
At the top of the page, the main header displays the name and description of the report, offering the option to edit the report name and description.
On the left side, information displays on any filters applied from the query. Below this section, a table showing the data appears, including a button to export the data.
In the center section, a column of buttons appears containing tools, including:
- Share Report
- Schedule
- Download: Available when a chart type has been selected, this button appears to allow
On the right side, a menu of options for chart and data table options includes:
- Chart Type: Offers the use of a visualization to better represent your data
- Chart Options: Set your X Axis and Y Axis and change the appearance of your chart by toggling on/off Axis Labels, Hover Values, Horizontal Lines, and showing only the Top ‘X’ amount of values
- Filters
- Table Breakdown: Toggle on/off the detail and the total of your table
More information on each of these features is included below.
Chart Type
Depending on the report, applicable chart types will be available as options.
Click on the dropdown Chart Type to select a visualization to better represent your data. The options that appear on your screen are dependent on the query you choose and how that query uses groupings.
Stacked chart options are available to use in situations where, for example, you have two groupings in your query. Choosing a stacked option allows you to not only select the two groups you want to show in the graph, but it also allows you to rename the X and Y-axis labels, managed under Chart Options in the side panel.
Chart Options
Under Chart Options edit Axis grouping and labels. The "Show" section provides the ability to further refine how you view your data.
- Toggle switches allow you to show or hide axis labels, hover values, values as percentages, and horizontal lines.
- The "Top X Values" toggle enables you to limit the data to show only the top 3/5/10/Custom values in your data set. Switching the ‘Top X Values’ toggle on gives you the option to show all other values under the label “Other”. These values are grouped together and shown in gray on your visualization.
Filters
Expand Filters to add and/or manage existing report filters. Filters are optional tools which allow you to trim down a large data set to a particular subset of that data, such as only those app cycles in 2021.
Filters are added to your report to include the information you want others to be able to access. Any filters automatically applied are inherited from the query used to build the report. If those filters need to be updated, that action must be done on the query level.
Filter options that appear in the menu that are related to your site's specific data (i.e. parameters, process elements). After a filter is selected, you are able to drill down and pick the desired value options for each filter.
When a filter is added, it is automatically part of a new filter group. Additional filters can be added to a filter group using the "Add Filter" button. Any filters within a group use AND logic.
Additional filter groups can be added by clicking the "Add filter group" button. Clicking on the pencil icon between filter groups makes it possible to switch between AND and OR logic.
Table Breakdown
Expand Table Breakdown to see options that only pertain to the data table: Detail and Totals. By default, both of these toggles will be switched on.
By switching off the Detail toggle, the table hides all excess information and only shows the data that is represented by the X and Y axes.
Deactivate the Totals toggle to ungroup totals in the data table.
Report Tools
The report builder interface includes tools for sharing and scheduling your reports. These options are located in the middle section section.
The four icons located in this area correspond to the following actions, which are further described below.
- Show/hide chart options
- Share report
- Schedule report
- Download
Share Report
The final step available in building a report is "Share". This is an optional step that an office might use if they only want to share a report with an individual or group and do not want to configure scheduling.
To share a report, click the share button in the side panel.
The share interface to appear on the screen.
This share interface consists of the following options and fields:
-
Notify Users by Email
- Enabling this toggle setting means that an email will be sent to any individuals or groups to which the report has been shared, notifying them that a report is available to view by logging into the site.
- If the toggle is not enabled, then the report will be available for viewing when the individuals or groups log into the site. However, no email notification will be sent to them alerting them of this share action.
-
Group
- Select from any of your site's staff groups.
- Ensure that all desired users are in the group before adding it as an option.
-
Individual
- Enter the email address of the desired user. Selecting the appropriate email address will populate the fields for "First Name" and "Last Name".
-
Add User or Group to Share
- Click this option to add another individual or group.
If any individuals or groups need to be removed from the share options, click on the "delete" icon.
Schedule
The next step in building a report is "Scheduling". This is an optional step that an office might use if they want to create recurring, automated notifications in which users are either notified that the report is available or sent an updated copy of the report.
To create a schedule, click the clock button in the side panel and wait for the Schedule modal to appear on the screen.
Within the Schedule modal, your first option to complete is to Enable Scheduling. This box must be checked off in order to use the scheduling feature for your specific report:
After this, you will be presented with the options to Schedule Recurring or Schedule Once. You will find details on these schedule options below:
Schedule Recurring:
Selected by default, this option allows admins to schedule reoccurring reports and define the actions that should take place with each run. If you choose to proceed with this option, you will be asked to complete the following fields:
- Start Date: The date on which the report will first be sent. By default, this will be the date on which the report scheduling is configured.
- End Date: The final date on which the report will be sent. Must be 365 days or less from the start date.
- Frequency: Select how often you wish the action for your report to occur. Choose from hourly, daily, weekly, or monthly. The hour listed will be the hour for which the action will occur. The default hour listed can be modified. The minimum frequency interval is every 4 hours.
Schedule Once:
Handy for cases in which you would only like to take action on a report once, selecting the Schedule Once option will prompt you to complete the following fields:
- Date: The date you would like your report and any configured actions to take place.
- Hr:Mn The time you would like your report to run and any configured actions to take place.
Actions to Do After Run
After configuring your scheduling options, admins can then determine the actions they would like to occur automatically after the report has run:
- Export File: A file will be ready for the user after logging back into the site.
- Send Attached By Email*: A copy of the report will be emailed to users.
- Send on Email Body*: The report will be emailed within the body of an email along with a link to download the full report. This makes it such that the recipient does not have to be logged into the site.
- Notify Ready*: An email is sent that the report is ready to view.
- Export to SSH Folder: A "Reports" folder will appear in the client's Terra Dotta-provided SSH folder with the report in a .CSV file. Offices should ensure that they've set up access to their SSH folder prior to utilizing this option. This includes working with Terra Dotta to ensure the required environment setting is defined. Otherwise, the export will not be placed in the SSH folder as expected. For more information, see the Transferring Data to Terra Dotta article.
-
Email Recipients in Report Results*: After selecting this option, you next step to indicate the sender address for the emails. You will be prompted to select from whom the report should be sent using the "Send Email From" menu. Select from the logged-in user or the email addresses configured for your account (i.e. Contact 1, Contact 2). :
For the To* field, select an email parameter. The options that appear in the To* dropdown populate from the fields included in the report. When building your report's query, you must include an email parameter as a field in order to use the Email Recipients in Report Results feature:
Proceed to populate the Subject* line and email body text field. For a more customized recipient experience, add report merge fields by selecting fields from the Report Merge Fields dropdown menu, and clicking the plus icon to the right of the dropdown. When all is configured, click Save.
Clicking Save and enabling scheduling will close the scheduling modal. The recipients in your report results will receive the configured email on the set date(s) and time.
Notes on Emailing Users in Report Results:
- This functionality sends one email per row of data, so if a user is listed for two or more rows, they will receive two or more emails. This is useful in some circumstances. For example, when sending out reminders for applicants who have multiple applications open, (as the mail merge will apply separately for each instance of the email). However, admins will want to be careful not to use this functionality with query fields that generate multiple rows of data per person, such as an itinerary field that creates separate rows per location.
- Emails sent using this functionality are shown in the Maintenance > Sent Email log. Watch for future improvements to add these emails to the Activity & Notes section of a Study Abroad application, as well as the Communications Log for ISSS.
*For actions which involve sending an email notification:
For actions which involve sending an email notification, you will be prompted to select from whom the report should be sent using the "Send Email From" menu. Select from the logged-in user or the email addresses configured for your account (i.e. Contact 1, Contact 2).
Then the email recipients can be added using the "Users to Attach" section (for the actions of "Send Attached by Email" and "Send on Email Body") or "Users to Notify" section (for the action of "Notify Ready"). Enter in the email address of the site user. Select the correct email address that appears, and this action will populate the fields for "First Name" and "Last Name". Use the "+" icon to add an additional user, or delete a user using the trash icon. Remember to save your changes.
Important Note: Recipients must be a current user of the system in order to receive emails.
Download
When a chart has been selected for the report, the download button appears.
Clicking this button will download the chart in a PNG format.
Data Table
The lower section of the report editor displays the data in a chart format.
The export button can be used to export the data in .csv format.
Templates
In addition to standalone queries and reports created by ISSS Admins, the Scholar module also features several analytics templates created and deployed to client sites by Terra Dotta. Analytics templates provide a baseline for commonly-used queries that can be used as-is or further customized by ISSS Admins to filter, sort and report on scholar data.
Access the templates by navigating to Analytics from your Scholar Dashboard hamburger menu, and select “Templates” from the All Queries box on the Query List page. A list of existing templates will appear.
Currently, ISSS Scholar analytics includes 4 templates. Then just click on the “Use Template” button to get started.
- All Scholars (does not include Inactive): a list of all scholars from all visa categories sort by name, excluding those in Inactive status.
- Active Scholars: all scholars with Active status in ISSS Scholar, sorted by name.
- Pending Scholars (includes Sponsorship Requests): a list of scholars both in Pending status and with yet-to-be-processed Sponsorhip Requests, sorted by name.
- All Scholars with Future End Dates: all scholars in all statuses with end dates in the future, sorted by name.
Each template is built with default fields and filters to capture the information described in the template name. ISSS Admins can add additional fields and filters or remove the default fields and filters as needed to customize the template to their needs when using it to build a query. To view the default fields and filters in a template, click on the template name and a list of existing fields and filters will appear. ISSS Admins can also run template query as-is by clicking the Run Query button located in the upper right hand corner.
To use a template to build a query, click on the Use Template button located to the right of the desired template name. This will open up a Query Builder, where ISSS Admins can customize the template by giving it a new name, adding and removing fields and filters, and sharing the query with other ISSS Admins. Once built as desired, ISSS Admins can save and run the query, as well as use it as the basis of a report. Each template can be used many different times as the basis for several different types of queries and reports. For more detailed instructions on building queries, please refer to the Creating Queries section of this article.