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......!