Often, businesses are looking for ways to bring data together to gain enhanced visibility into key metrics and organizational performance. Of course, the process of data preparation can be a long and frustrating one if you do not have the right tools at your disposal. Combined data, however, is not always readily accessible with reports out of the box in SAP Business ByDesign. In addition, SAP does not allow report writing using SQL statements. But SAP provides a means to join or combine data sources in SAP Business ByDesign in order to create more complex and detailed reports.
In this blog, I’ll demonstrate the basics of creating a joined report using out of the box SAP Business ByDesign data sources.
Defining Your Report in SAP Business ByDesign
First, you need to identify the data you would like to include in your report. In this example, we will be looking for Project & Sales data along with Purchase Order Item Detail data. We are going to use the current “Project Cost and Revenue – Aggregated” report and join it with the “Purchase Order Item Details” report. In this scenario, we cannot simply pull these two reports together, but we’d need to use a third intermediary data source to link the two reports together.
Start off by accessing the Business Analytics work center and search through your data sources in order to locate the information you want to include in your report. In order to join data sources, you need to have an identical field throughout all data sources. For example, Project ID is the primary key of the project cost and revenue report. This is the key you will use in order to match data from other data sources. Unfortunately, in this case, the Purchase Order Item Detail report does not contain Project ID data. Conversely, the Project Cost and Revenue report does not contain Purchase Order IDs.
To link the data sources, we’d need to find an intermediate report that has a match between Project and Purchase Order. After a quick search, I identified the “Purchase Order Account Assignment” report as the intermediary. Using these three reports, we can now build a connection using the primary key’s Project ID and Purchase Order ID.
Below are the step–by-step instructions on how to join data sources in SAP Business ByDesign.
Go to Business Analytics > Data Sources > New > Joined Data Source. The difference between Combining and Joining data sources is simple. Combining data sources will take data from two locations and combine all of the rows of data into one report. Joining data sources takes a common key from two or more tables and links the data together, joining information into a single row in the new report.
In the following screen, you’ll want to name, describe and choose the join type, and then start adding data sources. In the Join Type dropdown, you can choose between an Inner Join or Left Outer Join.
Inner Join: When two tables are joined, the result is a table of records located in BOTH tables. Anything found in just one table is eliminated from the results.
Left Outer Join: The “anchor” table will display a full set of records with information included from the other tables where there is a match on the key. When there is no match the data returns a null value.
First, choose your anchor data source. This data set will return all rows and information you choose. It is important to choose the data source that is going to give you a complete list of target objects. In this case, I’m using the Project Cost and Revenue – Aggregated report because I want to view all projects in SAP Business ByDesign. You also need to choose all the fields you want to be available in the report by clicking the check boxes.
Now, you’ll need to add the intermediary data source: the Purchase Order Account Assignment report. This data source links Project IDs with Purchase Order IDs. Below you will see that the system automatically links these when we add the data source.
Note: It is important to remember that SAP Business ByDesign fields do not always link. For example, you might have a “Project ID” field in one data source and a “Project UUID” field in another report. It is possible that these two fields represent different values on the back end of SAP Business ByDesign. Typically, all IDs in the system have a front end ID, which is seen by users, and a back end UUID. This back end UUID stands for Universally Unique Identifier and is used on the back end to uniquely identify objects. The reason: to ensure that an object can be identified regardless of the context in which it is being read. A high-level example is the sharing of information between two systems where it could be possible to have duplicate IDs. As an UUID is a random 128-bit value (in most cases), it is nearly impossible to have a match. As such, you need to ensure that you are matching IDs to IDs or UUIDs to UUIDs when joining data sources in SAP Business ByDesign.
Now, it’s time to add your third data source which includes the PO details we’re looking for. Here, you will link your third data source to your second data source. This creates an indirect linkage between your first and third data sources.
At this point, you want to run a Check to make sure the join is consistent.
Hit Save and Close. You can now go ahead and create a new report using your new custom joined data source. These custom joined data sources will list your name as the creator (as opposed to SAP).
That’s it. You have now created a joined data source in SAP Business ByDesign.
For questions or more information, please contact us.