How to apply custom sort on cell colors in webi Report :
Requirement : In Web Intelligence 4.1, I have a report with conditional
formatting by color. Is there a way to sort by color assigned in the
In general sorting can be done on Measure and Dimensions
level. But when there colors applied on measure, want to do custom sort
according to their colors, would be challenge when you have more values.
In that scenarios, this will be one of the solution which we
have followed to fulfill our business. There no direct functionality in webi.
But we can try this below option.
- Created one test report using Efashion
- Ex : Drag Year, Quarter and Sales Revenue
3.Created conditional Variable using Sales revenue Measure
[Sales revenue] Between( 1000000; 2000000) Then RED
[Sales revenue] Between( 2000001; 3000000) Then ORANGE
[Sales revenue] Between( 3000001; 4000000) Then Light Green
revenue] > 4000001 Then Thick Green.
4. Apply on sales revenue (as per above, there will be 4 different colors Red, Light green, Green)
5. Apply this condition on Sales Revenue Field, here you can apply sorting ASC or DESC but we can’t do custom sort when we have more values…
6. Then Create a variable (as **Measure) called Color as below
Color = FormatNumber(If
[Sales revenue] Between( 1000000; 2000000) Then 1
[Sales revenue] Between( 2000001; 3000000) Then 2
[Sales revenue] Between( 3000001; 4000000) Then 3
[Sales revenue] > 4000001 Then 4;”#”)
7. Add Color objects next to Sales Revenue
8.You can apply sorting on “Color” object as usually ASC/DESC or Custom sort using Advanced in sort function
You can define in the numbering accordingly in Color variable (step 6)
9.After Color sorting you have to handle hiding the Color
object. You can make width of the Color column as “0”, it will be hiding.
Here is the Final Output which is color based sorting Reporting.