How-to: Configure parameters that automatically update
Yes, you can do this by linking together your time parameters. You can link any time element, except for timespan, to a single period parameter.
Example: You have a report with three segments with separate time parameters that you want to set up so that update automatically changing one parameter will automatically update the other parameters.
To link parameters:
- Drag and drop the first time element to the parameter bar
- Then, drag and drop the other two time elements on top of the first parameter
- After adding the parameters, right-click on the Period parameter to verify that the elements are connected
In Parameter Settings, several dynamic time options are available when you scroll to the top of the Initial Choice setting such as current month and last actuals month.
How-to: Calculate percent of sales for an account
You can use the Display as Percent report element to display results for multiple accounts.
Example: You have a revenue account called Book Sales want to know what Book COGS is as a percentage of Book Sales.
To calculate percent of sales:
- Click Display As and add the % of Account element to your report
- Right-click on the newly added % of Account element and select the Book Sales account as the denominator for the calculation
- Add any account that you want to see as % of Book Sales
How-to: Create a subtotal for different time periods from different versions
You can use the Custom Display As element to create a subtotal.
Example: You want to create a subtotal using the first six months a budget version and the last six months of a forecast version. Assuming that your report already has 2 segments set up (#1 first 6 months for the budget version and #2 last 6 months for the forecast version), we can set up a third segment that will contain our calculations for the subtotal.
To create a Custom subtotal:
- Add a new segment
- From the Calculations menu, add a Difference element to the new segment
- In the Difference Options, include the two versions that you want to use for the subtotal and make sure to offset the Subtract Version 6 months Backwards
- From the Calculations menu, add a Custom Calculation element below the difference element in the segment that sums the last 6 months
- Because the difference element is offset, it will sum the first 6 months with the last 6 months
- From the Display As menu, add a Custom element to the bottom of the segment and include the formula below where "offset version" is the version offset in the Difference element
if(this.version.name = "offset version", -ACCT.this, ACCT.this)
In the steps above, we are using a workaround to make the difference element a sum instead. The reason we use the difference is because it allows us to operate with 2 different versions in a single calculation, where as it is impossible, currently, to formulaically refer to values in another version from you current version.
How-to: Create a variance between two different versions and time periods
You can create a report that shows the variance between Budget 2020 version for FY2020 and Budget 2021 version for FY2021 by offsetting the time for one of the versions forward or backward.
To create the report:
- Place both the Budget 1 and Budget 2 versions in one segment
- Add a 2020 Time element to the segment or Filter
- Right-click on the Budget 2 version and select Properties
- Shift the version forward by 12 months.
- By shifting the time forward, the Budget 2 version will display data for 2021 when the Budget 2 version is displaying data for 2020
- Add a Difference element next to the versions.
- Right-click on the Difference element, select Properties and verify that the Difference element is creating the proper variance and that the Budget 2 version is shifted forward by 12 months.
The resulting report should give you 3 columns: one for Budget 2020, one for Budget 2021, and one for the variance between the two versions.
How-to: Remove total rows for accounts
Totals can't be removed for a parent account in Report Properties. When the option to "Show totals at bottom" is checked in Report Properties, the total for the parent account will be shown as a row at the bottom of the rollup. When you uncheck "Show totals at bottom," the totals will appear in the same row as the parent account at the top of the rollup. As a workaround to remove totals, you can remove totals by dragging each account to the report builder separately.
To remove totals:
- From Design Elements, add a custom header to the report, right-click to select Properties, and update the label to represent the parent account
- Drag and drop the accounts that rollup to the parent account that we didn't add to the report to prevent displaying its total
How-to: Reverse the sign for non-GL accounts in Reports
Because the reverse sign feature in reports only applies to GL accounts, you can use a custom calculation to reverse the sign for non-GL accounts in a matrix report.
- Formula -1*ACCT.Example
- Translation: Multiplies the account by -1 to reverse the sign for the account
- Formula: if([Budget]<[Actuals], div(([Budget]-[Actuals])/[Actuals])*-1, div(([Budget]-[Actuals])/[Actuals]))
- Translation: If the Budget value is less than Actuals, then take the variance and multiple by negative one, otherwise just take the variance