The perfect solution for creating powerful yet inexpensive business solutions by integrating data from multiple SharePoint lists. Enesys RS Data Extension lets you leverage all the power of Microsoft SQL Server Reporting Services with your SharePoint Data.

Knowledge Base and Samples

Reporting Solution - Reporting from a Survey list

By using the Survey statement (a new statement available in version 3.6), you can build sophisticated reports from your Survey lists.
This article explains how to use the generic reports - available available for download - with your own survey lists. This article also provides additional in-depth information that will let you go further and customize the generic reports.

Sample Survey Reports

We have prepared a couple of reports that can be used with any SharePoint Survey list.

In order to use the reports:

  1. Download the reporting solution zip file.
  2. Unzip in any folder.
  3. Open the solution.
    Reporting Solution
  4. Modify the Shared Data Source connection string to point to your own SharePoint server.

As you can see from the previous screen shot, the solution contains two reports:

  • The SurveySummary report displays a summary report from any Survey list.
  • The SurveyDetails report displays the details of a single answer from any Survey list.

SurveySummary report

The SurveySummary is a report with a look and feel similar to the "Graphical summary" that is available through SharePoint web interface.

However, using the power of Reporting Services, you can export the results into PDF, Excel or Word. You can also customize the report to better suit your needs...

When running the report:

SurveyDetails report

The SurveyDetails report is a report that lets you display a specific response.

When running the report:

  • Enter the relative site url to the Survey list:
    Enter the relative site URL
  • Press the "Tab" key in order to get all the Survey lists inside the specified site:
    Select the survey list to query
  • Select the appropriate list title. All the answers of this Survey will be retrieved from SharePoint so you can choose the answer you want details from:
    Select the user answer
  • Select the answer you want details on.
    Here is a sample of the report, with a "Choice" question, a "Rating scale" question and a "Several lines of text" one:
    Report sample

Those reports are generic enough so that they can be run on any Survey list (if we have missed something, do not hesitate to contact our support).

However, you are not limited to use those generic reports. The rest of the article provides step-by-step instructions on how to build the generic report. This should provide the necessary information to build your own reports or customize the ones available in the downloadable solution.

Building your own Survey report

We will detail step by step the creation of the SurveySummary report, that tries to mimic the "Graphic summary" wiew available within SharePoint.
This will allow you to see the important steps that are necessary for creating a report on top of Survey data.

Designing the query

The first step will be to design the query using the Query Designer.

The query is straightforward and only needs a single statement for querying the Survey data:

  1. Add a new survey statement:
    Add new survey statement
  2. Enter the URL to the SharePoint site and click on the "Get Lists" button. All the Survey lists from the specified site will be displayed. Select the desired list and click on the "Add" button:
    Select the Survey list

The survey statement doesn't require much configuration; you can execute the query within the designer to see the results:

Results of the query execution
The format returned by the survey statement can be disconcerting; you can find more information about it inside the Enesys SharePoint Query manual.

We will now design a report using the data returned by the Survey statement.

Designing the report

The report is a bit more complex than most reports because we want it to work with any survey list. Several steps will be necessary:

Preparing the report

We will add a Table (or Tablix with BIDS 2008 and later) to the report layout and configure a few properties.

  1. Add a new Table/Tablix to the report.
    Add a new Table
  2. Set the DataSetName property of the table to use the dataset containing the Survey data:
    Set the DataSetName property
  3. Rename the table to "SurveyTable" so that we can later reference the table within some expressions:
    Rename the table name
  4. Remove the header (and footer if present) rows:
    Delete the header row
  5. Each question has several rows of data associated to it. We will first group the results by question.
    Right click on the Details row and add a Parent Group:
    Add a parent group
  6. Select the QuestionID field, and check the "Add a group header" checkbox:
    Set the group properties
  7. Depending on your BIDS version, a column may be added with the QuestionID field, delete it if this is the case:
    Delete the QuestionID column
  8. Right click on the newly created row and edit its group properties:
    Set the QuestionID group properties
  9. Change its name to "QuestionID" (this name will be used in expressions later):
    Set the QuestionID group properties
  10. Edit the first cell expression to concatenate the QuestionID and FieldName fields:
                    =Fields!QuestionID.Value & ". " & Fields!FieldName.Value
    Set the textbox expression

With some more work on enhancing the appealing of the table:

  • Removing the borders of all the cells
  • Merging the three cells of the QuestionID group row
  • Setting the QuestionID row font weight to Bold
  • Adding some space before and after the QuestionID row (SpaceBefore & SpaceAfter properties)

Here is what the report design looks like:

Report design

If you look at the SharePoint Summary report, you can see that there are two different designs for the questions:

This imposes us to manage the Rating Scale question type and the other question types differently.

In a first step, we will design the "other" question types.

Designing the answer rows

We will try to replicate the SharePoint design:
Question sample

We already have the question title (FieldName), so for each question, we will need three more rows of data:

  • A group on each available Choice, that contains two lines:
    • One for the Choice value (e.g.: Email)
    • One for the percentage and a small bar graphic (e.g.: 3 (43%) and the graph)
  • And at the bottom, a row that contains the total number of answers for this question.

We will then add the group for displaying the choices and their graph. This group will have two rows and will be grouped by Choice. This group will only be visible when the question type is not a Rating Scale (e.g.: "GridChoice" question type).

  1. Edit the "Details" row properties to group on the Choice column:
    Edit the Details group
    Set Details group properties
  2. Then switch to the Visibility tab and select whether to show or hide the group based on an expression:
    Set Details group visibility
    The following expression allows to show this group only when the field type is not a Rating Scale and that there are at least an answer for this question:
                    =IIF(Fields!FieldType.Value <> "GridChoice" And CountDistinct(Fields!AnswerID.Value, "QuestionID") > 0, False, True)
  3. Add a second row to the Details group
    Add a Details row
  4. Set the Choice column inside the first row of the Details group, and merge the three cells:
    Set the first Details row
  5. Add an expression to the first cell of the last line:
                    =Count(Fields!Value.Value) & " (" & FormatPercent(Count(Fields!Value.Value) / Count(Fields!Value.Value, "QuestionID"), 0) & ")"
    The QuestionID string represents the name of the parent group that we changed at the 9th step of the previous chapter.
  6. Now, merge the second and third cells and add a new graph of type "Bar":
    Add a Bar chart
    With BIDS 2008R2, you can use the new "Data Bar" report item instead, that is the same as the Bar graph, without most the visual modifications that we will remove anyway (title, legend, axis titles...).
  7. Augment the row height and width so that we can see a full chart, as we need to make some modifications.
    Bar chart
  8. Add the Value field as a data field:
    Add Value field
  9. And edit its properties so that the value represents the percentage of times this value was chosen:
    Set the series properties
    The expression of the "Value field" is:
                  =Count(Fields!Value.Value) / Count(Fields!Value.Value, "QuestionID")
  10. With BIDS 2008R2, a Category group is automatically added when you add a Graph, you can delete it:
    Delete the Category group

We will now make some adjustments to the chart:

  • Hide the chart title
  • Hide the X-Axis and Y-Axis titles
  • Hide the Legend
  • Edit the X-Axis properties to only shows the values between 0 and 1 (the minimum and maximum percentage values):
    Edit the X-Axis properties
    Edit the X-Axis properties
  • Edit the Y-Axis properties in order to remove the side-margins:
    Edit the Y-Axis properties
  • Hide both X-Axis and Y-Axis.
  • Reduce the height of the row to match its previous height. Here is what the report looks like when previewed:
    Report preview

Make the following modifications to match the SharePoint design:

  • Remove the border around the graph
  • Add a gray background to the graph area
  • Modify the series color
  • Modify some font sizes and colors
  • Add some left indent

And here is what it looks like:

Report preview

Now that we have handled the "other" question types, we will need to manage the Rating Scale question type.

Handling Rating Scale questions

The SharePoint summary survey report looks like this for Rating Scale questions:

Rating Scale question sample

We will have two columns, one for the "sub question" title and one with a vertical graph displaying all the answers (from GridStartNum to GridEndNum, with an optional value for the N/A, set if GridNATxt is not null).

We add a second row group, adjacent to the "Details" row group. This group would only be displayed when the current question is a Rating Scale question.

BIDS 2005 doesn't allow you to have adjacent groups. You can simulate this by creating a "middle" group so that:
  • Its parent group is the QuestionID group
  • Its child group is the Details group
  1. Add a second group, adjacent to the Details row group:
    Add new adjacent group
  2. For the Rating Scales, we need to group on the SubQuestionTitle field that contains the title of the sub-question:
    Group on the SubQuestionTitle field
  3. Set the name of the group to SubQuestionTitle so that we can reuse the group name in expressions:
    Rename the group
  4. Switch to the Visibility tab and set that it should only be visible when the current question is a GridChoice:
                  =IIF(Fields!FieldType.Value = "GridChoice", False, True)
    Set the group visibility
  5. Merge the first and second cells of the newly created row, and set its value to the SubQuestionTitle field:
    Set textbox properties
  6. Now, add a graph of "Column" type inside the third cell:
    Add a Column chart
    And augment the row height so that you can fully see the graph.
  7. Add the Value field to the data fields:
    Add the Value field
  8. Right click on the field to edit its properties, and set the "Value field" to:
                    =Count(Fields!Value.Value) / CountDistinct(Fields!AnswerID.Value, "SurveyTable")
    The SurveyTable string represents the name of the Table that we changed at the beginning of the article.
    And set the "Category field" of the series to:
                    =IIF(Fields!Choice.Value = Fields!GridEndNum.Value + 1 And Not(IsNothing(Fields!GridNATxt.Value)), Fields!GridNATxt.Value, Fields!Choice.Value)
    Set series properties
  9. Add a category group on the Choice column:
    Add Category group on Choice column
  10. Then edit its properties, and set the Label property to:
                    =IIF(Fields!Choice.Value = Fields!GridEndNum.Value + 1 And Not(IsNothing(Fields!GridNATxt.Value)), Fields!GridNATxt.Value, Fields!Choice.Value)
    Edit group properties
  11. And, inside the Sorting tab, set the following sort:
                    =IIF(IsNumeric(Fields!Choice.Value), Val(Fields!Choice.Value), Fields!Choice.Value)
    Edit sorting properties

We will now make some adjustments to the chart:

  • Hide the chart title
  • Hide the legend
  • Hide the titles for both X and Y axis.
  • Edit the X-Axis properties:
    • Set the Interval value to =1
    • Set the Interval type to Number
    • Enable side margins
    Edit X-Axis properties

    Then, switch to the Labels tab and check the "Hide first and last labels along this axis" option.
    Edit X-Axis properties
  • Edit the Y-Axis properties to set the Minimum and Maximum to properties to 0 and 1:
    Edit Y-Axis properties
  • Hide the Y-Axis

You can now preview the report and see the current visual aspect of Rating Scale questions:

Report preview

Make the following modifiations to the design:

  • Vertically centering the sub-question titles (not working with BIDS 2008)
  • Changing the Axis colors (labels, major ticks and line) from Black to Gray
  • Changing the series color
  • Adding a Gray border to the Chart Area
  • Showing data labels and setting their Format to "0%" and their Color to Gray
  • Removing the chart border

Here is what it looks like once those modifications were done:

Report preview

There are two more things that need to be done:

  • Adding a row that displays the total number of answers for a question
  • Adding a visual clue of the value for Rating Scale questions (the "Very poor", "Average" or "Excellent" values that you can see inside the SharePoint report)

Adding a total row

The total row must be displayed once for each question, so we will add a new row inside the QuestionID group at the end of the table.

  1. Right click on the last line, and insert a row outside the group – below:
    Insert outside group
  2. Edit the first textbox properties and set this expression as its Value:
                    ="Total: " & IIF(Fields!FieldType.Value = "GridChoice", CountDistinct(Fields!AnswerID.Value, "SurveyTable"), CountDistinct(Fields!AnswerID.Value))
    This will count the distinct number of answers for the current question.
    Set row text

Modify the row visual properties:

  • Merge the three cells
  • Edit the cell properties:
    • Add a top-border
    • Add some left indent
    • Set the VerticalAlign property to Middle
    • Decrease the font size
    • Set the color to Gray

Here is what the report looks like with those modifications:

Report preview

The last thing left to do is to add the visual clues about the Rating Scale values.

Adding Rating Scale value clues

For this, we will add two more columns to the report that we will display just before the first chart of each Rating Scale question. This will give us three columns that will display the minimum value text, the middle value text and the maximum value text, that we get with the GridTxtRng1, GridTxtRng2 and GridTxtRng3 fields.

  1. Right click on the table header and add 2 columns:
    Add two columns
  2. Add a new row inside the QuestionID group, just above the SubQuestionTitle group:
    Insert new row
  3. Now merge the cells of the two new columns with all their previous cells, except for the newly created row:
    Merge cells
  4. And put the three GridTxtRng1, GridTxtRng2 and GridTxtRng3 columns into those cells:
    Set cell texts

Here is what the report looks like now:

Report preview

By making the necessary adjustments to the three cells:

  • Centering the middle text
  • Aligning the left and right text to the graph bounds
  • Changing font size and color

We get the result shown in the following screen shot:

Report preview