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.

16 December, 2011

Using ILMerge and Eazfuscator.NET

I ran into some new requirements on my last project.
I've made an autoupdate system which will be used for an upcoming project, and i wanted to merge all the dll's into the autoupdate executable.
Just to minimize trouble and communication when downloading the autoupdater.
I also wanted to obfuscate my executable.

Merge Assemblies:

Now first i stumbled upon ILMerge, which is used to merge assemblies.
This is a tool release by Microsoft so it should be safe to use, you can read more about it here:
http://research.microsoft.com/en-us/people/mbarnett/ilmerge.aspx

It's pretty simple to use:

ILMerge.exe /out:MyMergedExe.exe MyOriginalExe.exe Myfirst.dll MySecond.dll
First, all my projects use framework version 4, so we need to add some parameters so that ILMerge will be able
to merge a framework version 4.0 project.

ILMerge.exe /targetplatform:v4,"C:\Windows\Microsoft.NET\Framework\v4.0.30319" /out:MyMergedExe.exe MyOriginalExe.exe Myfirst.dll MySecond.dll
In the ILMerge folder (C:\Program Files (x86)\Microsoft\ILMerge\), create a file named ILMerge.exe.config:
<?xml version ="1.0"?>
<configuration>
 <startup useLegacyV2RuntimeActivationPolicy="true">
  <requiredRuntime safemode="true" imageVersion="v4.0.30319" version="v4.0.30319"/>
 </startup>
</configuration>
Great, now everything runs and works with .NET Framework v4.0.

Then i wanted a more dynamic way to include the DLL's without editing the ILMerge command line every time.
Luckily there is a parameter named /wildcards


ILMerge.exe /wildcards /targetplatform:v4,"C:\Windows\Microsoft.NET\Framework\v4.0.30319" /out:MyMergedExe.exe MyOriginalExe.exe *.dll
And to get some more information about the merge process, add the /log parameter.

That's basically everything you need to know about merging assemblies into a single executable.

Obfuscate Executable:

So let's see how we can obfuscate the executable.
There is a lot of obfuscators out there.
I tried 3-4 variants, and found a lot of buggy obfuscators out there. Some crashed, some did not work, some costs a lot of $.
Anyway i ended up with the free EazFuscator.NET. It works, and has a nice commandline interface.
You can read more about it here:
http://www.eazfuscator.net


Eazfuscator.NET.exe -s -o MyObfuscatedExe.exe MyMergedExe.exe

I also added -s to get some statistics about the merge process.
You dont need to use the GUI application that comes with Eazfuscator.NET.
We will use the commandline interface only.


Automate, using PostBuild event:

Now, let's automate all this information into a complete build:

In your project, add this to the postbuild event:

if $(ConfigurationName) == Release $(ProjectDir)\Build\PostBuild.cmd "$(TargetDir)", "$(TargetName)"

Create a folder in your project and name it Build.
Inside the build folder, create a file named Postbuild.cmd with this content:


REM Add this to the project Postbuild event: 
REM if $(ConfigurationName) == Release $(ProjectDir)\Build\PostBuild.cmd "$(TargetDir)", "$(TargetName)"
ECHO --------------------------------------------------------------------------------------------------------------------------
ECHO Executing PostBuild.cmd with parameters: TargetDir=%1, TargetName=%2
CD %1
ECHO Merging Assemblies Into Executable: %2
IF NOT EXIST %1MergedRelease mkdir %1MergedRelease
"C:\Program Files (x86)\Microsoft\ILMerge\ILMerge.exe" /wildcards /log /targetplatform:v4,"C:\Windows\Microsoft.NET\Framework\v4.0.30319" /out:MergedRelease/%2.exe %2.exe *.dll
ECHO Obfuscating Executable: %2
IF NOT EXIST %1MergedRelease\ObfuscatedRelease mkdir %1MergedRelease\ObfuscatedRelease
"C:\Program Files (x86)\Eazfuscator.NET\Eazfuscator.NET.exe" -s -o MergedRelease/ObfuscatedRelease/%2.exe MergedRelease/%2.exe
ECHO --------------------------------------------------------------------------------------------------------------------------


Now when building a release build:
The normal exe with separate dll's will be created under

$(ProjectDir)\Bin\Release\
Under this folder there will be a subfolder named MergedRelease:

$(ProjectDir)\Bin\Release\MergedRelease\
In this folder our merged exe with the pdb file will be created.

Under this folder there will be a subfolder named ObfuscatedRelease:

$(ProjectDir)\Bin\Release\MergedRelease\ObfuscatedRelease\
In this folder our merged and obfuscated exe will be created.

Now thats all, a merged and obfuscated exe everytime you do a release build.

07 March, 2011

More readable exception messages in Sharepoint 2010

I have the same trouble everytime i upgrade to a newer sharepoint version,
exception messages that doesn't tell me anything.
And everytime i search the net i find a lot of answers that is not working.

So after investigating this, i found the correct way to turn off custom error
message in sharepoint (This means enabling the display of the actual error message).

This picture shows default error meassages in sharepoint, not
very helpful when developing webparts:

Now to enable more useful error messages, which means turning OFF custom error messages:

Navigate and edit the web.config file located here:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\web.config

Change this line:
From:
<customErrors mode="On" />
To:
<customErrors mode="Off" />
Now when an exception occurs in any sharepoint site, it will look like this:

Much better, but if you are developing web parts you might want to turn on the call stack too.
To do this, navigate to the web.config file located under your web site, like:
C:\inetpub\wwwroot\wss\VirtualDirectories\MyTestSite80\web.config

Change callstack in this line:
From:
<SafeMode MaxControls="200" CallStack="false" DirectFileDependencies="10" TotalFileDependencies="50" AllowPageLevelTrace="false">
To:
<SafeMode MaxControls="200" CallStack="true" DirectFileDependencies="10" TotalFileDependencies="50" AllowPageLevelTrace="false">

Now when an exception occurs in any sharepoint site, it will look like this:

So just to clarify:
There is NO need to enable debug in any web.config files (debug=true).
There is NO need to change customerrors mode inside the web.config under the site, this can be left On.
There is NO need to change this file: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\CONFIG\web.config.

04 March, 2011

Custom web part pages with navigation, Sharepoint 2010

So even in sharepoint 2010, microsoft has still not added an option to add custom web part pages, just unbelievable :)
So we are still stuck with editing system files manually to get the look that we want.
Another thing that really annoys me is that a web part page has no navigation on the left side , i really can't see
any use of a sharepoint page without navigation.

Anyway let's see how we can make this work a little better.

When creating a web part page in sharepoint there are 8 available types which can be selected:
Web Part PageIndexContentPageImage
Header, Footer, 3 Columns2spstd2.aspxspstd2.gif
Full Page, Vertical1spstd1.aspxspstd1.gif
Header, Left Column, Body3spstd3.aspxspstd3.gif
Header, Right Column, Body4spstd4.aspxspstd4.gif
Header, Footer, 2 Columns, 4 Rows5spstd5.aspxspstd5.gif
Header, Footer, 4 Columns, Top Row6spstd6.aspxspstd6.gif
Left Column, Header, Footer, Top Row, 3 Columns7spstd7.aspxspstd7.gif
Right Column, Header, Footer, Top Row, 3 Columns8spstd8.aspxspstd8.gif

The system content pages can be found here:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\1033\STS\DOCTEMP\SMARTPGS\spstd?.aspx
Graphical view of the types can be found here:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\1033\IMAGES\spstd?.gif
This file links to spcf.aspx
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\1033\STS\DOCTEMP\SMARTPGS\_webpartpage.htm
This is the actual web part page creation form, which will list the 8 different options.
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\spcf.aspx

Which contains:
<select id="onetidWebPartPageTemplate" name="WebPartPageTemplate" size="8" onchange="DoTemplateOptionChange()"> 
   <option value="2" selected="true"><SharePoint:EncodedLiteral runat="server" text="<%$Resources:wss,webpagecreation_layout_option2%>" EncodeMethod='HtmlEncode'/></option> 
   <option value="1"><SharePoint:EncodedLiteral runat="server" text="<%$Resources:wss,webpagecreation_layout_option1%>" EncodeMethod='HtmlEncode'/></option> 
   <option value="3"><SharePoint:EncodedLiteral runat="server" text="<%$Resources:wss,webpagecreation_layout_option3%>" EncodeMethod='HtmlEncode'/></option> 
   <option value="4"><SharePoint:EncodedLiteral runat="server" text="<%$Resources:wss,webpagecreation_layout_option4%>" EncodeMethod='HtmlEncode'/></option> 
   <option value="5"><SharePoint:EncodedLiteral runat="server" text="<%$Resources:wss,webpagecreation_layout_option5%>" EncodeMethod='HtmlEncode'/></option> 
   <option value="6"><SharePoint:EncodedLiteral runat="server" text="<%$Resources:wss,webpagecreation_layout_option6%>" EncodeMethod='HtmlEncode'/></option> 
   <option value="7"><SharePoint:EncodedLiteral runat="server" text="<%$Resources:wss,webpagecreation_layout_option7%>" EncodeMethod='HtmlEncode'/></option> 
   <option value="8"><SharePoint:EncodedLiteral runat="server" text="<%$Resources:wss,webpagecreation_layout_option8%>" EncodeMethod='HtmlEncode'/></option> 
 </select>   
For some reason the different types are listed not in sequence, 1 and 2 are switched, sloppy  :)

Soulution 1:
If you want to include the left navigation bar in web part pages, then remove these lines from all of the spstd?.aspx files
...
<style type="text/css">
body #s4-leftpanel {
display:none;
}
.s4-ca {
margin-left:0px;
}
</style>
...
<asp:Content ContentPlaceHolderId="PlaceHolderLeftNavBar" runat="server"></asp:Content>
...
This will enable navigation, removing the style element is something new in sharepoint 2010.

If you want to customize a web part page like adding more web part zones etc you need to edit one of the spstd?.aspx pages.

The part which you need to change is this (located at the bottom in the file):

<table cellpadding="4" cellspacing="0" border="0" width="100%">
        <tr>
            <td id="_invisibleIfEmpty" name="_invisibleIfEmpty" colspan="3" valign="top" width="100%">
                <webpartpages:webpartzone runat="server" title="loc:Header" id="Header" frametype="TitleBarOnly" />
            </td>
        </tr>
        <tr>
            <td id="_invisibleIfEmpty" name="_invisibleIfEmpty" valign="top" height="100%">
                <webpartpages:webpartzone runat="server" title="loc:LeftColumn" id="LeftColumn" frametype="TitleBarOnly" />
            </td>
            <td id="_invisibleIfEmpty" name="_invisibleIfEmpty" valign="top" height="100%">
                <webpartpages:webpartzone runat="server" title="loc:MiddleColumn" id="MiddleColumn"
                    frametype="TitleBarOnly" />
            </td>
            <td id="_invisibleIfEmpty" name="_invisibleIfEmpty" valign="top" height="100%">
                <webpartpages:webpartzone runat="server" title="loc:RightColumn" id="RightColumn"
                    frametype="TitleBarOnly" />
            </td>
        </tr>
        <tr>
            <td id="_invisibleIfEmpty" name="_invisibleIfEmpty" colspan="3" valign="top" width="100%">
                <webpartpages:webpartzone runat="server" title="loc:Footer" id="Footer" frametype="TitleBarOnly" />
            </td>
        </tr>
        <script type="text/javascript" language="javascript">            if (typeof (MSOLayout_MakeInvisibleIfEmpty) == "function") { MSOLayout_MakeInvisibleIfEmpty(); }</script>
    </table>
This is the default layout for spstd2.aspx.
Now you can change the table, adding more webpart zones etc. Just remember to name the zones properly.

If you change the layout it's a good idea to change the spstd?.gif image too so that it will reflect the layout.

When you change the content page and save it, it will immediatly change in sharepoint for all of the pages that are based on the modified page.


Soulution 2:
There is also another option which kinda works but will require sharepoint designer everytime you create a new page.
It's not as smooth and integrated as editing the system files.

In sharepoint desinger 2010:
  • Open the site.
  • Select site pages, select file then new page from master.
  • Inside PlaceHolderMain, add a table, inside the cells add web part zones.
  • Save it as MyTemplate.aspx
  • Close sharepoint designer.
  • Open the site, then site actions, site settings.
  • Under galleries, site content types, Create.
  • Name: SpecialWebPartPage.
  • Select Parent content type from: Document Content Types
  • Parent Content Type: Web Part Page
  • Then press ok to save.
  • Select the SpecialWebPartPage.
  • Advanced settings.
  • Change document Template to: /Sites/YourSite/SitePages/MyTemplate.aspx
  • Save.
  • Create a document library with document template: Web Part Page
  • Go into library settings for the document library.
  • Advanced settings.
  • Enable allow management of content types.
  • Save.
  • Select Add from existing site content types.
  • Select the SpecialWebPartPage.
  • Save.
  • Select  new button order and default content type.
  • Select the new content type to visible position from top = 1 (which makes it default).
  • Select the old document type to visible = false.
  • Now view the document library, select documents, click new document.
  • Sharepoint designer will be started.
  • Now save the file in the appropriate folder in the document library.
  • View the page in the document library, select edit page, now you will see the web part zones as defined in your template.
And this page has the left navigation menu intact.

Works well except that you need sharepoint designer to save the new page.

It's probably possible to make it smoother if the template is located inside the TEMPLATE\LAYOUTS folder like the basic page template bpcf.aspx.
I did a couple of tests, and it certainly treats this template in the same way as basic page template,
but ran into several problems with the masterpage and includes which required a lot of modifications of the template.
Seems like there is a lot of logic in the basic page template that needs to be adopted.
But i haven't investigated this any further.

If anyone has a better solution for this let me know :)