Skip to Content

String aggregation on WEBI report level.


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


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.



You must be Logged on to comment or reply to a post.
  • 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


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




    • 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.

    • 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,




    • 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])

  • /
  • Thank you so much for this tip!

    It is really a life-saver!


    If possible, please somebody help with 1 problem that I have...?


    I want to apply another filter to Final Cities.

    For example, I only want to show San Francisco; Los Angeles;

    Problem is that it is using NoFilter()

    Do you think there is a way around this problem...?




    • Hi Hayden,

      Do you want to show  only San Francisco; Los Angeles; or you want to show only San Francisco; Los Angeles; for state California ?




  • Former Member I am very new to WEBI. Can you please tell me how you did step no. 5.

    The followed the same procedure except step no. 5 . Please look at the image to see the issue I am having , I think the reason I am having trouble cause I didn't do step 5.

    I do not know how to add report filter using measure.

    I will really appreciate your reply.

    Thanks in advance.