Data Shaping

[GetITDone]

I have all in 1 row 3 different surveys all of which I need to sum up for a report. I have check with my systems engineer and other collegue to no avail. (new)  It's a challenge for someone on this board!

3 Surveys on 1 row there could be up to 3 surveys per customer with varing techtypes.

Survey fields consist of  One Customer name, Survey company 1, Survey company 2, Survey company 3,  Cost 1, Cost 2, Cost3, # of Rooms 1, 2 and 3 , SurveyType1, SurveyType2, SurveyType3

I need to know

# of overall surveys per SurveyType per customer

# of overall surveys per Survey Company by SurveyType

Average costs per room per Survey company and per SurveyType

SurveyType have 5 types where 3 of them belong to one category and the other 2 another category.   I tried an if statement but this is just the tip of the iceberg and I am not sure where to go now.

HSIA Avg Cost Per Room = IF(reducedOpportunityView[siteSurveyType1]= "HSIA", (divide (reducedOpportunityView[All Surveys Total Cost],(reducedOpportunityView[HSIAGuestRoomsInclu

HSIA Avg Cost Per Room = IF(reducedOpportunityView[siteSurveyType1]= "HSIA", (divide (reducedOpportunityView[All Surveys Total Cost],(reducedOpportunityView[HSIAGuestRoomsIncluded]))))

I think ideally I need Survey 1 data in its own row, Survey 2 data in its own row and same with Survey 3 but how?

Thanks to anyone here looking for a challenge.

[Anonymous]

Where is your source data coming from/ why is it all in one row? Have you already asked them to reformat the data? 

[GetITDone]

Hello,

Yes I asked and they won't change it.

[GetITDone]

It's coming from CRM 

I will be asking the DBA if he could somehow split it so each Survey has  their own lines.

[GetITDone]

Having a resource issue, still no change