You can combine text strings with data to clarify your report. Here are two common ways to use concatenation:
- When you want the report title to include prompt responses to clarify the scope of the data presented.
For example, in a sales revenue report for the year 2024, the report title is Sales Revenue Report for 2024. To provide extra clarity, concatenate the LastExecutionDate with the title so that the title becomes Sales Revenue Report for 2024 as of 10/31/2024.
- When you want to provide information in a sentence.
For example, you can add a descriptive sentence, such as Colorado's best sales year was in 2024 at $843,584 to a report.
Example
To explore concatenating different data types, we will combine a string value and the Last refresh date free-standing cell formula.
Use the LastExecutionDate Function
The Last refresh date free-standing cell shows the date and time when the document was last refreshed. The date and time appear in this format: 06/04/24 3:15 PM.
The formula uses this syntax:
date LastExecutionDate(string data_provider)
This formula returns the date when a query was last refreshed. Put the name of the query in brackets. If your report has only one query, you can leave out the data_provider parameter. You can add the Last Refresh Date free-standing cell to a report and view the formula in the Formula Editor.
The formula appears as:
=LastExecutionDate()
To add a descriptive string before the date, change the formula to:
="Last Execution Date: "+LastExecutionDate()
This formula concatenates the last execution date character string with the LastExecutionDate() function. The Last refresh date cell now shows: Last Execution Date: 06/04/24.
Use Formatted Dates and Concatenated Strings
As well as being able to concatenate dates with strings, you can also display a date using a character string.
If the formula only uses the LastExecutionDate function, you can choose Format Display in the cell's context menu. Then, select a date format from the Date/Time area of the Format Display dialog box.
If the formula includes a text string, you cannot use the standard formatting method. To format a date joined with another string, use the FormatDate function.
Use the following syntax:
=FormatDate([Start Date];"dd/MM/yyyy")
To change the Last refresh date formula with the FormatDate function, use the following syntax:
="Last Execution Date: "+FormatDate(LastExecutionDate();"Mmmm dd, yyyy")
Let's Summarize What You've Learned
- Concatenating strings with dates converts the date to a string, allowing for clearer report titles and descriptions.
- Use the LastExecutionDate() function to display when a data provider was last refreshed.
- Apply the FormatDate() function to control the display format of dates within concatenated strings.
- Use the FormatNumber() function to convert numbers to strings for consistent formatting in reports.