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
condition?

Process :

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.

  1. Created one test report using Efashion
  2. Ex : Drag Year, Quarter and Sales Revenue

1.JPG

         

3.Created conditional Variable using Sales revenue Measure
as below

[Sales revenue] Between( 1000000; 2000000) Then RED

or
[Sales revenue] Between( 2000001; 3000000) Then ORANGE

or
[Sales revenue] Between( 3000001; 4000000) Then Light Green

or
revenue] > 4000001 Then Thick Green
.

4. Apply on sales revenue (as per above, there will be 4 different colors Red, Light green, Green)

2.JPG

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…

3.JPG

6. Then Create a variable (as **Measure) called Color as below

Color = FormatNumber(If
[Sales revenue] Between( 1000000; 2000000) Then 1

ElseIf
[Sales revenue] Between( 2000001; 3000000) Then 2

ElseIf
[Sales revenue] Between( 3000001; 4000000) Then 3

ElseIf
[Sales revenue] > 4000001 Then 4;”#”)

7. Add Color objects next to Sales Revenue

4.JPG

8.You can apply sorting on “Color” object as usually ASC/DESC or Custom sort using Advanced in sort function

OR

You can define in the numbering accordingly in Color variable (step 6)

5.JPG

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.

6.JPG

Thank you,

Pulendar Vadde

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply