Ready to Publish my Power View report on SharePoint 2013

My SharePoint 2013 environment is ready with SQL 2012 and I am going to play around publishing my Power View report.

Stay tuned for more tips and tricks.

Excel 2013 Power Pivot Sorting - Tip

While creating a Power Pivot based report i.e. Pivot table or Pivot chart, using Month slicer is a very common requirement. There are number of blogs that shows how to sort the Month in natural order in Pivot table, you can also found some workarounds to sort Month in natural order for slicer also. One such tip is explained well in MSDN blog here.

But with Excel 2013, there is not need to have such workaround.

Assuming that you have Date table with following columns

1. Year => 2010, 2011, 2012.......
2. Month Name => Jan, Feb March.....
3. MonthOfYear => 1, 2, 3..... 12

Now select the column Month Name, under sort and filter section of Home tab.

Now click on "Sort by Column.."

In this select the Column to be sorted and sorted by. Here, sort column is MonthName and By column is MonthOfYear. Click Ok.

Now you can add the Slicer of Month Name and it will sorted by natural order instead on alphabet. 

Using this functionality,any column can be sorted by any other column within the table.

Exploring Power View and Power Pivot with Excel 2013

The latest Buzz in Microsoft BI Space is Power View and Power Pivot. I had seen it initially when it was in CTP but now with Office 2013 is available, I have tried my hands with Power Pivot and creating my Power View reports using SQL data connection.

For the list of new features in Excel 2013 Power view see this link.

Though I am more interested in SQL 2012 with Power View for Multidimensional Model, it is still in CTP mode and can be downloaded from here.

I loved the Map Visualization so far and wants to explore the integration with SharePoint 2010 and SharePoint 2013.

Let's see how it goes.

SQL Server Reporting Service Deliver Extension with User Control

Introduction

Creation of a reporting service custom delivery extension is straight forward and there are many examples available over the Internet for reference (Print Delivery, FTP Delivery on CodePlex). But all of them has created custom server control if you want to take user inputs (like Rendering Format, Location etc) during subscription creation. Since creating a server control is both time consuming and tedious process, I will explain in this article, how to add user control (.ascx) instead. User control is relatively easy to create, debug, test and format.

Background

While I was trying my hand on creating custom delivery extension of the reporting service, all the examples I have seen was using custom server control. I personally do not like server control if the control is not re-usable and can not be attached with visual studio tool box. So I tried creating a simple user control and added to my extension. It worked like a charm and help me save lots of time. Only drawback is that we also have to deploy User control (.ascx )on the report server.

Details

The detailed article with source code is published at www.codeproject.com. Please click here

Open XML 2.0 and Word Processing

I am sure those who have tried his hands on Open XML SDK, must have used Word Content Control Toolkit available at codeplex.

The exe is prepared by Microsoft itself and pretty old but can be used to create word document quickly through code. It makes template creation for Mail Merge like solution very easy.

Use the tool to map your content controls with custom xml and use the below code to replace your content with xml data.

Below code has ContentXML variable that has required data.

using (MemoryStream ms = new MemoryStream())
            {
                ms.Write(_templateDocument, 0, _templateDocument.Length);
                using (WordprocessingDocument wordDocument = WordprocessingDocument.Open(ms, true))
                {

                    MainDocumentPart main = wordDocument.MainDocumentPart;
                    Document document = main.Document;
                    main.DeleteParts<CustomXmlPart>(main.CustomXmlParts);

                    //add and write new XML part
                    CustomXmlPart customXmlPart =   main.AddCustomXmlPart(CustomXmlPartType.CustomXml);
                    using (StreamWriter ts = new StreamWriter(customXmlPart.GetStream()))
                    {
                        ts.Write(ContentXML);
                    }

                    document.Save();
                }
              

References
 http://blogs.msdn.com/b/acoat/archive/2007/03/01/linking-word-2007-content-controls-to-custom-xml.aspx
http://msdn.microsoft.com/en-us/library/office/bb510135%28v=office.12%29.aspx

SharePoint 2010 Customize XSLT Changes

I wanted to perform as small task. When user clicks on my Purchase Order document library, instead of showing "Add document" link, I wanted to rename it to "Add Purchase Order" link.

There are multiple ways to achieve it

1. Write JavaScript to get the element by anchor tag id and modify the inner text
2. Customize the Xslt of the XSLTListViewWebPart by clicking on Customize XSLT -> Entire View in SharePoint designer. And change the AddNewText variable value for Template Type 115. 

Search for below text and change the value of "Add document" to "Whatever you like"
<xsl:with-param name="AddNewText">

But no matter whatever I modify, the changes are never reflected in browser. After trying 10-15 times and searching around. I found the solution.

Remove all the ddwrt:ghost="hide" attributes from the XSLT.

Wohoooooo... What a time waste.

WCF REST 4.0 Authorization & Form Authentication (SetAuthCookie)

Windows Communication Foundation provides tons of methods to authenticate users' check for authorization based on service type and it is quite confusing to implement simple form based authentication and role based authorization for WCF REST 4.0.
Note: This article assumes that WCF REST service is hosted with ASP.NET application and shares the same web.config. Make sure that Form Authentication is enabled in web.config file.

Something like this

WebGet(UriTemplate = "")]
[PrincipalPermission(SecurityAction.Demand, Role="Admin")]
public List<SampleItem> GetCollection(){}

But even though after user is authenticated using Membership provider and HTTPContext.Current.User.Identity and the context is available at service level, the principal permission attribute always throws a security exception.

The reason for that is because principal permission attribute checks for System.Threading.Thread.CurrentPrincipal.Identity and not for HTTPContext Identity.
To solve this problem, we have to create a Custom Principal and Authorization Policy for WCF Service. Then this Policy will be hooked with WCF REST Service using ServiceBehaviour.

For detailed code follow my post codeproject.com written sometime back.