Webi Report Merge Dimension

I have a webi report and i developed 2 tabs for the report
Lets say I have 3 columns

Contract ID , Primary Name and Contract Details in one report. I am getting the primary name here because I am using a report filter which says role=primary

And in another tab I am getting the results

Contract ID,Agent Name and Contract Details . I am getting agent name here because i am using role=agent at the report level.

Now I want to bring in a new tab all the below columns

ContractID , Primary Name ,Agent Name , Contract Details.

But when i am doing this i am removing the role filter in the report and dragging the columns. but the report generates in below format

Contract ID   Primary Name   Agent Name   Contract Details
A                       B1                     XXXXX
A                               C1             XXXXX
I want to eliminate the contract ID being displayed twice and having primary name and agent name empty and get all the values in the same row

I got the answer but wanted to share here , so that i could be used for future reference
There are two solutions:

First Solution: Create two queries, one with a query filter (Role = “Primary”) and the second with (Role = “Agent”).

Then merge the 2 queries with [Contract ID].

and build your table.

Second Solution (with one query):

Define the following variables:

Primary Name: =[Name] Where ([Role]=“Primary”) In ([Contract ID])

Agent Name; =[Name] Where ([Role]=“Agent”) In ([Contract ID])

and use both in the table

2 Likes

Or a third solution in Webi make a variable =max(contract id) and display the variable instead of the contract id.