When you include data from ActivityInfo in a Power BI dashboard, Power BI will retrieve the data through ActivityInfo's Query API.
While Power BI requires a data in rectangular, tabular format, ActivityInfo forms can be more complex and can include references to other forms as well as sub forms. For this reason, the Query API gives you control over how an ActivityInfo form is transformed into table for Power BI.
The Default Query
ActivityInfo provides a "default" query for each form that attempts to provide a form's data in a format most commonly needed by users. The "Default Query" can be accessed via the following URL:
https://www.activityinfo.org/resources/query/form/{formId}
Where {formId} is the alphanumeric id of the form, such as c1y5yyukfwb6x077. You can find the Form ID in the URL of your browser, or you can find it by selecting "Export", and then "Export via the API", and then "Query all fields as JSON"
The default query will include:
- The Record's ID in a column called "_id"
- The Last Edit Time of the record in a column called "_lastEditTime", as the number of seconds since 1970-01-01
- Two columns for Geographic Point Fields in the form "{field name}.latitude" and "{field name}.longitude"
- One column for each text, quantity, single select, serial number, date and other simple fields
- One column for each multi-line text field, truncated to 128 characters
- One column for multi-select fields, with the selected items separated by commas.
- One column for subforms, showing the count of subrecords
- A column for each key field in a referenced form, in the form of "{reference field name}.{field name}"
- A column for each field in the parent form, in the format "parent.{field name}"
If a field has been given a code in the Form Designer, then the code will appear in the column, otherwise the field's full label will appear.
Querying subforms
When querying a "parent" form, the resulting table cannot include records from any sub forms, as this would not fit in the tabular format expected by Power BI. If you want to include data from sub forms, then you must query the subform itself from Power BI.
You can do this by navigating from the parent form to the sub form and copying the query URL from the subform:
This will include ALL of the sub records in this subform, even though those that belong to a different parent than the current selection:
Customizing the Query
ActivityInfo's Query API allows you to customize the columns you receive from the API. You can do this by using "query parameters" of the URL in the column_name=field.
For example, if you want to only query month and the number of kits from sub form above, you could compose the URL:
https://www.activityinfo.org/resources/form/cn3wkxykmbwk6t59/query?reporting_month=month&total=KITS
This will yield the following table in Power BI:
The first part of the parameter, before the equals sign, is the name that will appear in Power BI. The value after the equals sign can be any valid ActivityInfo formula, including a field ID, code, or a formula like "MEN+WOMEN". Note that query parameters must be URL-encoded, so a the formula would "MEN+WOMEN" would need to be written as:
https://www.activityinfo.org/resources/form/cn3wkxykmbwk6t59/query?total=MEN%2BWOMEN
Using the Table View to build a query
The ActivityInfo Table View provides a convenient way to construct a custom query. Click the "Select Columns" button to open the column selection pane. You can then drag new columns onto the table, rename columns, and add a calculated fields to your table:
Once you are satisfied with your table, you can choose "Query selected fields as JSON" from the Export Menu:
Default URL
The default method generates a URL which encodes your selections in the URL string.
- Select "Default URL" and then click the "Generate URL" button.
That will give you a URL that includes precisely the fields you have selected:
https://www.activityinfo.org/resources/form/cn3wkxykmbwk6t59/query?Record+ID=cn3wkxykmbwk6t59._id&Province=c4q7r1xkmbwj7942.c40qj9zkmbwjrmy3.E00000012490000000001&TOTAL=MEN+%2B+WOMEN
ActivityInfo's server cannot accept URLs longer than 2,048 characters. When you construct a query which generates a URL in excess of 2,048 characters, then you will be prompted to use the "short" URL option instead.
Short URL
The short URL method generates a smaller, which does not encode your selections in the URL string. It also allows you to use the API to query for tables with a large number of fields or with very long column names.
The short URL method produces outputs similar to the default URL method.
- Select "Short URL" and then click the "Generate URL" button.
