Tuesday, June 24, 2014

SharePoint 2013 - Delete UserProfile Data with PowerShell

This PowerShell script will take two input files Users.csv (with all the user names under USERID column) and Properties.csv (with all the properties under TITLE column) and delete the values of UPA properties for the given users.

#PowerShell Script - Delete User Profile Properties Data in MySite 2013 UPA

Add-PSSnapin "Microsoft.SharePoint.PowerShell"

function WriteLog
    Param([string]$message, [string]$logFilePath)
    Add-Content -Path $logFilePath -Value $message


$logFile = "D:\PowerShellScripts\DeleteUserProfileDataInUPA\DeleteUserProfileDataInUPA.log"
$batchFile = "D:\PowerShellScripts\DeleteUserProfileDataInUPA\Users.csv"
$PropertiesFile = "D:\PowerShellScripts\DeleteUserProfileDataInUPA\Properties.csv"
$mysiteHostUrl = "http://company.MySite.com"

$mysite = Get-SPSite $mysiteHostUrl
$context = [Microsoft.Office.Server.ServerContext]::GetContext($mysite)
$upm =  New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)

$currentProfile = 0

$Properties = Import-CSV $PropertiesFile

$users = Import-CSV $batchFile
$totalProfiles = $users.Count
ForEach ($user in $users) 
    $currentProfile ++;
    $AccountName = $user.USERID
    $profile = $upm.GetUserProfile($AccountName)

        forEach ($Property in $Properties)
            $PropertyTitle = $Property.TITLE

            $OldValue = $profile[$PropertyTitle].Value;

            $profile[$PropertyTitle].Value = $null;

            $now = [System.DateTime]::Now
            $msg = $now.ToString() +  " : Deleting value from "+ $PropertyTitle +": "+ $OldValue +" for " + $AccountName + " (" +$currentProfile + " of " + $totalProfiles + ")"
            write-host $msg
            WriteLog $msg $logFile

    catch [system.exception]
        $msg = $Error[0].Exception.InnerException.InnerException.Message
        write-host -f red $msg
        WriteLog $msg $logFile
    $profile = $Null
    $user = $Null


Monday, June 16, 2014

SharePoint 2013 - Excel Services (BI Reporting)

Business intelligence (BI) is a set of theories, methodologies, architectures, and technologies that transform raw data into meaningful and useful information for business purposes. SharePoint 2013's Excel Services have made BI reports publishing a lot easier.

In this post, I am going to create a dynamic report using Power View in Excel and then will publish it in SharePoint 2013/Online. I will use a sample MS Access database (RptSampl.mdb) provided by Microsoft to build a new report. Same concepts covers the reports development for MS SQL Server. This post has following three sections:
  1. Importing the Data 
  2. Designing a Report 
  3. Publishing a Report to SharePoint.
Lets get started.

Importing the Data into a Excel Report

  1. Download sample database from Microsoft's site.
  2. Launch MS Excel 2013 and go to POWERPIVOT tab.

    1. If you don't see this menu then enable it by going to File -> Options.
    2. Under Add-Ins select Manage: COM Add-ins and then hit Go.
    3. Check Microsoft Office PowerPivot for Excel 2013 and then hit OK.
  3. POWERPIVOT tool bar will look like this
  4. Select Manage and the following window will appear where we will connect to the database, import the data and will arrange the relationship between tables.
  5. Select Get External Data -> From Database -> From Access
  6. Select RptSampl.mdb from your downloads  folder and it doesn't not need any login and password then hit Next.
  7. Choose Select from list of tables and views to choose the data to import in the next screen and hit Next again
  8. Select the tables you want to to add in your report. I will select Products and Suppliers then hit Finish.

    You will see data from two tables will get imported into Excel. It will also increase the size of the Excel file. Select Close after data import.
  9. You can select Diagram View from the tool bar to see the relation between tables.
  10. Hit Save and it will save the data and close the Dialog.

Designing a Excel Report

  1.  Go to INSERT tab and select Power View in Reports section.
  2. This will open a new work sheet, select Power View tab, show you the imported tables so we can design our report here.
  3. Expand tables and select fields that needs to be displayed in the report. For this report I will select ProductName and UnitPrice columns from Products table.
  4. This is your basic report. You can apply filter by clicking the filter button and can perform sorting by clicking the title of the column inside the report.
  5. You can present the information in a dynamic Chart too by selecting Bar Chart from DESIGN tab

    And your report should look like this
  6. You can add other columns like UnitsInStock, UnitsOnOrder to see how they will look in the report's chart.
  7. Save the Excel work book in local folder with the name Report.xlsx.

Publishing a Excel Report to SharePoint Online/2013

  1. Upload the Report.xlsx to a document library in SharePoint Online.
  2. Click the Report.xlsx inside the document library and SharePoint will open the report inside the browser with Excel Online which is a part of Office Web App (OWA).
And thats how you develop and publish a BI report using Excel Services.

Thursday, June 12, 2014

SharePoint 2013 - Displaying Charts using Excel Web Access

Excel Web Access web part comes with Excel Services capabilities in SharePoint. This web part is used to display data from an Excel workbook on a SharePoint site so that user can share reports and charts with other users.

In this post, I am going to explain how to display a chart from an excel file to a SharePoint Page using Excel Web Access web part.
  1. Create an Excel work book with the following data in it.
  2. Insert a graph chart by selecting the data rang
  3. Save the workbook (mine is called Sales.xlsx) and upload it in a document library in SharePoint.
  4. Now edit and add an Excel Web Access web part on any page where you want to display the chart.
  5. Edit web part to see its properties
  6. Set Workbook (select Salex.xlsx from the document library) and Named Item properties (Chart 1) and then hit OK on the web part. By default all charts are named as Chart 1, Chart 2 etc
  7. You should be able to see the chart from the Excel workbook on the page.
Every time you have a new data in Excel workbook, just upload the file into the document library and refresh the page and you will see the updated chart.

Wednesday, June 4, 2014

SharePoint Online(2013) - Web Part Deployment

In my previous post, I have explained how to develop a SharePoint 2013 web part using Visual Studio. Now lets deploy that web part to the production environment e.g A site collection in SharePoint Online.
  1. Open the existing Web Part project in Visual Studio 2013.
  2. Select Feature1 and you can change the scope of the deployment here. Scope: Site means this feature will be a Site Collection feature and web part inside this feature will be available in the whole site collection.
  3. Select Build -> Publish -> Publish to File System. Provide location and hit Publish. This will generate the WSP (myVisualWebPart.wsp).
  4. Go to the production site (my production site is on SharePoint Online).
  5. Go to Site Settings -> Solutions under Web Designer Galleries.
  6. Select Upload Solution -> Choose file and select the WSP file from your desktop and upload it.
  7. Once the solution package is uploaded select Activate. This action will activate the feature and make the web part available in the web part gallery.
  8. If you go to Site Settings -> Site collection features under Site Collection Administration. You will see your feature activated here.

  9. Now go to any page on the production site and add your web part. It should be available under Custom category.

Thanks heaven for the easy deployment =).

SharePoint 2013 - Web Part Development

In this post, I am going to demonstrate how to develop a SharePoint 2013 Web Part and then deploy it on local SharePoint site. For production deployment see this post.
Lets get started!
  1. Launch Visual Studio 2013 with Administrator privileges.
  2. Select SharePoint Solutions -> SharePoint 2013 - Visual Web Part and provide name of the project along with the location.
  3. Provide the url of local SharePoint site for debugging then select Deploy as a sandboxed solution option and hit finish.
  4. You will see the basic structure of the Visual web part created along with one User Control (ASCX) to handle GUI elements and one Feature to handle packaging (WSP).
  5. Open the user control (VisualWebPart1.ascx) and add following ASP.NET Grid.
    <asp:GridView ID="gvProducts" runat="server" />
  6. Open the code behind of the user control (VisualWebPart1.ascx.cs) and add the following code:

    Add Reference to SharePoint API
    using Microsoft.SharePoint;
     Add CreateChildControls() function
    protected override void CreateChildControls()
        SPSite objSite = SPContext.Current.Site;
        using (SPWeb objWeb = objSite.OpenWeb())
            SPQuery objQuery = new SPQuery();
            objQuery.ViewFields = "<FieldRef Name='ID' /><FieldRef Name='Title' /><FieldRef Name='Author' />";
            objQuery.ViewFieldsOnly = true;
            SPList objList = objWeb.Lists["ListA"];
            SPListItemCollection oCollection = objList.GetItems(objQuery);
            gvProducts.DataSource = oCollection.GetDataTable();
    Your code should look like this:
  7. Select Build -> Deploy Solution.
  8. Open IE and go to the site specified in the step 3. Edit the home page and select Insert -> Web Part. You should see this web part under Custom category, select and add the webpart.
  9. It should display like this
For Packaging and deployment check out the next post.

Sunday, June 1, 2014

SharePoint 2013 - App Development

In this post, I will explain how to read list data from a SharePoint 2013 App using Javascript Object Model (JSOM). So sit tight as it's about to get bumpy in here!!!
  1. Launch Visual Studio 2013 and Create a SharePoint 2013 App project.
  2. Data is in an Announcement list that will be accessed by this App. My Announcement list has two columns: Title(Text) and Status(Yes/No).
  3. Add a button and a table in Default.aspx in Pages in Solution Explorer
    <asp:Content ContentPlaceHolderID="PlaceHolderMain" runat="server">
            <p id="message">
                Click the button...
        <input type="button" value="Get Announcements" onclick="GetAnnouncements()" />
        <table id="tblItems" border="1" width="500"></table>
  4. Now open App.js under Scripts and add the following code
  5. 'use strict';
    var context;
    var web;
    var hostWeb;
    var user;
    var hostweburl;
    var appweburl;
    var appContextSite;
    var list;
    var collList;
    var appContextSite;
    function getUrl() {
        hostweburl = getQueryStringParameter("SPHostUrl");
        appweburl = getQueryStringParameter("SPAppWebUrl");
        hostweburl = decodeURIComponent(hostweburl);
        appweburl = decodeURIComponent(appweburl);
        $("#hostWebURL").text("Host Web URL: " + hostweburl);
        $("#appWebURL").text("App Web URL: " + appweburl);
    function getQueryStringParameter(paramToRetrieve) {
        var params =
        var strParams = "";
        for (var i = 0; i < params.length; i = i + 1) {
            var singleParam = params[i].split("=");
            if (singleParam[0] == paramToRetrieve)
                return singleParam[1];
    // This code runs when the DOM is ready and creates a context object which is needed to use the SharePoint object model
    $(document).ready(function () {
        context = SP.ClientContext.get_current();
        web = context.get_web();
    function GetAnnouncements() {
        appContextSite = new SP.AppContextSite(context, hostweburl);
        var oList = appContextSite.get_web().get_lists().getByTitle('Announcements');
        var camlQuery = new SP.CamlQuery();
            '<View><Query><Where><Geq><FieldRef Name=\'ID\'/>' +
            '<Value Type=\'Number\'>1</Value></Geq></Where></Query>' +
        this.collListItem = oList.getItems(camlQuery);
            Function.createDelegate(this, onQuerySucceeded),
            Function.createDelegate(this, onQueryFailed)
    function onQuerySucceeded() {
        var listItemEnumerator = collListItem.getEnumerator();
        var str = '';
        while (listItemEnumerator.moveNext()) {
            var oListItem = listItemEnumerator.get_current();
            var imageStatus = '';
            if (oListItem.get_item('Status') == 'Yes')
                imageStatus = '<img src=../Images/Yes.jpg />'
                imageStatus = '<img src=../Images/No.png />'
            str = str + "<tr><td>" + oListItem.get_item('Title') + "</td><td>" + imageStatus + "</td></tr>";
    function onQueryFailed(sender, args) {
        $('#message').text('Request failed. ' + args.get_message() +
            '<br/>' + args.get_stackTrace());
  6. Open AppManifest.xml and under Permissions tab, set List under Scope and FullControl under Permission section. This will tell user that this App needs access on Host Site's lists.
  7. Hit the Play button and if the forces are with you then your app should look like this after successful deployment.

Here are Common Programming Tasks using Javascript Object Model. Now you are ready to create your million dollar App =).

SharePoint 2013 - Hello World App

Hello World!!! Lets create a SharePoint 2013 App.
Following are the quick steps to create a SharePoint Hosted App using Visual Studio 2013:
  1. Launch Visual Studio 2013.
  2. Select File -> New Project
  3. Select App for SharePoint 2013 and provide name and location of the project
  4. Provide url of the developer site (Site collection created by using Developer Site template) and select SharePoint Hosted App in hosting option then select Finish.
  5. Hit Start from the tool bar, it will compile and deploy the app to your developer site.