You notice that in some data source (or reports based on these data sources), the system shows 2 lines for the same entry, where in one line the entry is correct, and in the other it is shown as # (Not Assigned). For example, when you add the characteristics Account and Sales Order, you will see 2 lines: One with the account and the respective sales order, and one with the same account and the sales order being shown as #. You would expect both entries to be combined into 1 line, and it is not clear why the second line is shown.
A frequently used data source that shows this behavior is Opportunity Header and Item.
SAP Hybris Cloud for Customer
SAP Business by Design
Reproducing the Issue
- Go to the Business Analytics work center, view Design Data Sources
- Search for the respective data source (for example Opportunity Header and Item) and click on Preview.
- Add the respective fields to the view.
You will find that there may be 2 lines for the same data, where one of the lines shows the correct value and the other shows #, as in the mentioned example.
This is the correct behaviour for Combined data sources and all reports based on them. For further details on how this data source type works, please check the help document Data Source Types.
In short, when the fields in the report view come from different basic data source, this will result in 2 lines, where one shows the data available in the first data source, and the other line shows the data from the second data source.
The data source Opportunity Header and Item is a combination of the data sources Opportunity Header (CODOPPH) and Opportunity Item (CODOPPIB). So for example the field Opportunity ID, the start and end dates of the opportunity and weighted / expected values come from the header level. They are part of the header information of the opportunity. The Product Category ID (as well as other fields like item quantities or other product details) come from the item level. Since this data source is combined (not joined), the header and item data is not merged. The purpose of this is to avoid wrong key figure results. For example, if the Expected Value, which is header data, was replicated to the item level, the Expected Value would be multiplied by the number of items, which would be wrong.
In order to check whether your data source is of type Combined, you can either perform one of the following actions in the Business Analytics work center, view Design Data Sources:
1. Add the Data Source Type to the data source list:
- Click on Personalize > This Screen
- Add the Data Source Type to the view and Save.
2. Click on the magnifying glass in the Documentation column of the data source list. The data source documentation will show the type of the respective data source.
3. Create a Left Outer Join Data Source between the 'Opportunity Item' and 'Opportunity Header' Data Source, using simple join conditions like Account and Opportunity. Using this Custom Join instead of, for example, Opportunity Header and Item Standard Data Source, will then prevent the multiple lines in the report.
If this does not suit your business scenario, you can either use a report built on a basic data source, or alternatively create a join between several basic data sources.