SPS CT: BI and Automation Tools for IT featuring SharePoint and SQL Server

Event:
SharePoint Saturday Connecticut
Saturday, October 10, 2015

Session Title:
BI and Automation Tools for IT featuring SharePoint and SQL Server

Abstract:
IT organizations are responsible for delivering and maintaining technology solutions and capabilities throughout organizations; sometimes at the expense of their own productivity. Resource constraints deter IT organizations from investing the time and attention necessary for process improvement, resulting in a reactive approach to problem solving. Now is the time to empower IT using reports, dashboards, and notifications.

This presentation demonstrates how to utilize SharePoint Server, SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) to create business intelligence and automation tools for IT. This session covers high-level concepts as well as practical, hands-on instructions, tips, and tricks.

Slides:

BI and Automation Tools for IT Featuring SharePoint and SQL Server

SPS NYC: BI and Automation Tools for IT featuring SharePoint and SQL Server

Event:
SharePoint Saturday New York
Saturday, July 25, 2015

Session Title:
BI and Automation Tools for IT featuring SharePoint and SQL Server

Abstract:
IT organizations are responsible for delivering and maintaining technology solutions and capabilities throughout organizations; sometimes at the expense of their own productivity. Resource constraints deter IT organizations from investing the time and attention necessary for process improvement, resulting in a reactive approach to problem solving. Now is the time to empower IT using reports, dashboards, and notifications.

This presentation demonstrates how to utilize SharePoint Server, SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) to create business intelligence and automation tools for IT. This session covers high-level concepts as well as practical, hands-on instructions, tips, and tricks.

Slides:

BI and Automation Tools for IT Featuring SharePoint and SQL Server

SPS NYC: Reporting and Data Integration Methodologies and Mechanics for SharePoint and SQL Server

Event:
SharePoint Saturday New York
Saturday, July 26, 2014

Session Title:
Reporting and Data Integration Methodologies and Mechanics for SharePoint and SQL Server

Abstract:
First, we’ll review the architecture of SharePoint with SQL Server Reporting Services (SSRS) and SQL Server Integration Services (SSIS) and define the role that the Business Intelligence capabilities play in an organization’s Enterprise Architecture (EA). From there, we will evaluate sample business scenarios and compare and contrast different possible solution approaches. We will design a reporting solution, and then build the solution step by step using SharePoint, SSRS, and SSIS. Finally, we will reflect on the overall approach that we followed and discuss further ideas and possibilities. This session covers high-level concepts as well as practical, hands-on instructions, tips, and tricks.

Slides:

Reporting and Data Integration Methodologies and Mechanics for SharePoint and SQL Server

SQL Server Reporting Services, Conditional Formatting Using Switch Function

Description

This post explains how to configure conditional formatting in an SSRS report, for an example that has three conditions.

Solution 

In order to achieve the desired result, we can modify the Background Color property in the SSRS report with a formula, using the Switch function. As described in an MSDN article, "The Switch function returns the value associated with the first expression in a series that evaluates to true" (MSDN, 2012). In the example below we are setting the Variance field Red if the value is less than -100,000, Yellow if the value is between -100,000 and 0, and Green if the value is greater than or equal to 0.

References

MSDN (2012). Expression Examples (Report Builder and SSRS). Retrieved April 21, 2014 from http://msdn.microsoft.com/en-us/library/ms157328.aspx

SPS Boston: Reporting and Data Integration Methodologies and Mechanics for SharePoint and SQL Server

Event:
SharePoint Saturday Boston
Saturday, April 12, 2014

Session Title:
Reporting and Data Integration Methodologies and Mechanics for SharePoint and SQL Server

Abstract:
First, we’ll review the architecture of SharePoint with SQL Server Reporting Services (SSRS) and SQL Server Integration Services (SSIS) and define the role that the Business Intelligence capabilities play in an organization’s Enterprise Architecture (EA). From there, we will evaluate sample business scenarios and compare and contrast different possible solution approaches. We will design a reporting solution, and then build the solution step by step using SharePoint, SSRS, and SSIS. Finally, we will reflect on the overall approach that we followed and discuss further ideas and possibilities. This session covers high-level concepts as well as practical, hands-on instructions, tips, and tricks.

Slides:

Reporting and Data Integration Methodologies and Mechanics SP2013 SQL2012.pdf
Note:  the instructional slides begin at slide #23

SQL Server Reporting Services (SSRS) Report Pagination InteractiveSize

Description


When viewing a deployed, SQL Server Reporting Services (SSRS) report, the default pagination does not provide a pleasant user experience because viewing more than a handful of rows of report data requires that the user page right and page left.

This characteristic is a result of the default InteractiveSize Properties of the report.  TechNet describes InteractiveSize Properties as, "InteractiveHeight and InteractiveWidth are used by the HTML rendering extension to provide the equivalent of PageHeight and PageWidth. Because the HTML rendering extension dynamically resizes a report to accommodate drilldown, drillthrough, and show/hide features, the report server uses different properties to support pagination on dynamic pages." (Microsoft TechNet, 2013)

Solution

To display all rows of data on the first page of the SSRS report, set InteractiveSize properties (Width and Height) to 0.

After updating the properties, Save the report, then Deploy the report.

Refresh the report in the browser and now all of the report data will display on the page. If viewing the report in SharePoint using a Report Viewer Web Part, then you may need to adjust the Height and Width of the Report Viewer Web Part to eliminate any scroll bars that appear as a result of the data being displayed on one page.

References

Microsoft TechNet (2013).  Controlling Report Pagination.  Retrieved November 29, 2013 from http://technet.microsoft.com/en-us/library/ms156282%28v=sql.90%29.aspx

Links: SQL Server Reporting Services (SSRS) SDK for PHP

Here are some references for information on interoperability between SQL Server Reporting Services and PHP:

Interoperability Bridges and Labs Center
http://www.interoperabilitybridges.com

SQL Server Reporting Services (SSRS) SDK for PHP
http://www.interoperabilitybridges.com/projects/sql-server-reporting-services-sdk-for-php

SQL Server Reporting Services (SSRS) SDK for PHP on Codeplex
http://ssrsphp.codeplex.com/

(Microsoft, 2010)

Reference

Microsoft (2010). SQL Server Reporting Services SDK for PHP. Retrieved May 11, 2010 from http://www.interoperabilitybridges.com/projects/sql-server-reporting-services-sdk-for-php.

SharePoint 3.0: Unable to Activate SQL Server 2008 Reporting Services Add-in for SharePoint

Background:

The SQL Server 2008 Reporting Services Add-in for SharePoint is available for download at this location:

Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=200fd7b5-db7c-4b8c-a7dc-5efee6e19005

The downloads include a readme_rsaddin.htm file, which contains complete instructions for installing and configuring the Add-in.


Description:

After running the rsSharePoint_x64.msi (installer) file, the setup appears to have finished. However, by following steps in the readme_rsaddin.htm file for activating the Add-in features, it is not possible to activate the features.

The reason it is not possible to activate the features is because setup experienced an error and the user does not have permission to add the feature to the site collection.

Navigate to:
C:\Documents and Settings\SetupUser\Local Settings\TempOpen the log file, RS_SP.0.log

Within the log file, look for an error; for example:Activating feature to root site collection: https://centraladmin.domain.com******* User does not have permission to add feature to site collection: https://centraladmin.domain.com


Solution:

To resolve this, either log in as a user that is a Site Collection Administrator on the Central Administration site collection or make the current user account a site collection Administrator by doing the following:

Navigate to Central Administration > Application Management > Site Collection Administrators.
Select the Central Administration site collection.

Add the desired user account as either primary or secondary site collection administrator for the site collection.

Once logged in as a Central Administration, Site Collection Administrator, run the rsSharePoint_x64.msi again, this time choosing the "Repair" option.

To verify the permission issue has been resolved, navigate to:
C:\Documents and Settings\nbisciotti\Local Settings\Temp

Open the log file, RS_SP.0.log

Within the log file, the entry should now read:
Activating feature to root site collection: https://centraladmin.domain.com
Activated feature for root site collection: https://centraladmin.domain.com

Next, navigate to Central Administration > Site Actions > Site Settings > Site Collection Administration > Site Collection Features

Activate the "Report Server Integration Feature"

Next, navigate to Central Administration > Application Management

The "Reporting Services" panel should now be available and visible. From here you can proceed with the SQL Server 2008 Reporting Services integration configuration.

MSSQL RS: Reference Items for Creating Datasource that Queries SharePoint List Items

Here are a couple, useful reference items to have when creating MSSQL Reporting Services datasources for querying SharePoint list item information:


1. The URL location of the list web service:

http://sample1portal.domain.com/subsite/_vti_bin/Lists.asmx



2. Sample syntax of an XML query to be used in the datasource:

Links: MS SQL Server Reporting Services Add-In for MOSS

Here are the links for downloading:

Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies:
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=200fd7b5-db7c-4b8c-a7dc-5efee6e19005

Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies:
http://www.microsoft.com/downloads/details.aspx?familyid=1E53F882-0C16-4847-B331-132274AE8C84&displaylang=en