To understand the basics of Results reporting first, click here.
By default, objects must be from the same report query in order to be pulled into the same report table. Otherwise, Apricot Results Reporting (ARR) will display the following error message: "The data objects are not compatible."
If you've created multiple queries, you may want to use objects from each of them throughout the report in various tables. To do this, you must create a kind of object called a merged dimension. Merged dimensions are created from a common object found in the involved queries that make the data objects compatible in a report table.
Follow the steps listed below to learn how to use multiple queries in a single report table.
Step 1: Add a common object to your queries
First, we must determine what object lies in all the queries we want to report on in the same table. The most frequently used objects are identifiers such as Record ID, Parent Record ID, and Link to Record ID.
Note: If you want to create a merged dimension between a Tier 1 form and its Tier 2 form, the commonality between them will always be the Tier 1 form's Record ID object and the Tier 2 form's Parent Record ID object.
In our example report, we've added the Tier 2 Services form's Parent Record ID to our first query and the Tier 2 Program Enrollment form's Parent Record ID to the second query.
Add your decided identifier into the Results Objects section of each query, then run your queries.
Step 2: Merge the objects
In the Document Objects panel on the right, expand each query that contains the common identifier.
Hold the Ctrl button on your keyboard and click on each instance of the identifier throughout the queries to select them. Then, click on the three-dot icon next to one of the highlighted objects and click Merge.
The report will refresh, and you'll now find a Merged Dimensions section above your Variables in the Document Objects panel. The dimension listed is automatically named after the first identifier object selected from the queries.
Click the three-dot icon next to its name and select Edit Properties to rename the dimension. Note: Spaces are not available when changing the merged dimension's name.
Step 3: Create a detail
Next, we add a variable that references the merged dimension. This variable is what's added to a report table in order for objects from different queries to coexist within it.
Open the variable editor by clicking '+ Add Variable' above your Document Objects.
Name the variable after the data it will display within the table (ex: Participant Record ID). Then, expand the Qualification dropdown field and select Detail.
An additional dropdown field named 'Associated dimension' will populate below it; click the three-dot icon next to it and select the newly-created merged dimension.
In the formula text box, edit the following formula to reflect your selected objects then select OK to save your variable:
= [T1].[Record X Values] WHERE ([T1].[Field Name] = "Name of field")
T1 is the name of the query from the Tier 1 form and X is the type of value (text, numeric, date, etc).
The new detail will appear in the Document Objects panel under Variables, which you can now drag into the table of your choice. Objects from other queries can now be pulled into the same table.
What else do you need help with?
Not what you're looking for? Navigate to overview