Tabbed View Report using SSRS

Today, I published my first Tech Net article about creating Tabbed View Report using SSRS. SQL Server Reporting Services is a powerful BI tool by Microsoft.

Soon, I will publish articles about SSRS and its integration with SharePoint. Read my article from the following link and share your valuable comments:

Tabbed View Report using SSRS

Scenario Explained in the article:

Suppose take an example employee database which stores their professional summary, salary information, personal information, etc. The end user will search for an Employee by Emp ID as the parameter only one time and the result will be displayed in a tabbed view like professional summary in one tab, salary in another tab and personal information in another tab. Each tab should be clickable and when a Tab1 is clicked, other Tab result should be hidden. In the similar way, show and hide data should work for all the other tabs.

Don't forgot to share this post if this helps you!

Update 22-Sep-2015
I am happy to share with you all that my first TechNet article is awarded for SQL BI and Power BI Technical Guru - August, 2015.
Gold Winner!!!!
Ref: http://blogs.technet.com/b/wikininjas/archive/2015/09/19/technet-guru-awards-august-2015.aspx

Read More

Power BI in SharePoint Online

I am very excited after Microsoft released Power BI GA (general availability) at the end of July. It comes with list of impressive new features which is explained in detail in this MSDN article.
I want my blog readers to know what is Power BI and how it can be used with SharePoint Online to leverage the BI capabilities of SharePoint. Let's learn the basics in this article.
Articles in SharePoint Business Intelligence (BI) Series:

Power BI in SharePoint Online
Creating reports using OData Feed in Power BI
Power View report in SharePoint
Display Power BI reports in SharePoint Online

Microsoft came with a new tool for BI for the following reasons:

1. Use the known tool  - Excel
2. Discover More  - Power Query
3. Analyze More – Power Pivot
4. Visualize  - Power View / Power Map
5. Combine with Office 365 – share with any one
6. Power BI sites – collaborate and share the data sets with your team
7. Dive into Q & A – ask a question and get answers visually
8. Stay Connected – Mobile Apps
9. Schedule data refresh and create security settings easily

So what is Power BI?

1. Cloud based service works with excel to provide users a complete self-service BI solution
2. Available in SharePoint & Excel 2013 Online (available only in E3 / E4 subscription)
3. With Power BI, you can easily deploy a cloud based BI environment for users to share insights, collaborate and access reports from anywhere on any device
4. With Power BI for Office 365, you can easily setup an online gallery for users to share insights
5. Mobile apps available in Apple store, Windows store & Google play store

How does Power BI differ from SharePoint BI?
 

Feature	Power BI	SharePoint BI
Content Viewing	It displays all document libraries with a preview to reports / dashboards	It displays only pivot gallery document library
Natural Language	Q & A provides the end user to search for data with your questions and it displays the results in visualization	No such option available
Workbook as a data source	Power BI the data model and the reports (created with Excel pivot tables + charts, or Power View for Excel) everything must be contained within a single .xlsx file	In SharePoint, we can upload a Power Pivot model to the Power Pivot Gallery and other reports can connect to it using a URL-based connection string. This works for Power View for SharePoint, Reporting Services, PerformancePoint, and Excel
Data Refresh Option	It is simpler for end users to manage the data refresh	Lot of options available for data refresh and it’s controlled by Admin
Data Management Gateway	It is a hybrid environment (partially cloud based and partially on premises)	No data management gateway available
Data rendering option	HTML 5 (hence cross platform support)	Silverlight for data preview
Maximum workbook size	Max is 250MB	2 GB
Security	Sign in with Organization ID (O365 ID)	AD security mechanism
Enterprise Search for data	Search query is the important feature and it can search public and data within organization	N/A
Mobile Application	Available in Microsoft, Apple and Google Play store	N/A

            
Where can I download the Power BI and related products?

1. Sign up for Power BI
2. Power BI for desktop
3. Power BI Analysis Services Connector
4. Power BI Business Gateway
5. Power BI Apps for mobile (apps for Apple, Microsoft and Android mobile)
6. Power BI pricing

Hope this article, gives an idea of Power BI and how it differs from SharePoint BI. I will explain about connecting data from different data sources, creating dashboards, reports, etc. with Power BI in the coming articles.
Hope this helps you! Please share you comments since it helps me write more. Also, share this post to your friends via the networks below.  

Read More

Power Query and Power Map in Excel 2013

As you all know, Microsoft is improving the BI capabilities with a lot of new features with exciting releases in the year 2015. Microsoft has introduced new add-ins called Power Query and Power Map for Excel 2013.
Believe me guys, this is really awesome add-ins which taken the Microsoft's BI capabilities one step ahead of all other BI competitors!
Let's try to create a power map by searching a public data using power query in Excel 2013 in this article.
Before trying a sample, let's understand what is power query and power map for excel 2013 and it's download links:
Power Query:

• Microsoft Power Query for Excel (a data analysis tool) is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, combine, refine, access and collaborate.
• Power Query supports importing data from a wide range of Web sources, including scrapping tables from HTML pages, as well as importing data from Web APIs. Power Query supports XML, JSON and OData formats
• It includes intuitive discovery features, a powerful query language, and a built-in ability to share your discoveries with others

Power Map:

• Power Map provides interactive, 3D visualization with geospatial and temporal data
• It enables information workers to discover and share new insights from data through
• rich, 3D visualization on a globe with fluidic appearance & guided cinematic tours
• seeing time-stamped data change over time (which you might not have seen in traditional 2D tables and charts) and to share it with others
• you can create a video tour easily in a single click
• Geo flow is another feature which shows how the
• data changed over a period of time

Excel Add-in Links:

1. Power Query for Excel 2013
2. Power Map for Excel 2013

Creating a Power Map with GeoFlow:
Let's add a public data from internet by using Power Query - connecting data from the existing excel file.
I have downloaded the U.S. Operable Generating Units in the United States by State and Energy Source, 2011 from the link. Thank you for the data!
Open the excel 2013 -> Power Query tab -> Get External Data -> From Excel

This will open the query editor where you can select the data sets (this downloaded excel sheet has various tabs with different power sources). You can combine the data using Power Query data editor as well.
Let's select only "Coal" worksheet, delete the unwanted columns and create a date column using initial month and year (which is required for showing the growth of coal energy plants across U.S by county in power map) as shown below:

Now, click Insert Map -> Power Map option in the excel file -> Power Map will load the data source and it should automatically detect the geo-location fields from the dataset.

I have created two types of visualizations, one which is heat map visualization which will show the coal power generated in U.S. by state as shown below:

 The other one is coal power generated in U.S. by county with a time by using Initial Month of Operation column as shown below:

    

Power Map gives the options of creating video tour in a single click from the menu. Here is the video, I created from this sample:

Cool, isn't it?
Thanks for reading! Don't forgot to share this post and post your valuable comments which will make me write more. 

Read More

Create Business Intelligence Center in Office 365

Recently, Microsoft has updated Power BI features in SharePoint online. It has enhanced the Business Intelligence features a lot and I am sure self-service BI is the ultimate goal for Microsoft:) In this article, we will learn how to create Business Intelligence Center in Office 365.
To get  Microsoft Office 365 trail with Business Intelligence Center (this feature is not available in all the versions of the O 365), you need to sign up using the E3 license. This includes the advanced features in it.
Office 365 Enterprise E3 
Once you sign up, navigate to Office 365 admin center -> Admin -> SharePoint -> It will open the SharePoint administration

Click Site Collections -> New -> Private Site Collection -> Give the Title and Web Site Address -> Template Selection -> Enterprise tab -> Business Intelligence Center ->  Enter the site collection Admin and click OK to create it.

You will see the new BI site collection as shown below:

Soon, will post about creating dashboards and reports in SharePoint 2013. Happy Learning!

Please share this post if this helps you!

Read More

New Features of SharePoint 2013

It has been a while here, there are many reasons and I was busy as well. Let’s start it again with SharePoint 2013J
SharePoint 2013 has many new features and it is trending in the market. Let’s see the list of features which is new and what is different from the other versions of SharePoint.

1. HTML 5 and CSS 3 support  - Cross browser compatibility and optimized browser experience.
2. Device Manager & Device Channels – new way of designing  theme for  the site using SharePoint Designer 2013.
3. Community Portal – Social Networking  makes collaboration within the organization easier.
4. New Search Experience (My favorite feature J) - Continuous Crawl – yes, you get the results once you publish the new contents to the site
   Fast Search is integrated in the search feature and it is not a separate product now, licensing becomes easier.
5. Content Search Web Part – display information from the other web apps which is not possible in other versions of SharePoint – and off course content has to be crawled and indexed for displaying in this web part
6. Rich Workflows – New actions, no code web service call
7. Shredded Storage  - removes the duplicate files  and hence reduces the amount of storage in the site
8. WCM – Managed metadata for navigation and shortening of URL’s
9. Apps & Apps Store – Create and publish apps
10. Mobile Experience –  Mobile usage has increased drastically and this feature allows you to view the SharePoint site in three view such as Classic, Contemporary, Full Screen UI.

Read More

Micro blogging in SharePoint 2013

Introduction:

Microsoft has introduced a new social feature called “Micro blogging” in SharePoint 2013 to enhance the social networking and collaboration features.

This powerful feature is added as an OOTB web part to SharePoint 2013 and ready to useJ

So, what is micro blogging?

It is a type of blog which allows users to share their updates. This updates are called micro posts and the use of this services is called micro blogging.

Ref: http://www.webopedia.com/TERM/M/microblog.html

Micro blogging in SharePoint 2013

The SharePoint Micro Blogging feature is like Facebook and Twitter which allows the users to communicate with others easily, by posting some questions, responding, comments and etc.

Following are the list of features:

  Ø  Update picture and links in the posts and replies

  Ø  Participate in a conversation by posting comments (with rich media and

        Links in the post) and replied

  Ø  Use mentions (@) to tag a user in  a post and in the replies

  Ø  Use tags (#) to define keywords so that the users can follow and search for   the same topic easily

  Ø  Like a post, follow people, documents, sites and tags

   Also, the author’s (one who posts an update or comment or reply for a post)

   presence (online, off-line, busy, in a meeting) are also displayed.

Add the Site Feed web part to the page:

Edit the page -> Insert -> Web part -> Social Collaboration -> Site Feed

 Once added, you will see a dialogue to post your feeds as shown below:

Add a new Post in the Micro blog:

You can add a post by typing your post in the text box and hit post to update your post

Add Rich Media & Links, Delete a Post:

In the Post you can add Rich media such as Image, Video, Links, etc.. Also, you have options of deleting  the post.

Like, Reply, Copy Link to conversation, Follow up, Lock Conversation:

Like other social networking as I mentioned earlier in this post you have options of Like, Reply, Copy Link to Conversation, Follow up and Lock a Conversation :)

Also, there is an option "Show More Posts" to view the old posts.

Tagging a User and Topic

The options of Tagging a user by "@" allows the other user to specifically tag a post to a user and also Tagging a topic by "#" allows the posts to be categorized and also it helps in searching the posts easier.

 See where the Micro blog contents are stored?

This micro feed are based upon the User Profile Service in SharePoint. This feeds are stored in a list called Micro Feed by default where you will see all the details stored.

Navigate to the following path to view the Micro Feeds:

http://SiteName Here/Lists/PublishedFeed/AllItems.aspx 

Check for the Micro Feed List

Clicking on the List, you will see a List with different columns which stores the feeds.

Hope you enjoyed the post. Feel free to share your valuable comments which always make me to write more. Also share this post to your friends if it is helpful. 

Read More

Create Custom Page Layouts in SharePoint 2013

In this article we will learn how to create Custom Page Layouts for SharePoint 2013. Like SP 2010, we can create Custom Page Layouts in SharePoint 2013 using Visual Studio 2012 and Microsoft SharePoint Designer 2013 either from the scratch or by using the existing Layouts.

New in SharePoint 2013:

In SharePoint Designer we have a new feature called “Design Manager” (under Look and Feel) where we can create a Custom Layout. Once created, it will be available in the Page Layouts Gallery.

Steps:

1.   Navigate to Site Settings -> Look and Feel -> Design Manager

   2.   Under Design Manager Click on 6. Edit Page Layouts -> you have an option to “Create a page Layout” and then create it.

  3.   When the Layout is created, it is available in Edit Page Layouts (under Look and Feel -> Design Manager) and in Master pages and page layouts (under Web Designer galleries)

  4. In the Master pages and page layouts section, you will see two files created for the layout created. “.aspx and .html files”

  5.  For editing the layout page, either you can download the HTML page from the Layouts Gallery or edit the HTML files in the SharePoint Designer

    Note: No Changes should be made to .aspx file in the layouts gallery.

  6.  Once the changes are made in the HTML file, upload the updated HTML file to the gallery so that the .aspx file updated automatically.

  7.   Check in and Publish the created layouts 

  8. Now, create pages (Site Settings -> Pages -> Create Page using New Document -> Page and select the Custom Page Layout which is created) 
Hope this helps you!

Read More

Creating Master Page with HTML Templates in SharePoint 2013

SharePoint 2013 has many new and improved design features for designing and branding it. In SP 2013, a new concept called Design Manager has been introduced where all the site branding is managed. The following article explains the step by step process of converting HTML Master Pages for using Design Manager and using the converted Master Page for the site collections.

Steps:

     1.   Create a Site Collection using Central Administration

     2.   Go to the Site Settings -> Enable SharePoint Publishing Feature in Site Collection Feature (Site Collection Administration -> Site Features) and Site Features level (Site Actions -> Site Features) as shown below:

    3.   Go to Site Settings -> Web Designer Galleries -> Master Pages and Page Layouts

    4.   I have created a folder called Custom Master Page inside the Master Pages and Page Layouts Folder and uploaded the HTML, CSS, JS, Images in the folder

    5.   Now navigate to Site Settings -> Look and Feel -> Design Manager -> Edit Master Pages -> Click Convert an existing HTML to Master Page 

  6.   Once converted you will see the converted Master Page in the List with the status Conversion Successful and make sure it is approved and published.

    7.   Now navigate to Site Settings -> Look and Feel -> Page Layouts and Site Templates select the Custom master Page created and use it for the site collection.
For branding in SP 2010 we need good web designing skills (HTML, CSS and JS). But in SP 2013 it has made very simple and easy! 

Read More

Snipping Tool in Windows Server 2012

Snipping tool is very nice tool for capturing the screens which comes with latest versions of Windows. In this article lets see how to enable the snipping tool in Windows Server 2012.

Snipping tool is not installed in the default installation of Windows OS. We have to enable it manually.

Steps:
1. Go to Server Manager -> Manage Features -> Features -> Select DESKTOP EXPERIENCE under user interfaces and infrastructure -> Next -> Feature will be installed and it will ask for restarting the server.
2. Restart the server and log in to the server. Under Windows accessories you will see the Snipping tool installed.
Screenshots for reference:

Hope this helps you!

Read More

Export SharePoint Group to Excel using PowerShell

$siteUrl="SiteURLHere"
$groupName="GroupNametoExport"
$Output = @("GroupName|Name|Login|Email|Department|Title")
$web = Get-SPWeb $siteUrl
$site = $web.Site
$rootWeb = $site.RootWeb
$UserList = $rootWeb.Lists["User Information List"]
$web.SiteGroups[$groupName].Users|%{$user = $UserList.GetItemById($_.ID)
if($user -ne $null)
{
$JobTitle = $user["JobTitle"]
$Department = $user["Department"]
}
$Output += ($groupName+"|"+$_.Name+"|"+$_.UserLogin+"|"+$_.Email+"|"+$ Department +"|"+$JobTitle)
}
$rootWeb.Dispose()
$web.Dispose()
$site.Dispose()
$Output > "D:\MembersExport.csv"

Read More

Import Excel to SharePoint List using PowerShell

# Import the .csv file, and specify manually the headers, without column name in the file 
$contents = Import-CSV ‘C:\Input.csv' -header("Employee ID", "Employee Name")
# Web URL
$webURL = “SITEURL here”
$web = Get-SPWeb -Identity $webURL
$listName = "ListNameHere"
$list= $web.Lists["$listName"] 
# Iterate for each list column
foreach ($row in $contents )
{
    $item = $list.Items.Add();
    $item["Employee ID"] = $row.GroupName
    $item["Employee Name"] = $row.Permissions
    $item.Update()
}
Write-Host -ForegroundColor green "List Updated Successfully"
$web.Dispose()

Read More

Import Users from Excel to SharePoint using PowerShell

# Import the .csv file, and specify manually the headers, without column name in the file 
$userList=IMPORT-CSV C:\UserToUpload.csv -header("GroupName","UserName")

#Get the site name to the variable
$web = Get-SPWeb SiteURLHere

foreach ($user in $userList)
{
$groupName = $web.SiteGroups[$userList.group]
$user = $web.Site.RootWeb.EnsureUser($userList.user)
$groupName.AddUser($user)
}
Write-Host -ForegroundColor green "Users Added Successfully"
}
$Web.Dispose()

Read More

Add Users to SharePoint Group using PowerShell

#Get the site name to the variable
$web = Get-SPWeb SitURLHere
#Get the group name to the variable
$groupName = $web.SiteGroups["Group Name Here"]
#Assign user to variable
$user = $web.Site.RootWeb.EnsureUser(“User ID Here”)
#Add user to group 
$groupName.AddUser($user)
Write-Host -ForegroundColor green "User Added Successfully";

Read More

Creating Web Service using ASP .NET

To know about basics of web service read this article.

1.   Create an ASP .NET web service application using Microsoft Visual Studio.

2. Take a scenario that we have to convert Celsius to Fahrenheit and vice versa.
3.   Below is the code for our scenario:

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;

[WebService(Namespace = "http://ConversionWebService.org/Temp")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class Service : System.Web.Services.WebService
{
    public Service () {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }
    [WebMethod(Description = "Convert Celsius to Fahreinheit")]
    public double ToFahreinheit(double C)
    {
        return C * (9.0/5.0) + 32;        
    }

    [WebMethod(Description = "Convert Fahreinheit to Celsius")]
    public double ToCelsius(double F)
    {
        return (F - 32) * (5.0/9.0);        
    }
   }

4. Build and run the web service, you will see the following output:

5.Now, we will test the web service by giving input values and invoke it.Below are the screen shots which will show the input given and the results:

                             
Hope this article, explains you about creating and running a simple web service using ASP .NET

Here is the link to download the full source code.

To know about consuming a web service using ASP .NET read this article.

Read More