Custom queries are reports designed by users and built by DevResults staff to provide folks with additional ways of organizing program information. While DevResults offers many built-in tools for examining program information, there might be some way of looking at your work that can't be generated by existing tools. Custom queries can be used to structure information in a particular way, or to consolidate information from multiple pages in one place. Once a custom query has been set up, you can run the query on-demand and either view the results in the browser or in an Excel download.
Note: Custom queries can only be used to organize or assemble program information in ways that can't already be produced by the software. DevResults does not build custom queries for indicator results data. To design custom reports for indicator results data, check out Matrix for single-indicator reports or MultiMatrix for reports with many indicators.
In this article, you can learn how to:
To view a list of all custom queries, go to the Tools menu and click on Custom Queries.
This will open the Custom Queries index page, which displays all the custom queries in your site that you have access to. From the Custom Queries index, you can download the query results in Excel by clicking the Export button, or you can view query results in your browser by clicking on either the name or the description of a query.
When viewing query results in the browser, you also have the option to Refresh Results. Typically you do not need to refresh results as they are generated each time you open a query, but it can be useful to re-run a query if you or a colleague have made recent changes.
Generally, any user group that has been assigned at least View permissions for custom queries (by default, only Owners) will be able to access and download query results. If multiple user groups are assigned View permissions, you can choose to limit specific custom queries to certain groups (see Establish Custom Query Permissions below for more detail).
All DevResults sites come with a standard set of custom queries; these queries are based on frequently requested information. Users can request additional custom queries at any time. In order to add a new query, you'll work with one of our Data Scientists to confirm your information needs, determine how the results table should be structured, and decide what data it should contain.
To request a new custom query, download this template and follow these steps:
- Row 1: Enter the column headings as you'd like them to appear in the query results table.
- Row 2: Tell us which page in the site this information should come from (e.g. Activity Index, Activity Overview, Activity Geography, Indicator Definition, etc.)
- Row 3: Tell us which field on that page the information should come from (e.g. Code, Reference #, Tags, etc.)
- Row 4: Give an example of how this information should appear. (e.g. If you're expecting a list of something, should the items be separated by commas?)
- Email this template to email@example.com with the subject line: Custom Query Request. Let us know which DevResults site you want the query for and what you want the query to be named.
- Include as much information as possible about the logic that the query should use. For example, it's insufficient to say "only include local activities." What defines a local activity? Is it an activity tagged with "Local", or is "Local" a funding mechanism, or is "Local" a framework and you want to include just the activities mapped to indicators in that framework?
- We will review your request and get back to you with any clarifying questions, then build the query, and make it available in your site.
Please note: in general, it takes us up to 10 business days from your initial request to return a finalized custom query.
Establish Custom Query Permissions
Access to custom queries is granted at the group level. Any group that needs to access custom queries must have View access to Custom Queries in the group's permissions page. Groups without this permission won't see the Custom Queries option in the Tools menu. By default, only the Owners group is given these permissions, but Owners may assign them to any other group. See this page for instructions on editing group permissions.
While controlling general access to custom queries is usually sufficient, you may wish to make different reports visible to different groups. For example, while generally Managers should be able to access custom queries, there may be specific queries that should be limited only to Owners.
Owners can edit permissions for individual reports using the Permissions button for each Custom Query.
This will open a pop-up to set permissions (by default, "All groups with permissions to Custom Queries" is selected).
To limit this query only the Owners group, click on the circle next to "Only selected groups" and then remove any groups that should not have access to this specific query (n.b. that Owners is always selected).
Click Done to close the window; the permissions will now be updated, and you can confirm this by looking at the Groups column in the Custom Queries index.
Didn't answer your question? Please email us at firstname.lastname@example.org.