PowerPivot - Choosing a calculated column type

Lately I have been using PowerPivot (more details on this at the end of this post) and I came across a problem whose solution might interest others.

The Problem

As I created a calculated column based on a date column, the result was always displayed as a number instead of a date. Worst the drop down list where you can choose the type of the column was greyed out.

Here is an example; CalculatedColumn1 is created using some number function (ROUNDDOWN). Yet I am working with Dates, while PowerPivot understandably consider the results to be a number, one can’t override this automatic setting of the Data Type. The “Data Type” DropDown list is just greyed out with a value of Number (Nombre in French in the screenshot below) :

The obvious drawback is that my column isn’t very readable, I mean no normal human beings can understand that 40255 mean 18/03/2010 00:00, else leave me a comment, I would be interested to meet you :)

My solution

Here is my workaround, just throw a date function in your function. In my case I choose to add TIME(0,0,0) which is a neutral operation :

Thus PowerPivot will understand that your column is a date, and you will be able to select a Date format:

Some Information on PowerPivot (aka Gemini)

For those who don’t know what PowerPivot is, you might know by its codename, Gemini. It is a Microsoft Addin for Excel and SharePoint 2010 that allows you to analyze tons of data very easily. It’s supposedly really fast due to its in In Memory Analysis. For BI noobs (a group I am sadly part of) you might see it as a personal version of Anaysis Service (PowerPivot for Excel) or as a Team flavored BI (PowerPivot for SharePoint), on this subject I found this blog post by the PowerPivot Team very interesting (http://blogs.msdn.com/powerpivot/archive/2010/03/12/comparing-analysis-services-and-powerpivot.aspx).

Microsoft released a series of nice videos to explain PowerPivot, here is the first one :

Here is what PowerPivot promises and, as far as my experience went, delivers:

• Analysis (Pivot Table and graph) of Data from multiples sources (DataBase, Excel files, flat files)
• Analysis of huge volume Data (I am talking millions here)
• Integration of simple relational constraint between your data sources
• And maybe the coolest part is that this is available to any Information Worker already familiar with Excel. I would take this one with a grain of salt; your Information Worker needs to be quite technical savvy to fully benefit from this tool.

Additionally the Integration with SharePoint is supposed to bring added performance and to deliver your reports more easily to multiple persons. I haven’t tested the integration, yet!

After spending some quality time with PowerPivot I have got to admit being very enthusiastic about this product. I can see numerous scenarii where it would have saved me hours in the past (More details and cool links at the end of the post). I just can’t wait to set my hands on the RTM version of Excel 2010 and PowerPivot to enjoy this tool without worrying so much about the too frequent crashes (in my experience double clicking on a graph horizontal axis freezes quite often).

To go further I would advise you to start with the official PowerPivot site (http://www.powerpivot.com ), especially the demo part.

To my French reader I would also recommend the hilarious BI video by Têtes à Claques that you might have enjoyed at the 2010 Techdays in Paris :(http://www.microsoft.com/france/serveur/sql/secretedouard/ )

Replacing delegate controls with custom actions

When using jQuery in a SharePoint application, you obviously need to deploy the jQuery javascript file. The deploy part is no big deal, the referencing is a bit more complex. In this post is the detail of a new way to reference the resources, it involves using Custom Action to load external resources such as javascript or css.

Current Solution

As explained by Jan Tielens (http://weblogs.asp.net/jan/archive/2008/11/20/sharepoint-2007-and-jquery-1.aspx there are usually three solutions considered to be available to do that:

• Add a <script src> tag in a Content Editor Web Part
• Add a <script src> tag in the Master Page itself
• Add a <script src> tag dynamically in the <head> using a DelegateControl placed in the standard master pages

In my case, only the third solution was usable. So I went with it, created my feature, added it to my solution.

The problem

The story would have ended here if it wasn’t for a detail I discovered quickly enough; the standard publishing pages (BlueGlassBand and so forth) do not have the AdditionalPageHead delegate control defined. Thus my solution worked really well with collaboration pages but failed on publishing pages.

The obvious solution was to modify the master page used by my client to add the Delegate Control tag but the client didn’t want any modification to the master page. Thus I was at this point :

How do I add a reference to some javascript and css files in the HTML <head> section of a page generated by a master page that doesn’t include any DelegateControl and that I can’t modify.

The solution

After digging a bit I found a very surprising (to me at least) but fully working solution. Use a Custom Action.

Before you start thinking I am nuts, let me explain, If you take a close look at the MSDN you will see that you can specify a ControlAssembly and a ControlClass, guess what, the class specified will do the actual rendering. Custom Actions aren’t just for Site Action Menu entries, ECB and so forth.

So here is more detail:

• Create a class inheriting from CompositeControl
• Override the constructor so that I can plug a method in the Load event.
• In my method triggered on load, access the page header and add a LiteralControl to declare the scripts and css I need

And here is a stripped down but fully working version of my code :

using System; using System.Collections.Generic; using System.Text; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Web.UI; namespace ItemsInTheSafeControl { class SiteActionMenu : CompositeControl { public const string mStrIncludedRessources = "<script type=\"text/javascript\" src=\"/_layouts/jquery-1.3.2.min.js\">"; public SiteActionMenu() { this.Load += new EventHandler(SiteActionMenu_Load); } protected void SiteActionMenu_Load(object Sender, EventArgs E) { LiteralControl vLiteralControl = new LiteralControl(SiteActionMenu.mStrIncludedRessources); this.Page.Header.Controls.Add(vLiteralControl); } protected override void Render(HtmlTextWriter Writer) { } } }

To plug this code as a custom action, I use the following ElementManifest

<?xml version="1.0" encoding="utf-8" ?> <Elements xmlns="http://schemas.microsoft.com/sharepoint/"> <CustomAction Id="SAG_CR_Ressources_Deploy_Action" Location="Microsoft.SharePoint.StandardMenu" GroupId="SiteActions" ControlAssembly="SAG.CommentsRating, Version=1.0.0.0, Culture=neutral, PublicKeyToken=406cabeaa3d2932a" ControlClass="SAG.CommentsRating.WebParts.SiteActionMenu"> </CustomAction> </Elements>

Now you just package the ElementManifest in a feature, add your dll and create a solution out of it (wspbuilder is my tool of choice). Your solution manifest will need to specify a SafeControl tag to be added for your class/namespace in the web.config for your CompositeControl.

Here you go, a non-intrusive quite generic solution to plug additional resources into SharePoint.