Hi, I am really stuck how to realise the following report to my partners:

I would like to provide to our partners info about the commissions that we have to pay them and the commissions that they have to pay us for the Deals that we have made with their reference.

As general the partner has to have access to table that provide data from the 

DEALS MODUL 

CONTACT MODUL

Here is what kind of colums the table has to have:

 DEAL NAME

 DEAL TYPE 

CONTACT ( this is lookup filed and provide the name of the final customer)

RENT START DATE 

RENT END DATE 

RENT PERIOD 

AMOUNT

COMMISSION OWNER ( this is lookup filed in the contact modul and provide the name of the partner)

COMMISSION PAID (Y/N field)

COMMISSION AMOUNT 

AMOUNT W/O COMMISSION 

AMOUNT COLLECTED BY (pickup column that could have the following values Us or Partner)

AMOUNT DUE TO US (this is formula field, that is missing in my DEALS MODUL, calculates the amount that has to be paid. The money from the deal are collected  by the partner or by the us or by the partner. If the partner has collected the moeny for the deal he has to pay us the AMOUNT W/O COMMISSION)

AMOUNT DUE TO PARTNER (this is formula field, that is missing in my DEALS MODUL, calculates the amount that has to be paid. The money from the deal are collected  by the partner or by the us or by the partner. If we have collected the moeny for the deal than we have to pay the partner the COMMISSION AMOUNT)

I tried to realise that using the following approaches and have the following problems:

1. Creating REPORT based on the DEALS system table 

The problem that I have here is following: 

For the lookup Columns like CONTACT, COMMISSION OWNER I do not have values but IDs

2. Creating REPORT based on the Query Table

The problem that I have here are following:

2.1  IF statement calculations

I could not calculate the values in the columns AMOUNT DUE TO US and AMOUNT DUE TO PARTNER because it is IF STATEMENT calculation and the QUERY Table, as far I see, alows just numeric calculations.

The IF statement  for the AMOUNT DUE TO US sounds liket that 

IF (AMOUNT COLLECTED BY = US, 0,AMOUNT W/O COMMISSION)

IF (AMOUNT COLLECTED BY = PARTNER, 0,COMMISSION AMOUNT)

I would like to provide the Full name of the Contact and the Commission owner but it is not possible in the Query table to concat the First Name and Family Name in a separate column. 

I have experience in excel, google spreadsheet, zoho sheets, but the SQL and Zoho Reports is something new for me. 

10x in advance for the hand given