How to properly merge N:N Dimensions from 2 queries from different Universes?

[WvandeHoef]

I am an end user with access to SAP WEBI Version SAP BusinessObjects BI Platform 4.3 Support Pack 3 Patch 5 Version : 14.3.3.4649.5 where the option to Link/Join data in the Data Window (ALT+4) is not enabled.

I can build my own queries from the available Universes, but cannot create or maintain those Universes.

For my team I need to create a report that uses data from 2 different Universes and combines that for further review by the individuals. I can do this by pulling the data and write an VBA Macro in Excel, sort and XLOOKUP the required data, but as the report needs to be scheduled and send direct to the end user this needs to be in BI without any further user interference.

The two queries pull the following data:

Query 1: Sales Order Data

Dimensions:

Sales Office (Text), Sold-To Party No (String), Sold-To Party Name (String), Material (String), Customer Material (String), Sales Document Type (String), Sales Document Number (String), Sales Document Item (String), Schedule Line (String), Requested Delivery Date (Date), Confirmed Delivery Date (Date)

Measure:

Confirmed Quantity (Numeric, Aggregation: Sum)

Query 2: Sales Deal Data

Dimensions:

Sales Office (Text), Customer (String), Customer Name (String), Material (String), Customer Material (String), Sales Deal ID (String), Created On (Date), Valid From (Date), Valid To (Date), Status (String)

Measures:

Deal Base Qty (Numeric, Aggregation: Sum), Deal Remaining Qty (Numeric, Aggregation: Sum)

Purpose of the report

The new report needs to show for each Sales Order Line, what is the ID, Valid To Date and Remaining quantity for the last Sales Deal created.

Example for Sales Order Data

Example Sales Order Data

Example for Sales Deal Data

Desired Report Output

I am struggling now for too long to get the report working but keep running into #MULTIVALUE and #CONTEXT errors whatever way I try. As it is not 1:1 joining but N:N I lack the knowledge how to properly do this and searching in the forum and watching Youtube video’s on this topic does not bring me the wanted solution as the data needed is not from the Measures, but from the Dimension.

So far, my attempts have been:

• Merge the common dimensions Sales Office (Text), Sold-To Party No (String), Sold-To Party Name (String), Material (String) and pull these first in the report and then pull the Sales Order Data (Primary Data) and then create Details for the Dimensions from the Sales Deal Data and pull these in the report. This results in #Multivalue errors, I cannot resolve in any way by changing table or query settings.

• Merge the common dimensions Sales Office (Text), Sold-To Party No (String), Sold-To Party Name (String), Material (String) and pull these first in the report and then pull the Sales Deals Data (Primary Data) and then create Details for the Dimensions from the Sales Order Data and pull these in the report. This again results in #Multivalue errors that I cannot resolve in any way by changing table or query settings.

• Remove the Merges and first pull the Dimensions and Measures from the Sales Order Data in the report and then create Match Key Variables for both Sales Orders and Sales Deals to create a N:1 match and use that in a Variable to show the Last Available Sales Deal like an XLOOKUP in Excel would do:

Find the last Valid To for any Sales Deal for this (Sales Office + Customer + Material)

Measure “Last Deal?” =Max([Valid To]) In ([Sales Deals].[Sales Office];[Customer];[Sales Deals].[Material])

Convert the Measure into Dimension so it can be used in a Formula:

Dimension “Last Deal” = [Last Deal?]

Create a Key like I would do in Excel to get a 1:1 Match

Dimension “SD Match Key” = If([Valid To]=[Last Deal];[Sales Deals].[Sales Office]+[Customer]+[Sales Deals].[Material];"")

Dimension “SO Match Key” =[Sales Orders].[Sales Office]+[Sold-to Party No]+[Sales Orders].[Material]

Now find the “Sales Deal ID” for that matching key:

Dimension “Last Created Deal ID” =[Sales Deal ID] In ([Sales Deals].[Sales Office];[Customer];[Sales Deals].[Material];[Sales Deal ID]) Where ([SD Match Key]=[SO Match Key])

Pulling this into the report results in a #CONTEXT error. When merging the common dimensions Sales Office (Text), Sold-To Party No (String), Sold-To Party Name (String), Material (String) again removes the error, but results in a blank cell, when unmerging Material, the #CONTEXT error appears again.

I could live with a report hat would do a full join/merge on all possibilities, as then I could create a Show/Hide Variable to hide all lines not needed, but at present I am running in circles and doing trial and error to get a solution but am lost in finding a proper solution and clearly miss some knowledge on how to do this properly.

Bottom line question: Any Expert advise on how to do this properly?