Tutorial: Add a Calculated Table
Before you start
- You have added a database using the Who's doing what where (3W) database template
- You have been assigned to a role with the “Add forms, folders and reports" operation granted on the resources that contain the data you want to analyze.
In this tutorial, you will use the Who's doing what where (3W) database template to add your first calculated table to a pivot table report. You will do this by combining multiple tables into a single table for consolidated analysis. For instance, you might need a summarized table from the WASH and Health sector forms showing the disaggregation of beneficiaries distributed by donors and partners across reporting dates.
It is possible that the structure of the WASH and Health sector forms made data gathering easier. However, you want to make the dataset convenient to analyze by integrating the sector, donors, partners, date, and number of beneficiaries into a single table. You can do this by using a calculated table with its table functions.
Add a calculated Table
- Go to “My Reports”.
- Click “Analyze”, then click “Add pivot table” to create a new Pivot Table report
- Select the Resources (Database, folder and forms) with the records needed for your report. From the Who's doing what where (3W) database template, we will select the “WASH form” and “Health form”.
- On the report design pane, you will click directly on "+ Add Calculated Table”
- Add the calculated table name. In this case, we are using the name: “Beneficiary Disaggregation Summary Report".
- In the left panel, you can find the ActivityInfo supported Functions and on the right panel you will find the WASH and Health forms and their fields .From here you can select the forms that contain the data you want to transform.
- To create a calculated table by combining the WASH and Health sector forms showing the disaggregation of beneficiaries distributed by donors and partners across reporting dates, we will now use the formula;
UNION( SELECTCOLUMNS(c5o9vzilk8habx32,"Individuals reached", totalBnfReached ,"Donor", donor.Name ,"Date", reportingMonth ,"Partner name", implementingPartner.Name,"Sector", Sector), SELECTCOLUMNS(c4vegjwlk7x6o613,"Individuals reached", TotalBeneficiaries,"Donor", donor.Name,"Date", reportingMonth ,"Partner name",implementingPartner.Name,"Sector", Sector))
Let’s break down each component:
- UNION(): This is the outer function that combines the results of two SELECTCOLUMNS functions into a single table. It stacks the rows from both SELECTCOLUMNS results on top of each other to create a unified table.
- SELECTCOLUMNS(): This is an inner function used to create a new table by selecting specific columns from an existing table or data source. It allows you to reshape the data by choosing which columns to include and giving them new names if desired.
- c5o9vzilk8habx32 and c4vegjwlk7x6o613: These are references to two different forms (data sources) from which you want to select columns and create a unified table.
- "Individuals reached", "Donor", "Date", "Partner name", and "Sector": These are the names we've given to the columns we want to select from the source tables. We are essentially renaming the columns in the resulting table.
- totalBnfReached, donor.Name, reportingMonth, implementingPartner.Name, and Sector: These are the actual column values we are selecting from the source tables. The SELECTCOLUMNS function is picking these values and assigning them to the corresponding column names we specified.
The formula is essentially taking two separate tables(forms), c5o9vzilk8habx32 and c4vegjwlk7x6o613, and creating a unified table labeled ““Beneficiary Disaggregation Summary Report”, by selecting specific columns from each of them and giving them new names in the resulting table. This combined table will have columns like "Individuals reached," "Donor," "Date," "Partner name," and "Sector," containing data from both source tables(forms), allowing you to analyze and compare the data across these columns.
- Click ‘done’.
- The table you have just created will now appear in the report design pane under the list of forms and fields.
- The fields that you have created as part of your calculated table will appear as fields in this report design pane.
- You can now add the calculated table's fields to your Pivot Table report. Drag the “individuals reached” to the measure pane, and as dimensions, drag the “sector”, “donor”, and “partner” fields to the rows, and “date” to the column; this will calculate to a table.
- View your calculated table
- You have successfully added a Calculated Table.