05 December, 2012

Enable CSS3 / HTML5 in SharePoint 2010

Ok i guess everyone that works with SharePoint 2010 has been hunting the holy grail of Sharepoint:
How to enable CSS3 / HTML5.

Or maybe not, but i have, and all the answers i have found so far has failed..

I really can't understand why SharePoint is so bound to IE8.

Latest version of Chrome / FireFox, they all work like a "charm" in SharePoint with CSS3 support.
But IE9 made by Microsoft ?? Nope.

Well it works but in IE8 mode only with no CSS3 support, the document mode is forced by the following meta tag in the master page:

<meta http-equiv='X-UA-Compatible' content='IE=8'/>

If you remove the tag, it renders CSS3 "perfectly" in IE9.
But unfortunately a lot of system functionality starts to fail.
Like saving a page, cancel out of a save, Peoplepicker starts failing etc.

I found one javascript fix for fixing the people picker.
But that was really huge and ugly.
Several other issues still exists, even if you fix that one.

Anyway so far i have noticed that the problems only happens when in edit mode.
So i decided to switch the IE8 document mode on / off dependant of the page mode.
Getting everything to work in IE9 mode seems to be impossible for now.

So when in View mode, then remove the IE8 meta tag and render CSS3.
If not then render the meta tag which will remove CSS3 and make the standard system functionality work.
There is also a second condition we need to check, we need to check if the page is in web part edit mode.
If we don't include the IE8 meta tag in this mode, you are not able to move the webparts within the zones.

Well basically the same situation, both are edits but needs to be handled separately.

I also included a "emergency" fallback, just add ?ie8=1 to the current url if you still need to turn
off IE9 mode in a page.
Or ?ie8=0 to turn on IE9 mode in a edit page or whatever for testing.

somesite.com?ie8=1

UPDATE 21.01.2012
Had to do some changes to the rendering.
Because if you remove the tag completely then the IE compability mode button is displayed.
And some users tends to click on it and disable the css3 rendering.
Which of course causes a lot of problems.
The solution is to always render the tag, but with IE=edge.
Which basically means the latest version of IE.
I also rewrote the fallback so that you can decide which IE mode to use:
somesite.com?IE=8
somesite.com?IE=9
somesite.com?IE=edge

So how can we do this (Updated code):

1. Create a new assembly, sign it, add this class:
namespace MyAssembly.UI.WebControls
{
    using System.Web.UI;
    using Microsoft.SharePoint;
    using Microsoft.SharePoint.WebControls;
    using System.Web.UI.WebControls.WebParts;

    public class LegacyDocumentModeFix : LiteralControl
    {
        protected override void Render(HtmlTextWriter output)
        {
            const string documentMode = "<meta http-equiv='X-UA-Compatible' content='{0}'/>";
            string ieMode = "IE=edge";
            string queryMode = Page.Request.QueryString["IE"];
            if (!string.IsNullOrEmpty(queryMode))
            {
                ieMode = "IE=" + queryMode;
            }
            else
            {
                System.Web.HttpBrowserCapabilities browser = Page.Request.Browser;
                if (browser.Browser == "IE")
                {
                    SPControlMode mode = SPContext.Current.FormContext.FormMode;
                    if (mode != SPControlMode.Display)
                    {
                        ieMode = "IE=8";
                    }
                    else
                    {
                        WebPartManager manager = WebPartManager.GetCurrentWebPartManager(Page);
                        if (manager != null)
                        {
                            if (manager.DisplayMode != WebPartManager.BrowseDisplayMode)
                            {
                                ieMode = "IE=8";
                            }
                        }
                    }
                }
            }

            output.Write(documentMode, ieMode);
            base.Render(output);
        }
    }
}

2. Include the assembly in your sharepoint project:

Package, Advanced, Add, Add assembly from project output.
And make it a safe control.

3. Add a reference in the master page:
<%@ Register Tagprefix="Fix" Namespace="MyAssembly.UI.WebControls" Assembly="MyAssembly.UI, Version=1.0.0.0, Culture=neutral, PublicKeyToken=yourassemblykeytoken" %>

4. Replace the meta tag inside the <head> tags in your master page:
Replace:
<meta http-equiv='X-UA-Compatible' content='IE=8'/>

With:
<Fix:LegacyDocumentModeFix runat="server"/>

Thats it, deploy.
CSS3 renders nicely in view mode, and degrades to IE8 mode in edit / new mode
which is ok for now.

Until SharePoint 2013 comes along ;)

I haven't tested every functionality in SharePoint with this fix, only the publishing part that i use.
Some of the site settings functionality, all site actions etc.
Haven't found any problems yet..

And i use this control both in my custom masterpage and in my v4 system masterpage.

If you still find something that fails, Please let me know.

29 September, 2012

Renaming a Term in SharePoint 2010 Glitch

I have been working a lot with SharePoint lately, struggling with all kinds of strange problems.
So it's about time to update this blog and share some of my findings.

Let's start with renaming a Term in the Taxanomy Term Store. I found some glitches regarding renaming Terms, so let's look into this in detail.

Prerequisites

Let's say we have a Termset Named Organization, and a Term named Logistics, like this:
Then we have a List with the following Taxaonomy Fields:  

Field            Type    
Organization     TaxanomyFieldType
OrganizationTaxHt0     Note
TaxCatchAll LookupMulti
TaxCatchAllLabel LookupMulti

One document is uploaded and tagged with Logistics like this:



Getting Field Information

Now let's write come code to Display the Taxonomy Fields for a given ListItem:
private void DisplayFields(SPListItem listItem)
{
    foreach (SPField field in listItem.Fields)
    {
        if (field.TypeAsString == "TaxonomyFieldType" || field.TypeAsString == "TaxonomyFieldTypeMulti")
        {
            TaxonomyField taxanomyField = (TaxonomyField)field;
            if (taxanomyField.AllowMultipleValues)
            {
                TaxonomyFieldValueCollection values = listItem[taxanomyField.Id] as TaxonomyFieldValueCollection;
                if (values.Count == 0)
                {
                    WriteInformationMessage(string.Format("TaxField Id : {0}, Type: {1}, Title: {2}, InternalName: {3}, 
                    TaxonomyFieldValue is null", field.Id, field.TypeAsString, field.Title, field.InternalName));
                }
                else
                {
                    foreach (TaxonomyFieldValue value in values)
                    {
                        DisplayField(field, value);
                    }
                }
            }
            else
            {
                TaxonomyFieldValue value = listItem[taxanomyField.Id] as TaxonomyFieldValue;
                DisplayField(field, value);
            }
        }
        else if (field.TypeAsString == "LookupMulti")
        {
            if (field.InternalName == "TaxCatchAll" || field.InternalName == "TaxCatchAllLabel")
            {
                SPFieldLookupValueCollection lookupValues =(SPFieldLookupValueCollection)listItem[field.Id];
                if (lookupValues.Count == 0)
                {
                    WriteInformationMessage(string.Format("TaxField Id : {0}, Type: {1}, Title: {2}, InternalName: {3},
                    LookupValue: {4}, LookupId: {5}", field.Id, field.TypeAsString, 
                    field.Title, field.InternalName, "<empty>", "<empty>"));
                }
                else
                {
                    foreach (SPFieldLookupValue lookupValue in lookupValues)
                    {
                        WriteInformationMessage(string.Format("TaxField Id : {0}, Type: {1}, Title: {2}, InternalName: {3},
                        LookupValue: {4}, LookupId: {5}", field.Id, field.TypeAsString, field.Title, field.InternalName, 
                        lookupValue.LookupValue, lookupValue.LookupId));
                    }
                }
            }
            else
            {
                //Other multi
                SPFieldLookupValueCollection lookupValues =(SPFieldLookupValueCollection)listItem[field.Id];
                if (lookupValues.Count == 0)
                {
                    WriteVerboseMessage(string.Format("OtherField Id : {0}, Type: {1}, Title: {2}, InternalName: {3},
                    LookupValue: {4}, LookupId: {5}", field.Id, field.TypeAsString, field.Title, field.InternalName, 
                    "<none>", "<none>"));
                }
                else
                {
                    foreach (SPFieldLookupValue lookupValue in lookupValues)
                    {
                        WriteVerboseMessage(string.Format("OtherField Id : {0}, Type: {1}, Title: {2}, InternalName: {3},
                        LookupValue: {4}, LookupId: {5}", field.Id, field.TypeAsString, field.Title, field.InternalName, 
                        lookupValue.LookupValue, lookupValue.LookupId));
                    }
                }
            }
        }
        else if (field.TypeAsString == "Note")
        {
            TaxonomyField taxField = GetTaxonomyTextField(listItem, field);
            if (taxField != null)
            {
                WriteInformationMessage(string.Format("TaxField Id : {0}, Type: {1}, Title: {2}, InternalName: {3}, Value: {4},
                RelatedTaxFieldInternalName: {5}", field.Id, field.TypeAsString, field.Title, field.InternalName, 
                listItem[field.Id], taxField.InternalName));
            }
            else
            {
                WriteVerboseMessage(string.Format("OtherField Id : {0}, Type: {1}, Title: {2}, InternalName: {3}, Value: {4}", 
                field.Id, field.TypeAsString, field.Title, field.InternalName, listItem[field.Id]));
            }

        }
        else
        {
            WriteVerboseMessage(string.Format("OtherField Id : {0}, Type: {1}, Title: {2}, InternalName: {3},Value: {4}", 
            field.Id, field.TypeAsString, field.Title, field.InternalName, listItem[field.Id]));
        }
    }
}

private TaxonomyField GetTaxonomyTextField(SPListItem listItem, SPField noteField)
{
    foreach (SPField field in listItem.Fields)
    {
        if (field.TypeAsString == "TaxonomyFieldType" || field.TypeAsString == "TaxonomyFieldTypeMulti")
        {
            TaxonomyField taxanomyField = (TaxonomyField)field;
            if (taxanomyField.TextField == noteField.Id)
            {
                return taxanomyField;
            }
        }
    }

    return null;
}

private void DisplayField(SPField field, TaxonomyFieldValue fieldValue)
{
    if (fieldValue == null)
    {
        WriteInformationMessage(string.Format("TaxField Id : {0}, Type: {1}, Title: {2}, InternalName: {3},
        TaxonomyFieldValue is null", field.Id, field.TypeAsString, field.Title, field.InternalName));
    }
    else
    {
        WriteInformationMessage(string.Format("TaxField Id : {0}, Type: {1}, Title: {2}, InternalName: {3},
        TermLabel: {4}, TermId: {5}, WssId: {6}", field.Id, field.TypeAsString, field.Title, field.InternalName, 
        fieldValue.Label, fieldValue.TermGuid, fieldValue.WssId));
    }
}

Now let's view the fields on our listitem:
Get-ListTaxanomyFieldReport command started.
List Id: f7ed8478-bd9a-463e-9637-bc43e02777ff, Name: Document Archive
File: DocumentArchive/TestDocument.docx, Id: f1bdb360-f269-4de8-a73a-baaf657c09bf, Title: TestDocument

TaxField Id : 0f4b3ac1-7947-4cbf-a4ad-963e1fb892bd, Type: Note, Title: Organization_0, InternalName: OrganizationTaxHTField0, Value: Logistics|2293196a-2af6-4521-9aa0-1230099a9b61, RelatedTaxFieldInternalName: Organization

TaxField Id : 3b2705e1-8f4b-47e6-bb0f-cffb1958f7c7, Type: TaxonomyFieldType, Title: Organization, InternalName: Organization, TermLabel: Logistics, TermId: 2293196a-2af6-4521-9aa0-1230099a9b61, WssId: 11

TaxField Id : f3b0adf9-c1a2-4b02-920d-943fba4b3611, Type: LookupMulti, Title: Taxonomy Catch All Column, InternalName: TaxCatchAll, LookupValue: /jjIJ5eNSk2OsJp9BdEzVw==|LARTuP6U9kuzdmFGr+H5LA==|ahmTIvYqIUWaoBIwCZqbYQ==, LookupId: 11

TaxField Id : 1390a86a-23da-45f0-8efe-ef36edadfb39, Type: Note, Title: TaxKeywordTaxHTField, InternalName: TaxKeywordTaxHTField, Value: , RelatedTaxFieldInternalName: TaxKeyword

TaxField Id : 8f6b6dd8-9357-4019-8172-966fcd502ed2, Type: LookupMulti, Title: Taxonomy Catch All Column1, InternalName: TaxCatchAllLabel, LookupValue: Logistics#?|, LookupId: 11

TaxField Id : 23f27201-bee3-471e-b2e7-b64fd8b7ca38, Type: TaxonomyFieldTypeMulti, Title: Enterprise Keywords, InternalName: TaxKeyword, TaxonomyFieldValue is empty
Get-ListTaxanomyFieldReport command ended.
So far so good, Organization, OrganizationTaxHTField0, TaxCatchAllLabel all says Logistics.

Renaming the Term

Now go into the Taxanomy Term Store in Central Admin and rename Logistics to Logistics1 and hit save.
View the fields again:
Get-ListTaxanomyFieldReport command started.
List Id: f7ed8478-bd9a-463e-9637-bc43e02777ff, Name: Document Archive
File: DocumentArchive/TestDocument.docx, Id: f1bdb360-f269-4de8-a73a-baaf657c09bf, Title: TestDocument

TaxField Id : 0f4b3ac1-7947-4cbf-a4ad-963e1fb892bd, Type: Note, Title: Organization_0, InternalName: OrganizationTaxHTField0, Value: Logistics|2293196a-2af6-4521-9aa0-1230099a9b61, RelatedTaxFieldInternalName: Organization

TaxField Id : 3b2705e1-8f4b-47e6-bb0f-cffb1958f7c7, Type: TaxonomyFieldType, Title: Organization, InternalName: Organization, TermLabel: Logistics, TermId: 2293196a-2af6-4521-9aa0-1230099a9b61, WssId: 11

TaxField Id : f3b0adf9-c1a2-4b02-920d-943fba4b3611, Type: LookupMulti, Title: Taxonomy Catch All Column, InternalName: TaxCatchAll, LookupValue: /jjIJ5eNSk2OsJp9BdEzVw==|LARTuP6U9kuzdmFGr+H5LA==|ahmTIvYqIUWaoBIwCZqbYQ==, LookupId: 11

TaxField Id : 1390a86a-23da-45f0-8efe-ef36edadfb39, Type: Note, Title: TaxKeywordTaxHTField, InternalName: TaxKeywordTaxHTField, Value: , RelatedTaxFieldInternalName: TaxKeyword

TaxField Id : 8f6b6dd8-9357-4019-8172-966fcd502ed2, Type: LookupMulti, Title: Taxonomy Catch All Column1, InternalName: TaxCatchAllLabel, LookupValue: Logistics#?|, LookupId: 11

TaxField Id : 23f27201-bee3-471e-b2e7-b64fd8b7ca38, Type: TaxonomyFieldTypeMulti, Title: Enterprise Keywords, InternalName: TaxKeyword, TaxonomyFieldValue is empty
Get-ListTaxanomyFieldReport command ended.
No change of course because the list's are not updated immediatly, but through a job named Taxanomy Update Scheduler.

Go into Central Administration, Monitoring, Review Job Definitions, Find the Taxanomy Update Scheduler job, there will be one for each site collection. So be sure to pick the right one.
Now run the job. (The Job is scheduled to run every hour).
Make sure it has executed properly by looking at the Timer Job Status, it should appear in the History.
Ok let's look at our fields again:

Get-ListTaxanomyFieldReport command started.
List Id: f7ed8478-bd9a-463e-9637-bc43e02777ff, Name: Document Archive
File: DocumentArchive/TestDocument.docx, Id: f1bdb360-f269-4de8-a73a-baaf657c09bf, Title: TestDocument

TaxField Id : 0f4b3ac1-7947-4cbf-a4ad-963e1fb892bd, Type: Note, Title: Organization_0, InternalName: OrganizationTaxHTField0, Value: Logistics|2293196a-2af6-4521-9aa0-1230099a9b61, RelatedTaxFieldInternalName: Organization

TaxField Id : 3b2705e1-8f4b-47e6-bb0f-cffb1958f7c7, Type: TaxonomyFieldType, Title: Organization, InternalName: Organization, TermLabel: Logistics1, TermId: 2293196a-2af6-4521-9aa0-1230099a9b61, WssId: 11

TaxField Id : f3b0adf9-c1a2-4b02-920d-943fba4b3611, Type: LookupMulti, Title: Taxonomy Catch All Column, InternalName: TaxCatchAll, LookupValue: /jjIJ5eNSk2OsJp9BdEzVw==|LARTuP6U9kuzdmFGr+H5LA==|ahmTIvYqIUWaoBIwCZqbYQ==, LookupId: 11

TaxField Id : 1390a86a-23da-45f0-8efe-ef36edadfb39, Type: Note, Title: TaxKeywordTaxHTField, InternalName: TaxKeywordTaxHTField, Value: , RelatedTaxFieldInternalName: TaxKeyword

TaxField Id : 8f6b6dd8-9357-4019-8172-966fcd502ed2, Type: LookupMulti, Title: Taxonomy Catch All Column1, InternalName: TaxCatchAllLabel, LookupValue: Logistics1#?|, LookupId: 11

TaxField Id : 23f27201-bee3-471e-b2e7-b64fd8b7ca38, Type: TaxonomyFieldTypeMulti, Title: Enterprise Keywords, InternalName: TaxKeyword, TaxonomyFieldValue is empty
Get-ListTaxanomyFieldReport command ended.
The Note field, OrganizationTaxHTField0 is not updated properly.

But if we look at the list:

Seems like it's updated, viewing and editing the properties of this field also displays Logistics1. If you edit the properties, then hit save, then the Note field is properly updated to Logistics1.

Fixing the Problem

Anyway, i don't like things that are not 100%, so let's see how we can refresh the Note Field from code.
I tried a lot of things, but found one solution that works, here is the code:
private void RefreshTaxanomyFieldsOnListItem(SPListItem listItem, string taxFieldInternalName, string taxFieldNewLabel)
{
    bool changed = false;
    for (int i = 0; i < listItem.Fields.Count; ++i) //Dont use foreach
    {
        SPField field = listItem.Fields[i];
        if (field.TypeAsString == "TaxonomyFieldType" || field.TypeAsString == "TaxonomyFieldTypeMulti")
        {
            if (string.IsNullOrEmpty(taxFieldInternalName) || field.InternalName == taxFieldInternalName)
            {
                TaxonomyField taxanomyField = (TaxonomyField)field;
                if (taxanomyField.AllowMultipleValues)
                {
                    TaxonomyFieldValueCollection values = listItem[taxanomyField.Id] as TaxonomyFieldValueCollection;
                    if (values != null)
                    {
                        foreach (TaxonomyFieldValue value in values)
                        {
                            if (string.IsNullOrEmpty(taxFieldNewLabel) || value.Label == taxFieldNewLabel)
                            {
                                WriteVerboseMessage(string.Format("Refreshing Taxanomy Multi Field: {0}", 
                                taxanomyField.InternalName));
                                taxanomyField.SetFieldValue(listItem, values);
                                changed = true;
                                break;
                            }
                        }
                    }
                }
                else
                {
                    TaxonomyFieldValue value = listItem[taxanomyField.Id] as TaxonomyFieldValue;
                    if (value != null)
                    {
                        if (string.IsNullOrEmpty(taxFieldNewLabel) || value.Label == taxFieldNewLabel)
                        {
                            WriteVerboseMessage(string.Format("Refreshing Taxanomy Single Field: {0}",
                            taxanomyField.InternalName));
                            taxanomyField.SetFieldValue(listItem, value);
                            changed = true;
                        }
                    }
                }
            }
        }
    }

    if (changed)
    {
        WriteInformationMessage(string.Format("Updated File: {0}, Id: {1}, Title: {2}", listItem.Url,
        listItem.UniqueId.ToString(), listItem.Title));
        listItem.SystemUpdate(false);
    }
}

So let's run this code on our list, then look at our fields again:
Get-ListTaxanomyFieldReport command started.
List Id: f7ed8478-bd9a-463e-9637-bc43e02777ff, Name: Document Archive
File: DocumentArchive/TestDocument.docx, Id: f1bdb360-f269-4de8-a73a-baaf657c09bf, Title: TestDocument

TaxField Id : 0f4b3ac1-7947-4cbf-a4ad-963e1fb892bd, Type: Note, Title: Organization_0, InternalName: OrganizationTaxHTField0, Value: Logistics1|2293196a-2af6-4521-9aa0-1230099a9b61, RelatedTaxFieldInternalName: Organization

TaxField Id : 3b2705e1-8f4b-47e6-bb0f-cffb1958f7c7, Type: TaxonomyFieldType, Title: Organization, InternalName: Organization, TermLabel: Logistics1, TermId: 2293196a-2af6-4521-9aa0-1230099a9b61, WssId: 11

TaxField Id : f3b0adf9-c1a2-4b02-920d-943fba4b3611, Type: LookupMulti, Title: Taxonomy Catch All Column, InternalName: TaxCatchAll, LookupValue: /jjIJ5eNSk2OsJp9BdEzVw==|LARTuP6U9kuzdmFGr+H5LA==|ahmTIvYqIUWaoBIwCZqbYQ==, LookupId: 11

TaxField Id : 1390a86a-23da-45f0-8efe-ef36edadfb39, Type: Note, Title: TaxKeywordTaxHTField, InternalName: TaxKeywordTaxHTField, Value: , RelatedTaxFieldInternalName: TaxKeyword

TaxField Id : 8f6b6dd8-9357-4019-8172-966fcd502ed2, Type: LookupMulti, Title: Taxonomy Catch All Column1, InternalName: TaxCatchAllLabel, LookupValue: Logistics1#?|, LookupId: 11

TaxField Id : 23f27201-bee3-471e-b2e7-b64fd8b7ca38, Type: TaxonomyFieldTypeMulti, Title: Enterprise Keywords, InternalName: TaxKeyword, TaxonomyFieldValue is empty
Get-ListTaxanomyFieldReport command ended.

Finally everything is correct.

I wrote a Commandlet that did this refresh, but i didn't want to execute the Taxanomy Update Scheduler job manually every time so i found this: TaxonomySession.SyncHiddenList(site);
I thought that this was the command that the Taxanomy Update Scheduler job was executing, but thats not the case.
If i ran this command, no changes where made to the list, if i ran this command after running the Taxanomy Update Scheduler job then the list item was reverted back to the old name in all fields ??
If anyone can tell me where i can find the source for the Taxanomy Update Scheduler job i would be really happy, didn't have time to investigate this any further.

Anyway i decided to run the job before i executed the RefreshTaxanomyFieldsOnListItem like this:
private void RefreshHiddenList(SPSite site, int timeoutSeconds)
{
    foreach (SPJobDefinition job in site.WebApplication.JobDefinitions)
    {
        if (job.Name.Equals("UpdateHiddenListJobDefinition") && job.Status == SPObjectStatus.Online)
        {
            DateTime dateStart = DateTime.Now;
            job.RunNow();

            while (job.LastRunTime < dateStart)
            {
                if (DateTime.Now > dateStart.AddSeconds(timeoutSeconds))
                {
                    Console.WriteLine("Taxanomy Update Scheduler did not finish executing due to a timeout !");
                    break;
                }

                Thread.Sleep(1000);
            }

            break;
        }
    }
}

So the correct sequence are:
1. Rename the Term.
2. Run RefreshHiddenList() or run the Taxaonomy Update Scheduler.
3. Run RefreshTaxanomyFieldsOnListItem().

I'm not sure what the consequences are when the Note field isn't updated as it should. But if you are using the Note field in your custom code, webparts etc. you should be aware of that it could be wrong.

22 March, 2012

VMware image with Sharepoint 2010, MSSQL 2012, SSRS, Performance Point and PowerPivot

This is how you can install a VMware Image with Windows 2008 r2, SharePoint 2010 and Microsoft SQL Server 2012 Denali.
I will also show you how you can configure SSRS, Performance Point and PowerPivot.
At first i tried to install this without a domain, and got everything working except PowerPivot.
At present time there is no workarounds for getting PowerPivot to run in a non domain environment.
Bummer, so i had to scrap it and start over with a domain.
Anyway installing a domain is pretty easy, so just live it and use a domain in your SharePoint playground.
It just makes your life so much easyer.

Sorry no screenshots, if there are parts you don't understand please contact me.

I spent a couple of days doing this, so maybe someone will find this useful.

Install windows

  1. Install VMware Workstation.
  2. Install Windows 2008 R2 With SP1 with username dev.
  3. Change computername to DevServer.
  4. Turn off the Firewall.
  5. Turn off UAC.
  6. Turn off IE ESC (Server manager down on the right).
  7. Add server roles: Web Server (IIS) and Application Server.
  8. Run windows update over and over again, until the green checkmark of delight appears. (Microsoft PLEASE, 2012 and this is still not automated in any way) .
  9. Power off the VM, and take a snapshot. VMware can only clone snapshots taken during power off (just a little tip if you need to clone it in a later life).

Install the domain controller

  1. Click Start, then run.
  2. Type in dcpromo and hit enter.
  3. Don't check use advanced mode installation.
  4. Select create a new domain in a new forest.
  5. FQDN of the forest root domain: xtrm.local or whatever.
  6. Forest functional level, just select windows 2008 r2.
  7. Select additional options for this domain controller, make sure DNS server is selected.
  8. Change to static IP in the IPV4 settings of your network card.
  9. For example IP: 10.10.10.1, Subnet mask 255.255.255.240.
  10. Continue the wizard: Select no, i will assign static IP addresses to all physical network adapters.
  11. A delegation for this DNS server could not be created. Click Yes.
  12. Location for Database, Log Files ans SYSVOL, just leave the defaults.
  13. Directory services restore mode administrator password: Use a strong password.
  14. Summary, click next, click Finish, and reboot.
  15. Switch back to dhcp, obtain ip address and dns automatically because of internet access through the VM.
  16. Now start Active Directory Users and Computers.
  17. Create 3 new users: sqladmin, spadmin, spfarm. Use same password, user cannot change password, password never expires.
  18. Add spadmin to the Domain Admins group.
  19. Reboot, and we are done with setting up the domain.

Install Microsoft Sql Server 2012

  1. When installing MSSQL 2012 Denali, make sure you install these options: Reporting Services - Native, Reporting Services - SharePoint, Reporting Services Add-in for SharePoint Products.
  2. In other words, select all features except the replay controller / client.
  3. Install a sql instance named SharePoint.
  4. Run all services with the xtrm\sqladmin user.
  5. Add dev, sqladmin, spadmin and spfarm user to the sql server / analysis services administrator group.
  6. Install and Reboot.
  7. Microsoft SQL Server 2012 is now up and running.

Install SharePoint 2010

  1. Login as spadmin, IMPORTANT !.
  2. Install SharePoint 2010 Prerequisites.
  3. Start the SharePoint 2010 server install.
  4. Wait until done.
  5. Run the SharePoint Products Configuration Wizard (Started automatically).
  6. Select Create a new server farm.
  7. Database server: DevServer\SharePoint
  8. Database name: SharePoint_Config
  9. Database Access account: xtrm\spfarm
  10. Passphrase: Enter a strong password.
  11. Force port number for SharePoint Central Administration.
  12. Select NTLM.
  13. Finish the wizard.
  14. Start SharePoint Central Administration,, configure your SharePoint farm (If not automatically started).
  15. Service account: Use existing xtrm\spfarm.
  16. Create a top level site, Team Site or whatever.
  17. SharePoint 2010 is now up and running in a farm on Microsoft SQL Server 2012.

Configure Reporting Services in SharePoint 2010

  1. Start SharePoint Central Administration.
  2. Click on General Application Settings.
  3. Under SQL Server Reporting Services, click on Reporting services integration.
  4. DON'T bother, because this is for SQL Server 2008 /2008 R2 only.
  5. This is how you integrate against SQL Server 2012:
  6. Start SharePoint 2010 Management Shell, right click and do a run as administrator. IMPORTANT !
  7. Type in: Install-SPRSService
  8. Type in: Install-SPRSServiceProxy
  9. Type in: get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance
  10. Now go back to SharePoint Central Administration.
  11. Click on System Settings, Manage services on server.
  12. Then you will see a service named: SQL Server Reporting Services Service with status Started.
  13. Goto Application management, Service Applications, Manage Service Applications.
  14. Select New then SQL Server Reporting Services Service Application.
  15. Name: SSRSApp, New app pool: SSRSAppPool, App pool account: xtrm\spfarm.
  16. Select windows authentication, select web application association: SharePoint 80.
  17. And we are done with SSRS integration against SharePoint 2010.

Configure Performance Point in SharePoint 2012

  1. Just make sure PerformancePoint Service is started in System Settings, Manage Services on Server.
  2. And that the Performance Point Service application has been created under Application Management, Manage Service Applications.

Configure PowerPivot in SharePoint 2010

  1. Login as spadmin.
  2. PowerPivot is a separate install, so first we need to install it.
  3. Run Microsoft Sql Server Setup.
  4. Add features to an existing installation.
  5. Next screen, you need to select Perform a New installation of Sql Server 2012.
  6. Then select Sql Server PowerPivot for SharePoint, do not select add database instance.
  7. It will default to named instance PowerPivot.
  8. Run the Sql Server Analysis Services as the xtrm\sqladmin account.
  9. Add dev, spadmin, spfarm and sqladmin as administrators.
  10. Finish the wizard and it will be installed.
  11. Start the PowerPivot Configuration Tool.
  12. Select Configure or Repair PowerPivot For SharePoint.
  13. Default Account: xtrm\spfarm
  14. Database Server: devserver\SharePoint
  15. Enter SharePoint passphrase.
  16. Click on Register Sql Server Analysis Services (PowerPivot) on local server and enter password for sqladmin user.
  17. Click Validate, then Run.
  18. Click yes on the confirm messagebox.
  19. And wait until it succeeds.
  20. Start SharePoint Central Administration
  21. If you look at System, Services On Server, you now have a new service named: SQL Server PowerPivot System Service.
  22. Goto Application Management, Manage Service Applications.
  23. You will have a new Service Application named: Default PowerPivot Service Application.
  24. Now there are 2 farm solutions that has been deployed in the farm.
  25. To view them goto System Settings, Manage farm solutions.
  26. PowerPivotfarm.wsp is globally deployed.
  27. PowerPivotWebApp.wsp is deployed to each web app.
  28. So if you create a new webapp, then you need to deploy this solution to the new webapp.

Great, finally everything is installed and configured.
Later i will write a post about how you actually use what you have installed:
  • SSRS both Native and SharePoint.
  • Performance Point.
  • PowerPivot.

A final tip: ALWAYS run IE and SharePoint Management Console as administrator.
(Right click and run as administrator). If you don't do this there will be a lot of menu options in
SharePoint Central Administration that will be missing.
And the scripts will fail with some cryptic errors.
If you find any errors above, unnecessary steps or other ways to improve the sequence,
please let me know.

13 February, 2012

How to calculate median in MSSQL

Definition of median:
Median is described as the numerical value separating the higher half from the lower half of a
sample, a population or a probability distribution.

The median value of a list of numbers can be found by arranging all the observations from lowest value to the highest value and picking the middle one.
If there is an uneven number of observations, then median will be the middle value.
If there is an even number of observations, the median is then usually defined to be the mean of the two middle values.

So lets say we have this sample data:
Lot Wafer ResultName Value Comment
8485474 1 someresult 50
8485474 1 someresult 40
8485474 1 someresult 30 <- Median 1 / Median 2     (Median1 + Median 2) / 2 = Median = 30
8485474 1 someresult 20
8485474 1 someresult 10
8485474 2 someresult 60
8485474 2 someresult 50
8485474 2 someresult 40 <- Median 1
8485474 2 someresult 30 <- Median 2     (Median1 + Median 2) / 2 = Median = 35
8485474 2 someresult 20
8485474 2 someresult 10

First we need to sort and number the resultvalues ascending.
Then we need to get the result count for each wafer.
To do this, we can use the row_number() function / count(*) function together with over / partition by.

(select l.lot, 
          w.waferno, 
          dr.resultvalue,
          row_number() over(partition by l.lot, w.waferno order by dr.resultvalue) as rownum,
          count(*) over(partition by l.lot, w.waferno) as resultcount
  from lot l inner join
  wafer w on l.lot = w.lot inner join
  results r on w.waferid = r.waferid inner join 
  resulttype rt on r.resulttypeid = rt.resulttypeid
where l.lot = 8485474 
and rt.resultname = 'someresult') as medianvalues

The above query will give us this resultset:
Lot Wafer ResultName Value RowNum ResultCount
8485474 1 someresult 10 1 5
8485474 1 someresult 20 2 5
8485474 1 someresult 30 3 5
8485474 1 someresult 40 4 5
8485474 1 someresult 50 5 5
8485474 2 someresult 10 1 6
8485474 2 someresult 20 2 6
8485474 2 someresult 30 3 6
8485474 2 someresult 40 4 6
8485474 2 someresult 50 5 6
8485474 2 someresult 60 6 6

Now we can filter out the middle rows with the follwing formula:
(resultcount + 1) / 2 and (resultcount + 2) / 2)
So for wafer 1:
(5 + 1) / 2 = 3
(5 + 2) / 2 = 3  (3.5 will be 3 when converted to int)
And for wafer 2:
(6 + 1) / 2 = 3 (3.5 will be 3 when converted to int)
(6 + 2) / 2 = 4 
So now that we have the two middle rows, we can just do an average of the two rows:
So for wafer 1:
30 + 30 / 2 = 30  (Row 3 + Row 3) / 2
And for wafer 2:
30 + 40 / 2 = 35  (Row 3 + Row 4) / 2

Now let's put it all together:
select lot, 
       waferno, 
       resultcount, 
       avg(resultvalue) as median, 
       (resultcount + 1) / 2 as middlerow1, 
       (resultcount + 2) / 2 as middlerow2
from
  (select l.lot, 
          w.waferno, 
          dr.resultvalue,
          row_number() over(partition by l.lot, w.waferno order by dr.resultvalue) as rownum,
          count(*) over(partition by l.lot, w.waferno) as resultcount
  from lot l inner join
  wafer w on l.lot = w.lot inner join
  results r on w.waferid = r.waferid inner join resulttype rt on r.resulttypeid = rt.resulttypeid
where l.lot = 8485474 
and rt.resultname = 'someresult') as medianvalues
where rownum in((resultcount + 1) / 2, (resultcount + 2) / 2)
group by lot,waferno, resultcount
order by lot,waferno
Or if you prefer the new with statement:
with medianvalues as
(
select l.lot, 
          w.waferno, 
          dr.resultvalue,
          row_number() over(partition by l.lot, w.waferno order by dr.resultvalue) as rownum,
          count(*) over(partition by l.lot, w.waferno) as resultcount
  from lot l inner join
  wafer w on l.lot = w.lot inner join
  results r on w.waferid = r.waferid inner join resulttype rt on r.resulttypeid = rt.resulttypeid
where l.lot = 8485474 
and rt.resultname = 'someresult'
)
select lot, 
       waferno, 
       resultcount, 
       avg(resultvalue) as median, 
       (resultcount + 1) / 2 as middlerow1, 
       (resultcount + 2) / 2 as middlerow2
from medianvalues
where rownum in((resultcount + 1) / 2, (resultcount + 2) / 2)
group by lot,waferno, resultcount
order by lot,waferno

The above query will give us this resultset:
Lot Wafer ResultCount Median MiddleRow1 MiddleRow2
8485474 1 5 30 3 3
8485474 2 6 35 3 4

09 February, 2012

Formatted TimeDiff UDF for MSSQL

I just created a UDF function for displaying a formatted time diff value in MSSQL.
Why ? I googled this and found a lot of solutions that did not work :(
So i just had to look into it.

I wanted the time difference to be displayed in the following format:
HHH:MM:SS (@type = 0)
Where HHH = Total number of hours,  so if the difference is one week,
then this value will be 168. (168:00:00).

I also created another variant:
D MM:HH:SS (@type = 1)
Where D = Total number of days, so if the difference is one week,
then this value will be 7. (7 00:00:00).

create function [dbo].[TimeDiff](@date1 datetime, @date2 datetime, @type smallint)
returns varchar(50)
as
begin
declare @days int
declare @totalhours int
declare @hours int
declare @minutes int
declare @seconds int
declare @ret varchar(50)
set @seconds = datediff(ss, @date1,@date2) 
set @totalhours = @seconds / 3600 
set @days = @totalhours / 24 
set @hours = @totalhours - (@days * 24)
set @minutes = ((@seconds - (3600 * @totalhours)) / 60)
set @seconds = ((@seconds - (3600 * @totalhours) - (60 * @minutes)))
if(@type = 0)
begin
    --HHH:MM:SS 
    set @ret = case when @totalhours < 10 then '0' + convert(varchar(20), @totalhours) else convert(varchar(20), @totalhours) end + ':' + right('0' + convert(varchar(20), @minutes), 2) + ':' + right('0' + convert(varchar(20), @seconds), 2)
end
else
begin
    --D HH:MM:SS
    set @ret = convert(varchar(20), @days) + ' ' + right('0' + convert(varchar(20), @hours), 2) + ':' + right('0' + convert(varchar(20), @minutes), 2) + ':' + right('0' + convert(varchar(20), @seconds), 2)
end
return @ret
end

07 February, 2012

Custom multiply aggregation function in AS2008

In a recent project of mine i needed a custom aggregation function that needed to multiply som yield numbers of a specific measure in a given dimension.

My yield numbers are actually stored as a number between 0 and 100. And not between 0 and 1 which i guess are more common when expressed as percentage.
The reason for this is some limitations in our 3rd party reporting charts.
Any way this is how i made the custom aggregation function that will work with both variants:

Create a c# class library.
Add a reference to this dll (ADOMD.Net interfaces):
C:\Program Files\Microsoft Analysis Services\AS OLEDB\10\msmgdsrv.dll
Add a class named ASUtils with the following content:

namespace ASUtils
{
    using System;
    using Microsoft.AnalysisServices.AdomdServer;

    public class Multiplication
    {
        [SafeToPrepare(true)]
        public static Nullable<double> Multiply(Set inputSet, Expression inputExpression, double divider, bool excludeZeroValues)
        {            
            Nullable<double> num = null;
            bool first = true;
            foreach (Tuple tuple in inputSet.Tuples)
            {
                MDXValue mdxValue = inputExpression.Calculate(tuple);
                bool isEmpty = (mdxValue.ToChar() == 0x00);
                if (!isEmpty)
                {
                    double value = mdxValue.ToDouble();
                    if (value != 0 || (value == 0 && excludeZeroValues == false))
                    {
                        if (first == true)
                        {
                            num = value / divider;
                            first = false;
                        }
                        else
                        {
                            num *= value / divider;
                        }
                    }
                }
            }

            return num * divider;
        }
    }
}
I did not find any built in functions that could check if a value was empty or not, but this seems to work:
bool isEmpty = (mdxValue.ToChar() == 0x00);
Any other solutions out there ?

Build the project, then in your AS2008 cube project, add a reference to the new class library.
In your cube, under calculations. Add a new calculated member named YieldM with this code:
ASUtils.Multiply([Process].[ProcessNoGroup].Children,[Measures].[Yield], 100.0, false)
[Process] = Dimension
[ProcessNoGroup] = Hierarchy
[Measures].[Yield] = Measure containg numbers between 0 and 100.

If the measure contains numbers beteen 0 and 1, then you can change the multiplyer to 1 like:
ASUtils.Multiply([Process].[ProcessNoGroup].Children,[Measures].[Yield], 1.0, false)
Empty values are always eliminated from the multiply.
You can also exclude 0 (zero) values by changing the last parameter to true.

So if we now look at the yield measure over time and process, we have this test data:
Time Dimension
Process Dimension
Yield
W01
Process1
94.88
W01
Process2
96.42
W01
Process3
95.03
W02
Process1
98.86
W02
Process2
96.42
W02
Process3
94.03

When we aggregate this by week and look at our calculated YieldM measure:
Time Dimension
YieldM
Formula
W01
86.93
(94.88*96.42*95.03)
W01
89.63
(98.86*96.42*94.03)

And voila, our custom aggregation is working as it should.