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 https //cdn cdata com/help/rfk/jdbc/pg datamodel htm 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 soql select firstname, lastname, createddate from lead where createddate = last n days 30 this will create a report that will include all leads created in the last 30 days for more information about soql, see https //developer salesforce com/docs/atlas en us soql sosl meta/soql sosl/sforce api calls soql htm 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 https //velocity apache org/engine/2 0/user guide html 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 a 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