SharePoint grouping by week

Overview
In SharePoint 2007, to achieve grouping list items by week, a week start or week end date is required. Calculated columns can be used to work out the start day of the week and end day of the week.

Week starting Monday

=[Created]-WEEKDAY([Created],2)+1

Week starting Sunday

=[Created]-WEEKDAY([Created])+1

Week ending Saturday

=[Created]+7-WEEKDAY([Created])

Week ending Sunday

=[Created]+7-WEEKDAY([Created],2)

Week ending Monday

=[Created]-WEEKDAY([Created],2)+1+IF(WEEKDAY([Created],2)=1,0,7)

Screen shot:

List view issue when grouping by date
SharePoint 2007 has many glitches, grouping dates on the list view is one them. If you want to create a weekly list view using the week start or week end column that you created in the previoius section, you will likely see this error:



To work around this there are two options:

Option 1 - Expanded by default:

• Edit your list view, under the Group By section, select Expanded for "By default, show grouping:".



• This is not ideal since, everything is expanded which pretty much defeated the purpose of grouping.



• To further work around this, you can add a jQuery to collapse the groups. The downside is losing the SharePoint's ability to remember the last expand and collapse groups.



• Refer to a post by Spyral Out for more information and suggests of other alternatives.

The jQuery

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js"></script>
<script type="text/javascript">
function collapseGroups() {
    $("img[src='/_layouts/images/minus.gif']:visible").parent().click();
}

_spBodyOnLoadFunctionNames.push("collapseGroups");
</script>

Option 2 - Group by Text field
The other option is to change the week starting or ending calculated columns to a text field. However, the downside is that the date needs to be formatted as "yyyy/MM/dd" in order for sorting to work. For example:

=TEXT([Created]-WEEKDAY([Created],2)+1,"yyyy/MM/dd")

Conculsion
Both options have their pros and cons, it will be depend on what works for your users.

Initialise a class from string variable in C#

Ever wonder if it is possible initialise a class from a string in C#? Although it is not as simple as in PHP but it's possible:

PHP

<?php
    $classname = "test";

    $oTest = new $classname();

    class test{ ... }
?>

C#
The class

namespace CSharpApp
{
    public class Test
    {
        public void Run() { ... }
    }
}

The code

string oName = "CSharpApp.Test";
// To create an instance
Type typeObj = Type.GetType(oName);
object instanceObj = Activator.CreateInstance(typeObj);
// To call a method from the class
typeObj.InvokeMember("Run",
    BindingFlags.InvokeMethod  BindingFlags.Instance  BindingFlags.Public,
    null, instMerchant, null);

A very good explaination and examples, such as calling different types of methods in the class, can be found here:
http://www.csharp-examples.net/reflection-examples/

SSIS: Handling failure in Send Mail Task within a FOREACH loop container

An issue had come up recently that the customer reports that sent three times a day was not delivered. After some investigation, I have realised one of the email address in the database was wrong hence causing the process failed half way through. Hence half of the clients missed out on the report.

Surely, it was a user typo, however, the process should be smart enough to report the error and carry on with the rest of the clients having good email addresses. Therefore, two improvements are put forward to be implemented in the SSIS process:

1. Report the error by email
2. Continue sending emails to other unaffected clients

After some research on Google, I have found two links that were very useful to help me solve this problem.

Report the error by email
This is in fact very simple:

1. Select the Send Mail Task, then click the Event Handler tab;
2. Select the OnError event, then click the link in the middle of the screen to create the event;
3. Add a Send Mail Task to the OnError event to send email notification when an error occur.
4. That's all, do the next step if you do not want the error to interrupt the looping.

Web reference: http://stackoverflow.com/questions/5433132/handling-failure-of-the-send-mail-task-in-ssis

Continue sending emails to other unaffected clients

While the first step was very simple, the second step is even simpler:

1. To Prevent the error from going further up the chain, open up the System Variables (i.e. SSIS menu > Variables, then select Show System Variables) from within the OnError event handler of the Send Mail Task;
2. Find and change the Propogate property’s value to False;
3. That's all you need to do, do the same steps to all the tasks within the FOREACH loop container to prevent an interruption to the loop.

Web reference: http://sqlblog.com/blogs/rushabh_mehta/archive/2008/04/24/gracefully-handing-task-error-in-ssis-package.aspx

Call a function from string variable in PERL, PHP and C#

PERL

my $function_name = "hello";
&$function_name(10);
function hello() {
  my $param = shift;
  print "hello $param\n";
}

PHP

$function_name = "hello";
$function_name();
function hello($param) {
  echo "hello $param\n";
}

C#

class Program
{
  static void Main(string[] args)
  {
    Type type = typeof(MyReflectionClass);
    MethodInfo method = type.GetMethod("Hello");
    MyReflectionClass c = new MyReflectionClass();
    method.Invoke(c, null);
  }
}

public class MyReflectionClass
{
  public void Hello(int num)
  {
    Console.WriteLine("hello {0}", num.ToString());
  }
}

Develop a Windows Service in C#

Have you ever wanted to utilise the Windows service to run background tasks on your Windows server? Use the following starter guides to help you kick start your Windows Service projects.

For Visual Studio 2005. A guide posted by The Reddest to creating a simple Windows Service in C# will help get you started.
http://www.switchonthecode.com/tutorials/creating-a-simple-windows-service-in-csharp

For Visual Studio 2008. The MSDN has a good walkthrough guide here:
http://msdn.microsoft.com/en-us/library/zt39148a(v=vs.80).aspx

SharePoint KPI list - Click on link returns error

The Problem

When click on a certain link from the KPI dashboard list (like above), an error "Incorrect function. (Exception from HRESULT: 0x80070001)" was returned.



The Solution
After some research I have found a helpful post which gives me an answer. Basically, to fix the issue, a Details Link is needed to be specified. Edit that KPI list item, a collapsed section "Details Link" can be located near the bottom of the edit screen. Specify details page to go to when the KPI list item link is being clicked.


Observation
I have also noticed that this issue seem to be affecting the KPI list item that is linking to a SharePoint list. The details link does not need to be specified for KPI list item that is linked to an Excel spreadsheet or manually entered.

How to find all the column name from a database?

This helpful query will give you the list of tables:

SELECT t.name AS table_name,
  SCHEMA_NAME(schema_id) AS schema_name,
  c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name;

Web Reference
Ramani Sandeep's Blog: Query to Find Column Name From All Tables