Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
This article describes how to use the Reporting visual in the Business performance planning application. To fully use this application, you must install Microsoft Power BI visuals. For information about how to install Power BI visuals, see Power BI visuals.
The Reporting visual is a powerful tool for seamlessly generating professional-looking financial reports in Power BI. You can use this visual to create comprehensive financial statements that offer advanced row and column formatting capabilities. Examples include cash flow statements, income statements, and balance sheets.
Benefits
- Tailored financial reports – Create detailed financial reports with precision by using advanced row and column formatting options.
- Custom calculations integration – Seamlessly integrate custom calculations and subtotals at any point on the report to ensure accuracy and tailored insights.
- Professional data presentation – Enhance the presentation of financial data by using advanced formatting features to give the reports a polished and professional appearance.
- Insightful variance views – Gain deeper insights into financial data through variance views that are included on the reports. Then use these insights to help with comprehensive financial analysis and decision-making.
Prerequisites and installation
For more information about prerequisites and installation, see Install business performance planning visuals.
Note
Because the Reporting visual doesn't write data back to Dataverse, it doesn't require that you enter API details.
Use the Reporting visual
You can use the Reporting visual to add subtotals and custom calculations to the financial report. The enhanced formatting options let you create a professional and polished presentation of financial data. You can also include variance views on the reports to facilitate deeper understanding and analysis of financial data.
Initial setup
To begin to use the Reporting visual, put the Amount column from your cube into the Values field in the Visualizations pane. Then populate your row and column data. For a financial report, the rows are typically populated with an account name, and the columns are populated with a time period, such as month, quarter, or year.
Tip
To find the Amount column, expand your cube in the Data pane, and look for a field that has a Sum symbol next to it. A Sum symbol next to numeric fields indicates that they're measures.
Custom calculations
To add custom calculations, select Edit or the ellipsis (…) in Visual settings in the upper-right corner of the visual.
Row calculations
In the Edit section, add new rows by selecting and holding (or right-clicking) in the place where you want to add them.
Column calculations
Select and hold (or right-click) the column header where you want to add a calculation, and then select Before or After. Then select the column header items to use in the calculation. The editor shows a list of all the row and column calculations. To edit the calculations, select the item that you want to work with.
Supported operators
| Operator | Name | Syntax | Associativity | Example | Result |
|---|---|---|---|---|---|
| (, ) | Grouping | (x) | None | 2 * (3 + 4) | 14 |
| [, ] | Matrix, Index | […] | None | [[1,2],[3,4]] | [[1,2],[3,4]] |
| {, } | Object | {…} | None | {a: 1, b: 2} | {a: 1, b: 2} |
| , | Parameter separator | x, y | Left to right | max(2, 1, 5) | 5 |
| . | Property accessor | obj.prop | Left to right | obj={a: 12}; obj.a | 12 |
| ; | Statement separator | x; y | Left to right | a=2; b=3; a*b | [6] |
| ; | Row separator | [x; y] | Left to right | [1,2;3,4] | [[1,2],[3,4]] |
| \n | Statement separator | x \n y | Left to right | a=2 \n b=3 \n a*b | [2,3,6] |
| + | Add | x + y | Left to right | 4 + 5 | 9 |
| + | Unary plus | +y | Right to left | +4 | 4 |
| - | Subtract | x - y | Left to right | 7 - 3 | 4 |
| - | Unary minus | -y | Right to left | -4 | -4 |
| * | Multiply | x * y | Left to right | 2 * 3 | 6 |
| .* | Element-wise multiply | x.* y | Left to right | [1,2,3] .* [1,2,3] | [1,4,9] |
| / | Divide | x / y | Left to right | 6 / 2 | 3 |
| ./ | Element-wise divide | x ./ y | Left to right | [9,6,4] ./ [3,2,2] | [3,3,2] |
| %, mod | Modulus | x % y | Left to right | 8 % 3 | 2 |
| ^ | Power | x ^ y | Right to left | 2 ^ 3 | 8 |
| .^ | Element-wise power | x .^ y | Right to left | [2,3] .^ [3,3] | [8,27] |
| ' | Transpose | y' | Left to right | [[1,2],[3,4]]' | [[1,3],[2,4]] |
| ! | Factorial | y! | Left to right | 5! | 120 |
| & | Bitwise and | x & y | Left to right | 5 & 3 | 1 |
| ~ | Bitwise not | ~x | Right to left | ~2 | -3 |
| | | Bitwise or | x | y | Left to right | 5 | 3 | 7 |
| ^| | Bitwise xor | x ^| y | Left to right | 5 ^| 2 | 7 |
| << | Left shift | x << y | Left to right | 4 << 1 | 8 |
| >> | Right arithmetic shift | x >> y | Left to right | 8 >> 1 | 4 |
| >>> | Right logical shift | x >>> y | Left to right | -8 >>> 1 | 2147483644 |
| and | Logical and | x and y | Left to right | true and false | false |
| not | Logical not | not y | Right to left | not true | false |
| or | Logical or | x or y | Left to right | true or false | true |
| xor | Logical xor | x xor y | Left to right | true xor true | false |
| = | Assignment | x = y | Right to left | a = 5 | 5 |
| ? : | Conditional expression | x ? y : z | Right to left | 15 > 100 ? 1 : -1 | -1 |
| : | Range | x : y | Right to left | 1:4 | [1,2,3,4] |
| to, in | Unit conversion | x to y | Left to right | 2 inch to cm | 5.08 cm |
| == | Equal | x == y | Left to right | 2 == 4 - 2 | true |
| != | Unequal | x != y | Left to right | 2 != 3 | true |
| < | Smaller | x < y | Left to right | 2 < 3 | true |
| > | Larger | x > y | Left to right | 2 > 3 | false |
| <= | Smallereq | x <= y | Left to right | 4 <= 3 | false |
| >= | Largereq | x >= y | Left to right | 2 + 4 >= 6 | true |
Precedence
The operators have the following precedence, from highest to lowest.
| Operators | Description |
|---|---|
| (…), […], {…} | Grouping matrix object |
| x(…), x[…], obj.prop, : | Function call, matrix index, property accessor, key/value separator |
| ' | Matrix transpose |
| ! | Factorial |
| ^, .^ | Exponentiation |
| +, -, ~, not | Unary plus, unary minus, bitwise not, logical not |
| See the next section | Implicit multiplication |
| *, /, .*, ./, %, mod | Multiply, divide, modulus |
| +, - | Add, subtract |
| : | Range |
| to, in | Unit conversion |
| <<, >>, >>> | Bitwise left shift, bitwise right arithmetic shift, bitwise right logical shift |
| ==, !=, <, >, <=, >= | Relational |
| & | Bitwise and |
| ^| | Bitwise xor |
| | | Bitwise or |
| and | Logical and |
| xor | Logical xor |
| or | Logical or |
| ?, : | Conditional expression |
| = | Assignment |
| , | Parameter and column separator |
| ; | Row separator |
| \n, ,, ; | Statement separator |
For each row and column, you can assign a style that you set in the visual properties. Alternatively, select Measure to specify that the measure format should be used.
Formatting
The following options are available on the Format tab of the Visualizations pane in Power BI:
- Row and column styles – In the Row/Column Style section, you can define row and column styles for any row that this style is assigned to. By assigning these styles to the calculations, you can achieve a format option that applies to all items that use this style. Examples include text formats such as font weight, color, underlines, and overlines. After the style is set, enter edit mode, select the row in the visual, and then select the style on the header of the report.
- Conditional formatting – In the Conditional formatting property of the visual, you can set up three flexible rules for conditional formatting. You set up a rule by using the Val keyword to refer to the value of the specific measure in the visual. You can use all the mathematical options that are outlined in the Supported operators section. For example, the rule Val > 2000 formats all values above 2,000 by using the format for that measure.
Row/column spacing
You can increase the space between a row/column by selecting and holding (or right-clicking) the column header.
Variance visualization according to IBCS
Use the Reporting visual to add a variance visualization according to International Business Communication Standards (IBCS) principles. To add the variance visualization to your reporting, use the Variance section in the visual properties. You can flexibly set the value and comparison columns by pointing to the column number. Alternatively, you can set the calculation in the visual by selecting and holding (or right-clicking) the Variance column. In this case, you can reference the columns in the nested dimension section, or you can select the Absolute Indexing checkbox to use an Absolute reference to any column in the visual.
You can switch between absolute and relative variance displays by selecting the delta symbol (Δ) or the percentage sign (%), as appropriate.
Comments
The Reporting visual supports the display of cell-based comments that can be formatted in HTML. To activate these comments, turn on Comments in the properties. You can also specify the width of the comment box.
Measure header
In the Measure header section, in the visual properties, you can set the format for a measure underline to clearly identify a specific scenario type according to the IBCS guide.
Data-driven styles
Automatically apply format styles. Here are some examples:
- Number Formats – Use .NET number format conventions.
- Color – Enter a hex code or color name.
- FontSize – Enter an integer.
- Font-weight – Specify bold, n, or italic, or enter an integer.
- FontStyle – Specify italic, oblique, or normal.
- LineFormat – Specify u for a single underline, u2 for a double underline, o for a single overline, or o2 for a double overline.
In the table that contains the row members, add a column that contains the format style that you want to apply. Use the following format:
{"numberFormat": "#,0,.0;(#,0,.0)","FontSize": 8,"color": "blue","FontWeight": "bold","FontStyle": "","LineFormat": "n","LineColor": "blue"}
Refresh button
Turn on the Refresh option in the Visualization section to make the Refresh button available on the visual.
Export data
You can export data in Excel or comma-separate values (CSV) format.
Cell setting and autowidth
You can dynamically adjust the column width for every column.
Rows header/flatten
You can use the Flatten feature to change the layout of the visual.