Ben Nagel

Qualtrics Tip: Rich Reporting by Importing Responses

Imagine being the administrator of two different surveys, both delivered to university sophomores, one in fall, and one in spring.  Responses to both surveys have been collected, and the surveys have closed.  At that late stage, you realize that it would be very helpful to explore the correlations between a question on Survey A and several questions on Survey B.  It would also be helpful to include the college affiliation (e.g., College of Business versus College of Liberal Arts) of the respondents in your reporting.  In our office, we frequently find ourselves in similar situations.  Obviously, there are many solutions to the problem, but Qualtrics provides a good one.

It is relatively straightforward in Qualtrics to approach the problem this way:

• create a survey, Survey C, that is a hybrid of Survey A and Survey B
• add an Embedded Data element (a Qualtrics term for extra information attached to a survey response) to hold the college affiliation of each respondent
• fill the new survey with selected answers from Survey A and Survey B
• include data from your student information system (SIS), such as the college of each respondent
• quickly put together charts, response statistics, and crosstab reports to analyze the hybrid data within Qualtrics.

I will follow this post with an example of how we’ve used this technique for quick, ad hoc reporting that brings together data from multiple surveys with SIS information using only duct tape and common household items.  Stay tuned.

Using the Enhanced VALUES Clause in SQL 2008

For those of us who spend time querying Microsoft SQL Server, each new version of the product gives us new functionality that can be as helpful as a new belt full of tools to a carpenter.  One tool (first available in SQL 2008) that has proved useful in our ad hoc queries is the expanded VALUES clause.  I use it in three primary ways:

1. A more intuitive way to insert data into a table.
2. A great technique for quick and dirty derived tables.
3. A way to aggregate data over columns rather than rows.

Some SQL statements that spell out the details of those three techniques follow.  The first two are fairly easy to understand by simply running the SQL code.  The third technique is a bit more involved:  for more explanation and code samples, refer to this article in SQL Server Magazine by Itzik Ben-Gan.

-- declare a table variable for use below
declare @TableVar table
      (
      TermId int,
      LicenseId int,
      MaleCount int,
      FemaleCount int,
      [Description] varchar(500)
      ) 

-- first use of the values clause:  one insert statement inserting multiple rows.
insert into @TableVar
      values (20083, 1, 12, 15, 'Sample data - bogus'),
      (20093, 4, 31, 47, 'Sample data - bogus'),
      (20103, 3, 50, 22, 'Sample data - bogus'),
      (20113, 2, 67, 83, 'Sample data - bogus'),
      (20123, 4, 39, 58, 'Sample data - bogus')

-- second use of values clause:  inline, quick & dirty derived table
select t.TermId, t.LicenseId, t.MaleCount, t.FemaleCount, tmp.*
      from
            (
            values -- use values to create derived table
                  (1, '130301-N','Science Chemistry 9-12 + 5-8 General Science'),
                  (2, '130302-N', 'Science Physics 9-12 + 5-8 General Science'),
                  (3, '130400-N', 'Science Earth 9-12 + 5-8 General Science'),
                  (4, '130600', 'Science General 5-8')
            ) as tmp (LicenseId, LicenseCode, LicenseDescription) -- name columns
      join @TableVar t
      on tmp.LicenseId = t.LicenseId

-- third use of values clause:  aggregate function over columns
-- rather than rows.  Better performance than unpivot with large sets.
select TermId,
      (
      select max(mycol)
      from 
            (values ('FemaleCount', FemaleCount), ('MaleCount', MaleCount)) as mytable(mylabel, mycol)
      ) maximum_value
    from @TableVar 

-- compare syntax above to case statement
select TermId, 
	case 
		when FemaleCount > MaleCount then FemaleCount
		else MaleCount
	end maximum_value
	from @TableVar

-- now compare to unpivot 
select TermId, max(col) as maximum_value
    from 
		(
		select TermId, col
			from @TableVar
			unpivot (col for cols in (FemaleCount, MaleCount)) as unpivott
		) p
    group by TermId