Pivot Table and Pivot Chart

Pivot table is a function, which will help in doing some pictorial representation and more clarity on the data available in Excel.


Thought of sharing this knowledge about how to use the Pivot table in Excel.


To share this knowledge, I would take a scenario, where in, it talks about Travel from source to destination and also about the fare, duration of journey and also the mode of transport. We have the data already in the excel as below.

Source	Destination	Mode	Start	End	Fare	Duration	Passengers
Bangalore	Delhi	Train	10:00 AM	9:50 AM	1000	23.5hrs	5
Bangalore	Delhi	Bus	11:00 AM	9:00 AM	2000	22hrs	10
Bangalore	Delhi	AirBus	12:00 PM	2:00 PM	7000	2hrs	5
Mumbai	Delhi	Train	10:00 AM	9:50 AM	1000	23.5hrs	10
Mumbai	Delhi	Bus	11:00 AM	9:00 AM	2000	22hrs	5
Mumbai	Delhi	AirBus	12:00 PM	2:00 PM	7000	2hrs	10
Bangalore	Mumbai	Train	10:00 AM	9:50 AM	1000	23.5hrs	5
Bangalore	Mumbai	Bus	11:00 AM	9:00 AM	2000	22hrs	10
Bangalore	Mumbai	AirBus	12:00 PM	2:00 PM	7000	2hrs	5

For this kind of data, we can use the pivot table or pivot chart. For this we need to follow the things mentioned below:


Step 1:


For the data that you want to check in the excel for the Pivot information, we need to first, select the desired data as shown above.

Step 2: 


Now click on Insert from the Excel Menu, where you will be finding the Pivot table as shown below. Click on either Pivot Table or Pivot Chart.

If you are clicking on Pivot Table, then you will see the Create Pivot Table window, click on OK, and this will re-direct to a different sheet with in the book, and this will have the look as below.

If you are clicking on Pivot Chart, then you will see the Create Pivot Chart window, click on OK, and this will re-direct to a different sheet with in the book, and this will have the look as below.

Step 3: 


In Pivot Table
Now that if you select the check boxes available in the Pivot table Field List, you will be able to see the data 
as below:

In Pivot Chart

In the above pics, highlighted in bold red color are having fields that can be dragged and dropped where required among Report Filter / Column Labels / Row Labels / Values.


Hope this post, gives some basic information about how to use the pivot table in Excel.








Date Changes, Month Changes, Year Changes, Be Yourself, No Matter What ever Changes......!

SendEmail2Gmail - Send SharePoint Alerts to Gmail

In this post, I would like to share my knowledge on configuring Gmail with SharePoint Server 2010, such that we can receive alert mails to our Gmail Email Account.

Purpose of this Post:

Many of us will be having Development Server, some of which will not be having the Active Directory access wherein we cannot get the User profile information into SharePoint. In such scenarios, the only thing that we have is an administrator account for which, there will be no email associated with it. We can always have alternatives, so in such scenarios, we can send the alerts to our personal Email Accounts such as Gmail – Any other Email Accounts which will allow POP and also smtp of that Email Server should be known.

Targeted Audience:

This post will be more useful to those who wanted to work on SharePoint Alerts where they don’t have the Active Directory in network.

Pre-requisites: 

• SharePoint 2010 Foundation (or) SharePoint Server 2010 Installation
• Windows Server 2008 R2 Standard Edition
• Administrator Account on Windows Server 2008 to configure SMTP
• Windows Server Setup related files for installing smtp server.

Note: SharePoint 2010 Foundation or SharePoint Server 2010 Installation is out-of-scope in this post. Some of the features or actions will or may be possible only if the installation of SharePoint 2010 is complete up and running.

**Conditions Apply: This post is not intended to be used in any of the Production Servers or Corporate Servers with prior notice to the asset owners; however this is for self-learning to check the email functionality in SharePoint Applications.

Please find the below steps to configure Gmail with SharePoint Server to receive SharePoint Alerts to your Gmail Account.

Step 1:

Go to Control Panel, click on Turn Windows features on or off, go to Features, click Add Features, select SMTP Server from the list, click Add Required Role Services, click next (a few times), and click Install.

Step 2:

Open Internet Information Services (IIS) 6.0 Manager under Administrative Tools, expand the node with your local computer name on it, right click on SMTP Virtual Server, click on Properties.

Step 3:

On the Access tab, click on Relay …, Select All except the list bellow and click OK

Step 4:

On the Delivery tab, click on Outbound Security, on the new window that opens select Basic Authentication and type your Gmail (or Google Apps) email and password in the Username and Password fields, and also select TLS encryption

Step 5:

On the Delivery tab, click Outbound connections… and in the new window that opens change TCP port to 587

Step 6:

Once again in the Delivery tab, click on Advanced and in the new window that opens, in the Smart host field type smtp.gmail.com and click OK

Step 7:

Click Apply, click OK

Step 8:

Make sure POP is enabled for you Gmail account (it’s under Settings -> Forwarding and POP/IMAP).

Step 9:

To configure SharePoint server to send emails through Gmail:

• Open SharePoint Central Administration, go to System Settings, go to Configure outgoing e-mail settings.

• Type your computer name in the field Outbound SMTP server.

• Type your Gmail email address in field From address.

• Click OK

Wish this helps you to start working on the SharePoint Alerts that you might create using OOB features or SharePoint Designer.

Date Changes, Month Changes, Year Changes, Be Yourself, No Matter What ever Changes......!

SharePoint 2010 - New Capabilities and Features

Many of us might be wondering, why SharePoint 2010 is becoming so popular and why is that there are a lot of migrations are happening from MOSS 2007 to SharePoint 2010?  - I was looking for some articles and finally found something useful, which I thought to share with you.


SharePoint 2010 can be broadly classified into 3 based on its new capabilities and features. They are


Tools for Developer Productivity
Rich Platform Services and
Improved Flexibility for Deployments


Lets see what is there inside each of these.




TOOLS FOR DEVELOPER PRODUCTIVITY

• Microsoft Visual Studio 2010 SharePoint project types and items
• Microsoft Visual Studio 2010 SharePoint tools extensibility
• Microsoft Visual Studio extensions for SharePoint upgrade
• Windows® 7 and Windows Vista® operating system support
• Microsoft SharePoint Designer 2010
• Developer dashboard
• Visual Studio Team Foundation Server 2010 integration

RICH PLATFORM SERVICES

• SharePoint Ribbon
• SharePoint Dialog Framework
• Microsoft Silverlight® Web Part
• List lookups and relationships
• Business Connectivity Services
• LINQ to SharePoint
• Performance enhancements
• Solution throttling
• Event enhancements
• Workflow enhancements
• Client object model
• Open Data Protocol (OData) REST APIs

IMPROVED FLEXIBILITY FOR DEPLOYMENTS

• Sandboxed solutions
• Silverlight application deployment
• SharePoint Online
• Upgrading solutions

Further to this, if we want to understand the Development Platform Stack for SharePoint 2010, please refer below pic, taken form SharePoint 2010 SDK.

Further to this, if we want to understand the difference between SharePoint 2007 and SharePoint 2010, I found the below from one of the Walkthrough article of Microsoft Site.

 Will keep you posted more on the information acquired on SharePoint 2010 going forward.




Date Changes, Month Changes, Year Changes, Be Yourself, No Matter What ever Changes......!

Extract URL from Hyperlinks from a SharePoint List using Excel

Scenario: In a SharePoint Custom list, in one of the column, it stores the title of the SharePoint Sites as a Hyperlink, however, there is no column to store the URL. There were about 1000's of records of such type in this custom list. Now that the client is requesting to get the URL's of each of the Title of the SharePoint Site.

Resolution: This can be acheived by exporting the data to excel and writing a macro and running it.

Restriction: This is a manual process and will be helpful for getting monthly data kind of requirements. Automation for retreiving this data is out of scope in the blog.

Solution:
Follow the below steps to extract the URL's from the Hyperlinks in a SharePoint Custom List.

Step 1: Browse the SharePoint Custom list and select "Actions -> Export to Spreadsheet". Now that Save the file in Excel format. Now try to create a column to extract and save the URL's beside Hyperlink column in which the titles are stored.

Step 2: Now in Excel menu, click on Developer, and select Visual Basic. Browse the tree in the left menu of the VBA Project and double click on the sheet name in which the current data is saved.

Step 3: Add the below code and save and click on Play button to run the macros and then close this VB xlsx.

Sub ExtractHL_AdjacentCell()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value =
HL.Address
Next
End Sub


NOTE: Make sure that the macros is enabled in Excel. This can be checked from Excel menu -> Developer -> Macro Security and modify the Macro settings as needed.

You will now see all the extracted URL's in your Excel.


Step 4: Now copy the data and paste it in SharePoint list by creating a new column to store the URLs.



Date Changes, Month Changes, Year Changes, Be Yourself, No Matter What ever Changes......!