Create report with custom dataset(s)
In Business ByDesign, customers are given the possibility to create custom data reports. These reports can be tailored to personal needs, using a dataset that contains all data necessary. When creating a new report, you have the possibility to select or deselect fields called Key Figures and Charateristics. These are the properties of the report and determine what will be displayed. These Key Figures and Characteristics are provided by the underlaying Dataset which the report is based on.
This means that all reports in Business ByDesign are based on Datasets which have to contain all necessary information to make the required report possible. Datasets are data cubes which can be used single handedly, or can be combined using joins or combinations of datasets. There are a few possibilities to combine datasets;
Gives you the possibility to create a new dataset which combines 2 or more separate datasets. These datasets are not bound to each other, and will report their data individually.
All rows from Dataset 1 are shown, then all rows of Dataset 2 are shown
Left Joined Dataset
A left joined Dataset combines two or more datasets and uses a reference key to look up rows in the connected dataset. The join is only from left to right, which means that all rows from the left dataset are shown, and only keys that are also found in dataset 2 are also shown. Otherwise the system will show a “Not Assigned.” Or “#” value.
On the left hand side the information of Dataset 1 is shown, and on the field “purchase order id” we left bind the purchase order set. This results in some rows having purchase order information available, and some dont. Though, all rows from Dataset 1 are shown.
Inner Joined Dataset
Binds Dataset 1 and 2 together, and only where the bound key exists in both Dataset 1 and Dataset 2, the row is shown.
With the inner joined purchase orders, we only see the rows that have a valid purchase order ID. All other rows are not shown.
Will not be discussed in this document.
To create a new Dataset, go to the workcenter Business Analytics -> Design Data Sources.
Here you will be able to create a new custom dataset, which will be based on existing datasets available in the system.
Once you know what dataset you have to create in order to get the data you need, its time to build the dataset. In my scenario, we did not have a standard dataset available for the Covered Registered Products on a contract, so I had to find a way to get those products in a dataset.To create this dataset, I used the SAP Cloud Applications Studio.
First you open the studio, connect to your system, and create a new solution.
Then you right click your solution -> Add -> New Item.
In the next screen, you select the option Data Source as object.
In the following screen, you have to provide the object you want information off to export to your dataset. In my case I wanted to get the Covered Registered Products, which can be found under a Customer Contract. A Customer Contract is available under “/xi/AP/CRM/Global”.
In the next step you are required to select the properties that you want to make available in the dataset. For my scenario I wanted to be able to have a link to the Customer Contract, so of the “ToParent” node, I include the ID.
I do not require any data transformation, so I skip step 3/6.
I also do not require any referenced Data Sources in my set, so I skip step 4/6 as well.
Lastly, I also do not require any aggregation behaviour.
I review if everything is correctly filled, and click finish.
You save and activate the newly created Data Source, and close the project.
Now go to your browser and login to your system again. Once we go to the Business Analytics -> Design Data Sources view, we are able to find our newly created Data Source. From this location we can test the new set by clicking Preview.
Now we only need to make a combination of standard available datasets, combined with our newly created data source. To do this, click new and select the data source you want to create.
(In my case, joined data source)
Set all parameters and make sure you select the correct method of binding.
(In my case, left outer join)
Add the data sources which you want to connect together using the Add Data Source button. You can find your custom created data source under the name which you gave it in the studio. Once you added both data sources, make sure to link the sources together. You can do this by creating a link between 2 Characteristics. To do this, fill in 2 characteristics on one row. Be sure to delete the characteristics that are duplicated, duplicate characteristics are not allowed by the system. If done successfully, a chain icon will appear infront of the row. To check the link between the items, press the “Check” button.
Now save your new data source, and test it using the preview button. Using the preview functionality, I get the following results:
If you are satisfied with your binding and everything works correctly, click the Create Report button. Another way to create the report is to go to the Business Analytics workcenter, go to Design Reports and click the New button.
Select your joined datasource as the datasource for your report, and select all characteristics and key figures you want to show in your report. Fill in all variables which have to be filterable, and finally assign the report to the workcenter of your needs. Keep in mind when assigning a report, that the Access Context is important. This determains which workcenters it can be assigned to and which it cannot be assigned to.
I hope this tutorial was usefull to you, if you have any questions, let me know!
Just a short question:
How can i know that the Customer Contract is available under "/xi/AP/CRM/Global".?
Are there any references which i can refer on?
Thanks with regards,
Hey Yap Mun Keong,
If I understand the question correctly, yes.
In your SAP Cloud Applications Studio, press View -> Repository Explorer.
Here you can look up the Customer Contract object (and all other objects available), and here you can also find detailed information about the objects and how you can use them.
Does that answer your question? 🙂
Thanks a lot for the information!
This certainly answers my question 🙂
Thanks with regards,
Also another small tip:
Allot of objects tend to have different names in the repository explorer compared to ByDesign. To look up objects that you find in ByDesign, try to look at the "UI Text" of objects, cause this is whats displayed in ByDesign as the objects' name.