Customise Tag Formats

Any field recorded in SmartyGrants can be pulled out into a report. By default, those fields are produced in the format it is stored as in the database.

There may be an occasion where you might want to amend the format of some of these fields. Below, we have outlined some of the fields where this is possible by editing the Tag. You should be familiar with reporting rules and how tags work in order to make these changes. See Reports & Correspondence to learn more.

Viewing Tags in Word

If you are editing a template in Microsoft Word, by default, you will find your template fields show like this: <Date_Submitted>. This isn’t the actual tag code, but is shorthand that Word displays to represent code.

To edit the actual code behind the tag, you can use the shortcut ALT + F9 (Windows) or FN + ALT/OPTION + F9 (Mac) to toggle viewing code.

Alternatively, to permanently show Tag codes, you can go into Options (Windows) or Preferences (Mac) and under Advanced, select the Show fields codes instead of values.

Once enabled (or the shortcut is used), tags will now appear as field codes, such as:
{AUTOTEXTLIST  \t "<wr:out select='${app}/date_submitted/@datetime' nickname='&lt;Date_Submitted&gt;' />"}

Windows:

Mac:

Date & Time Fields

Some date fields will also return a time with it if this is how it is recorded in the system (eg, Date Submitted field). An example of this is shown here:

Microsoft Word	Microsoft Excel

If you did not want the time to appear and only wanted the date to appear, the field code (tag) can be amended. When editing the template, edit the code to remove the /@datetime from it (or in some cases, just the word time so that this part of the tag reads /@date). For example:

Word Format

Highlight "time" from the field code and delete it:

The field code should now look like this:

Once you upload the amended template, and run the report, the result will look like this:

Excel Format

Highlight "time" from the field code and delete it:

The field code should now look like this:

Once you upload the amended template, and run the report, the result will look like this:

Customise Date Format

You can customise the format of date outputs by adding 'type=’DATE’ format=’YYYY/MM/DD’' into the tag code. For example, consider the formatter added to this project end date field code:

<wr:out select='${app}/standard/project_end_date' nickname='[SQ:project_end_date]'type=’DATE’ format=’YYYY/MM/DD’/>

You can change this format to whatever you prefer, such as 'YY-MM-DD'. Whatever you choose will override the default format of displaying the date data, and force it to output as you specify instead. See Editing Today’s Date Format for further examples.

Number & Currency Fields

Number and currency fields are produced in reports in the following format by default:

Microsoft Excel

It may be that you would like to produce this information in a format that includes a decimal or a comma:

Microsoft Excel

Editing Number & Currency Format

There are a number of different combinations available to display number and currency fields in different formats.

Consider the below for a range of possibilities:

If reporting on currency information from the Decision tab instead, the following element can be added to the relevant report tags:

Today's Date Tag

Use these instructions to insert a new tag for today's date to appear in a Word or Excel report.

Word Format

If you add the field code from below (you can copy and paste) anywhere in the report template, it will print today's date:

{ AUTOTEXTLIST \t "<wr:out select='/report/@date' type='DATE' format='d MM y' />" }

Example of field code in template:

Example of report output:

Excel Format

If you add the field code from below (you can copy and paste) anywhere in the report template, it will print today's date:

<wr:out select='/report/@date' type='DATE' format='d MM y' />

Example of tag in template:

Example of report output:

Editing Today’s Date Format

There are a number of different combinations available to display the date in different formats. Consider the below for the full range of possibilities (mix and match combinations as required):

Editing Time Format

There are a number of different combinations available to display the time in different formats. Consider the below for the full range of possibilities:

IF and ELSE Statements

You are able to use IF and ELSE statements in report tags. You can use IF and ELSE tags for many form responses including number, currency, and free text. 

IF Tag are used to conditionally display content in your Report Template. An IF tag is basically an expression, or condition, whose value determines whether the contents of the IF tag are processed. When the contents of the IF tag are met (or TRUE), then the report will process the content between the IF and ENDIF tags. If the condition is not met, we can use the ELSE tag to populate a value into the report has the IF condition has not been meant (e.g. FALSE or X).

When running reports against multiple Applications or Contacts, we are able to filter the relevant list so that the report will only output information for the filtered items. However, filtering may not refine the reported output to the level required. For example, if there are multiple assessors assessing each Application and we only want to report on one of those assessor's responses, filtering will not be sufficient to refine the reported output. 

In cases such as these, you may want to utilise IF and ELSE tags.

Here is an example of an IF and ELSE tag. 

[if:Is Mr Tom Jones[else]Is Not Mr Tom Jones:if]

This tag is setting the condition that the Applicant Name is exactly Tom Jones and when this condition is true, the report will populate a Is Mr Tom Jones for every application that Tom Jones is the applicant of, for all the applications the report template has been run against.

In the template this would look like . . . 

And when the IF condition is met, the output will look like . . . 

The functions that can be applied to IF tags are  . . .

Word Format

The links below are to a report template that has been created that uses IF and ELSE tags. The first link is to the template, and the second to the output of the template. You are able to download these templates to access the tag templates for your own reporting requirements.

IF & ELSE Report Template Example.docx

Report - IF & ELSE Report Template Output Example.docx

Excel Format

The IF and ELSE tags can be used in Excel reports as well, but only the tag information located between < and > needs to be extracted. For example:

Reporting on Database ID numbers/including links in Reports

The Database ID is unique not only to your account, but to the entire SmartyGrants platform. The only place the Database ID is visible in your account is within the page URL at the top of your browser. You might include this number as a truly unique identifier in your reports. Application ID numbers are generally unique – but it is possible to double up Application ID numbers in your account. You might include this number as an identifier for contacts (i.e., contacts that do not have an equivalent Application ID number). You also might want to include the URL that links to the relevant record on the manage site within your reports for your colleagues, assessors or other internal users to easily click on.

To do this, you can use a tag similar to this (which will report the Application URL):

https://manage.smartygrants.com.au/<Application:ID>

In the template it would look like . . . 

Word Format

The below link is to a report template that is using these tags. You are able to download this template to access the tag templates for your own reporting requirements.

Database ID Report Template Example.docx

Excel Format

The below tags can be copied and pasted into an Excel report to report on the database ID or URL:

Reporting on Response ID numbers

Each form added to a submitted application (for example an Acquittal form) has a Response ID. The Response ID is unique not only to your account, but to the entire SmartyGrants platform. The only place the Response ID is visible in your account is within the page URL at the top of your browser. You may wish to use the Response ID in you reports, as an attached form does not have an Application ID as each form is part of the overall submitted application.

To do this, you can use a tag similar to this (which will report the Response):

This is how it will look in a Word report template.

Word Format

The below link is to a report template that is using these tags. You are able to download this template to access the tag templates for your own reporting requirements.

Response ID tag.docx

Excel Format

The below tags can be copied and pasted into an Excel report to report on the Database ID or URL:

Reporting on multiple choice formats

Your report output will group any multiple choice responses together in one cell, but if you would like to separate out the responses into separate cells, you can use an Excel formula:

Template
Output

The formula used to generate this output is:

=IFERROR((IF(FIND(B1,A2),B1)),"No Matching Value")

In this case:

1. B1 is the column heading (e.g., Cardboard)

2. A2 is the cell where the multiple choice output has populated (e.g., "Cardboard, Organics, Metal")

Note that the values referred to would differ depending on your report layout and absolute references (if applicable).

The formula works as follows (brackets first):

If you are looking to obtain a precise match (i.e., where you need to distinguish between Cardboard and Corrugated Cardboard), the formula used to generate this output is more complex. Please find below an Excel spreadsheet you can use with this more complex formula if some of your choices need to be distinguished:

Example.xlsx

Limiting Reports by Form Type

When creating a Multi Program template, a user can filter applications in the report based on the status of forms rather than individual forms themselves. This can be useful if, for example, a user wishes to filter out any applications that contain any unsubmitted assessment forms.

If a user was looking to limit their report to applications that only have submitted assessment forms, as the below example specifies, they can add a generic form start and end tag to omit any application records that have open assessment forms against them. This is in addition to the regular start and end tags that appear across all report templates.

In the following codes, you can substitute the following words:

• Open or Submitted (whether you want to include responses from forms that’s status is either open or submitted)

• Application or Assessment or Acquittal or Admin or Variation (specifying which forms you wish the report to generate applications from)

You can insert the following start codes into your multi-program report and exchange these variables as highlighted:

Make sure that both of these start tags have their corresponding end tags.

Please find below an example of a simple Multi Program Word template that makes use of each example of these modified start tags:

Limiting_Reports_Word.docx