String aggregation on WEBI report level.
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:
Now I want to aggregate city names as per state.
So I want result as shown below:
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
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))
good
Great Webi trick !
You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !
William
Thanks William...
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
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,
Thanks,
Swapnil
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])
Not a bright idea... When we export it into excel , we could see two tables which is little weird for the business..
I agree, it is not perfect, but do not see any other option in the current version.
I just voted up the concept in SAP Ideas. I recommend all do the same.
https://ideas.sap.com/ct/ct_a_view_idea.bix?c=1DA84A30-1E5A-43FA-95C5-857A8B99D197&idea_id=CF097185-E798-4494-9C93-E1941…
THANKS ! This was exactly what I was looking for !
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...?
Thanks...
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 ?
Thanks
Sumonta
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.
-Nusrat