September 5, 2017

Uninstall SharePoint 2013 - One or more required office component failed to complete successfully.

Issue:
While uninstalling SharePoint Server 2013, you may get below error message.

Error:
"One or more required office component failed to complete successfully. For more information, consult the setup log file." as shown in below screenshot.
 

Resolution: To fix the issue follow below steps:
 
1. Go to your Database Server.
2. Go to Services section:- Go to Run (Windows + R) and type "services.msc".
3. Stop the following services:
  • SQL Full-text Filter Daemon Launcher (MSSQLSERVER)
  • SQL Server (MSSQLSERVER)
  • SQL Server Agent (MSSQLSERVER)
  • SQL Server Analysis Services (MSSQLSERVER)
Again go to Control Panel and uninstall SharePoint 2013, you will not get this error this time.

In case, if you reinstall SharePoint server, do not forget to restart all those services.

If you have any questions you can reach out our SharePoint Consulting team here.

September 1, 2017

Office 365 - Exchange Online: Allow specific External Domian Users to send emails to Distribution Groups

Problem Statement:
We came across a requirement to create a Distribution Group where -
1. Users from internal organization can send emails to this Distribution Group.
2. External Users ONLY from partner organization domain can send emails to this Distribution Group.

Distribution Groups in Exchange Online provides a functionality to restrict External Users to send emails. But here, in this case, we need to allow users from one External Domain (let's say domain is demowork.xyz) to send emails to Distribution Group.

Domain based sender filtering is NOT directly available when we create the Distribution Group, but we can achieve this requirements by creating a Transportation Rule in Exchange Online.

Resolution:
We can use Transportation Rule to restrict a Distribution Group to receive emails from internal organization and specific external domain only. Steps to create a new Distribution Group/Distribution List(DL) and configure the Transportation Rule for sender domain restriction are summarized below:

A. Create Distribution Group:
1. Login to Exchange Control Panel (ECP).
2. Navigate to "Recipients" -> "Groups".
3. Click "+" icon and select "Distribution Group".
4. Click "To create a new distribution group, click here" option (as highlighted in below screenshot) from newly opened window:

5. Populate the required information to create new group:
  • Display Name: Desired Group Name (We will name this as "Helpdesk" in this example).
  • Alias: Desired Group Alias Name (We will keep this as "Helpdesk" in this example).
  • Email Address: Desired email address for group (In this example, it is like "helpdesk@binaryrepublik.com")
  • Add Owners and Members of the group as needed.
  • Select Group Membership options as needed. We will select "Closed" for both the options - (1) Choose whether owner approval is required to join the group. (2) Choose whether the group is open to leave.
  • Click "Save".
6. This newly created Distribution Group by default accepts emails only from senders within organization. So, we need to apply a change for this Distribution Group to accept emails from Outside of organization. In order to enable this -
  • Select this Distribution Group and Click Edit icon.
  • Go to "Delivery Management" in newly opened window.
  • Select "Senders inside and outside of my organization" option and click "Save".
7. Now, we have a group created that accepts emails for senders from inside and outside organization (any sender from any domain).

Now, We have to create a Transportation Rule to restrict this Distribution Group to receive emails only from senders inside organization and from specific partner domain (e.g. demowork.xyz).

B. Create Transportation Rule:
1. Go to Exchange Control Panel -> Mail Flow -> Rules.
2. Click "+" icon and select "Create a new rule".
3. Click "More Options" at bottom of the newly opened window.
4. Furnish the information to create new rule -
  • Name: Meaningful name for the rule (e.g. - Helpdesk Restriction)
  • Apply this rule if -> select "The message..." -> "To or Cc box contains this person".
  • Select newly created DL and click OK. (In this example, we will select "Helpdesk").
  • Do the following -> "Block the message..." -> "Reject the message and include an explanation". Specify the desired explanation like "This email address is only for specific people. You are not allowed to send emails to this address" and click OK.
  • Click "add exception" under "except if" section.
  • Select "The sender..." -> "domain is"
  • Add internal organization domain (in this example, binaryrepublik.com) and partner domain (in this example, demowork.xyz) and click OK. Basically, here we need to define domains of allowed senders.
5. Click "Save".

The Distribution Group & Transportation Rule is configured.

Conclusion:
This way, using Transportation Rule, we can allow a Distribution Group in Exchange Online to receive emails only from senders of specific domain(s).

If you have any questions you can reach out our SharePoint Consulting team here.

August 30, 2017

SharePoint Basics - Find out Site Template used to create a SharePoint Site

Sometimes, we need to find out Site template used to create SharePoint site. A very easy way to find out the template without writing any code is as below.

Step 1: Open your SharePoint Site/Sub site in browser.
Step 2: Right click in the browser and select "View page source".
Step 3: Find Keyword "g_wsaSiteTemplateId". It will show a code for Site Template.
Step 4: Here, "STS#0" is used which is the template code for "Team Site" template. You can refer this TechNet Article for Template Code Reference.
 

If you have any questions you can reach out our SharePoint Consulting team here.

Export large number of records from Database to Excel using C#

Objective
In this blog, I've explained how we can export large number of records in Excel file within very few seconds. I have implemented this solution in one of the project, where I need to export around 100,000 records in less than a minute.

Approach
  • First step is to download the using NuGet Package with following command:
      
 
  • It will add DocumentFormat.OpenXml.dll into your project:
    
 
  • I had a situation where I was getting data in Generic List, So I converted Generic List to DataTable first. (Note: If you have all the records in DataTable itself, you don't need to execute below function!)
 public static DataSet ToDataSet<T>(this IList<T> list)  
 {  
       Type elementType = typeof(T);  
       DataSet ds = new DataSet();  
       DataTable t = new DataTable();  
       ds.Tables.Add(t);  
       foreach(var propInfo in elementType.GetProperties())  
       {  
            Type ColType = Nullable.GetUnderlyingType(propInfo.PropertyType) ?? propInfo.PropertyType;  
            t.Columns.Add(propInfo.Name, ColType);  
       }  
       foreach (T item in list)  
       {  
            DataRow row = t.NewRow();  
            foreach (var propInfo in elementType.GetProperties())  
            {  
                     row[propInfo.Name] = propInfo.GetValue(item, null) ?? DBNull.Value;  
            }  
            t.Rows.Add(row);  
       }  
       return ds;  
 }  

  • Now use below code to export the records in Excel file.
 //Convert Generic List to DataSet  
 DataSet ds = ToDataSet(objList);   
 //Specify the column names which would be the Headers in Excel File when exported.  
 ds.Tables[0].Columns[0].ColumnName = "Account Name";  
 ds.Tables[0].Columns[1].ColumnName = "NAV Date";  
 ds.Tables[0].Columns[2].ColumnName = "NAV ₹";  
 var filename = ExportDataSet(ds);  
 return filename;  
 private static string ExportDataSet(DataSet ds)  
 {  
      string destination = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString() + ".xlsx");  
      using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))  
      {  
           var workbookPart = workbook.AddWorkbookPart();  
           workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();  
           workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();  
           foreach (System.Data.DataTable table in ds.Tables)  
           {  
             var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();  
             var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();  
             sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);  
             DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();  
             string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);  
             uint sheetId = 1;  
             if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)  
             {  
                       sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s =>s.SheetId.Value).Max() + 1;  
             }  
             DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };  
             sheets.Append(sheet);  
             DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();  
             List<String> columns = new List<string>();  
             foreach (System.Data.DataColumn column in table.Columns)  
             {  
                  columns.Add(column.ColumnName);  
                  DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();  
                  cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;  
                  cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);  
                  headerRow.AppendChild(cell);  
             }  
             sheetData.AppendChild(headerRow);  
             foreach (System.Data.DataRow dsrow in table.Rows)  
             {  
                  DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();  
                  foreach (String col in columns)  
                  {  
                      DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();  
                      cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;  
                      cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());  
                      newRow.AppendChild(cell);  
                  }  
                  sheetData.AppendChild(newRow);  
             }  
           }  
      }  
      return destination;  
 }  


If you have any questions you can reach out our SharePoint Consulting team here.

August 2, 2017

SharePoint List View Web Part - Open documents in new browser tab

Scenario:
I wanted to show documents stored in a Document library, on a page with the help of  List View Web Part. I added List View web part and configured the same to display documents from a document library. Everything looked good apart from one thing. I wanted documents to open up in a new tab. But, instead, documents were opening in same tab.  

If there had been navigation links just thrown up on the page without any web part, I would have just added target="_blank" attribute. But, here, I had used Listview web part. So, I was compelled to come up with a different solution. A solution that should be working without jeopardizing the page view and other functionalities. I kept on turning over different solutions until I ran across a solution that looked easy and could be carried out with minimum efforts. I added a content editor web part and punched in my code into it. Saved the page and clicked on the document. And as expected, It didn't let me down.

Solution: Here, I am sharing the steps that I've performed to rid myself of that issue:

Step 1:
Click on Edit page from Site Settings Gear icon.
 

Step 2:
Now, Click on Add a web part.

Step 3:
Seek "Media and Content" and add "Content Editor" web part.  It should be on the top in the all web parts listed in Media and Content gallery.

Step 4:
Once web part has been added, click on "Click here to add content".

Step 5:
You should see "Format Text" ribbon and in that ribbon, You should see "Edit Source".

Step 6:
Click  on Edit Source and tap this code into that window. Click on OK.
 <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js" type="text/javascript" />  
 <script type="text/javascript">  
 $(document).ready(function(){  
   $('#onetidDoclibViewTbl0 a').removeAttr('onclick').attr('target', '_blank');}  
 );  
 </script>  

 
Step 7:
 Now, go to Edit Web Part menu and change appearance settings to hide title and chrome for this web part.

Step 8:
Click on Stop Editing to save the page.

Step 9:
Check it in and publish it.

Step 10:
Now, Click on a document name and it should be opening in a new browser tab.

If you have any questions you can reach out our SharePoint Consulting team here.

SharePoint - Calendar Web Part with Color Code and Overlays

In this blog, I'll walkthrough in detail how we can configure SharePoint Calendar Web Part (in OOTB manners) that displays different color code for different category of events. In this example, "Category" is a choice column (having three choices - "Tasks", "Training" and "Current Events") defined in the Calendar List.

Once configured, calendar web part will look as shown in below screenshot. This will show different color for different category of events. For example, we took three different categories that are "Tasks", "Training" and "Current Events".


Now Let's check how we can achieve this.


Step 1: Create three different views for all categories.
1. Create a calendar view that will show events filtered by Category = "Current Events".

  • Navigate to Calendar List -> Select Create View (from Ribbon).

  • Select Calendar View.

  • Fill Information and Apply Filter. This view will hold all events filtered by Category = "Current Event".

2. Repeat above steps to create two more calendar views that will show events filtered by Category = "Tasks" and "Training".

3. Create one common calendar view "ALL". Don't Apply filter in this view.

Step 2. Now we will add Calendar Overlays in newly created "ALL" calendar view.
1. Define Calendar Overlay for different category specific views in "ALL" Calendar View we have created.
  • To add Calendar Overlay, Select Calendar > Open "ALL" Calendar View > Calendar Overlays (from Ribbon).

  • Click New Calendar.

  • Fill information and apply color code for your view > Press OK.


2. Repeat these steps for "Training" and "Tasks" to define Calendar Overlay for these categories too.

3. We have "ALL" Calendar View defined with overlay. Now, we can use this view on Calendar View Web Part.

4. To observe the results, let's add Calendar web part on SharePoint Page. And select "ALL" Calendar View from the web part properties. We will see the result as expected with different color codes on Calendar for different category of events.

So, this way we can configure Calendar web part to display different color code for different category of events with Calendar Overlay.
 
If you have any questions you can reach out our SharePoint Consulting team here.

July 31, 2017

SharePoint 2013 - Managed Metadata Navigation is not visible to Anonymous and Read Permission Users.

Scenario:
Managed Metadata service Application lets you define your site navigation using term sets. It lets you tag your content with term sets making structure of your content relevant. But, you might wind up in situation wherein users having Read Permission or Anonymous users are not able to see the Managed Metadata navigation. This issue crops up due to "" being selected in Navigation tab for the term. If you want to rid yourself of this issue, you have two options:

Option #1: You can either change it to "Simple Link or Header in Navigation tab"  
                                                                 OR
Option #2: You can set your target page in " " in Term-Driven Pages tab and save the settings, this issue should go away.

Steps for both approaches are summarized below:

Step 1: From your Site Collection, go to Term Store Management Under Site Administration from Site Settings.


Step 2:
 - If you want to go for Option #1 as resolution, under your Term Set -
     - Select your Term.
     - Select "Navigation" Tab in right panel.
     - Select "Simple Link or Header" under Navigation Node Type section.
     - Click "Save".


 - If you want to go for Option #2 as resolution, under your Term Set - 
     - Select your Term.
     - Go to Term-Driven Pages tab and set page for Change target page for this term after checking the checkbox and click Save.

Step 3: Do the same steps for all the terms that are invisible to Anonymous and Read only users.




If you have any questions you can reach out our SharePoint Consulting team here.

July 27, 2017

JavaScript Best Practice Tips

Introduction
In this article, I'll share important factors to be taken into considerations while working with JavaScript. This information will help you to prevent most common mistakes in web development.

Quick Tips
  1. Use === while comparing two variable instead of ==
  2. Remember undefined is not null
  3. Remember JavaScript falsy value:   0, '', NaN, null, undefined
  4. Remember JavaScript truthy value:   '0', 'any string', [] (Empty array), {} (Empty object), 0 (any non-zero number)
  5.  Always declare all variables at the beginning of every scope
  6.  "use strict"; In JavaScript to avoid unwanted bug due to a variable.
  7. Avoid global variable declaration
  8. Reduce global variables e.g var name='abc', isValid=false; Should be written as var common={name:'abc', isValid:false};
  9. Always declare local variables
  10. Never declare Number, String or Boolean Objects ( e.g. Never use: new Number(1), new String("abc"), new Boolean() )
  11. Use {} instead of new Object()
  12. Use "" instead of new String()
  13. Use 0 instead of new Number()
  14. Use false instead of new Boolean()
  15. Use [] instead of new Array()
  16. Use /()/ instead of new RegExp()
  17. Use function (){} instead of new Function()
  18. Avoid Using eval()
  19. Don't use short hand (e.g Always use curly bracket with conditional operation)
  20. Place scripts at the Bottom of the page
  21. Declare variables outside of the loops and conditionals (such as if, for, while, switch and try)
  22. Properly comment your code
  23. Never pass a string to SetInterval and SetTimeOut. Instead, pass a function name
  24. Always, Always Use Semicolons
Reference Links
If you have any questions you can reach out our SharePoint Consulting team here.

July 6, 2017

Power BI – Develop Reports from Google Analytics using Power BI

Power BI is a great business analytics service provided by Microsoft. It provides wide range of interactive visualizations with self-service business intelligence capabilities for analytical reports and dashboard development. Now, for analytical reports and dashboard development, source of data is very important. Power BI is flexible enough to consume data from variety of data sources

We can also develop Power BI Reports & Dashboard using Google Analytics as the source of data.

Google Analytics is a web analytics service offered by Google that tracks and reports website traffic. So it helps us to analyze the web traffic in more effective manners if we have BI reports available for Google Analytics data. Here, I'll walkthrough in detail how to create a Power BI report consuming Google Analytics as data source.

This report will be developed in Power BI Desktop. First, we need to connect to data source in Power BI Desktop.
Connect to Data Source (Google Analytics):

1. Open Power BI Desktop, and click “Get Data”:

2. Select “Online Services” -> "Google Analytics" as data source and click “Connect”:

3. Continue to connect to a third-party service:


4. Click "Sign In" to login to your Google Analytics account:


5. Enter your Google Account credentials:


6. Allow Power BI to access Google Analytics data:

7. Now we are logged in to Google Analytics account. Just click "Connect" to consume Google Analytics data:

So, now Google Analytics data is loaded to Power BI Desktop. We can consume the data to develop the report as per our requirements.

Develop the Power BI Report (Google Analytics):

For this example, we will develop the report that will help us to visualize the users visited the web site from different locations.

1. From available data, load -
  • Users from "User" section.
  • City, Region and Country from "Geo Network" section.


2. Select a Map from Visualizations and configure as shown in below image so that we will be able to see users from different locations on map with Country -> Region -> City drill-down capabilities in Power BI:


3. Now, we have configured the visual and report will appear as shown in below image. Report also provides us information on no. of users from different countries.

4. Enable the Drill-Down capabilities and that will allow us to drill-down to any Country -> Region -> City.:


4. So we get the user visits data for specific country. User can click any specific region from country to further drill down to region level to see city specific website visit information:

So, it becomes easier and helpful to analyze the website visits in geographic manner.

Publish the Report (Google Analytics):

 We can publish the developed report to web. To publish the report to web, we must have Power BI account. If you don’t have account, you can sign-up here.

1. Click “Publish” in ribbon bar and sign in as your account in Power BI Desktop:


2. Enter your credentials and click “Sign In”:

3. Select “My workspace” as destination to publish the report.

4. So, report is published to Power BI web. Please click the provided link to open the report on web.


5. Report is opened now on the web:


If you have any questions you can reach out our SharePoint Consulting team here.

Link to SharePoint List in Navigation is not visible for Anonymous user.

Recently working on a SharePoint Project wherein I wanted to have Calendar list as a link in main navigation of the site. I rushed to Site Settings and went inside Navigation in Look and Feel section. I added main heading as "Calendar" and under "Calendar" heading, I added "Events" link which was pointing to Calendar list. Everything was looking great until I checked it with Anonymous user.

Surprisingly, Anonymous user couldn't see "Events" link. I was baffled and was scratching my head as to what would have caused this behavior.  I started to dig out in more detail, checked permission, and I discovered that it all boils down to one feature "Limited-access user permission lockdown mode". If this feature is activated, fine-grain permissions for limited access users are reduced. Hence, Application Pages won't be visible to Anonymous user. If you want Anonymous users to see Application pages, this must be deactivated.

Here are the steps which will help you sail through the issue.

1. As you can see in below screenshot, Events link is not visible for Anonymous user.
 
2. Now, Login with Admin user. Go Site Settings >> Site Collection Features , you would see that
"Limited-access user permission lockdown mode" feature is activated.
 
3. Deactivate this feature.
 
4. Now, checking "Events" link with Anonymous user. It should be visible.



If you have any questions you can reach out our SharePoint Consulting team here.

July 5, 2017

Prepare environment for SharePoint Framework (SPFx) Development

Microsoft has already announced the General Availability for SharePoint Framework. We can develop modern web parts for SharePoint using SharePoint Framework (SPFx). Web Parts developed using SPFx can be added to modern view pages as well as classic pages in SharePoint.

Prepare your development environment for SharePoint Framework development:

A. Install Node Dependencies for SharePoint Framework.

1. Download and Install Node.js.
2. Open command prompt and execute below commands one by one to install bower, grunt-cli and yeoman respectively.
i. npm install -g bower
ii. npm install -g grunt-cli
iii. npm install -g yo

3. Install Microsoft SharePoint Generator by executing below command in command prompt.
i. npm i @microsoft/generator-sharepoint -g

4. Install Gulp by executing below command in command prompt.
i. npm install gulp -g

B. Install desired source code editor (e.g. Visual Studio Code, ATOM, Sublime).

Build your first web part using SharePoint Framework (SPFx):

A. Open Node.js Command Prompt.
B. Navigate to the directory where you want to have your solution located at (e.g. cd c:\SPFx).
C. Execute command: yo
D. This will list down the available generators. As we have already installed Microsoft SharePoint Generator, this will appear as an option here.
E. Select “Microsoft SharePoint Generator” and press enter.

F. This will ask for below details, enter appropriate details and move forward:
i. Solution Name – e.g. “HelloWorld”.
ii. Select “Use the current folder” option.
iii. You can select the JavaScript Web Framework from available options. I have selected “No JavaScript Web Framework” option for this application.
iv. What is your webpart name? – e.g. “HelloWorldWP”
v. What is your webpart description? – e.g. “Hello world web part developed using SharePoint Framework.”.
vi. This will take a few minutes to prepare the solution.

G. Once solution is created, we can see success message for solution creation as shown below:

H. To check the solution, open the solution in Visual Studio Code from file system directory (C:\SPFx) where we created the solution.

I. As this post is focused on to prepare the development environment for SPFx, we will not go in depth with the source code, but as we can see in below screenshot, default Hello World web part appears in the solution:

Run SPFx solution on development environment:

A. Open Node.js Command Prompt and navigate to the directory where we created the solution (C:\SPFx).
B. Execute command “gulp serve” as shown in below screenshot:

C. On execution, we get SharePoint User Interface in browser on localhost (It is NOT required to have SharePoint installed on development environment). And when we click “+” button on modern UI, we can see Hello World web part in available options as show below:

D. When web part is added on the page, we can see the same as shown below:

So, the environment is prepared and verified for SharePoint Framework (SPFx) development. For verification, we have used default Hello World web part, we can develop the web parts as per requirements. 

If you have any questions you can reach out our SharePoint Consulting team here.