Question Re: SAP BusinessObjects - How to split string values into several items? in Technology Q&A

SAP BusinessObjects - How to split string values into several items?

Former Member — Tue, 17 Mar 2020 10:28:40 GMT

Hello Experts,

I am an amateur user of SAP BO and would like to seek advice from the community to tackle one of the issue I am currently facing for my project utilizing BusinessObjects.

From my current database, I can pull three fields namely "Class", "Name" and "Favourite Food". My objective is to split the string values of the items in "Favourite Food" into several records while still maintaining its relationship with other fields.

Is this even possible theoritically? since BusinessObjects only allows quering data but not manipulating or adding/splitting items the database itself. Please kindly advice how can I achieve below screenshot in BusinessObject. Thank you very much!

I look forward to your opinions.

Re: SAP BusinessObjects - How to split string values into several items?

mhmohammed — Tue, 17 Mar 2020 17:09:54 GMT

Hi Owen,

I've a solution for it which is going to be 2 parts, first part should be implemented at the Universe level, and 2nd part requires some work in the Webi report. I don't have time to try it out, may be you can give it a shot, I'm almost certain it will work.

Universe updates: Create N objects in the Universe to get separate rows, where N is the max count of values which can appear in one row. Ex: In the data set, we see that Tony has 3 Favorite Foods (Cake, Candy, Steak), so we'll need to create 3 objects.

First Favorite Food (Object 1) formula will be the equivalent of the Webi formula below, which will get you substring starting 1st Character through the the character position before the first comma.
=Susbtr([Favorite Food];1;Pos([Favorite Food];",")-1)
For SQL Server database, the formulas for those objects

First Favorite Food will be: SUBSTRING(FavoriteFoodColumnName, 1, CHARINDEX(',', FavoriteFoodColumnName)-1)
Second Favorite Food will be: SUBSTRING(FavoriteFoodColumnName, Len(First Favorite Food object)+1, CHARINDEX(',', SUBSTRING(FavoriteFoodColumnName),Len(First Favorite Food object)+1,Len(FavoriteFoodColumnName))-1)

You'll have to determine, the formulas to extract the 2nd favorite food (call the object Second Favorite Food), 3rd favorite food (call the object Third Favorite Food), and so on.

Webi Report updates: You'll have to update the query used to bring the data, by adding N number of combined queries with a Union:

In the 1st combined query you'll be using First Favorite Food object
In the 2nd combined query you'll be using Second Favorite Food object
In the 3rd combined query you'll be using Third Favorite Food object and so on

Pre-requisites for Combined queries:

All combined queries need to have same number of objects
Data Type of object at each position needs to be the same
Object (after the report is run) that was switched in each combined queries will have the name of the object in the first combined query, which is First Favorite Food.

Hope you're able to achieve it without any issues.

Thanks,
Mahboob Mohammed

Re: SAP BusinessObjects - How to split string values into several items?

Former Member — Wed, 18 Mar 2020 10:51:56 GMT

Thank you very much for the detailed instruction Mahboob ! Its really helpful!

Re: SAP BusinessObjects - How to split string values into several items?

Former Member — Wed, 18 Mar 2020 10:52:51 GMT

Hi Mahboob,

Thank you very much for your detailed input. Much appreciated!

Just wondering, what if the data source is from excel file (Assuming we cannot alter anything in excel file and process it as is) ? It seems we are not able to use Combine Query function if data is taken from elsewhere other than universe.

Re: SAP BusinessObjects - How to split string values into several items?

mhmohammed — Wed, 18 Mar 2020 15:06:36 GMT

Hi Owen,

If assuming you can alter anything in Excel, you should create an excel based Universe or, load that Excel data to a Microsoft access data base (saved on shared drive accessible through BO) and create a Universe using that database. That's one way, if you don't want to alter Excel.

Or, you create another excel document, having formula in each cell pointing to each cell of the Excel (with data), and do some transformations there, to split the Favorite Food column into multiple columns (then copy that in different tabs), and then create multiple queries.

Thanks,
Mahboob Mohammed