[Solved] Crosstab summary calculation

[intrepid402]

Hi,
I am having trouble getting a calculation summary in a crosstab wrong.


In this example I only have to calculate the score (86) from above questions by multiply each answer by different number and adding it up. Meaning, multiply answer to Question ID 201 with 2, 202 with 5 ,so on and so forth and add those numbers . This can be done in crosstab, right? I don't have to manipulate the answers in the crosstab.

I made a data item named "Score" in a query. But I think my expression syntax is wrong.

The expression I have now is:

total(
(case
when ([Model Query Subjects].[Survey Question History].[SurveyQuestionID] = '201' then [EmployeeAnswer] *2 for [SurveyRunIdentifier],[EmployeeID]  )
when ([Model Query Subjects].[Survey Question History].[SurveyQuestionID] = '202' then [EmployeeAnswer] *5 for [SurveyRunIdentifier],[EmployeeID]  )
when ([Model Query Subjects].[Survey Question History].[SurveyQuestionID] = '203' then [EmployeeAnswer] *4 for [SurveyRunIdentifier],[EmployeeID]  )
when ([Model Query Subjects].[Survey Question History].[SurveyQuestionID] = '204' then [EmployeeAnswer] *4 for [SurveyRunIdentifier],[EmployeeID]  )
when ([Model Query Subjects].[Survey Question History].[SurveyQuestionID] = '205' then [EmployeeAnswer] *5 for [SurveyRunIdentifier],[EmployeeID]  )
end)

I would really appreciate if anyone knows how to to do this. Thank you so much!

[Rahul Ganguli]

Hi,

You can try the following formula

total(
(case
when ([Model Query Subjects].[Survey Question History].[SurveyQuestionID] = '201') then ([EmployeeAnswer] *2)
when ([Model Query Subjects].[Survey Question History].[SurveyQuestionID] = '202') then ([EmployeeAnswer] *5)
when ([Model Query Subjects].[Survey Question History].[SurveyQuestionID] = '203') then ([EmployeeAnswer] *4)
when ([Model Query Subjects].[Survey Question History].[SurveyQuestionID] = '204') then ([EmployeeAnswer] *4)
when ([Model Query Subjects].[Survey Question History].[SurveyQuestionID] = '205') then ([EmployeeAnswer] *5)
else 0
)
for [SurveyRunIdentifier],[EmployeeID])

Regards,
Rahul

[intrepid402]

Thanks Ganguli.
Thats the formula I plugged in, but for some reason report studio did not like single quotes around '201', '202' and so forth. With that formula and single quotes taken, it worked as expected.

Thank you again!