For some years have to support SharePoint Solution and operational work and had to work with different types of Managers.
Why some managers have the idea that one person can do everything about SharePoint (since Administration, Develop, Customization, Branding, Documentation, BI, Migration, Upgrades, Consulting, Search Server, Configuring Hardware, Maintain SQL Servers, Reporting, Auditing, Web Services, Support Clients, Maintain Desktops, Consulting, Infopath, Office, Project, Visio, Workflows, Powershell Scripting , AD configuration, Windows Servers, Architecture and Microsoft CRM and speak multiple languages "Portuguese, Spanish, English, French, now i am learning German...") and this continue for some years and i do all that at same time "a true Fireman (I always think this is the wrong approach on SharePoint but from some years to now everything is going on right track :) )...whatever type of Company from Small <10 to Big ones >100.000 users , but i have to admit with all this i have learned a lot of IT and SharePoint and meet a lot of fantastic people.
In resume, i understand a lot of the pain from SharePoint Consultants and their day by day work and this article is for you, who need to work with this tool and show how you can use the SharePoint and Reporting Services Integrated service to have some BI in your company.
Requisites:
Depending of your existing IT Architecture you can have multiple ways of install Reporting Services with SharePoint Integration, what i recommend is create and evaluate how important the BI solution will be for the company and what are the existing resources you have a system where your BI will live, this is one of the most important steps to have a reliable environmental and delegate task to different divisions .
Install Reporting Services in your SQL Server 2008 R2 Instance
Integrate Reporting Services for SharePoint 2010 Integration using "Reporting Service configuration"
http://www.bybugday.com/Lists/Posts/Post.aspx?ID=43
http://www.codeproject.com/Articles/88285/Integrating-Sharepoint-2010-and-SQL-Reporting-Serv
http://social.technet.microsoft.com/wiki/contents/articles/integration-of-sharepoint-2010-and-ssrs.aspx
Configuring Reporting Services for SharePoint 2010 Integration
http://technet.microsoft.com/en-us/library/bb326356.aspx
Some of the SharePoint 2010 Configurations have installed Kerberos and extended Farm with complex physical Architecture with different Servers and Front webs need to makes delegation accounts access between the BI environment.
When you are configuring Reporting Services With SharePoint 2010 with Kerberos, this document will help:
After you have installed the Reporting Services Integrated with your SharePoint 2010 and in the Central Administration of SharePoint will need to have the Url of the Reporting Reporting Service with the correct Type of Account and activate the features in the Site Collections, here a example:
General Application Setting with Reporting Services install in Central Administration Page:
Configuration of the Reporting Service:
Access to the SharePoint Site Collection Settings you will use for your BI solutions and access in the Site Collection Features and validate if the Feature is activated.
After we have all the configuration working you can create a Document Library, this folder can be use to store your Reports and Data Sources.
Here are some tools used for this examples:
After we create the Document Library where will be stored the Reports we need to change the Type of Document Library and add Content Types associated to this Type of Report for this you will need to change the Content Type Managements, Access to Document Library Ribbon "Library > Library Settings > Advanced Settings"
After we activate the managements of the Content Types, need to be added at the Document Library Settings, for this Task you need to access "Document Library > Settings > Add Content Type"
If you select the Content Type "Report Data Source" you will create a connection to your external Data. This Data Source will be used to call the data for your DataSets in your Reports.
After you select the Data Source Type you will need to add the Connection String to your Custom Database using a Windows Authentication.
If you want to validate your Connection string you can use the but "Test Connection" and verify the output from the connection.
Kerberos authentication
If you are using Kerberos authentication with "Windows Authentication" Method you will get issues with the user permission method because the way Kerberos and Reporting Services is configured in SharePoint 2010 for this credentials works you will need to make some changes in your authentication Method,
Change to "Stored Credential", this account will be use to access the SQL Server, i can recommend check the option "Use as Windows Credentials" you can need this parameter when you are make a query, for example:
Your name is MyDomain\Peter and want to filter all boxes associate to him
Example:
SQL: Select Boxes from Building where BoxAssociate = 'MyDomain\Peter'
If you don't have the option checked you will get something like this,
SQL: Select Boxes from Building where BoxAssociate = 'MyDomain\Alage'
Of course this is not the expected result....
Create a Report using Microsoft SQL Server 2008 R2 Report Builder 3.0
With the Data Source created we can start create our Report, for that you can open Microsoft SQL Server 2008 R2 Report Builder 3.0 and add new clean Report.
On the Left side of your Report you will have the parameters used in the Report to call the Data,
To use the Shared Data source you will need to access Folder "Data Sources" and select "Add Data Source"
Create DataSet
After we add the Shared Data Source to out Report, we need to create the Datasets, this block of data will be use to be display in the Report using Multiple controls like Matrix or Tables...
Here a example of the controls we can use:
In this example i am using the Matrix to create a Dataset using the wizard of the control.
After we select the Data source, we need to create the query that will be associate to your Dataset and will be displayed in the control Matrix.
After select the option "Query Designer" the connection to your SQL Server will be made and you can select the fields you want to display in your Matrix
After select the fields we can organize wish field will be displayed and grouped.
Configure Expand and collapse
The expand and collapse option in Tables are very important when you want to display large quantity of data and this data is grouped, In the reporting you can define wish groups can be collapsed or expand to organize the way the user will se the data on the first time the report is executed.
For this example i select that all values from Group "TeamColor" have to be expanded, for this i select the cel "TeamColor" and change the properties "InitialToggleState" for False to change the "Collapse" image to "Expanded" image.
Then in the Row Groups Options i have selected the groups "Details" and in the option "Visibility" change from hide to Show.
After this change i have execute the report and all the values are expanded to the final user.
Connect Data Source to SharePoint List
The SQL Server 2008 R2 Report builder give you the opportunity to create external Connection to SharePoint Lists, for this example i use a embedded connection and select connection type "Microsoft SharePoint List".
After select the Type, the Connection String is the SharePoint Site where is stored the Lists.
There you can select the List and check the fields you want to display and filter.
After the query is made you can validate the query and close the Dataset associated SharePoint List Connection
You can now create you Table with data from SharePoint List.
After you add the Table and data you can execute the report and the SharePoint List Data will be display in the Report.
Redirects to Report and SubReport with Parameters between reports
Report Builder give you the ability to create and navigation between reports, this is very important when you define different type of Reports and create a type of Navigation.
For this example was define for one Cell, to create a Action that will redirect to other Report, to create this type of Action access to the TextBox and right Click in the Properties from there you can choose "Action" and select "Go to Report" from there you can define wish Parameters will want to transfer for the second Report.
After you define the parameter from the first report and his navigation to the second Report, We need to configure the Second report to receive the parameter.
Received Parameter Report
This new Report will have a Text Box that will receive the Parameter from the first Report and Display in the new one.
For this example we create Parameter @Club in the new Report, for that we need to go for the Left Menu Folder and select "Add Parameter".
There you can multiple option on how the parameter can be, since this is a received Report,
To receive the parameter from the first report the name of the Parameter have to be exactly the same as the first Report.
When you execute the First Report and you click in the cell with the Navigation you will redirect to the second Report and the Parameter @Club is displayed.
How to use formulas
One of the great features of creating Report is the ability to use formulas or conditions
For this example was created a Text Box where will be define some Formulas...
The expression will give the ability to create formulas and data from Dataset to response client requirements and filters.
By default Reporting Services already brings some useful item with information that can help you define your Report and how your Report will be displayed like in the Built-in Fields.
For me the IIF conditions is one of the most used because with this formula we can define a lot of the Business requirements when you collect the Data and need to prepare how the data will be display to the Client..
Example of IIF Condition:
=IIF(2<3, "True","Return to School...")
The Output will be"True"
Example Multiples IIF Conditons:
=IIF(2<3, IIF(2>3, "True","Return to School..."),"Return to School...")
The Output will be "Return to School..."
Manage Images in reports
In the Left menu of you Report you will have a folder call "Images".
The report builder give you the chance to add Images in you report, there is 3 way to add your Image:
(Don't use big images can have impact in the report if is displayed multiple times in the reports)
Add Report Viewer Web Part in SharePoint Page
After all the reports are created and add in the Document Library we can start to manage and display in our SharePoint Pages, the first this we need to do is access to a SharePoint Page, Edit the Page, and insert the Web Part "SQL Server Reporting", this Web Part will be use to display out reports in our SharePoint Page.
After we add the Web Part we need to add the Report we want to display in the SharePoint Page for this we select the Document Library where are stored all the Report.
Navigate to the folder where is stored the reports add select the report you want to display.
After you select the Reports the Report will be displayed in the SharePoint Page....
Subscribe Report
One great feature that comes with Reporting Services is the ability to create Subscription of Reports and manage Shared Report to different Groups or users.
For this feature to be activated recommend configure in Reporting Services the mail SMTP to be able the send email to the users and create task associated to this service.
The subscription option can be accessible in the Web Part for the Reporting Service "Actions > Subscribe", or accessing in the "Site Actions > Site Settings"
This option will give you different options and Types of Reports you can use to store the report associated for the schedule Reports.
The subscribe option also give you the ability to configure and schedule Tasks for the reports.
Reporting Services SQL Server 2008 R2 is a very good and flexible tool for reporting , now i am exploring how the new SQL Server 2012 will work with SharePoint 2010, i hope create a new article about SQL 2012 and the new capabilities for SharePoint 2010.
If you want to have more details about BI, Reports with SharePoint 2010 this Microsoft Link can help:
"Microsoft SharePoint Server 2010 Business Intelligence Insights Training Downloads"
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26944
Hope you like this article....
Kind regards
Why some managers have the idea that one person can do everything about SharePoint (since Administration, Develop, Customization, Branding, Documentation, BI, Migration, Upgrades, Consulting, Search Server, Configuring Hardware, Maintain SQL Servers, Reporting, Auditing, Web Services, Support Clients, Maintain Desktops, Consulting, Infopath, Office, Project, Visio, Workflows, Powershell Scripting , AD configuration, Windows Servers, Architecture and Microsoft CRM and speak multiple languages "Portuguese, Spanish, English, French, now i am learning German...") and this continue for some years and i do all that at same time "a true Fireman (I always think this is the wrong approach on SharePoint but from some years to now everything is going on right track :) )...whatever type of Company from Small <10 to Big ones >100.000 users , but i have to admit with all this i have learned a lot of IT and SharePoint and meet a lot of fantastic people.
In resume, i understand a lot of the pain from SharePoint Consultants and their day by day work and this article is for you, who need to work with this tool and show how you can use the SharePoint and Reporting Services Integrated service to have some BI in your company.
Requisites:
Depending of your existing IT Architecture you can have multiple ways of install Reporting Services with SharePoint Integration, what i recommend is create and evaluate how important the BI solution will be for the company and what are the existing resources you have a system where your BI will live, this is one of the most important steps to have a reliable environmental and delegate task to different divisions .
Install Reporting Services in your SQL Server 2008 R2 Instance
Integrate Reporting Services for SharePoint 2010 Integration using "Reporting Service configuration"
http://www.bybugday.com/Lists/Posts/Post.aspx?ID=43
http://www.codeproject.com/Articles/88285/Integrating-Sharepoint-2010-and-SQL-Reporting-Serv
http://social.technet.microsoft.com/wiki/contents/articles/integration-of-sharepoint-2010-and-ssrs.aspx
Configuring Reporting Services for SharePoint 2010 Integration
http://technet.microsoft.com/en-us/library/bb326356.aspx
Some of the SharePoint 2010 Configurations have installed Kerberos and extended Farm with complex physical Architecture with different Servers and Front webs need to makes delegation accounts access between the BI environment.
When you are configuring Reporting Services With SharePoint 2010 with Kerberos, this document will help:
- Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products
After you have installed the Reporting Services Integrated with your SharePoint 2010 and in the Central Administration of SharePoint will need to have the Url of the Reporting Reporting Service with the correct Type of Account and activate the features in the Site Collections, here a example:
General Application Setting with Reporting Services install in Central Administration Page:
Configuration of the Reporting Service:
- Report Server url with SharePoint where you can access your Reports
- Authentication mode (Windows Authentication/Trusted Account"SQL account")
- The option Activate Reporting Services feature will activate the features in all Site collection or selected Site Collections.
Access to the SharePoint Site Collection Settings you will use for your BI solutions and access in the Site Collection Features and validate if the Feature is activated.
After we have all the configuration working you can create a Document Library, this folder can be use to store your Reports and Data Sources.
Here are some tools used for this examples:
- Microsoft SQL Server 2008 R2
- Microsoft® SQL Server® 2008 R2 Reporting Services Add-in for Microsoft SharePoint® Technologies 2010
- Microsoft® SQL Server® 2008 R2 Service Pack 1 (SP1) Reporting Services Add-in for Microsoft SharePoint® Technologies 2010
- Microsoft SQL Server 2008 R2 Report Builder 3.0
After we create the Document Library where will be stored the Reports we need to change the Type of Document Library and add Content Types associated to this Type of Report for this you will need to change the Content Type Managements, Access to Document Library Ribbon "Library > Library Settings > Advanced Settings"
After we activate the managements of the Content Types, need to be added at the Document Library Settings, for this Task you need to access "Document Library > Settings > Add Content Type"
You will get a windows where you can add the content Type associated to report Server, will help your create your Reports and your Data Source.
If you return to your Document Library and select to create a new Document you will get 3 new Content types associated to Reporting Services and to Build your reports
If you select the Content Type "Report Data Source" you will create a connection to your external Data. This Data Source will be used to call the data for your DataSets in your Reports.
After you select the Data Source Type you will need to add the Connection String to your Custom Database using a Windows Authentication.
If you want to validate your Connection string you can use the but "Test Connection" and verify the output from the connection.
Kerberos authentication
If you are using Kerberos authentication with "Windows Authentication" Method you will get issues with the user permission method because the way Kerberos and Reporting Services is configured in SharePoint 2010 for this credentials works you will need to make some changes in your authentication Method,
Change to "Stored Credential", this account will be use to access the SQL Server, i can recommend check the option "Use as Windows Credentials" you can need this parameter when you are make a query, for example:
Your name is MyDomain\Peter and want to filter all boxes associate to him
Example:
SQL: Select Boxes from Building where BoxAssociate = 'MyDomain\Peter'
If you don't have the option checked you will get something like this,
SQL: Select Boxes from Building where BoxAssociate = 'MyDomain\Alage'
Of course this is not the expected result....
Create a Report using Microsoft SQL Server 2008 R2 Report Builder 3.0
With the Data Source created we can start create our Report, for that you can open Microsoft SQL Server 2008 R2 Report Builder 3.0 and add new clean Report.
On the Left side of your Report you will have the parameters used in the Report to call the Data,
To use the Shared Data source you will need to access Folder "Data Sources" and select "Add Data Source"
A new windows will popup and there you have 2 ways of making your connection, the Shared Connection created in your SharePoint Site or a connection embedded in the Report, since we already create the connection in the SharePoint Site we need to make the link to that connection.
To call this external connection we need to select the option "Browse" and access to SharePoint Site and Document Library where the Connection is stored.
After we select the file a link to the connection will be made, validate testing the connection and return to you Report.
Create DataSet
After we add the Shared Data Source to out Report, we need to create the Datasets, this block of data will be use to be display in the Report using Multiple controls like Matrix or Tables...
Here a example of the controls we can use:
In this example i am using the Matrix to create a Dataset using the wizard of the control.
After we select the Data source, we need to create the query that will be associate to your Dataset and will be displayed in the control Matrix.
After select the option "Query Designer" the connection to your SQL Server will be made and you can select the fields you want to display in your Matrix
After select the fields we can organize wish field will be displayed and grouped.
The Wizard also give the opportunity to define the layout and the way the field are organize in the Control
After the wizard finish you are able to see the Matrix control in your Report Layout a new DataSet appears in the left Menu.
You can execute the report and visualize how the report is displayed to final User.
Configure Expand and collapse
The expand and collapse option in Tables are very important when you want to display large quantity of data and this data is grouped, In the reporting you can define wish groups can be collapsed or expand to organize the way the user will se the data on the first time the report is executed.
For this example i select that all values from Group "TeamColor" have to be expanded, for this i select the cel "TeamColor" and change the properties "InitialToggleState" for False to change the "Collapse" image to "Expanded" image.
Then in the Row Groups Options i have selected the groups "Details" and in the option "Visibility" change from hide to Show.
After this change i have execute the report and all the values are expanded to the final user.
Connect Data Source to SharePoint List
The SQL Server 2008 R2 Report builder give you the opportunity to create external Connection to SharePoint Lists, for this example i use a embedded connection and select connection type "Microsoft SharePoint List".
After select the Type, the Connection String is the SharePoint Site where is stored the Lists.
There you can select the List and check the fields you want to display and filter.
After the query is made you can validate the query and close the Dataset associated SharePoint List Connection
You can now create you Table with data from SharePoint List.
After you add the Table and data you can execute the report and the SharePoint List Data will be display in the Report.
Redirects to Report and SubReport with Parameters between reports
Report Builder give you the ability to create and navigation between reports, this is very important when you define different type of Reports and create a type of Navigation.
For this example was define for one Cell, to create a Action that will redirect to other Report, to create this type of Action access to the TextBox and right Click in the Properties from there you can choose "Action" and select "Go to Report" from there you can define wish Parameters will want to transfer for the second Report.
After you define the parameter from the first report and his navigation to the second Report, We need to configure the Second report to receive the parameter.
Received Parameter Report
This new Report will have a Text Box that will receive the Parameter from the first Report and Display in the new one.
For this example we create Parameter @Club in the new Report, for that we need to go for the Left Menu Folder and select "Add Parameter".
There you can multiple option on how the parameter can be, since this is a received Report,
To receive the parameter from the first report the name of the Parameter have to be exactly the same as the first Report.
The new Parameter will appear in the Left menu and you will cann associate to the Text Box control or other or use for formulas.
When you execute the First Report and you click in the cell with the Navigation you will redirect to the second Report and the Parameter @Club is displayed.
How to use formulas
One of the great features of creating Report is the ability to use formulas or conditions
For this example was created a Text Box where will be define some Formulas...
The expression will give the ability to create formulas and data from Dataset to response client requirements and filters.
By default Reporting Services already brings some useful item with information that can help you define your Report and how your Report will be displayed like in the Built-in Fields.
Example of IIF Condition:
=IIF(2<3, "True","Return to School...")
The Output will be"True"
Example Multiples IIF Conditons:
=IIF(2<3, IIF(2>3, "True","Return to School..."),"Return to School...")
The Output will be "Return to School..."
Manage Images in reports
In the Left menu of you Report you will have a folder call "Images".
The report builder give you the chance to add Images in you report, there is 3 way to add your Image:
(Don't use big images can have impact in the report if is displayed multiple times in the reports)
- External - You need to define a URL for the image.
- Embedded - You add the image directly in the report
- Database - You can call binary data from SQL and display in your reports.
Add Report Viewer Web Part in SharePoint Page
After all the reports are created and add in the Document Library we can start to manage and display in our SharePoint Pages, the first this we need to do is access to a SharePoint Page, Edit the Page, and insert the Web Part "SQL Server Reporting", this Web Part will be use to display out reports in our SharePoint Page.
After we add the Web Part we need to add the Report we want to display in the SharePoint Page for this we select the Document Library where are stored all the Report.
Navigate to the folder where is stored the reports add select the report you want to display.
Subscribe Report
One great feature that comes with Reporting Services is the ability to create Subscription of Reports and manage Shared Report to different Groups or users.
For this feature to be activated recommend configure in Reporting Services the mail SMTP to be able the send email to the users and create task associated to this service.
The subscription option can be accessible in the Web Part for the Reporting Service "Actions > Subscribe", or accessing in the "Site Actions > Site Settings"
This option will give you different options and Types of Reports you can use to store the report associated for the schedule Reports.
The subscribe option also give you the ability to configure and schedule Tasks for the reports.
If you want to have more details about BI, Reports with SharePoint 2010 this Microsoft Link can help:
"Microsoft SharePoint Server 2010 Business Intelligence Insights Training Downloads"
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26944
Hope you like this article....
Kind regards
4 comments:
Great post, as usual!
As for awkward projects and managers, remember any project in particular? ;-)
Reading this above all information really explained everything about SharePoint 2010 Reporting.
with my pleasure welcomes you share more about sharepoint.
Just an amazing post!!! Very useful!!!!
Congratulations my friend!!
Pedro
it's a greate artical
we are successfuly configured ssrs in singel domain
now i want configure multidomains, domain a and domain b.
domain a installed sharepoint 2010 and reporting server 2008r2
i am fetching user in domain b using "LDAP" domain a&b user trying to create a reports we are getting error "can not connect to the spcified URL, we have proper privilege" please help me friend
Post a Comment