00:00:00 Introduction

Hello everyone, good afternoon. Thanks so much for joining us today. Today, we have our second installment of our Office Hours sessions. For those of you who haven't attended an Office Hours session before, this is something new that we're offering as part of our webinar series. We wanted to give you the opportunity to come to us with any questions that you have, any comments, or simply talk through any issues or challenges that you're encountering in your use of ActivityInfo. Rather than us delivering a formal presentation and telling you how to use ActivityInfo, this is more an opportunity to hear from you. We are here to provide answers, help you in your implementation, and guide you.

For those who don't know me yet, my name is Jeric Kison. I'm the Director of Customer Success here at ActivityInfo. Joining me on this call today are my colleagues: Ganesh, who is an Implementation Specialist, and Rose, who is our Marketing Specialist. For these Office Hours sessions, we thought it would be good to pick a specific theme to guide our discussions. Last week we focused on database design, covering questions about configuring forms and considerations for good design. For this week's session, we thought it would be good to focus on reports. If you have any questions about how to configure a report, how to do certain kinds of analysis, or how you connect different sets of data in your database to get to the analysis that you need, this is the forum for you to ask those questions.

We can keep it fairly informal. If you have a question or a comment, feel free to post them in the Question and Answer section on Zoom, or you can easily just raise your hand and we'll unmute you so you can ask your question live. While you are thinking about those questions, Ganesh and I thought it would be good to get the discussion rolling by covering some of our frequently asked questions regarding the reports functionality in ActivityInfo.

00:04:17 How do I filter my reports?

Starting off with the first question on this list: How do I filter my reports? This is a very common question. We are working with loads of data, and not all of those data points are relevant to the specific analysis that we need to do. Sometimes, you need to only display a certain subset of the full data set in a particular report. In ActivityInfo, we do have some filtering options available. I will switch over to a sample database to demonstrate how that works.

I have my training monitoring demo database here. To show you what data is contained in this database, we have a reference folder containing standard information about the trainings being implemented and the different training institutions. In terms of the actual results, those are captured in the forms we see here. First, there is a Beneficiary Outreach form which has a record for each training session implemented, showing who implemented it, when it was held, and a subform for the participants. Then there is a Post-Training Evaluation Survey where we have records for survey responses regarding participant satisfaction. These forms are linked to the reference forms, pulling in information like training institute and location.

If you wanted to summarize the survey results, I'm going to click on 'Analyze' to create a pivot table report. For my analysis, maybe I want to summarize the overall score respondents gave to the trainings. I'll drag the respondent's score field into the measures section and change the aggregation from a sum to an average. To break this data down, I might use gender. I'll drag the gender field into the 'Rows' section to see the breakdown between male and female. If I wanted to incorporate age range, I might drag that into the 'Columns' section.

Now I have a pivot table with cells for each demographic category. However, if not all of these categories are useful—say I'm only interested in the 41 to 60 age group—I can filter this data set. To do that, I right-click on the header for '41 to 60'. A box shows up with options to 'Hide this category' or 'Show only this category'. If I click 'Show only this category', it hides the other columns. You can create additional layers of filtering as well. If you only wanted to focus on males who are between 41 and 60, you can right-click on 'Male' and select 'Show only this category'.

You can repeat those steps as many times as needed to get the precise breakdown you need. Later on, if you want to display those hidden categories again, you can click on the 'Show hidden' button at the top. It presents the hidden categories in a grayed-out color. To undo the filtering, you can right-click on any of the categories and select 'Unhide all categories'. You can also do it the other way around by choosing to exclude certain groups by clicking 'Hide this category'.

00:14:03 Connecting different data sources into a single report

Another common question is: Can I connect different data sources into a single report? In many cases, customers have databases with many different forms because they are collecting different categories of data, but there might be relationships between these forms. You might need to do analysis that connects those different sets of data for a more integrated analysis. This is possible using pivot table reports.

Let's imagine a scenario where I want to combine different kinds of data. I have information about the participants who attended each training session, and I have data about their satisfaction. What if I wanted to do an integrated analysis that looks at each training session and compares the participation against the satisfaction? I'll start by creating a pivot table report from the post-training evaluation survey. I will show the average score broken down by the specific training participants attended.

To compare this against what the participation looked like, recall that the participation data was stored in a different form. You can link that other form as a data source to bring it into the same pivot table. Click on the 'Select forms' button. This reveals the full list of databases you have access to. You are not limited to the database you are currently in; you can incorporate data from other databases. I will navigate to the 'Beneficiary Outreach' form and check the subform that has the records of participants.

Now, on the left-hand side of the Report Builder, we have the fields for 'Beneficiary Outreach' and 'Participants list' included. I might want to cross-analyze the scores and the number of participants. I can add the count of all records in the participants form to the measures section. However, you might notice that the value reported is against a blank row; it's not recognizing the dimensions for the specific training sessions.

To fix this, I need to ensure I am using the field that connects the two forms together. Both forms are pulling from the same reference form for trainings. I need to replace the dimension from the survey form with the 'Training Name' field from the trainings reference form. By incorporating that field, I can take advantage of the fact that both the survey form and the participants form are connected using the same reference form. Now, I can see both the score and the count of participants mapped onto the specific trainings. You can further organize this by dragging measures and dimensions to see them side by side, allowing you to interpret the data more easily.

00:23:33 Summarizing dates by month and year

Another question we commonly get is: Can I summarize dates by month and year? In some cases, you have a field for the full date (year, month, and day) stored in a record. It is often useful to collect the full date, but you might need to do analysis at a higher level of aggregation. ActivityInfo has a date hierarchy feature to do this summary.

Let's go back to our pivot table. Say we wanted to group the respondents by higher levels of date aggregation. I will add the 'Date of birth' field to the rows section. By default, you have the full list of specific dates. If you want to do a time series analysis, you can convert that into a line chart. But if your analysis requires higher levels of date intervals, you can group them.

Click on the date field to open its settings. You will see the 'Date level' option. By default, it shows the specific date, but there are many options to choose from. I can switch that over from date to month or to year. The value in the pivot table will automatically be grouped according to that level. From here, you can use the data to create a bar chart where you can more easily interpret the variations between years. This is a kind of hidden feature that is very useful once you realize it is possible.

00:28:11 Automatically calculating scores from survey responses

Many customers use ActivityInfo for traditional surveys with Likert scale questions asking about satisfaction or ratings. You might need to summarize those responses quantitatively using a formula. This is easily implemented in ActivityInfo. In our evaluation survey, the questions are Likert scale questions ranging from "Strongly agree" to "Strongly disagree." You might have a formula that combines these dimensions into an overall score.

To do this, we configure calculated fields to generate numerical scores. In the form settings, we've created a calculated field for each question to convert the response into a numerical score using nested IF statements. For example, the formula looks at question one: if the response is "Strongly agree," assign a score of 5; otherwise, if "Agree," assign 4, and so on. We multiply the result by 100 to see finer gradations.

We apply that same formula structure to all questions, ensuring we reference the correct field code (e.g., Q1, Q2). After setting up calculations for each question, we can take a final total score. That formula is a simple matter of taking the sum of all those numerical scores and dividing it by six to get an average. This converts survey responses into a quantitative score, which you can then report on in a pivot table, aggregating by sum, mean, minimum, or maximum.

00:34:28 Q&A: Is ActivityInfo only for data analysis?

Edwin asks: "Is ActivityInfo only for the analysis of collected data?"

Jeric: Good question. ActivityInfo is not just for analysis. At its core, ActivityInfo is a relational database that allows you to manage and organize your data. As we've seen, you can create different sets of data and relationships between them for a holistic picture. You can also use ActivityInfo to collect data from the field, so your data collection and management processes are tightly integrated. You can use the built-in data entry forms or collection links to disseminate surveys. In effect, ActivityInfo is an end-to-end solution for your data lifecycle needs, starting from data collection, through data management, and finally to analysis and reporting.

00:36:54 Showing different data sets on a map

Another question is: How can I show different sets of data on a map? You might have data tied to geographic locations and want to discern differences between categories on a map. In our survey results, we have geographic information under 'Training location'. We want to plot where various training sessions are being implemented.

We have the training session information in a reference field, but the measure we want to display is the count of records. To see how respondents differ based on the training attended, we need to create different measures to add as layers to the map. We will convert the information about the training session into a quantitative measure using a calculated field.

I'll create a calculated field referencing the training name. I'll use an IF statement: IF(TrainingName == "Income generation activities", 1, 0). This creates a filter that selectively displays only responses where the training was "Income generation activities." This creates a new card in our layers. We can display this as an icon, for example, a financing icon.

We can repeat this process for another layer. I can create another calculated field with the same formula structure but select a different training course, like "English for business activities." I'll assign a different icon and color to this layer. Now, on the same map, we can see which locations had attendees for the English course and which had attendees for the income generation course. This combines different sets of information to easily glean different categories of data.

00:47:33 Q&A: Alerts for incomplete data

Muhammad asks: "Can we generate some sort of alert if a cell wasn't updated through a period of time? Like, the system will notify me whenever someone doesn't update the cell?"

Jeric: We don't yet have notification features built into ActivityInfo, although that is something we are looking into. For now, you can configure fields in your form to check the value in another field. You can create a calculated field that marks a record as incomplete.

For example, we can use the ISBLANK function. The formula would be: IF(ISBLANK(Q1), "Form incomplete", ""). This acts as error checking. If I edit a record to remove an option, that column dynamically updates to show the message "Form is incomplete." From there, you can create a pivot table report that summarizes the data to see how many records are incomplete and need action. You would need to actively check the records in your table or report to become aware of missing data.

00:53:42 Closing

That covers the questions we had prepared. Thanks so much everyone for taking the time to join us today and for your engagement. Hopefully, what we covered gives you more confidence in building your own reports in ActivityInfo. We will make the recording available via email and on the website.

We have another Office Hours session scheduled for next week with a different theme: overall management of your database and change management within your organization. If you have questions related to those topics, feel free to sign up and submit your questions ahead of time. Thank you everyone, and we hope to see you again in the next one.