Hi everyone,
I’m working on a P&L report in a Data View and I’m stuck on a formatting issue involving the Row style template (Data model > Format) and the Block Format. I’ve spent quite some time on it and tried several approaches, so I’m sharing everything in detail in the hope someone has a clean solution.
Environment: Board 14.5, Cloud, Data View on a P&L layout.
1. Context
The report has these columns: Actual N-1, Actual N, Budget, ΔM€ N-1, Δ% N-1( Target column ) , ΔM€ Budget, Δ%Budget :
The rows are the P&L lines (one Entity). At the bottom, there are 4 KPI rows (CoEx, RWA, RONE, PNB/RWA) which are ratios, not amounts in M€.
A Row style template is applied to that Entity to manage fonts, bold rows and background colors.
2. What I need
- The “%” symbol on all rows of the Δ% columns.
- The “bps” symbol only on 3 rows (CoEx, RONE, PNB/RWA) of the ΔM€ N-1 column and ΔM€ B2026 column . No “bps” on the other rows, for example “Produit net bancaire”.
- No symbol on Actual N-1, Actual N and Budget.
- Values must stay numeric after “Export data to .xlsx”, because users apply Excel formulas on the exported cells.
3. The problem
As far as I understand, the two formatting layers don’t combine well for this case:
- the Row style template applies a “Symbol before/after” to the whole row (all columns);
- the Block Format applies a “Symbol after” to the whole column (all rows).
So I can’t say “bps on 3 rows of this column only”. With the initial template I got unwanted results, like “889 %” in an Actual column and “bps” on rows where it makes no sense.
4. What I tried
Attempt 1: Text algorithm with &”%”
Round(IF(b="SYN_937",0,100*(g-f)/f),2)&"%"
Algorithm type = Text. The “%” displays correctly on every row, but the result is text. In the Excel export these cells are not numbers, so the users can’t use formulas on them. It doesn’t meet requirement 4.
Attempt 2: Numeric algorithm + “Symbol after” in the Block Format
I went back to Numeric (Digits = 2, no &”%” in the formula):
IF(b="SYN_937",0,100*(t-s)/s)
and set Symbol after = “%” and Decimal digits = 2 in the Block Format (Value and Total) for that column. In the Block Format preview the “%” is displayed. In the real Data View, the “%” doesn’t show on most rows. The Row style template seems to override it.
Attempt 3: cleaning the Row style template
I emptied “Symbol before” and “Symbol after” in the template, then used RESET TO DEFAULT in the Options panel. Result:
- on the row where I did the reset (“Produit net bancaire”), the bps is gone and the “%” appears;
- on the other rows, the “%” still doesn’t appear, as if the template still kept a value;
- I couldn’t multi-select all cells (Values, Totals, Grand totals) to reset them in one go, so I’d have to do it row by row on a long list of members.
Attempt 4: “Ignore Format Template” on the column
I tried Design > Columns > select the Δ% column > Ignore Format Template. The “%” is more reliable, but the column loses the row colors and bold from the template. When unchecked, the template applies again and the “%” is lost on some rows.
Attempt 5: removing “bps” from the Block Format
The ΔM€ columns had “bps” as Symbol after in the Block Format, which put “bps” on every row. I removed it. As expected, “bps” disappeared everywhere, including on the 3 rows where I need it.
Attempt 6: Text algorithm only for “bps” on the 3 rows
IF(OR(b="SYN_979",b="SYN_976",b="SYN_963"),ROUND(<formula>,1)&" bps",ROUND(<formula>,0))
The display is right, but the column becomes Text: same Excel problem, plus I lose the parentheses for negatives and the Color Alert on that column.
An important detail about the “bps” and the workaround I used
The “bps” symbol is defined in the Block Format (Symbol after = “bps”) of the ΔM€ columns. A Block Format applies to the whole column, so “bps” is displayed on every row of that column. This is wrong: “Produit net bancaire” shows “93 bps” and “-92 bps”, where I only want “bps” on CoEx, RONE and PNB/RWA.
To hide “bps” on the first reporting rows, I had previously entered a single space in Symbol after of the Row style template (Data model > Format) for those rows. The Row style template has priority over the Block Format, so the space replaced the “bps” with an empty-looking symbol, and “bps” disappeared on these rows. This was only a workaround: it is fragile, it has to be done row by row, and it also blocks the “%” I want to set through the Block Format on other columns.
When I go back to the Row style template and remove the space from Symbol after, “bps” comes back on the first row in the ΔM€ N-1 column (see the first screenshot). This is the incorrect behavior, because “bps” should only appear on the 3 ratio rows.
So the situation is:
- with the space in the template → no “bps” on the first rows, but the template interferes with the “%” in the Block Format;
- without the space → the “%” can work, but “bps” appears on rows where it shouldn’t.
I could not find a way to have both: “%” on all rows of the Δ% columns and “bps” only on 3 rows, with numeric values.
Thanks a lot for your help on this one, any workaround is welcome!