Creating reports with Microsoft Reporting Services
Introduction
This white paper is designed to help UNICOM Intelligence users produce reports with Microsoft Reporting Services. The paper describes many of the standard market research crosstabs. Market research requirements are different from requirements that are commonly required for other types of BI reporting. The white paper is not designed to cover all situations and assumes a basic knowledge of Microsoft Reporting Services. Examples are provided with step-by-step instructions.
Note The following examples were tested with Microsoft Reporting Services in Microsoft SQL Server 2012.
Microsoft Reporting Services work area
The Reporting Services work area is divided into three main sections:
▪Tool box: Provides tools that can be directly dragged into the report's working area.
▪Working area: Used to design and preview reports.
▪Solution area: Used to add and remove project data sources, data sets, and reports.
Data sources are used to connect to databases. Data sets are used to define a report's data scope.
Building a report - preparation
1 To create a new project, start SQL Server Data Tools: From the Windows Start menu, navigate to Microsoft SQL Server 2012 > SQL Server Data Tools
Note SQL Server Data Tools are report design tools that are based on Visual Studio. In previous SQL Server versions, SQL Server Data Tools was called Business Intelligence Development Studio.
2 Create new project (Report Server Project) named DataCollectionReports, that is based on Reporting Services template.
3 Create a data source for connecting to the data that is used when building reports.
Under the newly created DataCollectionReports project, right-click Shared Data Sources and then click Add New Data Source. The Shared Data Source Properties dialog displays.
Input the SQL Server database parameters by using Edit... to build the connection string. For example, you could enter information for connecting to the Household database.
Server name: Enter an appropriate SQL Server IP address.
Log on to the server: Enter the appropriate SQL Server log in credentials.
Connect to a database: Select the appropriate database from the list.
Enter the appropriate server and database information and select the Save my password option to avoid inputting credentials each time the data source is used.
Click OK to return to the Shared Data Source Properties dialog. The defined database connection properties display.
Click OK. The new database connection is now listed under Shared Data Sources.
4 Create a dataset.
Under the newly created DataCollectionReports project, right-click Shared Datasets and then click Add New Dataset. The Shared Dataset Properties dialog displays.
Input the appropriate query parameters.
Name: Input an appropriate dataset name (for example, DS_Top_Dimension).
Data source: Select the data source that was created in step 3.
Query type: Select Text, then input a SELECT query clause in the Query field. The clause defines the data that is rendered in the report. For example:
SELECT [Serial]
,Region.Label AS region
,tenure.Label as tenure
FROM [Dimensions] D
inner join region_Lookup region on D.region = Region.CategoryID AND region.Language = 'ENU' AND region.Context = 'Analysis' AND region.LabelType = 'Label'
inner join tenure_Lookup tenure on D.tenure = tenure.CategoryID AND tenure.Language = 'ENU' AND tenure.Context = 'Analysis' AND tenure.LabelType = 'Label'
order by Serial
You can first test the SELECT query clause in SQL Server, and then paste it into the Query field. You can also use a stored procedure that was created in SQL Server.
5 Create a report: Under the newly created DataCollectionReports project, right-click Reports, and then click Add New Report.
6 Enable report deployment to the web.
▪Configure the Web Service URL to the project's TargetServerURL property (this makes reports visible on the web).
▪In the Project menu, select DataCollectionReports Properties.
▪Enter the Web Service URL that is specified in Reporting Services in the TargetServerURL property field and click Apply.
▪Deploy to the web server (if the reports are ready): File > Deploy
Creating a basic report
Note This example uses the UNICOM Intelligence Museum example, and assumes that a data source, that connects to the Museum database, already exists.
1 Open the DataCollectionReports project in SQL Server Data Tools. Windows Start menu > Microsoft SQL Server 2012 > SQL Server Data Tools
2 Run the report wizard via the Add New Report feature.
Select the Museum data source on the wizard's Select the Data Source page and then click Next.
Input a SELECT query clause on the Design the Query page. The clause defines the data that is rendered in the report. For example:
SELECT [Serial]
,gender.Label AS gender
,age.Label as age
,biology.Label as biology
FROM [Dimensions] D
inner join gender_Lookup gender on D.gender = gender.CategoryID AND gender.Language = 'ENU' AND gender.Context = 'Analysis' AND gender.LabelType = 'Label'
inner join age_Lookup age on D.age = age.CategoryID AND age.Language = 'ENU' AND age.Context = 'Analysis' AND age.LabelType = 'Label'
inner join biology_Lookup biology on D.biology = biology.CategoryID AND biology.Language = 'ENU' AND biology.Context = 'Analysis' AND biology.LabelType = 'Label'
order by Serial
You can first test the SELECT query clause in SQL Server, and then paste it into the Query field. Click Next after you enter the SELECT query.
Select Matrix on the Select the Report Type page and then click Next.
Select the appropriate columns, rows, and details from the available fields on the Design the Matrix page.
Move age to the Columns list.
Move biology to the Rows list.
Move Serial to the Details list.
Select the Ocean style on the Choose a Matrix Stylepage and then click Next.
Enter Museum_Basic as the report name in the Completing the Wizard page and then click Finish.
3 Change the cell details by clicking in a table cell and changing the value to CountDistinct(Serial).
4 You can add also add a summary row and column for the selected fields. For example:
Highlight the [biology] field, right click the field, and select Add Total > After. A summary item for the [biology] row is created.
by
5 You can preview the report by switching focus to the Preview tab.
Filtering records
Reports can be filtered to focus on specific field information.
1 Identify the dataset that is used by the current report: Choose the Tablix control, and from the report's Properties dialog, locate the DataSetName property, taking note of the value.
2 Add a filter by right-clicking the appropriate DataSets value. The Dataset Properties dialog displays.
3 Click Filters to display the Change filters page.
4 Enter the appropriate filter criteria and then click OK.
5 You can preview the filtered report by switching focus to the Preview tab.
Updating the detail as count percentage
The following steps provide information on updating the detail as count percentage.
The referenced text box controls are illustrated in the following image:
1 Open the Museum_Basic report and re-save it with the name Museum_Percentage.
2 Change the detail to display as a percentage value.
Right-click the first data cell (<<Expr>>) and select Expression. The Expression dialog displays.
Update the Value expression to the following:
=CountDistinct(Fields!Serial.Value)/ReportItems!Textbox14.Value
Click OK.
Open the properties dialog for the <<Expr>> cell and change the Format field to P2. This change forces data to display as a percentage with two decimal places.
3 Change the summary item to display as a percentage value.
Select the first data row, right-click, and select Insert Row > Below.
Name the new row Total, and update the expression to the following:
=ReportItems!Textbox5.Value /ReportItems!Textbox14.Value
Open the properties dialog for the new row's <<Expr>> cell and change the Format field to P2. This change forces data to display as a percentage with two decimal places.
4 Create a new summary column.
Select the last table column, right-click, and select Insert Column > Right.
Name the new column Total, and update the expression to the following:
=ReportItems!Textbox13.Value /ReportItems!Textbox14.Value
Open the properties dialog for the new column's and change the Format field to P2. This change forces data to display as a percentage with two decimal places.
5 Hide the old summary row.
Select the old summary row, right-click, and select Row visibility. The Row Visibility dialog displays.
Select Hide and click OK.
6 Hide the old summary column.
Select the old summary column, right-click, and select Column visibility. The Column Visibility dialog displays.
Select Hide and click OK.
7 Preview the report and verify the percentage and summary values.
Viewing column totals as percentage values
The following steps provide information on viewing column totals as percentage values.
1 Open the Museum_Percentage report and re-save it with the name Museum_ColumnPercentage.
2 Change the detail to display as a percentage value.
Right-click the first data cell (<<Expr>>) and select Expression. The Expression dialog displays.
Update the Value expression to the following:
=CountDistinct(Fields!Serial.Value)/ReportItems!Textbox5.Value
Click OK.
3 Change the summary column expression.
Right-click the summary column's data cell (<<Expr>>) and select Expression. The Expression dialog displays.
Update the Value expression to the following:
=ReportItems!Textbox5.Value /ReportItems!Textbox5.Value
Click OK.
4 Preview the report and verify the percentage and summary values.
Creating a multiple response report
Creating a multiple response report is similar to creating a basic report.
1 Open the DataCollectionReports project in SQL Server Data Tools. Windows Start menu > Microsoft SQL Server 2012 > SQL Server Data Tools
2 Run the report wizard via the Add New Report feature.
Select the Museum data source on the wizard's Select the Data Source page and then click Next.
Input a SELECT query clause on the Design the Query page. The clause defines the data that is rendered in the report. For example:
SELECT D.[Serial]
,gender.Label AS gender
,age.Label as age
,museums.Label as museums
FROM [Dimensions] D
inner join gender_Lookup gender on D.gender = gender.CategoryID AND gender.Language = 'ENU' AND gender.Context = 'Analysis' AND gender.LabelType = 'Label'
inner join age_Lookup age on D.age = age.CategoryID AND age.Language = 'ENU' AND age.Context = 'Analysis' AND age.LabelType = 'Label'
inner join museums_Lookup museums on D.museums = museums.CategoryID AND museums.Language = 'ENU' AND museums.Context = 'Analysis' AND museums.LabelType = 'Label'
order by D.Serial
If you want to include null values for museums, you can update the query as follows:
SELECT D.[Serial]
,gender.Label AS gender
,age.Label as age
,museums.Label as museums
FROM [Dimensions] D
inner join gender_Lookup gender on D.gender = gender.CategoryID AND gender.Language = 'ENU' AND gender.Context = 'Analysis' AND gender.LabelType = 'Label'
inner join age_Lookup age on D.age = age.CategoryID AND age.Language = 'ENU' AND age.Context = 'Analysis' AND age.LabelType = 'Label'
Left Outer Join museums museumsData on museumsData.Serial = D.Serial
Left Outer Join museums_Lookup museums on museumsData.Response = museums.CategoryID AND museums.Language = 'ENU' AND museums.Context = 'Analysis' AND museums.LabelType = 'Label'
order by D.Serial
Select Matrix on the Select the Report Type page and then click Next.
Select the appropriate columns, rows, and details from the available fields on the Design the Matrix page.
Move age to the Columns list.
Move museums to the Rows list.
Move Serial to the Details list.
Select the Ocean style on the Choose a Matrix Style page and then click Next.
Enter Museum_Multiple as the report name in the Completing the Wizard page and then click Finish.
3 Change the cell details by clicking in a table cell and changing the value to CountDistinct(Serial).
4 Add a summary row and column.
5 Preview the report by switching focus to the Preview tab.
Presenting categories with the same order that is defined in the MDD
The following example provides the museums question definition.
museums "Museums and galleries visited or plans to visit"
categorical [0..8]
{
National_Museum_of_Science "National Museum of Science",
Museum_of_Design "Museum of Design",
Institute_of_Textiles_and_Fashion "Institute of Textiles and Fashion",
Archeological_Museum "Archeological Museum",
National_Art_Gallery "National Art Gallery",
Northern_Gallery "Northern Gallery",
"Other" "Other",
Not_answered "Not answered" NA canfilter nofix
} defaultanswer({Not_answered});
In the prior Museum reports, categories do not display in the same order as the questionnaire. The following steps explain the process of making the report category order match the questionnaire category order.
1 Create a report, similar to the multiple response report, and input the following SELECT query clause on the Design the Query page.
Note The following query surfaces CategoryIndex in the lookup table.
SELECT D.[Serial]
,gender.Label AS gender
,age.Label as age
,museums.Label as museums
,age.CategoryIndex as ageIndex
,museums.CategoryIndex as museumsIndex
FROM [Dimensions] D
inner join gender_Lookup gender on D.gender = gender.CategoryID AND gender.Language = 'ENU' AND gender.Context = 'Analysis' AND gender.LabelType = 'Label'
inner join age_Lookup age on D.age = age.CategoryID AND age.Language = 'ENU' AND age.Context = 'Analysis' AND age.LabelType = 'Label'
inner Join museums museumsData on museumsData.Serial = D.Serial
inner Join museums_Lookup museums on museumsData.Response = museums.CategoryID AND museums.Language = 'ENU' AND museums.Context = 'Analysis' AND museums.LabelType = 'Label'
order by D.Serial
2 Name the report Museum_Multiple_Sorted and finish the report wizard.
3 Change the cell details by clicking in a table cell and changing the value to CountDistinct(Serial).
4 Add a summary row and column.
5 Configure the category sort order to match questionnaire sort order.
In the Row Groups pane, right-click the Museum entry (should be named similar to matrix1_museum) and select Group Properties from the menu to open the Group Properties dialog.
Select the Sorting option to display the Change sorting options page.
Select [museumsIndex] as the Sort by value, A to Z as the Order value and then click OK.
6 Preview the report by switching focus to the Preview tab. The report's category order now matches questionnaire order.
Creating reports for grid questions
Creating a report for grid questions is similar to creating a basic report.
1 Open the DataCollectionReports project in SQL Server Data Tools. Windows Start menu > Microsoft SQL Server 2012 > SQL Server Data Tools
2 Run the report wizard via the Add New Report feature.
Select the Museum data source on the wizard's Select the Data Source page and then click Next.
Input a SELECT query clause on the Design the Query page. The clause defines the data that is rendered in the report. For example:
SELECT D.[Serial]
,D.[ratingSerial]
,LevelIDLookup.Label as LevelID, LevelIDLookup.CategoryIndex as LevelIDIndex
,ColumnLookup.Label as [Column], ColumnLookup.CategoryIndex as ColumnIndex
,[Factor]
FROM [rating.Dimensions] D
inner join [rating.Column] ColumnData ON D.Serial = ColumnData.Serial AND D.ratingSerial = ColumnData.ratingSerial
inner join [rating.Column_Lookup] ColumnLookup on ColumnData.Response = ColumnLookup.CategoryID AND ColumnLookup.Language = 'ENU' AND ColumnLookup.Context = 'Analysis' AND ColumnLookup.LabelType = 'Label'
inner join [rating.LevelID_Lookup] LevelIDLookup on D.LevelID = LevelIDLookup.CategoryID AND LevelIDLookup.Language = 'ENU' AND LevelIDLookup.Context = 'Analysis' AND LevelIDLookup.LabelType = 'Label'
You can first test the SELECT query clause in SQL Server, and then paste it into the Query field. Click Next after you enter the SELECT query.
Select Matrix on the Select the Report Type page and then click Next.
Select the appropriate columns, rows, and details from the available fields on the Design the Matrix page.
▪Move Column to the Columns list.
▪Move LevelID to the Rows list.
▪Move Serial to the Details list.
Select the Ocean style on the Choose a Matrix Stylepage and then click Next.
Enter Report_Rating as the report name in the Completing the Wizard page and then click Finish.
3 Change the cell details by clicking in a table cell and changing the value to CountDistinct(Fields!Serial.Value).
4 Sort the report columns, based on ColumnIndex, by
5 Preview the report by switching focus to the Preview tab. The table includes an automatically generated Other row, which you can filter out as follows:
Follow the instructions in the Filtering records section, and input the following values in the Change filters page.
▪Expression: [Column]
▪Operator: <>
▪Value: NA (Auto Created)
Click OK.
Preview the report by switching focus to the Preview tab. The Other row is no longer present in the report.
Creating reports for Loop questions
A loop is a set of questions that is asked multiple times for a different subject. For example, a single questionnaire can apply to the entire household, but contain data for each person in the household (the same question is asked for each household member). Reporting can occur at a specific level (household level, person level, TV day level, and other levels).
The following steps explain the process of creating a report against the Household sample's TV day level.
1 Open the DataCollectionReports project in SQL Server Data Tools. Windows Start menu > Microsoft SQL Server 2012 > SQL Server Data Tools
2 Run the report wizard via the Add New Report feature.
Select the Household data source on the wizard's Select the Data Source page and then click Next.
Input a SELECT query clause on the Design the Query page. The clause defines the data that is rendered in the report. For example:
SELECT D.[Serial]
,[personSerial]
,[tvdaysSerial]
,[personLevelID]
,LevelIDLookup.Label As LevelID,LevelIDLookup.CategoryIndex As LevelIDIndex
,[ColumnLookup].Label As ColumnData, [ColumnLookup].CategoryIndex as ColumnIndex
FROM [person.tvdays.Dimensions] D
inner join [person.tvdays.Column_Lookup] [ColumnLookup] on D.[Column] = ColumnLookup.CategoryID AND ColumnLookup.Language = 'ENU' AND ColumnLookup.Context = 'Analysis' AND ColumnLookup.LabelType = 'Label'
inner join [person.tvdays.LevelID_Lookup] LevelIDLookup on D.LevelID = LevelIDLookup.CategoryID AND LevelIDLookup.Language = 'ENU' AND LevelIDLookup.Context = 'Analysis' AND LevelIDLookup.LabelType = 'Label'
Select Matrix on the Select the Report Type page and then click Next.
Select the appropriate columns, rows, and details from the available fields on the Design the Matrix page.
▪Move LevelID to the Columns list.
▪Move ColumnData to the Rows list.
▪Move Serial to the Details list.
Select the Ocean style on the Choose a Matrix Style page and then click Next.
Enter Report_Household_tvdays as the report name in the Completing the Wizard page and then click Finish.
3 Change the cell details by clicking in a table cell and changing the value to CountDistinct(Fields!tvdaysSerial.Value).
4 Add a summary row and column.
5 Sort the report columns, based on ColumnIndex.
6 Preview the report by switching focus to the Preview tab.
To modify the report to provide information for the Person level
1 Change the cell details to CountDistinct(Fields!personSerial.Value)
2 Remove the summary row and column.
3 Create a summary row and column.
4 Preview the report by switching focus to the Preview tab.
See
