Sometimes it makes good sense to apply break on measures.
Let’s build a query based on the eFashion universe that contains product Lines, product Promotion Cost, Store Name, and Sales Revenue. Promotion cost is money spent for advertising a product, it is not directly related to stores. So the report is showing the same product promotion cost for different stores.
Technically speaking, the queries from two contexts are merged in the table. The first query is from “Promotion” context, it contains [Lines] and [Promotion Cost USD]. Second query is from “Shop facts” context and it contains [Lines], [Store name], and [Sales revenue]. The queries are merged by [Lines].
This looks not nice because it might seem that the same amount is spent for each store for a product. The readability can be improved by defining break on the measure.
Add break on Promotion Cost, Remove break header and footer rows, Show table header, Center values across break
However defining the break only on the measure is a wrong way. You can get very unexpected results. For example, when the promotion cost will be same for a couple of products, or when a calculation is used (see a puzzle in the end of the post). It would be more correct to define a break by product lines and then the break by promotion cost.
Remove break from “Promotion Cost”, Add break for the column “Lines”, and then again Add break for the column “Promption Cost”, Remove break header and footer rows, Show table header, Center values across break
Ok, that’s it. The point it is if you want to have a break for a measure, you have to define break for the corresponding dimension.
The next question is what if we do not want to see the break by Lines as a break, i.e. we want the product line name in each row. A simple solution is to duplicate column Lines, and “hide” the one with the break.
Add another column “Lines”, change expression for the first column to =[Lines]+””, Hide the column “Lines”
This does not look neat because you cannot really hide the column. But you can build nice appearance using alert that replaces value of the cell with Promotion Cost.
Remove column “Promption Cost”, Expand the first column (“Lines” with break), Move it to the place of “Promotion Cost”, create alert with condition “Cell content is not null”, specify =[Promotion Cost USD] in the Display field, configure alignment and color.
Now a puzzle for geeks
You have a table with Lines, Store name, Promption Cost USD, and Sales revenue, the break is defined on the column Promption Cost USD.
Replace expression in the column Promption Cost USD with:
=If [Lines]="Dresses" Or [Lines]="Accessories" Then [Promotion Cost USD] Else 0
and explain the result:
Why is 9,500 shown for “Dresses” and not 13,000?
Very nice article Sir!!!
I have two things to discuss with you here. If we want to break on measure, you have done it on measure first and then correspoding dimension. Ok it is fine till now. Now you give solution if we want to every product line name in each row, then you have added a duplicate column and try to hide it as much as it can.
Can’t we just go to Break Properties and select show duplicate values in break of product line to have our solution.
Also i am thankful to you for giving me one more option to do this i.e by alerters. I will try this and then revert back to you if i got any problems in doing this.
2nd question : Your question for geeks is very interesting 🙂 Can you please provide me the answer for the same.
Good point about the break properties! I overlooked this simple solution – thanks 🙂
Concerning the puzzle – I do not know:) I assume the breaks on formulas are not quite supported. Or maybe this is a bug. I was too lazy to raise a support case with SAP about this.
BTW, you can use more simple formula instead of if-then, e.g. =[Promotion Cost USD]+0
Thanks for the article. The reminder that you can break on measures was a huge help.
I couldn’t follow your example of using an alerter to replace a break. Perhaps it’s related to the fact we’re still using WebI ver. 3.1..
There is another option by using a formula with context keywords:
=[Promotion Cost USD] forAll ([Store Name])
/* removes the store from the calculation context */
=[Promotion Cost USD] in ([Lines];[OtherDimension])
/* explicity state the calculation context */
Hope this helps!