Difference between revisions of "Using Variables in the Report Builder"
From SmartWiki
| (13 intermediate revisions by 2 users not shown) | |||
| Line 20: | Line 20: | ||
* Add the field "'''Branch/Offices - Parent List'''" to the '''Report Builder''' window. This field returns a string containing the list of companyIDs that define the [[Hierarchy|hierarchy]] for the current companyID. This string is delimited by commas. | * Add the field "'''Branch/Offices - Parent List'''" to the '''Report Builder''' window. This field returns a string containing the list of companyIDs that define the [[Hierarchy|hierarchy]] for the current companyID. This string is delimited by commas. | ||
* Set the criteria for this field to '''like '%,@companyid@,%'''' This criteria will compare the current company ID to the '''Parent''' list and include all company IDs where the current company is referenced as a parent. Note the use of the wildcards "%" and the commas in the criteria string. You should hide the field in the report. | * Set the criteria for this field to '''like '%,@companyid@,%'''' This criteria will compare the current company ID to the '''Parent''' list and include all company IDs where the current company is referenced as a parent. Note the use of the wildcards "%" and the commas in the criteria string. You should hide the field in the report. | ||
| + | |||
| + | * Other syntaxes include '''@me.parent.parent.companyid@''' to return the [[companyid]] of the [[parent company]] of the current user's company. | ||
==objectid== | ==objectid== | ||
| Line 28: | Line 30: | ||
==parentlist== | ==parentlist== | ||
| − | * The full SmartSimple path to the object. | + | * The full SmartSimple path to the object. |
| + | * For example, if a company is under three other organizations in the [[organization hierarchy]], the [[companyid]]s of each of the companies above it in the organization hierarchy will be displayed, separated by commas, using @parentlist@ | ||
| + | |||
| + | ==System Variables== | ||
| + | * [[System Variables]] can also be used as report criteria. | ||
| + | * This is useful if, for example, you have a series of reports that will use the same fixed date range. The report criteria can be set to include ''=@system.StartDateForReports@'' and ''=@system.EndDateForReports@'' and store the start and end dates in [[System Variables]] named ''StartDateForReports'' and ''EndDateForReports''. | ||
==Referencing criteria entered in the same report== | ==Referencing criteria entered in the same report== | ||
| − | * If you ever need to reference the user entered criteria within the same report, you can use the '''@ | + | * If you ever need to reference the user entered criteria within the same report, you can use the '''@arg''x''@''' variable where ''x'' = 1,2,3.. depending on what number the argument is. The idea is similar to using @args[1]@ if [[Passing Prompt Criteria to a Sub-Report|referencing the criteria within a sub-report]] but this is for a single report. |
* This syntax can be used within '''Calculated Field''' and '''Criteria'''. | * This syntax can be used within '''Calculated Field''' and '''Criteria'''. | ||
| − | + | A sample use case would be if you are prompting for a date range in the report, but within another column of the report you want to use the user-entered date to further limit the records using ''date_add'' or ''[[How do I create a report that returns within a range of the user input?|date_sub]]''. | |
==See Also== | ==See Also== | ||
| Line 41: | Line 48: | ||
* [[Determining the userid]] | * [[Determining the userid]] | ||
* [[Using Data from a Report as Criteria for Another Report]] | * [[Using Data from a Report as Criteria for Another Report]] | ||
| + | * [[Passing Prompt Criteria to a Sub-Report]] | ||
| + | * [[ReportProperty]] | ||
| − | [[Category:Reports]][[Category:Variables]] | + | [[Category:Reports]][[Category:Criteria]][[Category:Variables]] |
Latest revision as of 12:32, 20 October 2017
When creating a report, variables that are commonly used as parameters include:
- @companyid@
- @userid@
- @objectid@
- @rolelist@
- @parentlist@
Contents
userid
- @userid@ - returns records where the value of the field contains the user id of the person running the report. Syntax: =@userid@
This variable can be used to create an individual user-specific report, such as sales for that user, or login history for that user.
companyid
- @companyid@ - returns records where the value of the fields contains the company id of the user running the report. Syntax =@companyid@
Using this variable eliminates the need to create different reports for different branches but this technique will not display records in the report related to sub-branches. This technique will not display records for sub-branches of the user's branch.
If you wish to create a report that displays both the user's branch and all sub-branches of the user's branch then you need to use the following technique:
- Add the field "Branch/Offices - Parent List" to the Report Builder window. This field returns a string containing the list of companyIDs that define the hierarchy for the current companyID. This string is delimited by commas.
- Set the criteria for this field to like '%,@companyid@,%' This criteria will compare the current company ID to the Parent list and include all company IDs where the current company is referenced as a parent. Note the use of the wildcards "%" and the commas in the criteria string. You should hide the field in the report.
- Other syntaxes include @me.parent.parent.companyid@ to return the companyid of the parent company of the current user's company.
objectid
- When using any of the ReportProperty Variables (recordcount, exportdata and csvdata) the object ID (opportunityid, eventid, userid, etc) will be passed to the report processor. If the specified report includes the variable: @objectid@, it will be replaced by the object ID of whichever object (Level 1, Level 2, Contact, etc) called the ReportProperty attribute.
rolelist
- Returns a list of roles belonging to the referenced object. @me.rolelist@ can be used to return a comma-delimited list of the current user's roles.
parentlist
- The full SmartSimple path to the object.
- For example, if a company is under three other organizations in the organization hierarchy, the companyids of each of the companies above it in the organization hierarchy will be displayed, separated by commas, using @parentlist@
System Variables
- System Variables can also be used as report criteria.
- This is useful if, for example, you have a series of reports that will use the same fixed date range. The report criteria can be set to include =@system.StartDateForReports@ and =@system.EndDateForReports@ and store the start and end dates in System Variables named StartDateForReports and EndDateForReports.
Referencing criteria entered in the same report
- If you ever need to reference the user entered criteria within the same report, you can use the @argx@ variable where x = 1,2,3.. depending on what number the argument is. The idea is similar to using @args[1]@ if referencing the criteria within a sub-report but this is for a single report.
- This syntax can be used within Calculated Field and Criteria.
A sample use case would be if you are prompting for a date range in the report, but within another column of the report you want to use the user-entered date to further limit the records using date_add or date_sub.
