Preface:

Software used:

SAP BO V 4.1 SP2

Universe : eFashion.

I have shown the way we can aggregate the values on report level.

I have dragged State & City objects

My initial report looks like below:

First report.png

Now I want to aggregate city names as per state.

So I want result as shown below:

Final report.png

To achieve the result follow the steps:

1) First drag State & City objects on report and sort result by State and City in ascending order.

2) Create dimension like

Cities =[City]+”;”+Previous(Self;([State]))

3)Create measure like

length_cities=Length([Cities])

4) Create another measure to calculate rank

Ranking =Rank([length_cities];[City];([State]))

5) Apply report level filter using newly created measure Ranking

where Ranking = 1

6) Create another dimension

Final_Cities  =NoFilter([Cities])

7) Drag this Final_Cities to report & then hide the column City.

Check your report you got your desired result.

I hope this will help while developing reports.

Thanks,

Swapnil

To report this post you need to login first.

12 Comments

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

  1. Kevin Van Dorn

    Thanks for the post.  I would add one more thing to remove the semi-colon at the end of the string.  Create a measure called Length_Final_Cities  =Length([Final_Cities])

     

    Then use this formula to create a dimension called Cities Display

    =Substr([Final_Cities];1;([Length_Final_Cities]-1))

    (0) 
  2. Santhosh kumar

    TanQ for the trick…..But how about the Measure values and if there are more cities for state , the lenght of the Column will increase… This is nice trick but very rare we need this in WEBI as we will be showing the measure values for dimension values.

    If we aggregate the Cities here , just wanted to ask .. Have you checked this along with the measures ? Are they getting aggregated ? Are they bringing back the correct values ?

     

    Thanks,

    Santhosh

    (0) 
    1. Kevin Van Dorn

      It appears that it only brings back the measure for the last city.  The workaround:  Append another table with State and the measures.  Hide the state column and position the 2nd table right next to the first table, such that it appears as one continuous table.

      (0) 
    2. swapnil bhootwala Post author

      Hi Santhosh,

       

      This will work only for dimension objects as for measure you have to specify projects and for string how can you derive the projection?

       

      I hope you could understand same,

       

      Thanks,

      Swapnil

      (0) 
    3. DIVYA P

      Hi Santosh,

       

      If my understanding is correct, you want to know how the measure values getting aggregated when you use State,concatenated cities.

      So in this case you should not use the direct measure instead use measure with context operators. Meaning, you have to ignore city dimension from context so try to use =Sum([Sales revenue]) ForAll([City])

      (0) 
  3. Hayden T

    Dear Swapnil,

     

    Thank you for this great tip. It has been life-saver for me…

     

    If possible, can I ask you 1 question…?

     

    I want to aggregate two columns within the same table…

     

    1. Cities

    2. Shop Names

     

    Is it possible…?

    (0) 

Leave a Reply