Creating Substitution Variables In Excel Reports
- 3 Minutes to read
- Print
- DarkLight
- PDF
Creating Substitution Variables In Excel Reports
- 3 Minutes to read
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
The process is the same as for formatted reports.
- Power or Contributor users create one or more substitution variables in the model.
.png)
- Power or Contributor users create one or more Excel Reports and insert the substitution variables.
.png)
- Power or Contributor users specify substitution variable expressions in the design of the report. See Example Substitution Variable Expressions for details about expressions.
- Users can change the value of the substitution variable using the Substitution Variables menu item from the Excel Report ribbon.
.png)
- With the cursor anywhere in the report, click Substitution Variables. The Substitution Variables box appears. It shows the name of the variable and the current value.
- Click the Member Select icon.
- From the member selection box, select the month that you want to see in the report and click Select.
.png)
- Click Refresh to refresh the report.
.png)
.png)
.png)
.png)
Example Substitution Variable Expressions
This topic is intended for administrators and designers and provides an example of expressions, syntax, and expression chains.
Consider the following dimension hierarchy for a fiscal year that begins in July each year. Assume that the model contains only these three years.
| Time Dimension | ||
|---|---|---|
FY2023 | FY2024 | FY2025 |
Q1 2023 | Q1 2024 | Q1 2025 |
|
|
|
|
|
|
|
|
|
Q2 2023 | Q2 2024 | Q2 2025 |
|
|
|
|
|
|
|
|
|
Q3 2023 | Q3 2024 | Q3 2025 |
|
|
|
|
|
|
|
|
|
Q4 2023 | Q4 2024 | Q4 2025 |
|
|
|
|
|
|
|
|
|
Expression Syntax
The following expressions are available for use with substitution variables. Assume that @CurYear@ is FY2018 and @CurMth@ is Jul 2017.
Expression | Offset # Required? | Description | Example Syntax | Resulting Value |
FirstChild | No | Returns the first child in the hierarchy. | @CurYear@.FirstChild | Q1 2024. CurYear is FY2024 and its First Child is Q1 2024. |
LastChild | No | Returns the last child in the hierarchy. | @CurYear@.LastChild | Q4 2024. CurYear is FY2024 and its Last Child is Q4 2024. |
FirstSibling | No | Returns the first sibling in the hierarchy, at the same level | @CurYear@.FirstSibling | FY2023. CurYear is FY2024 and its First Sibling is FY2023. |
LastSibling | No | Returns the last sibling in the hierarchy, at the same level | @CurYear@.LastSibling | FY2025. CurYear is FY2024 and its Last Sibling is FY2025. |
Lead | Yes | Returns the next member in the hierarchy, at the same level; skips over parent or child members | @CurYear@.Lead(1) | FY2025. CurYear is FY2024 and the next member at the same level is FY2025. |
|
|
| @CurYear@.Lead(2) | Member Not Found (There is no FY2026 in the model.) CurYear is FY2024 and skipping ahead 2 years would result in the member that follows FY2025, but there is no such year in the model. |
|
|
| @CurMth@.Lead(4)
| Nov 2023. CurMth is Jul 2023 and skipping ahead 4 months is Nov 2023. |
Lag | Yes | Returns the previous member in the hierarchy, at the same level; skips over parent or child members. | @CurYear@.Lag(1) | FY2023. CurYear is FY2024 and going back one year is FY2023. |
|
|
| @CurYear@.Lag(2) | Member Not Found (There is no FY2022 in the model.) CurYear is FY2024 and going back two years would result in the member that precedes FY2023, but there is no such year in the model. |
|
|
| @CurMth@.Lag(4)
| Mar 2023. CurMth is Jul 2023 and 4 months before July is Mar 2023. |
Parent | No | Returns the parent member in the hierarchy. | @CurYear@.Parent | Time. CurYear is FY2024 and its Parent is the Time dimension. |
|
|
| @CurMth@.Parent.Parent
| FY2024. CurMth is Jul 2023 and its Parent is Q1 2024, and Q1's Parent is FY2024. |
Expression Chains
You can chain expressions together. Each entry in the expression builds on the results of the previous entry, reading from left to right.
@CurMth@.Parent.FirstChild = Sep 2023. CurMth’s Parent is Q4 2023 and the First Child of Q4 2023 is Sep 2023.
@CurMth@.Lag(3).Parent = Q2 2023. CurMth’s 3-month Lag is Jun 2023 and the Parent of Jun 2023 is Q2 2023.
@CurMth@.Parent.Lead(2) = Q1 2024. CurMth’s Parent is Q4 2023 and 2 quarters ahead is Q1 2024.
Note:
We recommend to chain only two expressions together.
When adding functions to a substitution variable, you may find the need to change the order or to remove one of the expressions. Click the menu button next to the expression to make the changes.
If you want to cancel out from adding expressions, click inside the Design Manager or press Esc.
Was this article helpful?