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 cte

    Sample 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 space

    with 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 end

    Thanks 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