PowerPivot: How to create Power View report in PowerPivot gallery on SharePoint using a SharePoint list as the data source.

1. Configuring data source:

 First option is to Export Data Feed from the SharePoint list. For this you need to activate the following feature on the site collection: "PowerPivot Feature Integration for Site Collections".

This will give you a .atomsvc file. And you can open this file in MS Excel and can verify the connection under Data and PowerPivot tab.

If the PowerPivot tab is not visible then you should go to File > Options > Add-ins > Manage: Com Add-ins > Check PowerPivot option.

The above connection option may throw the following error while refreshing in PowerPivot gallery on SharePoint: "The payload kind 'Binary Value' of the given data feed is not supported".

Recommended option to prepare source data connection:

Go to the PowerPivot tab in the Excel workbook. And modify the existing connection URL, currently refering to local .atomsvc file OR

Go to POWERPIVOT tab in Excel > Manage > Get External Data > From Other Source > Other Feeds

and set the following URL:

https://SharePoint.ServerName.com/sites/SiteCollectionName/SubSiteName/_vti_bin/ListData.svc/ListName

(List name would be the display name of the list without any spaces. And its case sensitive. If the list name starts with any number then just prefix it with _c)

SharePoint 2013's way:

https://SharePoint.ServerName.com/sites/SiteCollectionName/SubSiteName/_api/web/lists/getbytitle('List Name')/items

(No need to worry about spaces in list name in this format. You can also add query string parameters. e.g. _api/web/lists/getbytitle('List Name')/items?$top=1000&$orderby=Created desc)

Test the above connection and save the workbook.

In case your data source is an Excel file instead of a SP list: To get rows from Table1 in a workbook named Employees.xlsx, that is stored on a SharePoint library named Documents, you can use a URL as follows.

http://<serverName>/_vti_bin/ExcelRest.aspx/Documents/Employees.xlsx/OData/Table1

Specify the above URL in POWERPIVOT tab  > From Data Service > From OData Data Feed.

2. Preparing Power View Report:

Upload the above workbook in the PowerPivot gallery on SharePoint. Click the below icon to create the report.

Click on manage data refresh and enable the data refresh.

3. Embedding power view report:

Once the report is ready it can be embedded to a page viewer web-part or in an iframe.

There are some query string parameters those can be used to hide the toolbar and to adjust the covered area.

Example:

https://SharePointServer/sites/SiteCollection/SubSite/_layouts/15/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=ReportURL.rdlx&ViewMode=Presentation&ReportSection=ReportSection&PreviewBar=False&Fit=True

ViewMode: Presentation | Edit

ReportSection: The default view is named as “ReportSection” and any new view that you add is named as “ReportSection2” and so on.

Fit: Auto resize report.

PreviewBar: Show or hide toolbar.

How to restrict SharePoint users to create only one entry in a List?

Create a column of type 'Single line of text'.

Set it to 'Enforce unique values'.

Set its 'Default value' to a 'Calculated Value'.

And set the formula to =[Me]

Done!

SharePoint: How to add iframe in an application page?

Add <WebPartPages:AllowFraming runat="server" /> in the corresponding master page.

Then you can use iframe in the application page:

<iframe id="iframe1" src="https://navneetchanalia.blogspot.in/"></iframe>

To dynamically set the URL from code behind:

Add following on aspx page:

<asp:Literal ID="ltHTML" runat="server"></asp:Literal>

And on code behind:

protected void SetIframe(string url)

        {

            ltHTML.Text = string.Format("<iframe id=\"testFrame\" src=\"{0}\" style=\"width: 800px; height: 700px;\"></iframe>", url);

        }

How to query Data Table in C# ?

To get distinct rows:

using System.Linq;

var distinctRows = dt.DefaultView.ToTable(true, "Resource_x0020_ID").Rows.OfType<DataRow>().Select(k => k[0] + "").ToArray();


To filter using where:

var product = from row in tempTable.AsEnumerable()
where row.Field<int>("ProdID") == 100 &&
(row.Field<string>("Category") != "Closed" ||
row.Field<string>("Type") == "Electroncs")
select row;

DataRow[] results = table.Select("A = 'foo' AND B = 'bar' AND C = 'baz'");


To get count:

int count = dt.Select("Product_x0020_Type = 'Mobile'").Length;

int blrCount = dt.Select("Work_x0020_Location like '%Bangalore%'").Length;

How to prevent SharePoint users from discarding check out on behalf of other users?

This issue can be resolved by managing the SharePoint permissions properly.

Any user who has following level of permission on the document would be able to discard the checkout done by other users:

• Full Control 
• Design 
• Edit 

So, to stop users from discarding check out, you should add users in site-members group instead of owners group.

Appropriate permission level: Contribute.

If required then a custom permission level can also be created to get the desired result.