Thursday, 27 August 2015

SharePoint : Creating Dashboards and Charts for SharePoint Lists using PerformancePoint and Excel Services from Start to Finish

I really thought making SharePoint Lists into pretty graphs and charts in SharePoint would be far more straightforward than it’s turned out to be. Recently, I was given a project where I had to do just that and while I read through about 30 SharePoint BI books, none really gave a straight answer on which of the various SharePoint reporting technologies I should use. It seems I had a choice between:
  • Reporting Services
  • SharePoint’s Reporting Services add-on
  •  A near-useless SharePoint Chart Web Part
  • Excel worksheets
  • Excel Web Services
  • PerformancePoint Dashboards w/ a SharePoint list data source, SQL Server Analysis Server data source, Excel Workbooks data source, and/or Excel Services as a data source
So overwhelming.
Initially, I decided PerformancePoint Dashboards and PowerPivot would best suit my needs. PowerPivot is an Excel Add-on + a SharePoint Add-on that basically surfaces Analysis Services for reporting and as awesome as it is, the farm I’m using doesn’t currently support it (setting up PowerPivot is a bear, I’ll admit.) The good news is that it will be, and when it is, it’ll be SQL Server 2012’s version that comes with Power View and PowerPivot. Whattt!
Ultimately, I decided that I’m going to accomplish my task using Excel Services andPerformancePoint Dashboard Designer. This solution addresses automatic refreshing so your reports will stay (mostly) up to date. For complete beginners, this is basically how it works: You use export a SharePoint list query as a data source in an Excel spreadsheet, use Insert->PivotChart to work with the data, publish those specific charts and tables back to SharePoint using Excel Services, access those published items using PerformancePoint Dashboard Designer (which you can’t download from the Internet — you download the ClickOnce file from your SharePoint page), then publish a dashboard and tada!

(End User) Pre-Configuration

  • Obtain access to a SharePoint 2010 server running Excel Services and PerformancePoint Services
  • Update: Automatic data refreshing doesn’t work with Excel Services when using SharePoint lists as a data source. For that, you’ll need a client side script or PowerPivot.
  • Enable PerformancePoint Services Site Features and SharePoint Server Enterprise Site features on your site (Site Actions -> Site Settings -> Manage Site Features) if they aren’t already.
  • Add some items to the Tasks list that comes built-in to Team Sites. In the example below, I have 108 items, but you won’t need that much; I’m just working with live data.
  • Create a new Business Intelligence Site. Site Actions -> New Site -> Business Intelligence Center

Working in Excel

  • In the SharePoint Task List you just populated, click List in the ribbon bar, and Export to Excel. You’ll be prompted to download a lil .iqy file to your desktop.
  • Open a new Excel Worksheet
  • Insert -> PivotTable
    • When prompted, click Use an External Data Source
    • Choose Connection -> Browse for more… -> Select the .idq file you just downloaded
    • This is important: Even though this connection is to a SharePoint list and you’d think the updates would be instantaneous, you have to explicitly set the connection to refresh in order to keep your chart up-to-date. Let’s do this by clicking “Properties” -> Refresh Control -> Enable background refresh, Refresh Every 60 minutes, Refresh data when opening file. Don’t click any of those. If you need data refreshes, use PowerPivot or this client-side script.
    • Click all those boxes, then OK. If you ever need to get to this screen again, you can find it at Data -> Existing Connections ->  Right Click -> Edit Connection Properties.
  • A new toolbox will appear on the right. Drag “Status” to “Row Labels” and the “E” looking thing. This will setup the data to make a chart that counts the number of Completed tasks, New Tasks, Tasks Assigned to Someone Else.. and so on.
  • Yay, now click PivotChart (under PivotTools -> Options)
  • Choose a chart. I’m just gonna go with the default by clicking OK. Now, your spreadsheet should look something like this, with a PivotTable and PivotChart:
  • See how it says “Chart 1” in the upper left hand side? That’s the default Chart name. Let’ change that by clicking on the PivotChart then clicking PivotChart Tools -> Layout -> PivotChart Name:
  • I’m going to name mine Task Status Chart
  • K, now click in the PivotTable area, and under “PivotTable” at the top, click Options. On the left, you’ll see where you can change the PivotTable Name. Do that if you want.

Publish Your New Chart (Don’t just Save)

  • Save the Excel file to SharePoint
    • File -> Save and Send -> Save to SharePoint -> Set Publish Options
    • Publish Options -> <b>Select the individual items</b> by clicking on “Entire Workbook” then selecting All Charts -> OK
    • Go ahead and check the PivotTable (notice we didn’t change that PivotTable’s name so it’s still the default PivotTable1) options, too.
    •  Save your file to SharePoint by browsing for a location or saving it to a library that’s already been saved.

Show Published Excel Chart in a PerformancePoint Dashboard

  • Browse to the Business Intelligence site you created in the Pre-reqs.
  • Hover over Create Dashboards then click Start using PerformancePoint Services.
  • You’ll then see a page that says “Run Dashboard Designer“. Click that.
  • Your computer will install about a 16mb ClickOnce application and this is where you’ll always come to launch it.
  • This interface is a little confusing. All you have to know right now is that you’ll be using two item things — Other Report and Dashboard.
  • Let’s first get the Report item: Click on PerformancePoint Content on the left hand side to make the “Create” menu available. Then Create -> Other Reports -> Excel Services
  • Use the selection tools to navigate down to your spreadsheet, and ultimately, your published items. Below, I’ll select Task Status Bar Chart.
  • Name your Report in the Properties Tab. You can now click Save or move onto the next step.
  • Now we’re going to create a dashboard. Click the Dashboard Icon in the upper left hand corner.
  • Select your layout. In the example below, I went with two panes, just because.
  • When the dashboard appears, you’ll see “Details” on the right hand side.
  • Expand Reports -> PerformancePoint Content -> Drag the report to any pane.
  • While we could just click the Office ball in the upper left hand corner and “Deploy“, we need to adjust the size of the chart real quick
  • Click the down arrow on the report within the pane, and select Edit Item -> Size -> Set to Auto-size Width & Auto-size Height.
  • Now it’s time to Deploy your Dashboard! Click the Office ball thing -> Deploy
  • Drill down to select your Dashboards library within your BI site.
  • The new dashboard will load automatically.
  • Congrats, you’ve done it! Time to bask in the glory of your work.

Wednesday, 26 August 2015

Sharepoint Master Page Customization based on sharepoint groups

If you want to customize your master page, you may want to do the changes here.

Go to sharepoint site setting page -> Web Designer Galleries -> Master pages and Page Layouts
-> Download seattle.master file and make the changes. Now open the master file using visual studio, 
Here I have removed the "Site Actions" icon based on sharepoint groups.
Include the script file in header section.
 <script type="text/javascript" src="........./Style%20Library/jquery.min.js"></script>
        <script type="text/javascript">
            var clientContext;
            $(document).ready(function () {
                // Make sure the SharePoint script file 'sp.js' is loaded before your
                // code runs.
                SP.SOD.executeFunc('sp.js', 'SP.ClientContext', HideSiteActions);

            function CheckSiteAdmin() {

                var context = new SP.ClientContext.get_current();
       = context.get_web();
                this.currentUser = website.get_currentUser();
                context.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
            function onQuerySucceeded(sender, args) {
                //Site Collection administrator               
                var value = currentUser.get_isSiteAdmin();
                if (value) {
                    $(".ms-siteactions-root > span >").show();
            function onQueryFailed(sender, args) {
                alert('request failed ' + args.get_message() + '\n' + args.get_stackTrace());

            //Hide Site Action Icon
            function HideSiteActions() {
                //By default hide the site actions icon
                $(".ms-siteactions-root > span >").hide();
                //Check current user is site admin
                //Check Current user is a member of the group Admin & Employee
                IsCurrentUserMemberOfGroup("Admin", function (isCurrentUserInGroup) {
                    if (isCurrentUserInGroup)                      
                        $(".ms-siteactions-root > span >").show();
                IsCurrentUserMemberOfGroup("Employee", function (isCurrentUserInGroup) {
                    if (isCurrentUserInGroup)                       
                        $(".ms-siteactions-root > span >").show();
            //Current User is a member of the group
            function IsCurrentUserMemberOfGroup(groupName, OnComplete) {
                clientContext = SP.ClientContext.get_current();
                var currentWeb = clientContext.get_web();
                var currentUser = clientContext.get_web().get_currentUser();
                var allGroups = currentWeb.get_siteGroups();
                var group = allGroups.getByName(groupName);
                var groupUsers = group.get_users();
                clientContext.executeQueryAsync(OnSuccess, OnFailure);

                function OnSuccess(sender, args) {

                    var userInGroup = false;
                    var groupUserEnumerator = groupUsers.getEnumerator();
                    while (groupUserEnumerator.moveNext()) {
                        var groupUser = groupUserEnumerator.get_current();
                        if (groupUser.get_id() == currentUser.get_id()) {
                            userInGroup = true;
                function OnFailure(sender, args) {

Save the master file and rename the file . 
After that upload the renamed document file in the master page gallery.Finally publish the major version of the file.

Now the latest file has been uploaded in the gallery. If you want use the new master page for your sharepoint site collection -> Go to Site Settings -> Look and Feel - > Select Master page 
Here you can select your master file which has been uploaded by you , will be listed in the dropdown. 
Select your master file for both site master page & system master page section.

Finally your changes will be applied for the sharepoint site collection. If the user not in the group or not an site collection administrator, "Site Actions" icon will not be listed in your sharepoint site.

How to Display the Master Page link in Site Settings for SharePoint 2013

If you create a SubSite in SharePoint 2013, you may want it to take on the same look and feel from the parent site. This is easily done with enough permissions. However, by default, the link to the Master Pages was not visible on our site.
If you know the link you can navigate to it directly, but if you don’t breathe SharePoint, it might be a little easier to have the link available.
Follow these steps to get the Master Page link to display:
  1. Navigate to Settings -> Site Settings of the Parent Site.
  2. Under Site Collection Administration click on the Site collection features link.
  3. Enable the SharePoint Server Publishing Infrastructure site collection by clicking the Activate button.
    SharePoint 2013 SharePoint Server Enterprise Site Collection features
  4. Navigate to  Settings -> Site Settings of your SubSite.
  5. Under Site Settings click on the Manage site features link.
  6. Enable the SharePoint Server Publishing Site Features click the Activate button.
    SharePoint 2013 SharePoint Server Publishing
  7. Navigate back to the Site Settings of your SubSite, under Look and Feel section notice a new link called Master page. Click this link.
    SharePoint 2013 Site Settings Master Page
  8. You can now update the settings of your SubSite to inherit the master page from the Parent Site.
    SharePoint 2013 Inherit Master from Parent

Tuesday, 25 August 2015

Error CAML Query containing special characters

If you are building a CAML Query to match a value which contains a special character like & (say SharePoint&InfoPath), you will receive an error and query will fail. 

Resolution - Handle this the way you handle special characters in XML. 
Include your value within < ![CDATA[ value ]] >

Incorrect (throws error) - 
<Eq><FieldRef Name="Title" /><Value Type="Text">SharePoint&InfoPath</Value></Eq> 
Correct - 
<Eq><FieldRef Name="Title" /><Value Type="Text"><![CDATA[SharePoint&InfoPath]]></Value></Eq> 

In real scenarios, build your CAML Query by appending the value in the query, like – 
<Eq><FieldRef Name="Title" /><Value Type="Text"><![CDATA[‘ + value + ‘]]> </Value></Eq>’ 
[where value = text to be matched]

Friday, 24 July 2015

AlaSQL & How to find index of an item in JavaScript Object Array?


We focus on speed by taking advantage of the dynamic nature of javascript when building up queries. Real world solutions demands flexibility regarding where data comes from and where it is to be stored. We focus on flexibility by making sure you can import/export and query directly on data stored in your own JSON object, Excel files, localStorage, IndexedDB, and SQLite.

How to use

For the browser: Include alasql.min.js and call alasql() with your SQL statements:
<script src="//"></script> 


    alasql("CREATE TABLE cities (city string, population number)");

    alasql("INSERT INTO cities VALUES ('Rome',2863223), ('Paris',2249975), ('Berlin',3517424),  ('Madrid',3041579)");

    var res = alasql("SELECT * FROM cities WHERE population < 3500000 ORDER BY population DESC");

   // res now contains this array of object:
   // [{"city":"Madrid","population":3041579},{"city":"Rome","population":2863223},{"city":"Paris","population":2249975}]   

var data = [ 
    { Phase: "Phase 1", Step: "Step 1", Task: "Task 1", Value: "5" },
    { Phase: "Phase 1", Step: "Step 1", Task: "Task 2", Value: "10" },
    { Phase: "Phase 1", Step: "Step 2", Task: "Task 1", Value: "15" },
    { Phase: "Phase 1", Step: "Step 2", Task: "Task 2", Value: "20" },
    { Phase: "Phase 2", Step: "Step 1", Task: "Task 1", Value: "25" },
    { Phase: "Phase 2", Step: "Step 1", Task: "Task 2", Value: "30" },
    { Phase: "Phase 2", Step: "Step 2", Task: "Task 1", Value: "35" },
    { Phase: "Phase 2", Step: "Step 2", Task: "Task 2", Value: "40" }
    var res = alasql('SELECT Phase,  SUM(CAST([Value] AS INT)) AS [Value] FROM ? GROUP BY Phase',[data]);
document.getElementById("res").textContent = JSON.stringify(res);
<span id="res"></span>
[{"Phase":"Phase 1","Value":50},{"Phase":"Phase 2","Value":130}]
Play with this example in jsFiddle

How to find index of an item in JavaScript Object Array?

Recently while working I came across a scenario. I had to find index of a particular item on given condition from a JavaScript object array. In this post we will see how to find index of object from JavaScript array of object.
Let us assume we have a JavaScript array as following,
var studentsArray =
"rollnumber": 1,
"name": "dj",
"subject": "physics"
"rollnumber": 2,
"name": "tanmay",
"subject": "biology"
"rollnumber": 3,
"name": "amit",
"subject": "chemistry"
Now if we have a requirement to select a particular object in the array. Let us assume that we want to find index of student with name Tanmay.
We can do that by iterating through the array and comparing value at the given key.
function functiontofindIndexByKeyValue(arraytosearch, key, valuetosearch) {
for (var i = 0; i < arraytosearch.length; i++) {
if (arraytosearch[i][key] == valuetosearch) {
return i;
return null;
You can use the function to find index of a particular element as below,
var index = functiontofindIndexByKeyValue(studentsArray, "name", "tanmay");
In this way you can find index of an element in JavaScript array of object. I hope you find this quick post useful. Thanks for reading.