Connecting Excel to Project Server OData (Reporting Database)

Prerequisites

• Project server 2013
• Office Excel 2010 or 2013 (in our Blog we are using 2013)
•  Permission to access to Project Server OData Feed

*If you don’t have access to the Project Server OData feed please contact your SharePoint Admin

Let’s Begin

First of all I want to talk a bit about OData, so what is OData?

“The Open Data Protocol (OData) enables the creation and consumption of REST APIs, which allow resources, identified using URLs and defined in a data model, to be published and edited by Web clients using simple HTTP messages.” Source: www.odata.org

In Project Server 2013 Microsoft made ProjectData (WCF Data Service, also known as an OData service) available to us so we can use it to  make both online and on-premises queries of reporting data from a Project Web App instance, and we will be using that today in our example. (Source)

To have a better understanding of the Project Server OData service please visit http://msdn.microsoft.com/en-us/library/office/jj163015(v=office.15).aspx

Alright now we have a better understanding of the Project Server OData service lets connect our Excel and get some data.

1- Open Excel and click on the DATA tap on the Top

2- Click on the From Other Sources button and choose From OData data Feed

3- You will get the Data Connection Wizard, go ahead and put the Odata URL , in out example we will get the Project List as you will see in the screen shot below

4- You will also have the option to use the sign in that you are using for your account or other log in credentials in my example will be using my windows credential, click next

5- Select the table that have the data you want, in our example it will only be one which is the Projects table

6- Brows and save your connection and then click Finish

7- We will get the Import data window with all the options of how to display our data, I am going to choose the Table option and click OK

And here is our final table

Search Service application components in SharePoint Server 2013:

 

As you’re all aware ,there has been a major change in the search architecture on SharePoint Server 2013 when compared to SharePoint server 2010 and in this post I’ll  be discussing on the six different search service components in SharePoint 2013 .It’s very important that all these components are running fine without any issue for the Search service to function seamlessly and surface the results in the search page when a user executes a query in the search center.

Listed below are the six components available in SharePoint 2013 search service:

1. Crawl Component
2. Content processing component
3. Indexing component
4. Query processing component
5. Analytics processing component
6. Search administration component

 Now, let’s take a look on all these components separately …

1.Crawl Component :

This component takes care of crawling the content sources such as (SharePoint sites, websites & file shares etc…) and extracts the crawled properties and metadata and sends that to the content processing component.

2. Content processing component:

This component receives the information from the crawl component and then processes and sends it to the indexing component. It also interacts with the analytics processing component and is responsible for mapping crawled properties to the managed properties.

3. Indexing Component :

This component takes care of receiving the information from the content processing component and writes it to the search index. It also takes care of handling the queries and sends back the results to the Query processing component.

4. Query Processing Component:

This component handles incoming query requests and sends them to the indexing component for results. It also takes care of query optimization.

5. Analytics Processing Component :

This component takes care of analyzing what users are querying on and how they interact with the results.  This information is used to determine relevance, generate recommendations and also used for generating search reports.

6. Search administration Component:

This component manages administrative processes as well as changes to the search topology, such as adding or removing search components and servers.

Please note that these 6 search components can be distributed across multiple servers to provide high availability as well as improve performance as shown in the image below.

Search service application databases:

• Search Administration database :The Search Administration database hosts the Search service application configuration and handles crawl state orchestration, including the content source crawl history.
• Analytics Reporting database :The Analytics Reporting database stores the results for usage analysis reports and extracts information from the Link database when needed.
• Crawl Store database :The Crawl Store database stores the state of each crawled item and provides the crawl queue for items currently being crawled.
• Link database :The Link database stores the information that is extracted by the content processing component and the click through information.

Thanks for reading this post. Happy SharePointing!!!

SharePoint: Group by Year or Month in a View

The following is for SharePoint 2007, SharePoint 2010 and SharePoint Online.

Grouping by Year

Grouping by year is pretty straight forward, just add a calculated column to your list to display the year and then group on that column in a view.
Steps:

1. Add a new column to your list or library
   1. 2007: Click Settings and then Create Column
      2010: In the ribbon click the List or Library tab and click Create Column
   2. Select “Calculated column (calculation based on other columns)”
        
   3. Enter this formula: 
        =YEAR([yourdatecolumn])      example: =YEAR([Due Date])
        
   4. Create or edit a view and in the Group By section select your new calculated column
        
   5. Set the return type to text:
        
   6. Save the view and test

        

Two Problems!

What’s with the 1899 year and why the commas? The commas are because SharePoint, in spite of our selecting “Single line of text”, still thinks the digits are a number. The 1899 year is from items with no date entered.
Fixing the commas…
Easy, just force the result to be text by prefixing the year with an empty string:   “”
=  “”  &  YEAR([Due Date])
    
Fixing the 1899 / no date problem…
Just add an ”IF” to the formula to test for the date.
    =IF(  [Due Date]="" ,  "No Due Date",  ""&YEAR([Due Date])  )
    
The result:


If you want the “No Due Date” listed first, then just add a space before “No”:
  

  

Grouping by Month

The Group By option in a view groups on the entire field. There is no way to group on a part of a field, such as just the month and the year of a date. We can get there by creating a calculated column or two and then grouping on the calculated columns.
We can pull the Month using a formula similar to the one above by using MONTH(). You will need both the year and the month and as SharePoint will sort from left to right you will need to build a string that looks something like “2012/02”, “2012 02” or “2012 / 02”.
When we combine this with the “empty date IF” from above you will get something like this:
  =IF([Due Date]="","No due date",YEAR([Due Date])&"/"&RIGHT("0"&MONTH([Due Date]),2))

The final view:
    

Both Year and Month?

If you wanted to group on Year and then on Month you can:

1. Create both columns described above
   Month:  =IF(  [Due Date]="", "No due date", YEAR([Due Date])&"/"&RIGHT("0"&MONTH([Due Date]),2))
   Year:     =IF(  [Due Date]="", "No Due Date", ""&YEAR([Due Date])  )
2. Create a view and first group on Year and then group on Month

The result:
    

Year, Month and Day?

Sorry, but SharePoint views only support two levels of grouping. If you really need to do this then you can use SharePoint Designer to create a Data View Web Part to group to any number of levels. See here:http://techtrainingnotes.blogspot.com/2011/01/sharepoint-group-by-on-more-than-2.html

Group Headings

If you want to get rid of the group heading then see this article:
http://techtrainingnotes.blogspot.com/2009/06/sharepoint-removing-group-headings-from.html
That article is for SharePoint 2007. There are both 2007 and 2010 versions available in my book.
     

The expected version of the product was not found on the system

When Trying to install a update you get "The expected version of the product was not found on the system."

I came on my test environment across this problem when trying to install the latest update I’ve got the error message: “The expected version of the product was not found on the system” this can occur on Project 2007 and 2010.

Running the following command from the command prompt will do the trick 

1. Run --> CMD

2. Go to Exe Path

3. Run “FileName.Exe PACKAGE.BYPASS.DETECTION.CHECK=1″

PS: Make sure to run the Package bypass command with Caps

Install and Download SharePoint 2013 prerequisites offline (manually)

This article applies in a situation when you don’t have internet access in your SharePoint server , I have placed all the direct download links for the installers you might need.

This article assumes you are having a new fresh installation of windows server 2008 R2 SP1

If you tried to run the installer , The following should be installed (but you cant install them directly from here since you don’t have internet)

in this case  you can download and install SharePoint 2013 Prerequisites offline and run them manually as below:

I have placed what needs to be installed on a clean Window server R2 SP1 , you should not need more than this:

Prerequisite	Direct Link
Microsoft .NET Framework 4.5 (Full Stand alone package)	http://go.microsoft.com/fwlink/?LinkId=225702 Or Download tFx45_Full_x86_x64.exe from stand alone section from http://msdn.microsoft.com/en-us/library/5a4x27ek.aspx
Windows Management Framework 3.0	http://www.microsoft.com/en-us/download/details.aspx?id=34595
Microsoft SQL Server 2008 R2 SP1 Native Client	Download 1033\x64\sqlncli.msi from http://www.microsoft.com/en-us/download/details.aspx?id=26728
Windows Identity Foundation (KB974405)	http://go.microsoft.com/fwlink/p/?LinkID=226830
Windows Identity Extensions	http://go.microsoft.com/fwlink/?LinkID=252368
Microsoft Sync Framework Runtime v1.0 SP1 (x64)	http://go.microsoft.com/fwlink/p/?LinkID=224449
Windows Server AppFabric	http://go.microsoft.com/fwlink/?LinkId=235496  (Note: don’t install WindowsServerAppFabricSetup_x64_6.1 this is not the correct version that SharePoint installer seeks) Download it but don’t install it by yourself since it needs configuration, hence install all other prerequisites in this article (except this one) then let the prerequisite installer to install AppFabric and configure it manually for you by running the following command from command prompt: prerequisiteInstaller.exe /appfabric:[Full AppFabric Installer path] D: is the CD drive where SharePoint installer is placed
Microsoft Information Protection and Control Client	http://go.microsoft.com/fwlink/p/?LinkID=219568
Microsoft WCF Data Services 5.0	http://www.microsoft.com/en-us/download/details.aspx?id=29306
Cumulative Update Package 1 for Microsoft AppFabric 1.1 for Windows Server (KB2671763	Download AppFabric1.1-RTM-KB2671763-x64-ENU.exe from http://www.microsoft.com/en-us/download/details.aspx?id=29241 You need to Install AppFabric from above first then run this update, dont run this update if appFabric above is not installed yet

Configure SSL for SharePoint 2013

In this tutorial I will show you how to configure SSL for SharePoint 2013.

Prerequisites:

1. IIS 8
2. SharePoint 2013
3. Windows Server 2012
4. HTTP Web Application on Port 80

Steps:

1. Create Self Signed Certificate on IIS 8
2. Import Self Signed Certificate to SharePoint Certificate store
3. Add Self Signed Certificate to trust management in Central Administration
4. Configure IIS Binding
5. Configure AAM
6. Notes
7. Issues

Note: Make sure to perform these steps with admin privileges.

Step 1: Create Self Signed Certificate on IIS 8

Open IIS Manager and then go to Server name and choose IIS Section “Server Certificates”

Click on Create Self-Signed Certificate… on Actions pane

Specify a name like “SharePointSelfSignedCert” and click Ok

Double click on this created Certificate and go to details Tab and click copy to File…

Click Next (Welcome…),

Select No, do not export the private key and click Next ,

Select DER encoded binary and click Next,

Specify the location for the certificate and Click Next and then finish (Imported).

Step 2: Import Self Signed Certificate to SharePoint Certificate store

Open Manage Compute Certificate on Windows Server 2012 and go to SharePoint node and then right click All tasks >> import …

Click Next and then specify the location of exported certificate in previous step and then Click Next,

Make sure Certificate store is SharePoint and Click Next and then finish (Exported)

Step 3: Add Self Signed Certificate to trust management in Central Administration

Go to Central Administration >> Security >> Manage Trust (to inform SharePoint to trust this certificate also).

And Click New

And a name and specify the location for the certificate and Click Ok.

Step 4: Configure IIS Binding

Go to IIS Manager and choose your web application and then click on Binding in Actions pane

Click Add..

Type: Https

SSL Certificate: SharePointSlefSignedCert (which created previously).

Click Ok.

Step 5: Configure AAM

Go Central Administration >> Alternate Access Mapping and Choose your web application

And click on Edit Public URLs and then add HTTPS URL

And Click Save.

Now try to brows your site with HTTPS URL

Notes:

1. Don’t use Self-Signed Certification in production sites (you need to use commercial Certificates).
   http://www.digicert.com/ssl-certificate-installation-microsoft-iis-7.htm
2. If you add the Self-Signed Certificate to Trusted Root Certification Authorities then Certification error will disappear.
3. IIS 8 and windows server 2012 introduce New Feature Called “Server Name Indication-SNI” which allows an IIS 8 to host multiple SSL sites and certificates on a single IP Address based on Host Headers.
   http://www.iis.net/learn/get-started/whats-new-in-iis-8/iis-80-server-name-indication-sni-ssl-scalability
4. You can use URL Rewirte module in IIS 8 to redirect from HTTP to HTTPS or vice versa.
   http://www.iis.net/learn/extensions/url-rewrite-module/creating-rewrite-rules-for-the-url-rewrite-module
   http://ruslany.net/2009/04/10-url-rewriting-tips-and-tricks/ 
5. SSL Certificates required for Federation Services.
6. Test the SSL implementation using https://www.ssllabs.com/ssltest/ and make changes as in this articlehttps://www.hass.de/content/setup-your-iis-ssl-perfect-forward-secrecy-and-tls-12

Issues:

 

Issue #1: Mixed HTTP and HTTPS Content

If you login with HTTPS URL and then redirect the user to HTTP , the browser will ask the user again to login with HTTP URL.

Fix:

Go To Central Administration

Open Alternate Access Mapping (AAM)

Select your will application from the dropdown menu on top right

Click on Edit Public URLs and remove HTTPS URL

Click on Add Internal URLs and add HTTPS URL and select the same zone as HTTP URL

Firewall settings for SharePoint Farm

Some times in SharePoint when we access web sites, search and configure alerts we see network related errors even though we configured everything is good. There could be so many reasons for that. One could be Firewall. In this article I am providing the steps how and where to check for the firewall issue.

On each SharePoint 2013 Server, we will need to set a firewall rule to allow SharePoint intra-farm traffic and HTTP/HTTPS traffic. Alternatively, you can disable the Windows Firewall if you choose and if you have another firewall solution.

You can set the Windows Firewall rules by navigating to the Control Panel, then click System and Security, then click Windows Firewall, and finally click Advanced settings. In the Inbound Rules area, ensure that the server allows connections on port 80 (HTTP) and port 443 (HTTPS). Add the ports listed in the table below for the SharePoint 2013 inter-farm communication by following these steps: 

   

1.    In the Windows Firewall with Advanced Security window, click Inbound Rules.

2.    In the Actions panel, click New rule…

3.    In the New Inbound Rule Wizard window, select Ports as the Rule Type and click Next.

4.    Select the appropriate choice between TCP and UDP, and enter the desired port(s) for the Specific local ports. Click Next.

5.    Click Next. On the Profile screen, click Next.

6.    On the Name screen, enter the desired name and click Finish.

                    

                      

Rule Name	TCP or UDP	Ports
SharePoint Web Traffic	TCP	80,443
SharePoint Search Index	TCP	16500-16519
SharePoint Farm Communication	TCP	32843,32844,32845
SharePoint Profile Synchronizing (TCP)	TCP	5725,389,88,53
SharePoint Profile Synchronizing (UDP)	UDP	389,88,53,464
SharePoint User Code Service	TCP	32846
SharePoint SMTP Service	TCP	25

SharePoint Server Search  Administration Medium Could not find or connect to host controller on server

Issue: After we configure everything in related to search, when we try to find the search we see the error “A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond”

Event:

0x0BD8        SharePoint Server Search   Administration   Medium   Could not find or connect to host controller on server SERVER. Exception: Could not connect to net.tcp://SERVER/ceres/hostcontroller/nettcp. The connection attempt lasted for a time span of 00:00:21.0369864. TCP error code 10060: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 10.181.14.21:808.    Server stack trace:      at System.ServiceModel.Channels.SocketConnectionInitiator.Connect(Uri uri, TimeSpan timeout)     at System.ServiceModel.Channels.BufferedConnectionInitiator.Connect(Uri uri, TimeSpan timeout)     at System.ServiceModel.Channels.ConnectionPoolHelper.EstablishConnection(TimeSpan timeout)     at System.ServiceModel.Channels.ClientFramingDupl…        ff78fc9b-4b6c-b08b-52a2-ff7745f71f1f

Resolution: After I go through the Events and run the traces I come to know that the issue is related to network. I deep on diving the issue I could identify that something is blocking to search.

1.   Ensure to check the Fire wall settings.

2.   Check to stop the firewall and search. This will work but not recommended in production servers.

3.   Ensure that to create a new rule allowing Inbound traffic on TCP Port 808 on all SharePoint Servers

Go to->Windows firewall with advanced security->click on Inbound->create new rule to allow                                

                                   
                                     
              
                                           

Issue: After we configure everything in related to search, when we try to find the search we see the error “A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond”

Event:

0x0BD8        SharePoint Server Search   Administration   Medium   Could not find or connect to host controller on server SERVER. Exception: Could not connect to net.tcp://SERVER/ceres/hostcontroller/nettcp. The connection attempt lasted for a time span of 00:00:21.0369864. TCP error code 10060: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 10.181.14.21:808.    Server stack trace:      at System.ServiceModel.Channels.SocketConnectionInitiator.Connect(Uri uri, TimeSpan timeout)     at System.ServiceModel.Channels.BufferedConnectionInitiator.Connect(Uri uri, TimeSpan timeout)     at System.ServiceModel.Channels.ConnectionPoolHelper.EstablishConnection(TimeSpan timeout)     at System.ServiceModel.Channels.ClientFramingDupl…        ff78fc9b-4b6c-b08b-52a2-ff7745f71f1f

Resolution: After I go through the Events and run the traces I come to know that the issue is related to network. I deep on diving the issue I could identify that something is blocking to search.

1.   Ensure to check the Fire wall settings.

2.   Check to stop the firewall and search. This will work but not recommended in production servers.

3.   Ensure that to create a new rule allowing Inbound traffic on TCP Port 808 on all SharePoint Servers

Go to->Windows firewall with advanced security->click on Inbound->create new rule to allow