View source for Report Designer examples

{{Report TOC}}
This article describes several useful examples of using the [[Report Designer]]. If you are looking for examples specific for questionnaire reports, see [[Mobile reports for Resco Inspections]].

== Static image ==

Use static images for images that should not depend on the record(s) selected for the report. For example, you can add your company logo to the header block.

# Edit your [[app projects|app project]] in [[Woodford]], select [[Offline HTML]] from the '''Project''' menu, and upload your static image. In this example, we create a Reports folder and add the file image01.png. Publish your project.<br>[[File:Image in offline html.png|alt=Report designer examples: Static image (add image to offline html directory)|600px]]
# In Report Designer, select a cell in the header block and click '''Add Image'''. Set '''Binding Type''' to '''Constant'''. As '''Binding''', enter the path to your image. In our example Reports\image01.png.<br>[[File:Static image report designer.png|alt=Report designer examples: Static image (add image to report)|600px]]
# Synchronize the project and verify the result in the app.

{{Note|Content stored in Woodford is only available if you are using Resco Mobile CRM for generating the report. For example, such images are not available if you are using [[Resco Reports connector]].}}

== Dynamic image ==

Use dynamic images that depend on the record selected for the report. For example, consider a report for a single contact where you want to include that person's photo. The image is stored in the Annotation (Note) entity. The ID of the image points to the ID of the Contact. Ensure that the title (subject) of the image, for example, "ListImage", is unique.

::[[File:Dynamic image report designer.png|alt=Report designer examples: Dynamic image (add image - edit filter)|600px]]

We recommend that you add a [[Form#Add_media|Media tab]] to the [[form]] of the entity, where you can define the title (subject) of the note with the specific image/signature. This approach also ensures that there will only be one image with that note title.

=== Dynamic images on Salesforce ===

On Salesforce, you may run into the error <code>Invalid Query: Parent->Child links cannot be nested in SOQL.</code> This is a limitation of Fetch to Salesforce’s SOQL translation. Nested parent->child links are not allowed (basically subselect within a joined table). As a workaround, split this fetch in two. For example, sf_contentdocumentlink under sf_contentdocument.  First, get the Content Document by starting from the Content Document Link. Then, get the ContentVersion based on ContentDocument. It is slower, but it will get the job done.

== Multiply values ==

There are situations where you don't want to display two fields directly, but you want to perform an arithmetic operation and display the result. In the following unlikely example, we multiply the latitude and the longitude fields of your accounts and display the product in the report.

# Select the '''Body''' element of your report and click '''Edit Sources'''.
# Click '''Add''' to add a custom variable that stores the result of the multiplication. Enter a name and type, then click '''OK'''.<br>[[File:Custom variable in report designer.png|alt=Report designer examples: Multiply values 1 (create custom variable)|600px]]
# Click '''On Create''' and add a rule that performs the multiplication, then close the rules editor and variable editor.<br>[[File:On create rule report designer.png|alt=Report designer examples: Multiply values 2 (on create rule)|600px]]
# Add the field to your report.<br>[[File:Multiplication in reports.png|alt=Report designer examples: Multiply values 3 (add custom variable to report)|600px]]
# Verify the result in the app.

== Cell visibility ==

You can control the visibility of a text block or image block by setting the '''Visible''' property.

In the following example, we list all contacts related to an account in a repeater block. We're using the '''Visible''' property to hide the text block in the header section of the repeater if an account has no associated contacts.

=== Create the repeater ===
# Edit the report for your account.
# Select the body of your report and click '''Add Repeater'''.
# Select the repeater, move it to the appropriate location of your report and click '''Edit Sources'''.
# Select the '''Item''' and click '''Edit'''. Change the '''Entity''' to contact and click '''OK'''.<br>[[File:Add source for repeater.png|alt=Report designer examples: Create the repeater 1 (Add source for repeater)|600px]]
# Add the following filter, then click '''Save & Close'''.<br>[[File:Filter for repeater.png|alt=Report designer examples: Create the repeater 2 (Filter for repeater)]]
# Add the following text blocks to your repeater.<br>[[File:Repeater.png|alt=Report designer examples: Create the repeater 3 (add repeater to the report)|600px]]

=== Manage visibility ===

# Select the body of your report and click '''Edit Sources'''.
# Add a new variable (myContact) of the '''Type''' Fetch Result for the '''Entity''' contact. Select it, click '''Edit''', and add the same filter as above.<br>[[File:Filter for repeater.png|alt=Report designer examples: Manage visibility 1 (Filter for repeater)]]
# Add a new variable (myFlag) of the '''Type''' Boolean. Select it, click '''On Create''' and add the following rule:<br>[[File:On create rule report designer 2.png|alt=Report designer examples: Manage visibility 2 (On create rule)|600px]]
# Set the visibility of the text block in the header to depend on the boolean variable.<br>[[File:Cell visibility in report designer.png|alt=Report designer examples: Manage visibility 3 (Cell visibility)|600px]]
# Verify the result in the app. For accounts that have no associated contacts, the header cell should not be displayed in the report.

{{Note|If you need to achieve the opposite behavior, use an exclamation mark ("!") in front of the variable, e.g., <code><nowiki>{!myFlag}</nowiki></code>|Tip}}

== Automatic numbering of repeating blocks ==

In this example, we want to number the repeater blocks displayed in a report.
# Select the body of your report and click '''Edit Sources'''.
# Click '''Add''' and create a new decimal variable (count).
# Click '''On Create''' and initialize this variable to 1.<br>[[File:Initialize variable 1.png|alt=Report designer examples: Automatic numbering of repeating blocks 1 (Initialize variable)]]
# Close the rule editor and variables on body level.
# Select the repeater and click '''Edit Sources'''.
# Click '''Add''' and create a new decimal variable (count2).
# Click '''On Create''' and add the following steps.<br>[[File:Initialize variable 2.png|alt=Report designer examples: Automatic numbering of repeating blocks 2 (Initialize variable 2)]]
# Close the rule editor and variables on body level.
# Add the the second variable to your report.<br>[[File:Add count to repeater.png|600px|alt=Report designer examples: Automatic numbering of repeating blocks 3 (Add count to repeater)]]
# Verify the result in the app.

== Adding signatures to reports ==

Report Designer offers several ways of adding signatures to reports.
* On some reports, it is sufficient to use a static signature. In these cases, consider saving the signature as an image to the [[Offline HTML]] and then add the image to the report using Report Designer. See the section for [[Report_Designer_examples#Static_image|static images]] above.
* Alternatively, you may want to save the signature image as a note, related to a record. For this scenario, you can study the section for [[Report_Designer_examples#Dynamic_image|dynamic images]], or read our [https://blog.resco.net/2018/07/04/signature-questionnaire-report/ blog]. {{Badge|Blog}} 
* Resco Mobile CRM can be integrated with a [[DocuSign_integration|third-party solution from DocuSign]]. See how to [https://blog.resco.net/2018/05/02/please-sign-here-add-legally-binding-digital-signatures-to-forms-with-universign-integration/ Add legally-binding digital signatures]. {{Badge|Blog}}
* With the most recent addition to Report Designer, you can designate a place on your report, where you can add signature(s) and comment(s) at run time. This approach allows you, for example, to generate a report that the customer can view on your device, then sign it, and optionally add some comments. Once saved, the signature and comments are added to the report. This scenario is described below.

=== Signing reports at run time ===
[[File:Signature properties.png|alt=Report designer examples: Signing reports at run time (signature properties)|right]]
Report Designer can be configured to use enhanced signature workflow. It allows signing generated reports directly in the app. For the best user experience, we recommend combining it with appropriately configured [[Configure Run Report command|Run Report command]]. For inspiration, check the [[Report_Designer_examples#Save_and_close_signed_report_automatically|example in the next section]].

; Adding a signature
# Edit a report.
# Select an appropriate location on the grid, then click '''Add Signature'''.
# Configure the signature on the Properties pane:
#* Check '''Enhanced signature''' to enable the workflow that allows you to sign a generated report.
#* Use '''Binding''' to set the label of the signature on the report. If your '''Binding Type''' is a constant, enter text directly; if your '''Binding Type''' is a value, you can link to a record field.
#* In the '''Signature storage''' field, select the entity where the captured signature image should be stored. In the '''Label''' field, select the file name prefix for the image.
#* In the '''Report entity''' field, select the entity used for the report.
#* In the '''Comment field''' field, select the entity field where the signature comment should be saved. See also the notes about the signature comments below.
#* If you want the signature not to stay saved in the database, enable '''Discard signature after report is saved'''. The signature becomes part of the report but isn't saved elsewhere. This can be mandatory for legal reasons in some countries, and it can also save you database storage. However, the signer must sign the document anew if you need to regenerate the report.

; Multiple signatures
* You can add multiple signatures to one report.
* Make sure to use a distinct name for each of the captured signature images.
* Also, use a distinct field name for each comment.

; Signature comments
* Each person signing the report can also add a comment to the report.
* The comment is saved to a field defined in the properties of your signature, in the field '''Comment field'''. Leave the field empty if you don't want to allow comments.
* This must be a '''Single Line of Text''' or '''Multiple Line of Text''' type of parameter. The field must also be enabled in the Woodford project. App users must have permission to read, create, and update the field.
: [[File:Report comment.png|alt=Report designer examples: Signature comments (report comment)|300px]]
* This comment is not displayed on the report automatically, you have to place it, just like a regular text field, using the '''Add Cell''' button.

; See also {{Badge|Webinar|YouTube}}
* [https://youtu.be/q4_6DCfiMhk Configuration in the Report Designer] 
* [https://youtu.be/U-zciv-iwI0 Report signing in the app]

{{Note|Known issue: On Android devices, an enhanced signature is saved correctly; however, it is not properly displayed in the app. To see the signature correctly, select Open from the hamburger menu to open the document in an external PDF viewer (if available on the device).|Warning}}

=== Enhanced signature and reports from multiple records ===

With [[Releases/Spring 2025|release 18.1]], enhanced signature is also available for mobile reports that use multiple records as their source (e.g., use multi-selection and run report over all selected records). The following prerequisites must be met:
* The '''Source''' of the report template must be set to "Both".
* Signature(s) must be defined outside of the main repeater.

The following implications must be taken into consideration when using enhanced signature with multi-source records:
* When signatures are submitted, the signature and report are automatically attached to the first source record (note that signature can be automatically deleted once signature flow is completed using this feature, but it is not possible do delete the report automatically).
* The signature comments are added only to the first source record.

=== Save and close signed report automatically ===

In this example, the customer requirements were:
* Run the report from an entity form.
* As soon as the report is signed, save and close it.
* Keep the signature only as part of the report; do not save it separately.

This requires configuration in the Report Designer and in Woodford.

1. In the Report Designer, edit the report and set up the properties:
* Enable '''Enhanced signature'''.
* Enable '''Discard signature after report is saved'''.

[[File:Example-report-properties-with-signature.png|600px|example signed report]]

2. In Woodford, edit the form from which the report is initiated.
* Add the command "RunMobileReport" and select the appropriate report.
* On the '''Advanced report configuration''' screen, set '''Immediate action''' to "SaveAndClose" and enable '''Automatically save signed reports'''.

[[File:Example-with-advanced-report-config.png|600px|advanced report config example]]

== Add N:N entity to a report ==

If you have entities linked by an N:N relationship, you can access them in a repeater. In this example, we have the entities Competitor and Opportunity linked by an N:N entity Opportunitycompetitors. The procedure below explains how to add a list of related opportunities to the report for competitors.
# Edit the report.
# Add a simple repeater (not a child repeater).
# Select the repeater and click '''Edit Sources'''.<br>[[File:Variables.png|alt=Report designer examples: Add N:N entity to a report (create variable)|600px]]
# Select the "Item" and click '''Edit'''.
# On the Select Entity screen, select the "opportunity" entity, then click '''OK'''.
# Define the fetch filter using a linked N:N relational entity and the ID of the base record.<br>[[File:Fetch filter.png|alt=Report designer examples: Add N:N entity to a report (fetch filter)|600px]]
# Click '''Save & Close''' twice to return to the report.
# Notice that the fields pane on the left now includes fields of the opportunity. You can now use the fields in your report.

== Counting the tasks ==

In this example, we have a report for a single account. The report also includes a list of contacts, and for each contact, the number of tasks. 

Creating the contact list is straightforward: Just add a child repeater to the report. 

Displaying the number of tasks is a bit more tricky: There are actually two ways how to handle this. 

=== Brute force ===
# Add a Fetch variable in the repeater sources. 
# Use '''Set Variable''' to select only the tasks for the particular contact.
# Use '''Aggregates''' to calculate the count. 

Because the aggregate is calculated for every contact separately, this report might produce a high load on the server, and, depending on the number of contacts, it can be slow. 
 
=== Use Fetch Table variable ===
# Select the body of your report and click '''Edit Sources'''.
# Define the variable TaskTable. Use regid as the table key.<br>[[File:Task table variable.png|alt=Report designer examples: Use Fetch Table variable 1 (Task table variable)|600px]]
# In the Variables dialog, click '''Aggregate''' to define the aggregation properties.<br>[[File:Configure aggregates.png|alt=Report designer examples: Use Fetch Table variable 2 (Configure aggregates)|600px]]<br>The result of the query is a simple table, with the RegardingObjectId of each task and the number of times the same RegardingObjectId was encountered. In other words, we grouped the tasks by the RegardingObjectId and calculated the size of each group.
# To access the table, define a Fetch Table Lookup variable, however, this time on the repeater level:<br>[[File:Fetch table lookup.png|alt=Report designer examples: Use Fetch Table variable 3 (Fetch table lookup)|600px]]<br>The result is an object with both the regid and taskcount properties. This is useful if the Fetch Table has more aggregates (e.g., you want to calculate not only the count but also sum and averages).
# Add the field TasksPerContact.taskcount to your report.
# Verify the result in the app.<br>[[File:Result in the app.png|alt=Report designer examples: Use Fetch Table variable 4 (Result in the app)]]

== Custom fonts ==

You can add custom fonts to your reports. In this example, we use a custom barcode font to display product numbers as a barcode on a report.

# Add the font to your [[Offline HTML]] folder:<br>Using Woodford, edit an app project, select '''Offline HTML''' from the '''Project''' menu, and upload the new font to any folder. Save and publish the app project.<br>[[File:Add fonts to offline html.png|alt=Report designer examples: Custom fonts 1 (Add fonts to offline html)|600px]]
# Define a new style that uses the font:<br>Using Report Designer, edit the report, click '''Edit Styles''' to open the [[Style_editor#Report_Designer|style editor]], click '''Add''' to create a new style. Save the style and return to the report.<br>[[File:Create a new style using the font.png|alt=Report designer examples: Custom fonts 2 (Create a new style using the font)|600px]]
# Add the barcode field to the report:<br>Using Report Designer, edit the report to add a new field. As the '''Binding''', use the format <code>*{entity.fieldname}*</code>, and select the appropriate style. Save the report.<br>The syntax with asterisks as start/stop characters applies to Code39 barcode format. Other formats may use different delimiters. <br>[[File:Add the field to the report.png|alt=Report designer examples: Custom fonts 3 (Add the field to the report)|600px]]
# When you synchronize the app and run the report, you should now see the field printed as a barcode.<br>[[File:Barcode font in action.png|alt=Report designer examples: Custom fonts 4 (Barcode font in action)|600px]]

{{Note|Various barcode standards use start codes, stop codes, and/or checksum to ensure the code isn't tampered with. Resco reports do not include this function out of the box; you must calculate any necessary checksum manually and include them in the barcode to make the barcode compatible with standard barcode scanners. }}

== Conditional formatting with more than two styles ==

This example explains how to apply a custom style (color background) to a cell depending on its value. We have a list of related contacts on an account report and we want to apply different colors to the contact names based on the value of their city.

[[File:Report with dynamic styles.png|alt=Report designer examples: Conditional formatting with more than two styles (overview)|600px]]

# Define the necessary styles. If you decide to modify the background color of a cell as we do in this example, remember to disable transparency.<br>[[File:Defining a style with green cell background in report designer.png|alt=Report designer examples: Conditional formatting with more than two style 1 (Defining a style with green cell background in report designer)|600px]]
# Define new boolean variables that will control the visibility of the right color. In our example, we have one field (contact.name) that we want to highlight with one of the three styles, i.e., we need 3 variables.<br>[[File:Variables for color styles.png|alt=Report designer examples: Conditional formatting with more than two style 2 (Variables for color styles)|600px]]
#* Select the repeater that includes the field you want to highlight (or report body) and click '''Edit Sources'''.
#* Click '''Add''' to add the three new variables (type: Boolean).
# Select the variable at the bottom (in our example, UseNormal) and click '''On Create'''.
# Enter a rule with business logic that controls which colors to use.<br>[[File:Rule that controls which colors to use.png|alt=Report designer examples: Conditional formatting with more than two style 3 (Rule that controls which colors to use)|600px]]
# Save all changes in the rules and variables editor and return to the report structure.
# Add new rows to the repeater grid. Drag contact.name to the grid 3 times. For each, select the '''Style'''. As '''Visible''', enter the matching variable.<br>[[File:Add multiple rows with contact name and apply style to each.png|alt=Report designer examples: Conditional formatting with more than two style 4 (Add multiple rows with contact name and apply style to each)|600px]]
# As the final step, place all three contact.name fields to the same cell. One of the ways of doing this is to set '''Column''', '''Row''', '''Columns''', and '''Rows''' to the same values for all fields. The fields will be on top of each other, but thanks to the rule, only one of them will be visible.

== All questionnaires associated with a record ==

The following report example displays the content of all inspections (answered questionnaires) associated with a particular Work Order record. It is designed for field service scenarios and uses the resco_questionnaire answer entity, but it can be adapted for other use cases.

[[File:Inspections-results-for-work-order.png|600px|alt=inspections results for work order]]

* The report has a very simple and elegant structure. Most of the work is done in nested repeating blocks. The first repeater identifies all questionnaires associated with a record. For each questionnaire, you display all root questions, followed by all groups and their questions.
* No questionnaire-specific logic is used. This means that the report is generic and can display any questionnaire.
* More clever logic is hidden when populating the answer field. The Visibility property displays the answer text, the answer image (annotation), or "-" if the answer is not supposed to be visible on reports.

[[File:Report-multiple-values-in-a-field.png|600px|alt=multiple values in one field, controlled by visibility.]]

You can download the report from the wiki: [[:File:Inspections Summary Report.zip|Inspections Summary Report]]. Unzip the file and import the XML report template into your Report Designer.











{{Feedback}}
[[Category:Resco Cloud]]