how to massively drill down a survey
-
Good day all,
I have a matrix report which its column spans about 150 columns
I will represent it with a pivot code as sample:
select 1 as Base, InterviewerName,RetailerName,StoreName,Address,Phone,Email,Gender,MaritalStatus,Location,MordernTrade,NeighborhoodStore,OpenMarket,Question1,Question2,Question3,Question4,Question5_2,Question5_4,Question5_11,Question6a,Question6b,Question7a,Question7b,Question8a,Question8b,Question9a,Question9b,Question10_2,Question10_7,Question11_2,Question11_4,Question12,Question13a,Question13b,Question13c,Question13d,Question13e_2,Question13e_4,Question14,Question15_3,Question15_6,Question15_12,Question16,Question17a,Question17b,Question17c_2,Question17c_3,Question17c_7,Question18a_4,Question18a_7,Question18a_9,Question18b_4,Question18b_12,Question18c_2,Question18c_4,Question19,Question20,Question21a,Question21b,Question22,Question23a_2,Question23a_4,Question23b_4,Question23b_10,Question23c_2,Question23c_3,Question24,Question25,Question26_2,Question26_5,Question27_2,Question27_4,Question28,Question29a,Question29b
from
(
select PhoneNumber, QuestionName, Answer
from SingleAnswers where year(questiondate) = year(GetDate()) and phonenumber like '%grocery%'
) z
pivot
(
max(Answer)
for QuestionName in (InterviewerName,RetailerName,StoreName,Address,Phone,Email,Gender,MaritalStatus,Location,MordernTrade,NeighborhoodStore,OpenMarket,Question1,Question2,Question3,Question4,Question5_2,Question5_4,Question5_11,Question6a,Question6b,Question7a,Question7b,Question8a,Question8b,Question9a,Question9b,Question10_2,Question10_7,Question11_2,Question11_4,Question12,Question13a,Question13b,Question13c,Question13d,Question13e_2,Question13e_4,Question14,Question15_3,Question15_6,Question15_12,Question16,Question17a,Question17b,Question17c_2,Question17c_3,Question17c_7,Question18a_4,Question18a_7,Question18a_9,Question18b_4,Question18b_12,Question18c_2,Question18c_4,Question19,Question20,Question21a,Question21b,Question22,Question23a_2,Question23a_4,Question23b_4,Question23b_10,Question23c_2,Question23c_3,Question24,Question25,Question26_2,Question26_5,Question27_2,Question27_4,Question28,Question29a,Question29b)
) piv
Even though i did not use a pivot, but the report was done using ssrs and its in the format above.
The original dataset is this:
select PhoneNumber, QuestionName, Answer
from SingleAnswers where year(questiondate) = year(GetDate()) and phonenumber like '%grocery%'And the QuestionName forms the columns.
My question is that i want to do a thorough cross analysis of each question as against all replies that pertain to that question and i don't know how to go about it in ssrs
Below is a sample of the way i'm doing it in sql server, but its a lot of typing and calculating in tsql:
with cte as (
select 1 as Base, InterviewerName,RetailerName,StoreName,Address,Phone,Email,Gender,MaritalStatus,Location,MordernTrade,NeighborhoodStore,OpenMarket,Question1,Question2,Question3,Question4,Question5_2,Question5_4,Question5_11,Question6a,Question6b,Question7a,Question7b,Question8a,Question8b,Question9a,Question9b,Question10_2,Question10_7,Question11_2,Question11_4,Question12,Question13a,Question13b,Question13c,Question13d,Question13e_2,Question13e_4,Question14,Question15_3,Question15_6,Question15_12,Question16,Question17a,Question17b,Question17c_2,Question17c_3,Question17c_7,Question18a_4,Question18a_7,Question18a_9,Question18b_4,Question18b_12,Question18c_2,Question18c_4,Question19,Question20,Question21a,Question21b,Question22,Question23a_2,Question23a_4,Question23b_4,Question23b_10,Question23c_2,Question23c_3,Question24,Question25,Question26_2,Question26_5,Question27_2,Question27_4,Question28,Question29a,Question29b
from
(
select PhoneNumber, QuestionName, Answer
from SingleAnswers where year(questiondate) = year(GetDate()) and phonenumber like '%grocery%'
) z
pivot
(
max(Answer)
for QuestionName in (InterviewerName,RetailerName,StoreName,Address,Phone,Email,Gender,MaritalStatus,Location,MordernTrade,NeighborhoodStore,OpenMarket,Question1,Question2,Question3,Question4,Question5_2,Question5_4,Question5_11,Question6a,Question6b,Question7a,Question7b,Question8a,Question8b,Question9a,Question9b,Question10_2,Question10_7,Question11_2,Question11_4,Question12,Question13a,Question13b,Question13c,Question13d,Question13e_2,Question13e_4,Question14,Question15_3,Question15_6,Question15_12,Question16,Question17a,Question17b,Question17c_2,Question17c_3,Question17c_7,Question18a_4,Question18a_7,Question18a_9,Question18b_4,Question18b_12,Question18c_2,Question18c_4,Question19,Question20,Question21a,Question21b,Question22,Question23a_2,Question23a_4,Question23b_4,Question23b_10,Question23c_2,Question23c_3,Question24,Question25,Question26_2,Question26_5,Question27_2,Question27_4,Question28,Question29a,Question29b)
) piv
)
select sum(Base) as Base,
SUM(case when Gender = 1 then Base Else 0 End) as 'Male',
SUM(case when Gender = 2 then Base Else 0 End) as 'Female',
SUM(case when MaritalStatus = 1 then Base Else 0 End) as 'Married',
SUM(case when MaritalStatus = 2 then Base Else 0 End) as 'Single',
SUM(case when not(MaritalStatus in (1,2)) or MaritalStatus is null then Base Else 0 End) as 'Others',
SUM(case when Location = 1 then Base Else 0 End) as 'Lagos',
SUM(case when Location = 2 then Base Else 0 End) as 'Ibadan',
SUM(case when Location = 3 then Base Else 0 End) as 'Port Harcourt',
SUM(case when Location = 4 then Base Else 0 End) as 'Enugu',
SUM(case when Location = 5 then Base Else 0 End) as 'Abuja',
SUM(case when Location = 6 then Base Else 0 End) as 'Jos',
SUM(case when Location = 7 then Base Else 0 End) as 'Kano',
SUM(case when MordernTrade = 1 then Base Else 0 End) as 'Key Account Store',
SUM(case when MordernTrade = 2 then Base Else 0 End) as 'Independent Store'
from cteSample below of what i want to achieve:
-
Whenever you need a lot of columns like that, the best way to do it is to either have a query that delivers all the columns in T-SQL, or to have all the desired columns appear as rows where a given column in the row designates which column to appear in, and another column designates which row, and then use either of those queries in your SSRS report as a dataset. Most of the time, when you use the latter method, you use a Matrix control in SSRS, and with the former, you always use a Tablix.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Hi Steve,
Thanks for your reply, i actually used a string_split to handle the massive columns and did a pivot to handle each column and i got what i wanted.
Thanks very much.
This is the code rework below:
Declare @Questions VarChar(Max) = 'Gender,MaritalStatus,Location,Question1,Question2,Question3a,Question3b,Question3c,Question4,Question5a,Question5b,Question5c,Question6a,Question6b,Question6c,Question6d,Question7a,Question7b,Question8a,Question8b,Question9a,Question9b,Question9c,Question10,Question11a, ...';
--shortened because of spacewith sourceRows As
(
select PhoneNumber, QuestionName, AnswerValue
from SingleAnswers
where year(questiondate) = year(GetDate()) and phonenumber like '%grocery%'
),
selections As
(
select
SR.PhoneNumber,
SS.value As Selection,
SR.AnswerValue As Criteria
from sourceRows SR
join string_split(@questions, ',') SS On SR.QuestionName = SS.value
),
pivotAnswers As
(
select Count(*) as Base,
PhoneNumber,
Max(InterviewerName) As InterviewerName,
Max(RetailerName) As RetailerName,
Max(StoreName) As StoreName,
Max(Address) As Address,
Max(Phone) As Phone,
Max(Email) As Email,
Max(Gender) As Gender,
Max(MaritalStatus) As MaritalStatus,
Max(Location) As Location
from sourceRows
pivot
(
max(AnswerValue)
for QuestionName in
(InterviewerName, RetailerName, StoreName, Address, Phone, Email, Gender, MaritalStatus, Location)
) piv
group by
PhoneNumber
)
,
unpivotQuestions As
(
Select Base, PhoneNumber,Selection, Criteria, InterviewerName, RetailerName, StoreName, Address, Phone, Email, Gender, MaritalStatus, Location
from pivotAnswers PA
Cross Apply
(
Select 'All' As Selection, 'All' As Criteria
Union
Select Selection, Criteria From selections S Where S.PhoneNumber = PA.PhoneNumber
) X
),
cte as (
Select
cast(Selection as nvarchar(150)) as Selection,
'All Respondents' as Criteria,
Count(*) As Base,
SUM(case when Gender = 'Male' then 1 Else 0 End) as 'Male',
SUM(case when Gender = 'Female' then 1 Else 0 End) as 'Female',
SUM(case when MaritalStatus = 'Married' then 1 Else 0 End) as 'Married',
SUM(case when MaritalStatus = 'Single' then 1 Else 0 End) as 'Single',
SUM(case when not(MaritalStatus in ('Married','Single')) or MaritalStatus is null then 1 Else 0 End) as 'Others',
SUM(case when Location = 'Lagos' then 1 Else 0 End) as 'Lagos',
SUM(case when Location = 'Ibadan' then 1 Else 0 End) as 'Ibadan',
SUM(case when Location = 'Port Harcourt' then 1 Else 0 End) as 'Port Harcourt',
SUM(case when Location = 'Enugu' then 1 Else 0 End) as 'Enugu',
SUM(case when Location = 'Abuja' then 1 Else 0 End) as 'Abuja',
SUM(case when Location = 'Jos' then 1 Else 0 End) as 'Jos',
SUM(case when Location = 'Kano' then 1 Else 0 End) as 'Kano'
From unpivotQuestions
group by Selection
union all
select Selection, Criteria,
count(*) as Base,
SUM(case when Gender = 'Male' then 1 Else 0 End) as 'Male',
SUM(case when Gender = 'Female' then 1 Else 0 End) as 'Female',
SUM(case when MaritalStatus = 'Married' then 1 Else 0 End) as 'Married',
SUM(case when MaritalStatus = 'Single' then 1 Else 0 End) as 'Single',
SUM(case when not(MaritalStatus in ('Married','Single')) or MaritalStatus is null then 1 Else 0 End) as 'Others',
SUM(case when Location = 'Lagos' then 1 Else 0 End) as 'Lagos',
SUM(case when Location = 'Ibadan' then 1 Else 0 End) as 'Ibadan',
SUM(case when Location = 'Port Harcourt' then 1 Else 0 End) as 'Port Harcourt',
SUM(case when Location = 'Enugu' then 1 Else 0 End) as 'Enugu',
SUM(case when Location = 'Abuja' then 1 Else 0 End) as 'Abuja',
SUM(case when Location = 'Jos' then 1 Else 0 End) as 'Jos',
SUM(case when Location = 'Kano' then 1 Else 0 End) as 'Kano'
From unpivotQuestions
group by
Selection,Criteria
)
select * from cte where
order by Selection,case Criteria when 'All Respondents' then 1 else 2 endThanks Steve
-
You are very welcome!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply