on 03-17-2020 10:28 AM
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.
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
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:
Pre-requisites for Combined queries:
Hope you're able to achieve it without any issues.
Thanks,
Mahboob Mohammed
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.