SharePoint 2013 : How to enable PowerPivot Excel Plugin for Excel 2013

While working with SharePoint 2013 BI Stuff you must have come across an essential component called “PowerPivot Reports”.

On the same lines it is worth to know about an Excel Plugin called “PowerPivot Excel Plugin” that further complements the designing of PowerPivot Style Reports using diversified range of Data Sources.

In this article we will see how can we enable “PowerPivot Excel Plugin” for Excel 2013 and in subsequent articles we will also see how to leverage this Plugin while designing reports based on different data sources like SSAS Cube, SQL Server and so on.

  • Launch Excel 2013
  • Select Blank Workbook

1

  • Select Options

2

  • Select Add-Ins
  • Select COM Add-Ins
  • Click in on the Go… Button to look up all the available plugins

3

  •  Out of the available plugins look for “Microsoft Office PowerPivot for Excel 2013” and Select it
  • Click OK

4

Once we have done with all the steps above, we can see a new Tab added to the Excel Client by the name “POWERPIVOT”

5

When you click on this Tab, you can see extended Functions offered by this Plugin.

6

We will revisit this Plugin again in one of my upcoming articles and see it in action.

Hope you find it helpful.

Advertisements

Executing Direct SQL Queries on SharePoint Content Databases: Is it a good idea???

This article is based on the findings and lessons learnt during one of my recent assignments which included the development of an Analysis Tool which can gather all Vitals out of a SharePoint Farm which can be further leveraged to take decisions during the migration at some later stage.

While deciding the direct execution of SQL Queries on SharePoint Databases, you should consider the following questions and plan accordingly-

What could be the possible repercussions if we execute direct SQL queries on Content Database?

  • Reading from the SharePoint databases programmatically, or manually, can cause unexpected locking within Microsoft SQL Server which can adversely affect performance.
  • Any read operations against the SharePoint databases that originate from queries, scripts, .dll files (and so on) that are not provided by the Microsoft SharePoint Development Team or by Microsoft SharePoint Support will be considered unsupported if they are identified as a barrier to the resolution of a Microsoft support engagement.
  • If unsupported read operations are identified as a barrier to the resolution of support engagement, the database will be considered to be in an unsupported state.
  • To return the database to a supported state, all unsupported read activities must stop.

What are unsupported operations on SharePoint Content Databases?

It is clearly unsupported to update, delete, or insert records. The risks are surely far more obvious. Also be aware that any database changes would definitely break the supportability as stated by Microsoft. Examples of such database changes include, but are not limited to the following:

  • Adding database triggers
  • Adding new indexes or changing existing indexes within tables
  • Adding, changing, or deleting any primary or foreign key relationships
  • Changing or deleting existing stored procedures
  • Calling existing stored procedures directly, except as described in the SharePoint Protocols documentation
  • Adding new stored procedures
  • Adding, changing, or deleting any data in any table of any of the databases for the products
  • Adding, changing, or deleting any columns in any table of any of the databases for the products
  • Making any modification to the database schema
  • Adding tables to any of the databases for the products
  • Changing the database collation
  • Running DBCC_CHECKDB WITH REPAIR_ALLOW_DATA_LOSS (However, running DBCC_CHECKDB WITH REPAIR_FAST and REPAIR_REBUILD is supported, as these commands only update the indexes of the associated database.)
  • Enabling SQL Server change data capture (CDC)
  • Enabling SQL Server transactional replication
  • Enabling SQL Server merge replication

What are supported operations on SharePoint Databases?

  • Operations that are initiated from the SharePoint administrative user interface
  • SharePoint specific tools and utilities that are provided directly by Microsoft (for example, Ststadm.exe)
  • Changes that are made programmatically through the SharePoint Object Model and that are in compliance with the SharePoint SDK documentation

What happen if unsupported data modification is discovered?

If an unsupported database modification is discovered during a support call, the customer must perform one of the following procedures at a minimum:

  • Perform a database restoration from the last known good backup that did not include the database modifications
  • Roll back all the database modifications

What if previous version of the database that does not include the unsupported modifications is unavailable or if the customer cannot roll back the database modifications?

  • The customer must recover the data manually.
  • The database must be restored to an unmodified state before Microsoft SharePoint Support can provide any data migration assistance.
  • If it is determined that a database change is necessary, a support case should be opened to determine whether a product defect exists and should be addressed.

What can be done if still the content database needs to be queried directly for some reason?

Never run the direct SQL queries on Content Database in Production Environment

Take Following steps:

  • Restore the Database backup from Production to Development Environment
  • Take Database Offline
  • Run SQL Queries with [NOLOCK] option

Before running the above steps make sure the database is not in intermediate stage [nothing is checked out] else can get different document count then actual.

Key Takeaways: Based on the facts exposed by Microsoft Documentation on Direct Query Execution on Content Databases the key takeaways are:

  • This is completely unsupported by the EULA you agreed to when you installed SharePoint.
  • Your queries are not guaranteed to work after applying any patches or service packs to SharePoint since Microsoft could change the database schema anytime.
  • Directly querying the database can place extra load on a server and hence performance issues.
  • Direct SELECT statements against the database take shared read locks at the default transaction level so your custom queries might cause deadlocks and hence stability issues.
  • Your custom queries might lead to incorrect data being retrieved.

SharePoint 2013 : How to overcome List Threshold Limits using OData Continuation

While working with SharePoint Large List we all must have encountered the issues with restricted access to the list due to List Threshold Limits.

In this article we see the solution to this problem that can be implemented using OData Continuation in conjunction with REST API.

In order to demonstrate this solution I am making use of SharePoint Hosted App as in shown in below steps-

  • Create SharePoint App Project by choosing “App for SharePoint 2013” Project Template

1

  • Specify Host Web URL
  • Select SharePoint -Hosted as hosting model

2

Wait while Visual Studio configure the Project for you

3

In order to showcase the data access from a Large SharePoint List, I have added a list with 1 Lakh items in it and default Threshold limits are still intact

4

Now add some HTML for the UI in the Start Page of the App (default.aspx)

Step 1: Adding container for the HTML Button

Step 2: Adding HTML Button that will execute the code in action

5

So the final UI would look like as below-

6.1

Now add some JavaScript code in App.js File

Step 3: Bind the event handler with the HTML Button in “document.ready()” function

Step 4: Adding a helper function “getQueryStringParameter()” that will help us to extract the Query String Parameters

6

In the Callback function “oDataContinuations” we have following steps executing-

Step 5: Getting value of “SPHostUrl” by using “getQueryStringParameter()” helper function

Step 6: Getting value of “SPAppWebUrl” by using “getQueryStringParameter()” helper function

Step 7: Loading “SP.RequestExecutor.js ” JavaScript file as we need to issue a Cross Domain Call from SharePoint App to Host Web

Step 8: Prepare the URL to execute the Cross Domain Call to the Large List present in the Host Web

Step 9: Instantiate the object of Request Executor based on the App URL

Step 10: Calling “executeAsync” method of Request Executor Object

This function needs a JSON object with configuration values like

url: Specify the Url to execute Cross Domain Call

method: Specify the Request access method (GET or POST)

datatype: Specify the expected return type of the response (JSON)

headers: Specify the Request Headers

Step 11: On successful execution of this request, response object would return back with a property “__next” which contains the direct URL to the next set of records which can be queried from the list, check for this property and make sure it exists before proceed further

7.1

Step 12: Get the Url to the next result set

Step 13: Call the “getItems()” method recursively with the new Url returned by response object “__next” property

7

With this we are all done with the code.

But before we move any further we need to allow the permission for the App on Host Web since we need to query the data from the list which is hanging inside the Host Web.

Step 1: Go to App Manifest File => Permission Tab

Step 2: Specify Read permission

8

Build the Solution and Deploy it

9

Trust the App when asked for

10

Provide the credentials when asked for

11

Click on “Manage Threshold Limits”

12

See to the Result Panel and sure enough you will find records adding continuously based on the Paginated REST Calls driven by Odata Continuation

13

This is a simple demonstration to showcase how we can effectively make use of Paginated REST Calls driven by Odata Continuation to overcome issues related to List Threshold Limits.

Hope you find it helpful.

SharePoint 2013 : How to Render Single Line of Text as Date Range Control using JSLink

While working with one of the assignment I was encountered a requirement where we need to have a Date Range Control in the SharePoint List Form for user entry.

I got the same requirement in the past when I was working with SharePoint 2007, at that time I achieved this requirement by developing Custom Field Type for Date Range and believe me  it was too tricky to handle but we did not have any other option available at hand but this time I made use of Client Side Rendering Framework, which is a new concept introduced in SharePoint 2013.

In this article we will discuss the implementation details of Client Side Rendering Framework to convert a Single Line of Text Column to Date Range Control

And the awesome part about it is we can bring this big change without changing the existing schema of the List itself.

In order to work out the demo, we can start with an existing list “Products”, which have list of Products available in a catalog (an arbitrary scenario) as shown below-

1

All we need to do is to add Product Validity information into the catalog so that Products can be tracked based on Product Validity Date Range.

So let’s add a new column of type “Single Line of Text” by the name “ProductValidity” as shown below-

2

Now we need to add a new JavaScript File under any suitable location in your site, for this demo I am adding the file to “SiteAssets” by the name “Date Range – JSLink.js” as shown below-

3

Now lets’ add the JSLink plumbing to the JavaScript File in the below order-

Step 1: Add required reference to JQuery Files & CSS Files, for this demo we need to add the references to “jquery-1.10.0.min.js”, “jquery-ui.min.js” and “jquery-ui.css”

Step 2: Create a JSON Object that defines the behavior of the List Column on which it is implemented.

                Templates: This is a sub object that can store the information regarding List Template Type

                OnPostRender: Defines the method to be called after the List Column gets rendered

                Fields: Define the Field on which this JSLink should get implemented

Step 3, Step 4 & Step 5: Defines that this JSLink should be implemented on “ProductValidity” Column only when it is rendered in “NewForm” and “EditForm”

Step 6: Finally register Template Override to make sure that JSLink should get precedence over the OOB behavior of List Column rendering.

4

Now let’s get inside the function “DRFieldTemplate” which will act as Template Override Handler

Step 7: Instantiate Form Context by calling “GetFormContextForCurrentField” method

Step 8: In this step we are reading the “ProductValidity” Column value and split it by “-”, since the values saved to the field as “From Date – To Date”

Step 9: In this step we are preparing the custom HTML to render the List Field in a new way, this is the turning point of this implementation and here you can apply any HTML you like to. This also equally means that we can give any desired look to our List Field in this step.

Step 10: Here we are defining the Get Value Call Back function that supplies SharePoint a custom value for the List Column to save to.

In this step we are getting values from two different input controls (From Date & To Date) and format these values by separating them using “-” character, which can be used as splitter in Step 8 above.

5

Step 11: In Post Render Callback we are hooking up the jQuery Date Picker control to the HTML Input controls.

It is important to note that Post Render Callback will get execute once the default rendering of the List Form has been completed.

6

Since we have developed this JSLink to work in ”New Form” and “Edit Form” Mode only, so we need to first apply this JSLink in these two forms.

Apply JSLink in New Form:

Browse the Products List

From the Ribbon choose New Item

7

Once the New Form loaded, verify if “ProductValidity” List Field is rendered as “Single Line of Text”

8

Now in order to apply the JSLink, Edit the Page

9

Edit WebPart

10

Scroll down to the “Miscellaneous” Section in the WebPart Editor Part

Locate the JSLink Placeholder

11

Specify the path to the JSLink File

It is important to note that here we need to make use of SharePoint Tokens instead of specify the full path.

To get details on different kind of SharePoint Tokens you can refer the another article from me https://howtodowithsharepoint.wordpress.com/2015/01/16/sharepoint-url-tokens/

12

Click OK to save the changes.

And if there are no issues with the JSLink sure enough you will get the “ProductValidity” List Field converted from “Single Line of Text” to a Set of multiple controls with Date Picker hooked to it.

13

14

Apply JSLink in Edit Form:

For the Edit Form we have to follow the exactly same steps to Apply JSLink as we did in New Form –

Edit any List Item

15

Validate “ProductValidity” List Field

16

Edit Page

17

Edit WebPart

18

Specify the JSLink File Path and Click OK

19

20

21

22

While adding or editing an Item, we can select “Valid From” & “Valid To” Date to specify the Product Validity.

These dates will be stored in the Format as shown below

23

Every time when user edits the item, these dates will get split up based on “-” and set back to the respective date fields.

24

This simple demo shows how easily can we tweak around and enhance the default behavior of List Form rendering by making use of JSLink mixing it up with latest HTML, JQuery & CSS constructs.

Hope you find it helpful.