00:00:10 Introduction

Jane: All right, so 3:00 on my watch and change, I'll go ahead and get started. Hello. Today's webinar is "Designing reports with Pivot tables in ActivityInfo." My name is Jane, and I just joined ActivityInfo as a Marketing Specialist and I will help coordinate the webinar today. So, today, together with Jeric Kison, our Implementation Specialist, we will be hosting this webinar broadcasting from the Netherlands, and so thank you very much for being here.

Before joining ActivityInfo, Jeric worked as a Monitoring and Evaluation Officer at Pilipinas Shell Foundation, Inc., where he led a project to develop the organizational M&E system, which included the rollout of ActivityInfo as the organization's new Information Management System. It is very interesting now that he is with us as the Implementation Specialist, bringing together his experience on the ground and passion for data to help customers achieve success with ActivityInfo.

Now, before we start, I would like to share some housekeeping rules for everyone. Your microphone is muted, and you should all be able to see the shared screen. The webinar is recorded, and you will receive the recording after the webinar. The recording will also be available on our website in a few days' time, where you can also find recordings of previous webinars on M&E and using the software ActivityInfo. During the webinar, you can send us your questions via the Q&A session, which you can find in the toolbar at the bottom of the screen. This makes it easier for us to keep track of the questions. So please put your questions in the Q&A instead of the chat. Now I would like to give the floor to our speaker, Jeric.

Jeric: All right, great. Thanks so much for that introduction, Jane. Thank you everyone for joining us in this webinar today. Really excited to be here and happy to welcome people from all over the world. We're really excited to be talking about Pivot tables in ActivityInfo today. We've got a poll going on and I can see most of you have actually already participated in that poll.

Just to warm up, we asked: how long have you been using ActivityInfo for? It looks like actually there's quite a good distribution, and for about a third of you, you've already been using ActivityInfo for over a year. So thanks so much for your loyalty and your commitment to ActivityInfo. But for most of you, you've spent less than a year using the tool. So perhaps there's quite a few features that you have yet to discover, including Pivot tables. And so this will be a great opportunity for you to learn more about what the platform can do too.

Onto this second question, we asked: how familiar are you with pivot tables? Most of you had responded that you're somewhat familiar; you've used pivot tables on perhaps a few occasions. So, that's great. So some of the concepts here might already be familiar and intuitive to you, but you might learn some new things along the way as well. And even for those who are very familiar, we can explore how pivot tables in ActivityInfo have some unique features that you may not have known before. And for those of you who aren't familiar yet, this will be a great opportunity to get your feet wet in the world of pivot tables.

00:04:57 Agenda and overview

We've already started with our introductions and our housekeeping items. We'll then move on to just a very brief overview of what pivot tables are, why they're important for us to have in our toolbox, especially for monitoring and evaluation. We'll go over how pivot tables actually work within ActivityInfo within that section. And then for the bulk of this session, we'll actually go into a live demonstration of how we can configure all sorts of pivot tables and do all sorts of analysis using ActivityInfo. So, I'm going to run you through some scenarios, play around with some data, and we'll show you what you can do. Then we'll save some time at the end for an open Q&A session where you can ask any questions that we may not have covered yet during the session.

00:05:57 Why use pivot tables?

Why pivot tables? Pivot tables allow us to do all sorts of things with the data that we have. First of all, it allows us to summarize our data. We can do aggregations and take a step back and see what the bigger picture is telling us about our data. Secondly, we are able to make comparisons using pivot tables, so we might want to do some analysis where we're comparing a subset of our data with another subset and see if there's any difference between them. Thirdly, we can use pivot tables to identify broader patterns or trends in our dataset, and we can start to see whether there are any interesting changes that might be happening across a certain dimension, for example, over a time dimension.

We can do all of these kinds of analyses more efficiently using pivot tables. You can do perhaps some of these calculations through Excel or some other spreadsheet application. You can do calculations and aggregations and so forth, but using pivot tables allows you to do a big set of these calculations in an automated fashion altogether. So it will save you a lot of time and it'll get you to the insights that you're looking for more quickly.

For M&E, this is very powerful for us to have this in our toolkit. Looking back at the kind of analysis we can do, we can connect them to the kind of questions you can then answer as part of our work. For instance, we can use pivot tables to answer what the scale is of our reach across our different projects, which of our field offices have the largest workload, or how are test scores changing over time? So there are all sorts of questions that we find ourselves asking, needing to answer in our jobs in monitoring and evaluation that pivot tables will come in handy for.

00:08:33 How pivot tables work in ActivityInfo

Pivot tables generally comprise three main components. The biggest piece here are the measures. So these are the quantities that form the core, the basis of the analysis that you want to do. So, these measures can be any quantity, like the number of beneficiaries that you work with or the number of sites that you're operating in. And then beyond these measures, you might want to introduce into your analysis some dimensions. So these are attributes about your dataset by which you want to then analyze your data, or do some breakdowns around so that you can start to do these kind of comparisons that I was mentioning earlier.

Now, in a pivot table, you can arrange these dimensions either in rows or in columns, and in principle these work in the same way. But you'll realize, as you work with pivot tables more and more, that you might have preferences around which kind of dimensions you want to arrange in rows, and which you want to arrange in columns. So, these are the main components of a pivot table, and it's very similar to how pivot tables work in Excel.

Within ActivityInfo, we go beyond just organizing our data in a tabular format; we can then take the information in our tables and visualize them in different ways, so that we can glean these insights more quickly. There are three visualizations that are currently available in ActivityInfo, so you can convert your tables into a bar chart, a line chart, or a pie chart.

Finally, within ActivityInfo, we do have the ability to share the reports that you create. Of course, these reports are most powerful when they are shared with your stakeholders, and you can disseminate crucial pieces of insight with your colleagues or your partners. In ActivityInfo, you can share your reports internally by sharing with a select group of users that you identify as part of your database setup. Or you can share your reports externally as well. And you can do that by publishing a standalone web page, so that the reports that you created can be viewed by anyone, even outside of your organization and even without having to log in with an ActivityInfo account. Or if you have your own website, you can also embed these reports within your own website, and the interface on ActivityInfo allows you to generate a code to do that.

00:12:27 Scenario 1: IDP response

So for this part of the session, we're going to go into the actual platform. We'll take you through two different scenarios. The first scenario that I want to cover is this: say you are working for a project that is involved with an ongoing large-scale displacement of internally displaced persons, or IDPs. Now we have a database that's already been set up for us there, and the data that are available include data on tracking of assessments, of site monitoring data of the families that are currently in the settlements and the distribution of non-food items, or NFIs. And finally, data on an evaluation of how our response has been received so far.

We actually have a database template that is available for everyone to use. In case you want to follow along in this part, you can go ahead and download that template into your account. With this data available, there are some questions that we might want to ask. Who are affected? Who are these people who are being displaced, and what is their situation? Then you might want to look at: what then is the status of our response, and what progress are we making towards assessing these sites and distributing these NFIs to the affected families?

I'm going to go now into our database in ActivityInfo. We have just some basic reference data. We have lists of health areas and field offices that are being referenced in other places in this database. And we've got three data collection forms. First, we've got a form for assessment and distribution. This form collects information about all the different sites where the displacement is ongoing. And for each of those sites, we are collecting information about their assessment status, when those assessments have been made, and then the status of distribution, and finally quantitative information about how many distributions have been made and for whom.

There's a second form here for voucher fairs. So for each of the field offices that we have that are implementing our project, we have information about when that fair had been implemented and the families that had participated in those voucher fairs. And then finally, we have a third data collection form, the post-distribution evaluation. In this form, we are collecting information about what has been the impact of the project to those families after having attended the fair.

00:17:43 Creating reports for IDP response

So let's start to build some pivot table reports. To do that, I'm going to navigate into my reports section in ActivityInfo. I've already created a notebook for this dataset and for the IDP response. So to start off, I'm going to show you what kind of pivot tables you could create using the data that's in this database.

Recall those questions that we asked earlier. So we wanted to ask who's affected, so I might create this table that looks at the number of families and looks at where those families are situated based on which field office they are closest to. We can also take a look at the breakdown of how many families there are based on the duration of their displacement, and I can make comparisons based on these groups. Now, the second question that we had asked was, "What is the status of our response?" So, I might create a chart that looks at this time series over the months that have passed: how many sites have we actually assessed as part of our project? Another thing I could do is create this bar chart that looks at distribution, so I can see what the status is across our field offices: how many families have actually received? And if I look first, it's how many families are actually present in the site? And we can use this data to see where there are gaps and where we need to bolster our efforts around distribution.

So now, I'm going to attempt to recreate this notebook and configure each of these charts. I'm going to click on 'Add notebook' here, and the first step from here is to select your data source. I've selected the IDP response database, and I'm going to click on 'Done'. From here, I can see the report design toolbar on the left-hand side, and it shows me the different components that I can add. So naturally, I will want to create a Pivot table.

I wanted to create this table that analyzes the number of families and breaks that down by the field office. So, I'm going to look for the form where I had collected the information about the number of families. That information was collected in this 'Site assessment and distribution' form. I'm looking for the quantity of the number of families that are present across all of our sites. So, I'll simply drag this field into our measures section. Now that we have this base measure, I can then add other dimensions. I wanted to see how many families were dispersed across the field offices that we have that are working on this project. So, I'm going to scroll up and look for the 'Field office' field. Now what I want to do is drag the field office into our rows. So there I have that total value has now been broken down across this dimension of the field offices.

I might want to also include the percentages to see how each of these break down across the field offices. So by doing that, I get this statistic column so I can see both the sum and the percentage for that measure. Now, this is a bit difficult to interpret in this way, so I might want to rearrange just a bit and move this statistic column to the columns so that I have sum and percentage side by side. So I have one row for each of the field offices. This is much easier for me to interpret. I'm happy with how this table looks. I'm going to click on Done.

Let's take a look at the next chart that we have. We have here a pie chart that has a breakdown of these families based on the duration of their displacement. The data was collected as part of the Families form. So what I can start to do is first of all, pull in the total number of families that have participated in our voucher fairs. Now I have another field here in my Families form that asks how long the family has been displaced, and the records here are captured as months. So, if I bring that in and put that in our rows as a row dimension, what I'll see here is actually a row representing each of the responses in the form. So for each month, we have the total number of families that have said they've been displaced for that particular month.

But this is quite a long list, and it's not so easy for me to quickly interpret what this is saying. So, what I might want to do is group some of these months together. So I might group them into two buckets, one bucket being families that have been displaced for five months or less and the other being displaced for greater than five months. So how might I go about doing that? If you notice here towards the end of this list of fields, there's an 'Add calculated field' option. So this is going to come in handy for this situation.

I'm going to create a calculated field. I want to find those families that have been displaced for five months or less. And so to do that, I can craft an IF statement similar to how you might do one in Excel, and I'm going to evaluate this field of how long the family has been displaced. And I want to first pull in those families that have been displaced for less than or equal to five. And if that condition is true, I want to append a 1 to that record, otherwise 0. Doing this 1-0 notation in this IF statement means that I'm going to be able to count those records when I then aggregate the number of families that meet this criteria. I'm going to label this as 'Families displaced 5 months or less'.

Now I'll do the same process for the flip side of that inequality, so the rest of the families. So I can do a similar IF statement. I'm going to bring in that field again, and this time instead of less than or equal to, I'm going to put in greater than 5. Similarly, append a 1 if that's true or 0 if that's false. I'm going to label this 'Families displaced > 5 months'. So as you can see, by doing a SUM on those records that have been marked as meeting that criteria, I can see here, now, the total number of families that have been displaced for five months or less. I can delete the total count measure and I'll be left with this breakdown between these two groups.

And so in my report I had that as a pie chart. So now the last thing for me to do is to convert this table into a visualization. So to do that, I will select this button here and select pie chart. But we'll need to do this final step of moving the measures to this axis so that the system knows that these are the series that should make up the whole in a pie chart. So there we have it. We have now a quick way to interpret the situation.

The third chart that we had here was a line chart, and that looked at the time series of how we progress with our assessments. The assessment data were collected in our site assessment form. So again, I'll start by bringing in, first of all, the count of all of our records. So this represents all of the sites that we are assessing as part of this response. And then, what I want to do is bring in the date of the assessment so that I can see when those assessments are being done. So I'm going to bring that into my Rows dimension here.

Note here that in the form, that field was configured as a full date. So this means that we end up with quite a long table of all the dates and when those assessments have been made. Now, a useful feature within ActivityInfo pivot tables is you can actually group these according to date hierarchies, using any date field. So I'm going to do that. I'm going to click on the Date of Assessment field, and you can see here the date level option. I think for this data set what makes more sense to me is to group them by month. So I'm going to select months, and now I end up with a much shorter and more manageable table that shows how many sites have been assessed for each month. Now, the final step, as we saw earlier, the data was presented as a line chart. So, again, I'm going to click on set report visualization and convert this into a line chart.

And then moving into the final chart here, we have a chart on this distribution. The information on distributions was collected in our Site Assessment and Distribution form. I'm going to navigate down to the quantities that I want to analyze. So here we had the families present versus the families that have received an NFI kit. So, I'm going to scroll down to find those measures. So first, we have the number of families present here. And then I'm going to drag in the number of families that have received an NFI kit. So as you can see, we can include multiple measures in a single table so that we can compare different kinds of datasets and see any patterns between them.

But I wanted to add another layer of analysis here, which is to compare these numbers across our field offices. So I'm going to scroll up to find my Field Office field, and what I then want to do is to drag that into my Columns dimension. So now I can quickly see here how the number of distributions look across our three field offices. Now, it's quite difficult to interpret these as just raw numbers, and I think it'll be easier for me to interpret using a bar chart. So I'm going to convert that into a bar chart. Another way to present this, actually, is to swap those two dimensions, with the field office on the x-axis and the measures in the series, so that what I can do with this view now is to look at each office and how each field office has performed against the number of families that are present in the site.

00:37:39 Q&A: Data types and qualitative analysis

Jeric: All right, so let's take a look. So there's a question here from Akram: "Must the measures section have a variable with a quantity data type?" So, it does work mostly with fields that are quantity fields, and that allows you to do the aggregations on that, such as getting the sum or average, but you can work with fields that are not quantity fields as well, and you can still aggregate them.

Of course, there are limitations to how you can aggregate them, but I can show you an example of what we might do. Let's see, for example, we have sites. So let's go back to our data set on the site assessment, and we might want to do some analysis based on the sub-prefecture. If you do include things like text fields into your measures, the pivot table will, by default, apply the aggregation that is most applicable to the type of field it is. So in this case, you can't really do a sum on those text entries, but what you can do is do a count on them, or a count distinct. So you can include that and convert that piece of data into a quantitative measure that you can then slice and dice using other dimensions.

And then Klaus asked, 'How do we add data labels on graphs?' That is a great question. That is something that we are working on as a feature on ActivityInfo, and we hope to make that advanced functionality on data labels available very soon. But for now, what we do have are the sort of standard labels on charts. You don't have the ability to customize them at the moment, but that's something that we are developing further.

Right, so Klaus asked, 'The notebooks are already there. Can you show us how to create such?' And so in terms of creating a notebook from scratch, you would just need to go to your Reports tab and then click on the 'Add notebook' button that appears at the top part of the navigation. Doing that, it will prompt you to select what data source you want to use for your analysis, and then it will take you to the report builder for the notebook.

And we can answer one more question. Klaus asks, 'Can we use pivot tables for qualitative analysis or reporting?' So maybe for this one, Klaus, maybe I can invite you to ask this question so that you can say more about how you envisioned doing some analysis using qualitative information.

Klaus: Yeah, it's basically a bit similar to what happened before. So it's not if possible when we can count different, how often one expression or string appears, but really also to show really qualitative content, right? So, for example, just, yeah, the result of the qualitative information that is captured within ActivityInfo. So it's not really possible, I have a feeling, with pivot tables so far, so that we—which we rather would need to just export the Table View and understand so far. So pivot table is not the right tool, basically, to analyze, for example, you have, I don't know, under one indicator, you have several entries that just describe different situations at different times. Yeah, and I want to just kind of, yeah, get an overview of that.

Jeric: All right, I get what you're saying. Thank you so much for your question, Klaus. And I will say, I guess there are limitations to that kind of analysis that you can do on pivot tables. They are geared towards more quantitative analyses. And so you might want to take this data set outside of the pivot table for further analysis. But what you can do using pivot tables is still try and manipulate your qualitative information in such a way that you can group certain cases of qualitative information together and then do some calculations on that.

So, in quite a similar way to how I did the calculated fields on the families and the duration of displacement, I might apply the same series, similar series of steps to categorize responses to a survey that asks about qualitative information, like the descriptions of their current situation. So I might use calculated fields to tag certain types of situations, perhaps as being positive, or other types of situations, perhaps being negative, assign, maybe a quantitative score to each of those responses, and I can do some aggregations based on those calculated fields. So that might be one way to try and get kind of a quantitative analysis from qualitative data that you have in your database. But again, I think there are some limitations to what you can glean from that kind of information using pivot tables, but at least it is a starting point.

00:45:28 Scenario 2: Global M&E indicator tracking

So let's take a look at another scenario, which might be a common scenario for many of our attendees today. So in this scenario, we're going to take a look at global M&E indicator tracking. So we have a database where we have some data available around a set of projects that we're monitoring. For each project, we might have some information about a logical framework and indicators that are being used to track progress on those logical frameworks. As part of that, we might have some information on baseline values, target values. And then finally, we have monthly reports that we might be collecting from our project teams. So in these reports, they might be submitting the actual results that will allow us to track our progress against those indicators.

So this is our scenario: what kind of questions might we ask? So we might get asked, how are we progressing against our targets, first of all? And secondly, how is each project contributing to our goals? So let's take a look at this particular scenario.

We've got first of all reference data. So here we have the list of countries that are participating in our projects. You've got other stuff, partners, and you've got a list of strategic indicators. And so we'll come back to the importance of this in this scenario later on, but suffice it to say, these are quite important for this particular program, where we have these global-level indicators that we expect our project-level indicators to roll up to.

And then following our reference data, we have this main form for projects here. We're collecting information about all of our projects, what they are and how they're being implemented. And you've got for each project a subform for logical frameworks. Here, we've got a list of the results that we want to achieve for the project, and for each of these results, you've got the measurable indicators that we want to track. So, as you can see here, for each indicator, we have some baseline figures and the project target figures. And then finally, I want to point out here to start is the monthly report subform here. This is where our project teams can report on the actual results that they've achieved in their projects for each month, linking back to the indicators based on the logical framework for that project.

00:50:44 Creating reports for global M&E

So let's recall the questions that we asked earlier. First of all, how are we progressing against our targets? So to answer that, I might create this table where I'm pulling in all of these indicators globally across all of the projects into one table, and then alongside, have our values so that I can compare for each indicator what the actual value is that has been achieved in each of our projects, and I can compare those actuals against the baseline and the project targets.

So, I'll open a new tab once again. And I will create a new notebook. So for this one, I'm going to select the Global M&E database. The first table that we wanted to do was just this master list of all of our indicators. So, I want to scroll down to where our indicators are saved. So, under our projects form, we had an indicators subform. So here, I'm going to bring in the list of the name of the indicators. I want those as our rows for this table. Now, nothing shows up yet because you haven't entered the measures yet. So, the next thing I want to do is enter the measures that I care about. So, baseline and project targets.

All right, so you've got those in, and again, because we're working with multiple measures, we might want to move those measures to columns so that it's easier to read this table. Now, if you notice, we don't have the values, the actual values in this form; they were captured in another form, our monthly reporting form. So, that's a great thing about ActivityInfo. Built into ActivityInfo is you can combine these different fields, even though they are, they exist in different forms and separate forms, so these can all be linked together. So I'm going to navigate to my monthly reports. And under that, we have our indicator-based performance assessment, and there is where our values are actually being recorded. So I'm going to bring that in as a third measure. So there I have my full table now. For each of my indicators, I have a baseline, a target, and the value.

Now the second question that we had up here was, "How is each project contributing to our goals?" So we want to create something like this. So let's go back to our notebook, create another pivot table. So, the main dimension that I wanted to analyze here was our strategic indicators. As you recall from earlier, these were being stored in our reference form of strategic indicators. So bring in the name of the strategic indicator and put that as a row. And again, our actual values were stored in our indicator-based performance assessment form; I'll put in the values there. And I only care about the indicators that are attached to our strategic indicators. I'm going to omit those records that have blanks in them.

And the next thing I want to do here is to bring in the analysis on the per-project breakdown. So again, the project information is saved in another form, if you recall that, in our projects form. So what I want to do now is bring in the project code, which we use to identify each project. And I will bring that into the columns dimensions. So I can see for each of my strategic indicators how each of my projects has contributed to the total for that indicator. Now, to more easily interpret contribution to a whole, I might want to convert this table into a pie chart. And there we have our visual that gives us a clear indication of how each of my projects is contributing to my overall global goals.

00:55:34 Final Q&A and closing

Jeric: In the formula part, we use the equal sign, and so now you don't—you didn't need to use the equal sign for that bit, and we do have quite extensive documentation on our website about the syntax for our formulas. They're very much similar to how you would configure them in Excel, but with just some minor differences.

Fiona asks about exporting. So let's take a look at the exporting features that we have available. So quickly, we have the sharing features that I described earlier, so you can share with users who are logged on to ActivityInfo, and you can access these settings here in our sharing/publishing interface on the upper right-hand side. So you can select the roles within your database with whom you want to share this, or you can publish, and this entire notebook as a stand-alone web page, or you can embed it in another website. We are working on expanding on our export features as well, so that you can eventually export your reports as PDF files or PowerPoint files. So please stay tuned so you can know when those additional features will become available.

If I click on 'Go to analysis', what will we get? So clicking on 'Go to analysis' will take you back to the report builder, actually, so you can then reconfigure your pivot tables as you wish, if you want to change some of the measures or add additional measures that you want to include into your analysis, you can go back into that report builder using 'Go to analysis'.

Chris asks about how could I add my own dataset into this system for analysis? Certainly, you can combine these templates with your own datasets as you wish, and so naturally, ActivityInfo works as a database at its core. So you can easily add information into the database forms that you've already configured using the 'Add record' feature, or you can import larger datasets all in one go into your database as well. If you are working with a dataset from Excel or other spreadsheets, for example, I can send a link in the chat to our documentation on importing data into ActivityInfo.

I can see some questions around editing how the reports are actually presented in the notebook, and especially with regards to the charts. So, at the moment, we are quite limited still in the kind of customization that you can do to the layouts of these charts and the placement of labels and the lengths of the columns, for example, but there are some enhancements that are in development that will allow for further customization of how you can lay out your charts and tables in these reports. So do stay tuned for those and additional features.

All right, so, and thanks for all those questions. I think there's quite a long list of questions that have come in, and, unfortunately, we've reached the end of the hour. We really appreciate you all dialing in and submitting those questions, but feel free to reach out to us in case you wanted to learn more about ActivityInfo, and if you already have an existing subscription with us and you're needing further support on configuring your activity, we're here to help you at any point. You can reach out using our help desk at support@activityinfo.org, and we'll be sure to get back to you right away with an answer to any questions that you might have.

Right. And so again, thank you to everyone. We'll be sure to share the recording, which will be posted on our website. And Jane, any final reminders before we close the session?

Jane: No, I have no reminders at all. I will surely send you all the email that will include the recordings to everyone, so no worries at all. Although the recordings on our website might take a few days, the recording will hit your inbox right after this is uploaded.

Jeric: All right. Thanks for that, Jane. And thanks again, everyone, and I wish you all a great rest of your day. Until the next webinar, I'll see you again soon. Bye everyone.