Add Custom Report
Open the Custom Report Wizard
- Navigate to Reports.
- Click Add Report (+ icon) and select Add Custom Report.
- Provide a name, description (optional), and data source (Salesforce by default) and click Next.
Select table
- Select the object you would like to query from the Object dropdown list. The object will become the table that defines the rows and columns to view, sort, and filter report information.
- Click Next.
For a full list of available data, technical specifications, and how specific fields and tables are structured, refer to JDBC Driver for Salesforce's Data Model Documentation.
Custom query
Alternatively, you can enter your custom SOQL (Salesforce Object Query Language) query.
Under the Object dropdown list, click Click here to manually enter your query and enter a custom SQL query to pull out only the rows and columns you actually need.
Unaric Reports will run your SOQL, retrieve the data and then place that data into a report automatically. If you have any mistakes in your SOQL, a red box will appear with a detailed error message.
For example, to create a report with the first name, last name, and created date of all Leads created in the last 30 days, you would put in the following SOQL:
SELECT FirstName, LastName, CreatedDate FROM Lead WHERE CreatedDate = LAST_N_DAYS:30This will create a report that will include all Leads created in the last 30 days.
For more information about SOQL, see Salesforce Object Query Language (SOQL).
Pick fields
- Pick the fields you wish to include on the report from the available options in the fields box.
- If you know the name of the field you wish to add, use the search box to filter fields.
- You can double-click fields or use the Add Selected ( icon) and Remove Selected ( icon) buttons.
- To select multiple fields at once, hold the Ctrl key while you click them.
- You can arrange the fields after adding them all by using the Move Up ( icon) and Move Down ( icon) buttons. Move the selected fields to the top or bottom of the list using the Move to Top ( icon) and Move to Bottom ( icon) buttons.
- Click View related fields next to fields to see and join available related data. You can nest up to five levels of related data.
- Click Next to continue.
Add filters & sort order
- To filter a report by a field, click the Add (+) button next to Filters
- Locate the appropriate field you wish to add a filter to. You can use the Search box if you know the name of your field. Otherwise, navigate the list to find your field.
- Once you select your field, use the drop-down list on the right to select your filter parameters. Enter the value that satisfies your parameter requirements and click Save.
- To sort the report by the fields, click the Add (+) button next to Sort Order. Locate the appropriate field you wish to add a sort order to. You can use the Search box if you know the name of your field. Otherwise, navigate the list to find your field.
- Once you select your field, use the drop-down list on the right to select your sorting order. Next, use the drop-down list and decide how you wish to see null values on the report and then click Save.
- If you wish to limit the returned rows to a specific amount, enter the limit value in the Row Limit text box.
- If you wish to include deleted records in your query, select the box next to Search Deleted Records.
- If you wish to include remove duplicate records, select the box next to Remove Duplicate Records (Tabular only).
Value variables
You can use the following variables as the value:
Filter value {{FILTER_VALUE}}: The filter value sent over from a Salesforce button for a Solution that is running report.
Burst report variables {{Name}}: You can use any of the fields from your Burst report. For example, if your Burst report is on Account and you have included Name as a field, you could filter this report using the Name with the variable {{Name}}.
Salesforce button variables {{pvName}}: You can pass in additional variables on your Salesforce button using pv prefix. For example, let's say you have a Salesforce button on Account and in addition to using the Account ID as the filterValue, you also want to filter on the Name. In this case, you would use add a variable on the button of pvName={!Account.Name} and you would add a variable value of {{pvName}}.
Running user ID {{RUNNING_USER_ID}}: User ID of the person running a Solution that is running this report. Useful for creating a User report that can be attached to a Docusign Solution.
Formatting
At this step, you can customise the appearance and logic of your data before the report is finalized. Available options include labels, variable names, format, and formula.
Format
Enter a custom format for the data type.
Examples:
Data type | Original value | Format | Output |
|---|---|---|---|
Date | 1/1/2020 | MMMM dd, yyyy | January 01, 2020 |
Currency | $1,000.00 | $#,##0 | $1,000 |
Formula
Enter a custom expression for the data type using Apache Velocity Template Language (VTL) syntax. VTL allows you to take raw Salesforce data and manipulate, reformat, or apply logic to it before it appears on a report. For example, a formula can be used to categorize records: you can create logic that replaces a numerical "employees" value with the word "large" if the company has more than 100 employees, or "small" if it has fewer.
You will use $value as a placeholder for the data you are evaluating.
Velocity comes with a number of built in tools including the following: NumberTool, MathTool, DateTool, ComparisonDateTool, and EscapeTool. You can access these tools using $ and lowercase letter on the first word. For example, to divide a field by 500, use the following formula: $mathTool.div($amount, 500).
If your field has any chance of being null, (or - in Salesforce) you will need to account for that in your expression. See Example 1 below.
If you change a field that is a number into text, like in Example 1 below, you must make sure not to aggregate it on this report.
If you are comparing a string field to a string you must make sure to wrap the $value and whatever you are comparing it to in ' '. See Example 2 below.
You can only use a single tic ( ' ) in an expression not double quote ( " ) .
Examples
- Number: This example evaluates a number field to see if it is greater than or less than 1000. This field is sometimes null so you need to also account for that.
- Expression: #if($value) EMPTY #elseif ($value > 1000) GOOD #else BAD #end.
- Output: If value is null (or -) it will output EMPTY. If value is greater than 1000 it will output GOOD. If value is less than 1000 it will output BAD.
- Text: This example compares a string field (FirstName) to a string (Joe).
- Expression: #if($value == 'Joe') Joseph #else $value #end.
- Output: Any value that equals Joe will display as Joseph. All other values will display the original value.
- Date: This example reformats a date or date time field.
- Expression: #if($value) #if ($value.compareTo($dateTool.getDate()) > 0 ) IN THE FUTURE #else IN THE PAST #end ($formattedValue) #end Output: If there is a value or it is not null and the date time is greater than zero, then it will replace the date with IN THE FUTURE. If the date is less than zero, it will display IN THE PAST, otherwise it will display the current date.
- Phone Number: This example formats a phone number field.
- Expression: #if($value.length() == 10) $value.format('(%s) %s-%s', $value.substring(0, 3), $value.substring(3, 6), $value.substring(6, 10)) #end
- Output: This will format the phone number as (###) ###-####, e.g., (913) 732-2226.
For more information about Velocity syntax, see Apache Velocity Template documentation.
Groupings
Use the drop-down list to select the fields you wish to group by. You can choose up to 3 different fields.
Adding a grouping will automatically change your report from a tabular format to a summary format. Summary formats are great because they allow you to group your data and they also allow you to add charts!
Summary fields
If you wish to summarize fields, use the drop-down list to select the field you wish to summarize by. You can pick one or more of these options SUM (total), AVG (average), MIN (minimum), and MAX (maximum).
Click Finish to complete report creation.
Add child report
Child reports are secondary report components added to a parent report to aggregate and display data combining information from both. Once set up, these reports function like any other custom report; they can be downloaded, saved to cloud storage, or emailed on a schedule.
- On the Reports page, open a report.
- Click the Add Child Report button.
- Complete the report creation. This process is identical to the creation of custom reports.
- After building the child report, return to the parent report and run it. The report will include information from the child report.