Rahul Vartak

How to filter a date column on dates belonging to the current week - SQL

noreply@blogger.com (Rahul) — Thu, 23 Jan 2014 05:29:00 +0000

If you wish to filter a date column in SQL on dates that belong to the current week, below SQL can be used.

DECLARE @FIRSTDAY as DATE
DECLARE @LASTDAY as DATE

SET @FIRSTDAY = DATEADD(DD, DATEDIFF(DD, 0, DATEADD(DAY, 0 - (DATEPART(DW,GETDATE()) - 2), GETDATE())), 0) -- Monday
SET @LASTDAY = DATEADD(DD, DATEDIFF(DD, 0, DATEADD(DAY, 0 - (DATEPART(DW, GETDATE()) - 6), GETDATE())), 0) -- Friday

select * from table_name
where date_column_name >= @FIRSTDAY
and date_column_name <= @LASTDAY

2 and 6 in the query correspond to the day indexes in the week that you wish to consider as the start and end of the week respectively.
The functions DATEADD and DATEDIFF in the initial part of the query are required to remove the time component of the current date time.

Enable anonymous access in SharePoint 2010

noreply@blogger.com (Rahul) — Wed, 01 Jan 2014 02:59:00 +0000

- There is a basic way to let external users access specific lists or libraries in SharePoint using ‘Anonymous Access’ option.

First three steps in the below post describe how to configure it without writing any code.

http://www.codeproject.com/Articles/569965/GrantingplusAnonymousplusAccessplustoplusSharepoin

You can of course decide the exact permission anonymous users can have when using this approach.

Permission levels that you can set for anonymous users

- Second way is to extend the existing web-application of the Sharepoint site to another web application that supports anonymous access. When you extend the web app into a new zone, you actually create a separate IIS Web site to serve the same content, but with a unique URL and authentication type. In this case, the content creators, site administrators can add / modify contents in the main site and those will be reflected in the public facing site (as both the sites share a common content database).

Below is a post where you can find steps to implement this approach.

http://www.c-sharpcorner.com/uploadfile/bhushangawale/creating-anonymous-site-using-sharepoint-existing-web-application/

Note: One advantage is here is that you give the external users a different URL and can also get their requests on a different port number if required.

- The first two approaches don’t require the external users to login when accessing the site. If you wish to force the external users to register to your site and then login & use it, then you need to implement Form Based Authentication (FBA) in your extended web app.

Below is a 3^rd party component that allows you to manage user profiles. It consists of user management webparts such as membership request, change password, password recovery. This tool is free to use but they charge for support.
http://sharepoint2010fba.codeplex.com/

You can find the installation and configuration guide at the bottom of that page. This approach involves creating a SQL database which acts as a membership store.

Pass DataTable from one webpart to another using webpart connections

noreply@blogger.com (Rahul) — Mon, 11 Feb 2013 10:25:00 +0000

If you are developing webparts that need to be re-used, you may come across a scenario wherein you need to pass a DataTable from one webpart to another.

1. You may store it in a session variable and retrieve it in another webpart. OR,
2. Use Web part connections to pass the DataTable.

The below msdn link describes how to pass a set of data using the IWebpartTable interface.

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.webparts.iwebparttable.gettabledata.aspx

Browse to the GetTableData method within the Consumer webpart and make the following modifications..

private DataTable dTable = new DataTable();

private void GetTableData(object tableData)
{
DataRowCollection _tableRows = (DataRowCollection)tableData;
DataRow[] dRows = _tableRows.Cast().ToArray();
dTable = dRows.CopyToDataTable();
}

And there you have the DataTable ready to be used in the Consumer Web part !!

Teradata integration with SharePoint 2010

noreply@blogger.com (Rahul) — Tue, 15 Jan 2013 06:39:00 +0000

To display data from a Teradata database in SharePoint applications, we can use the following approach.

1. Install and configure SSRS in integrated mode with SharePoint

http://blog.cloudshare.com/2012/06/01/how-to-setup-ssrs-in-sharepoint-integrated-mode

2. With the help of Report Builder tool, use Teradata as a data source to create tabular reports or graphs

http://technet.microsoft.com/en-us/library/dd239351.aspx

http://www.mssqltips.com/sqlservertip/2691/sql-server-reporting-services-report-with-teradata-data-sources

3. Display the reports / graphs created with the help of Report Builder in SharePoint 2010 using Report Viewer Web part

http://msdn.microsoft.com/en-us/library/bb326347(v=sql.105).aspx

Other References:

Basics of SSRS in SharePoint Integrated mode

http://msdn.microsoft.com/en-us/library/ee210596(v=sql.105).aspx

Calendar Views: Hiding the time component in a single day event.

noreply@blogger.com (Rahul) — Sat, 28 Jan 2012 12:21:00 +0000

Time component being displayed in a single day event

In a calendar view, you may want to remove the time component displayed for single day events. Unlike MOSS 2007, the calendar view in SharePoint 2010 is displayed asynchronously after the page load. As a result, even if you try to fire a JavaScript /Jquery to remove these elements using the usual ‘_spBodyOnLoadFunctionNames.push('FunctionName');’ the code will not be able to find these elements simply because they would not be rendered by that time.

Mike Smith’s blog gives a wonderful solution to work around this problem. It worked for me !!

Migrating Existing Custom MOSS 2007 sites to SharePoint 2010

noreply@blogger.com (Rahul) — Mon, 19 Sep 2011 12:33:00 +0000

Various methods are documented on a high level by Microsoft here.

I preferred creating a site definition from the existing MOSS 2007 site and then restoring it in SharePoint 2010.

I have noted down the approach that I used in the process along with some references.

1. Create a new site definition (based on Team Site) from scratch: Objective here is to start with a minimal site on which our custom site can be built.

http://geekswithblogs.net/djacobus/archive/2008/10/27/126259.aspx

2. Using Solution Generator, Create list definitions for each list, include it in the project and then include it in your onet.xml

3. Deploy the site definition once in SharePoint 2007 and test if the site gets restored.

4. Convert & Deploy this site definition in SharePoint 2010.

Note:

The web-parts that might have been added through browsers in the web-part zones in SP 2007 will disappear. Those need to be added manually.

Look up columns disappear from the restored list in SP 2010

(Solution: The Field element is commented by Visual Studio. Need to uncomment it and replace the

ListId value with List URL (Relative))

Your existing master page in 2007 would not be useful in 2010, as it would not support ribbon controls.

o You may copy the contents of v4.master from SharePoint 2010 into your master page and build from there.

o Another approach would be to add the content placeholders required for SharePoint 2010 into your master page.

http://msdn.microsoft.com/en-us/library/ee539981%28office.14%29.aspx

5. Convert & add the custom solutions into the site template solution and re-deploy the solution.

6. Solutions developed in VS 2008 can be migrated to VS 2010 using the in-built converter in VS 2010.

7. WSP Builder for VS 2010 can be installed to create WSP’s for such solutions

8. <%@ Assembly Name="Microsoft.SharePoint.ApplicationPages, Version=12.0.0.0,
Culture=neutral,PublicKeyToken=71e9bce111e9429c" %>

The above reference needed to be changed as given below on all application pages.

<%@ Import Namespace="Microsoft.SharePoint.ApplicationPages" %>

9. An initialization feature receiver can be created and included in the ONET.XML file such that the feature gets activated on site provisioning. The feature can be used for foll. purposes..

A) Groups to be created with the site can be added here.

10. Finally it may be converted to a web template in SharePoint 2010 to ensure it stays compatible with
future versions of SharePoint.

(Refer an MSDN article here which indicates that ‘Site Definitions’ may no longer be supported in future versions of SharePoint)

Advantages of this method:

i) Minimum efforts in recreating the list schemas in 2010 due to use of Solution Generator

ii) List forms and views will also get carried along using Solution Generator.

iii) All the custom code can reside in a single solution along with the Site Definition creating only one
WSP.

Clone SharePoint List Items

noreply@blogger.com (Rahul) — Tue, 19 Jul 2011 07:18:00 +0000

A handy feature for your SharePoint lists would be ‘Cloning’ where the end users are allowed to Clone the existing list items.

This functionality can be achieved by providing a custom Ribbon button as shown below.

List View before Cloning

Clone Button in the ribbon

Alert confirming the Cloning process

Success message after cloning is done

List View after Cloning

Features:

A confirmation alert will be displayed to the users when they click on the ‘Clone’ button

The button would be enabled only if the user selects atleast one list item

A notification message is flashed at the top indicating whether the operation was successful or not.

Security: Only the users having the 'EditListItem' permission on the list will be able to Clone.

Limitations:
1. Attachments in the source item are not carried over.

How the functionality was achieved

1. A ‘CustomAction’ element was deployed and was attached to the ‘Generic Lists’ template
2. Permissions are setup by configuring the ‘CustomAction’ element.
3. The actual operation of cloning the list items was carried out using ECMA Script

Contact me on rahulyvartak@gmail.com to get the full solution.

Deploying SharePoint 2010 Sites using PowerShell

noreply@blogger.com (Rahul) — Tue, 14 Dec 2010 11:08:00 +0000

This post will cover the following details..

1. Deploying a complete site to another server (using Backup / Restore).

2. Deploying a complete site to another server (using Site Template as a WSP) .

3. Deploying increments done to another server (using Export / Import).

4. Deploying WSP Solutions to another server.

5. Upgrading the changes done to a WSP Solution into another server.

A. Deploying a complete site onto another server:

a. Consider a SharePoint site given below as the Source site where the development has taken place.

Figure 1: Source SharePoint site to be deployed to another web-app / server

b. As you can see, there are some minor changes that I have done to the default SharePoint site template. Also note the URL of the source site.

c. To deploy this site onto some other site, we first need to backup this site.

d. We can do so, either by using the STSADM commands or by using the PowerShell commands. As STSADM commands are scheduled to be deprecated, we will explore the PowerShell commands.

e. In order to combine all the PowerShell commands that would be used while deployment, they can be written in a single .ps1 file. Follow the steps below to create one.

f. Create a new file in Notepad and save it as a .ps1 file say ‘BackUpScript.ps1’.

g. Right click on this file and click on ‘Edit’ as shown below:

Figure 2: Edit the PS1 file

h. Paste the following commands in it

$file = "D:\Rahul\Backup.log"

start-transcript $file

trap { stop-transcript; break}

Backup-SPSite -Identity "http://SERVER_NAME:PORT_NO/sites/Example/" -Path "D:/Rahul/SiteBackup.cmp" -Force -NoSiteLock

stop-transcript

Here is what the script does….

$file is the variable where we define the path of the log file to be created.

The output of the commands present between the start-transcript and the stop-transcript keywords will be shown on the screen as well as logged in the log file.

The trap keyword is like an exception handling clause which continues to log the output in the log file even if an error is encountered.

The Identity value should be assigned the URL of the source site.

The Path value should be assigned the path where the log file would be stored

The PowerShell command in normal case locks the site out so that no changes are allowed to the site while the backup takes place. The –NoSiteLock parameter does not lock the site.

The file should look as shown below.

Figure 3: BackupScript.ps1

i. Open the ‘SharePoint 2010 Management Shell’ from the Windows menu.

ii. Go to the path where the .ps1 file exists and execute the script using the command given below.

./BackUpScript.ps1 (Refer snapshot below)

Figure 4: Running the BackupScript.ps1

Note that if you are running the command from a normal command prompt, you need to first load the PowerShell snapin. The SharePoint Management Shell does it for us.

iii. After the script gets executed, you will find two files created in the same folder.

The log file and the backup file in ’.cmp’ format will be created as shown below.

Figure 5: The log file and the backup file created

iv. Now it’s time to deploy the site onto another server.

v. Create a DeployScript.ps1 file on the same lines and paste the foll. code in it

$file = "D:\Rahul\Deployment.log"

start-transcript $file

trap { stop-transcript; break}

Remove-SPSite -Identity "http:// SERVER_NAME:PORT_NO /sites/ExampleClone"

Restore-SPSite -Identity "http:// SERVER_NAME:PORT_NO /sites/ExampleClone" -Path "D:/Rahul/ SiteBackup.cmp " -Force

stop-transcript

vi. Note that the SERVER_NAME:PORT_NO here should be the one of the destination site where we want the site to be deployed. The Remove-SPSite command is just an additional check just in case a previous version of the site already existed on the same URL

vii. Execute the script from ‘SharePoint 2010 Management Shell’ in the same way as we did for the BackUpScript. While the script gets executed, it will ask for confirmation both while removing the site (i.e. in case it exists on the destination site) and while restoring the backup. Enter A on both instances as shown below.

Figure 6: Confirmation asked before Removing / Restoring a site collection

viii. The destination site should now be ready as shown below.

For the sake of demonstration, I did it on the same server but on a different web application with a different port number. The same process can also be used to deploy it on a different server.

Figure 7: Site collection restored on the Destination web-app

B. Deploying the complete site onto another server (using Site Template as a WSP)

Another way to carry forward a complete site to another location is to create a Site template (which gets stored as a WSP in 2010) and then activate it on the destination server / web-app

a. Browse to the source site collection.
b. Click on ‘Site Actions -> Site Settings’ as shown below.

Figure 8: Site Actions -> Site Settings

c. Click on the ‘Save Site As Template’ link under the ‘Site Actions’ sub-menu as shown below.

Figure 9: 'Save Site As Template' link

d. Enter a template Name, a filename and check ‘Include Content’ if required as shown below. Click Ok.


Figure 10: 'Save as Template' Settings

e. You should get a Successfully Created message along with a link to the Solution Gallery

Figure 11: Success message on site template creation

f. Go to the Solution Gallery and click on the newly created template. Save it to a location on your machine.

g. Now, Go to Create Site Collection page from the Central Admin, and while selecting the template… Select ‘Select template later..’ option under the ‘Custom’ tab as shown below

Figure 12: Template Selection option while creating a site

h. Click OK.
i. Browse to the newly created site and go to its Solution gallery
j. Click on ‘Upload Solutions’ link under the Solutions tab

Figure 13: Upload Solution in the Solution Gallery

k. Browse the template saved before and click Ok
l. Click on ‘Activate Solution’ link from the pop-up window that appears

Figure 14: Activate the solution after it is uploaded

m. The Solution gallery should now show the solution as activated
n. Click on the Site Link at the top left and you should now be asked to select the template


Figure 15: Site Link

o. Select the Example Template that we uploaded as shown below. Click Ok


Figure 16: Select the custom site template

p. You will be asked to setup access Groups. Create new Groups or use existing groups and click OK


Figure 17: Set Up Groups for the restored site

q. The destination site would now be ready as shown below

Figure 18: Restored Site (after the uploaded site template is applied)

r. Given that you have a Site Template as a Sandboxed WSP ready, the process of creating a site from this custom site template can also be done using the following PowerShell script

$file = "D:\Rahul\RestoreWSPSiteTemplate.log"

start-transcript $file

trap { stop-transcript; break}

Remove-SPSite -Identity "http://DEST_SERVER_NAME:PORT_NO/sites/ExampleRestore"

New-SPSite "http:// DEST_SERVER_NAME:PORT_NO/sites/ExampleRestore" -OwnerAlias domain\rahul.vartak

Add-SPUserSolution -LiteralPath "D:\Rahul\ExampleTemplate.wsp" -Site "http:// DEST_SERVER_NAME:PORT_NO/sites/ExampleRestore"

Install-SPUserSolution –Identity ExampleTemplate.wsp -Site "http:// DEST_SERVER_NAME:PORT_NO/sites/ExampleRestore"

$x = Get-SPSite "http:// DEST_SERVER_NAME:PORT_NO/sites/ExampleRestore"

$y = $x.GetWebTemplates(1033) | Where{ $_.Title -eq "ExampleTemplate" }

[system.reflection.assembly]::LoadWithPartialName("Microsoft.Sharepoint")

$site= new-Object Microsoft.SharePoint.SPSite("http:// DEST_SERVER_NAME:PORT_NO/sites/ExampleRestore")

$site.OpenWeb().ApplyWebTemplate($y)

$site.Dispose()

stop-transcript

C. Deploying increments done onto another server (using Export / Import)

If your site is already deployed on the destination server, and you happen to create some lists / libraries or even a subsite; you can deploy the new lists, libraries or subsites with the help of Export / Import commands provided by PowerShell.

The same can also be used if some data has been added to the lists existing before and needs to be deployed. Usually applies to master or configuration lists.

Consider a SharePoint list ‘Knowledge Links’ in the source site that was created on the source site as shown below…

Figure 19: 'Knowledge Links' list to be carried over to another server / web-app

Now, if we have to push just this list onto another server which already has the same site deployed earlier without this list, we can do the following..

ix. Use the Export-SPWeb command as shown below.

Export-SPWeb -Identity "http://SOURCE_SERVER_NAME:PORT_NO/sites/Example" -Path "D:/Rahul/ListBackup.cmd" -ItemUrl "Lists/Knowledge Links" -IncludeUserSecurity -Force

The ItemUrl parameter should be given the relative URL of the list that we are trying to export.

The IncludeUserSecurity parameter ensures that permissions that we have set on the list are also carried over.

The Force parameter overwrites the backup file if another one already exists at the same folder location.

x. Run the script and you should find the exported list in specified folder in cmd format.

xi. We can now use the following Import-SPWeb command to import the list onto our destination server.

Import-SPWeb "http:// DEST_SERVER_NAME:PORT_NO /sites/ExampleClone " –Path "D:/Rahul/ListBackup.cmd" –IncludeUserSecurity

Notice that we don’t have to specify what item we are importing here (i.e. ItemUrl) as that information is already contained in the cmp file. The Import command is thus generic and can be used for a subsite or a library as well.

On running the import command, the list is generated as-is in the Destination web application as shown below.

Figure 20: 'Knowledge Links' list imported as-is onto another web-app / server

The Import-SPWeb command has several other switch parameters which may prove useful during deployment. Some of them are as follows.

o –ActivateSolutions : Adding this parameter will result in all user solutions being activated during import

o –IncludeUserCustomAction : This parameter ensures that all the custom user actions are also included in the import

More information can be found by running the following command in PowerShell.

Get-Help Import-SPWeb -full

The Export / Import Commands will also come to use in scenarios where the development requires the use of SharePoint Designer. In such cases, the customizations done through SPD can be carried over to different servers using Export / Import.

Note

: While restoring sites, lists, and libraries using the import-SPWeb cmdlet, the previous data will get overwritten unless the -UpdateVersions parameter is passed in the operation, which requires versioning to be enabled on the destination list or library. Using -UpdateVersions enables you to restore the information contained in the backup without overwriting new items created in the destination list or library

D: Deploying WSP Solutions to another server.

Deploying WSP Solutions has been made easier by PowerShell Commands. Find below the list of PowerShell commands which will be used extensively while deployment.

xii. Adding and Installing WSP Solutions

Add-SPSolution “D:\Rahul\SharePointSolution.wsp“
Install-SPSolution –Identity SharePointSolution.wsp –WebApplication http://SERVER_NAME:PORT_NO –GACDeployment

If you wanna add the solution as sandboxed, you can use the Add-SPUserSolution and Install-SPUserSolution commands instead. The GACDeployment parameter will not be used in that case.

xiii. To retract and remove a solution, we can use the following commands:

Uninstall-SPSolution –Identity SharePointSolution.wsp –WebApplication http:// SERVER_NAME:PORT_NO
Remove-SPSolution–Identity SharePointSolution.wsp

E: Upgrading changes done to the WSP Solutions.

To upgrade a solution, we can specify which solution is to be updated and with which new solution file:

Update-SPSolution –Identity SharePointSolution.wsp –LiteralPath “D:\Rahul\SharePointSolution.wsp” –GacDeployment

Using ASP.NET Chart Control in SharePoint 2007

noreply@blogger.com (Rahul) — Sat, 28 Aug 2010 08:56:00 +0000

An essential part of any SharePoint portal is Reporting. The Administrators of the site would always love to see different kinds of reports….specially those which will give them a fair idea of how their site is being used, how many users are using their site or for that matter…. what kind of content is present in their site. And what better way than giving them graphs of such statistics.

Many of such requirements are being fulfilled using SQL Server Reporting Services, or Out-Of-the-Box Site Usage summary.

What I would like to showcase in this blog is another way to implement Graphs … this one a very simpler and quicker way to create such graphs… and certainly a one which developers will love to exploit.

To begin with… install the following on your machine if you not have them already.

.NET 3.5 SP1: http://www.microsoft.com/downloads/details.aspx?FamilyID=ab99342f-5d1a-413d-8319-81da479ab0d7&displaylang=en

Microsoft Chart Controls Framework: http://www.microsoft.com/downloads/details.aspx?FamilyID=130f7986-bf49-4fe5-9ca8-910ae6ea442c&displaylang=en

Install the Visual Studio Add-in for MS Chart control.

http://www.microsoft.com/downloads/details.aspx?familyid=1D69CE13-E1E5-4315-825C-F14D33A303E9&displaylang=en

1. We can add the chart control in an application page created for SharePoint. However, before starting the implementation add the following tags inside the web.config file of your application.

a. Look for tags and place the following line inside it.

<add key="ChartImageHandler" value="storage=file;timeout=20;Url=~/chartTmpImages/;" />

b. Look for tags and place the following line inside it.

<add path="ChartImg.axd" verb="GET,HEAD" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false" />

2. Consider a SharePoint List ‘Knowledge Links’ as shown in the snap below.

A sample SharePoint list

Note that it has a column ‘Category’ that indicates which technology the record is related to.

Let’s say we need a chart that will display the count of records present for each category.

Here’s how we can achieve it.

Create a chart object as follows

System.Web.UI.DataVisualization.Charting.Chart Chart1 = new System.Web.UI.DataVisualization.Charting.Chart();

Set its Width ,Height, Palette properties.

Chart1.Width = 412;
Chart1.Height = 296;
Chart1.Palette = ChartColorPalette.BrightPastel;

Set its Render Type property

Chart1.RenderType = RenderType.ImageTag;

This will ensure that the chart is displayed as an image tag in the browser.

Set a temporary location on the server where the chart generated will be stored as an image.

Chart1.ImageLocation = "..\\..\\TempImages\\ChartPic_#SEQ(200,30)";

Add a title, chart area and a series to this chart.

Title t = new Title("List Data", Docking.Top, new System.Drawing.Font("Trebuchet MS", 14, System.Drawing.FontStyle.Bold), System.Drawing.Color.FromArgb(26, 59, 105));

Chart1.Titles.Add(t);
Chart1.ChartAreas.Add("Example Chart");
Chart1.Series.Add("Sample Series");

A Series is basically a collection of points which in our case will be the count of records belonging to each category.

Now it’s time to plot data values onto this chart.

We require the count of records in the list we created grouped by category.

There is no easy way to get this. I thought of getting this count with the help of some CAML query, but eventually ended up getting it through a simple DataTable object. 

Here’s how you can do it.

Get the list data in a DataTable first.

DataTable dtItems = SPContext.Current.Web.Lists["Knowledge Links"].Items.GetDataTable();

Now with the help of ToTable() function provided by the DataView object, we can split the records by grouping them by a particular column (in our case ‘Category’) and then have a separate column ‘Count’ store the number of records belonging to a particular Category. Just what we needed … isn’t it? :)

DataView dtView = new DataView(dtItems);
DataTable dtGroup = dtView.ToTable(true, new string[] { "Category" });
dtGroup.Columns.Add("Count");

foreach (DataRow drow in dtGroup.Rows)
{
drow["Count"] = dtItems.Compute("Count([Title])", "[Category] = '" + drow["Category"].ToString() + "'");
}

The Compute function is the one which does this trick for us.

Now that we already have all the values to be plotted, we can simply add them to the ‘Sample Series’ we created before.

foreach (DataRow dtRow in dtGroup.Rows)
{
DataPoint dPoint = new DataPoint();
dPoint.AxisLabel = dtRow["Category"].ToString();
dPoint.SetValueY(dtRow["Count"]);
Chart1.Series["Sample Series"].Points.Add(dPoint);
}

You can also play around with other properties of a Chart object such as its Border and legends.

Chart1.BorderSkin.SkinStyle = BorderSkinStyle.Emboss;
Chart1.BorderColor = System.Drawing.Color.FromArgb(26, 59, 105);
Chart1.BorderlineDashStyle = ChartDashStyle.Solid;
Chart1.BorderWidth = 2;

Chart1.Legends.Add("Sample Legend");
Chart1.Legends["Sample Legend"].Enabled = true;

Also don’t forget to set the page property of this Chart. It contains the refernce of the page in which this control is to be placed.

Chart1.Page = this;

And finally add this chart to some control such as an ASP Panel

pnlChartContainer.Controls.Add(Chart1);

What you see on the page then is a sleek graph plotting the count of list items belonging to each Category.

Bar graph displaying count of records belonging to each Category

By simply changing ChartType property of the Series object which is used, we can draw various types of graphs such as Pie, Bubble, Pyramid, Bubble, etc.

Add the following to convert your chart into a Pie Chart.

Chart2.Series["Sample Series"].ChartType = SeriesChartType.Pie;

Chart2.Series["Sample Series"]["PointWidth"] = "0.5";

Pie chart displaying count of records belonging to each Category

There is just so much you can do with this control. Things like having multiple Y-axis values on the same series, different types of charts, their color combinations are few things I suggest you try.

SharePoint 2007: Filtering List View web part with JavaScript

noreply@blogger.com (Rahul) — Sun, 01 Aug 2010 07:27:00 +0000

SharePoint Out-of-the-box views are extensively used in most of the portals developed.

Out of the box views are basically List View Web Parts which show the list items based on certain predefined configuration. E.g. The filter condition or sorting column. The columns to be displayed in the view are also pre-configured in the view settings.

Many a times we have a requirement wherein we are required to provide the users with the ability to filter on these views as per their wish.

One way is to create different views and pre-configure the views with all the possible filter conditions that are to be provided.

There is an easier and a user- friendly way by which we can achieve the filtering of these Out of the box list views.

Consider the following SharePoint list

A simple SharePoint list

It has a column called ‘Status’ which has a value ‘Approved’ if the record is approved and blank if the record is not Approved.

The list view web parts are pre-configured to accept certain query strings based on which they can filter themselves.

The query strings can be in the following format:

Somepage.aspx? FilterField1=Column_Name_1&FilterValue1=Column_Value_1&FilterField2=Column_Name_2&FilterValue2=Column_Value_2

Here FilterField1 , FilterField2 are names of the columns while FilterValue1 and FilterValue2 are the values for those respective columns on which the filtering needs to be done.

The FilterValues here can also be blank.

To insert a simple filter right at the top of this list view,

Add a CEWP web-part above the list and add the following HTML code in it.

<table width="40%">
<tr>
<td><b>Filter: </b></td>
<td>
<input type="checkbox" id="inpApproved" name="chckApproved" value="Approved" title="Approved" onclick="javascript:reloadFilter(this, 'approved');" /> Approved
</td>
<td>
<input type="checkbox" id="inpNotApproved" name="chckNotApproved" value="NotApproved" title="Not Approved" onclick="javascript:reloadFilter(this, 'notapproved');" /> Not Approved
</td>
<td></td>
</tr>
</table>

This will create two simple checkboxes as an interface for the user to select the filter criteria.
Now let’s define the JavaScript function that will reload the page with the required query string parameters as per the filter criteria selected by the user.

<script type="text/javascript" language='javascript'>

function reloadFilter(submitObject, type)
{
var url = window.location.toString();
var startURL = url.substring(0, url.indexOf('.aspx')+5);
var submitValue = submitObject.value;

if(submitValue == 'Approved')
{
if(submitObject.checked == true)
{
if(document.getElementById('inpNotApproved').checked == false)
{
window.location = startURL + '?FilterField1=Status&FilterValue1=Approved';
}
else
{
window.location = startURL;
}
}
else if(submitObject.checked == false)
{
if(document.getElementById('inpNotApproved').checked == true)
{
window.location = startURL + '?FilterField1=Status&FilterValue1=';
}
else
{
window.location = startURL + '?FilterField1=Status&FilterValue1=None';
}
}
}
else if(submitValue == 'NotApproved')
{
if(submitObject.checked == true)
{
if(document.getElementById('inpApproved').checked == false)
{
window.location = startURL + '?FilterField1=Status&FilterValue1=';
}
else
{
window.location = startURL;
}
}
else if(submitObject.checked == false)
{
if(document.getElementById('inpApproved').checked == true)
{
window.location = startURL + '?FilterField1=Status&FilterValue1=Approved';
}
else
{
window.location = startURL + '?FilterField1=Status&FilterValue1=None';
}
}
}
}

</script>

Now, that we have the page reloading JS ready, lets maintain the state of the checkboxes when the page refreshes with the help of the following JS function.
<script type="text/javascript" language='javascript'>

function LoadCheckBoxes()
{
var url = window.location.toString();
var status = '';
url.match(/\?(.+)$/);
var params = RegExp.$1;
var params = params.split("&");
var queryStringList = {};
var selectionFlag = false;

for(var i=0;i<params.length;i++)
{
if(params[i].indexOf('FilterField1=Status') != -1 )
{
status = params[i+1].substring(params[i+1].indexOf('=')+1);

if(status== 'Approved')
{
document.getElementById('inpApproved').checked = true;
document.getElementById('inpNotApproved').checked = false;
}
else if(status== '')
{
document.getElementById('inpApproved').checked = false;
document.getElementById('inpNotApproved').checked = true;
}
else if(status == 'None')
{
document.getElementById('inpApproved').checked = false;
document.getElementById('inpNotApproved').checked = false;
}
selectionFlag = true;

}
}

if(!selectionFlag)
{
document.getElementById('inpApproved').checked = true;
document.getElementById('inpNotApproved').checked = true;
}
}

_spBodyOnLoadFunctionNames.push("LoadCheckBoxes");

</script>

We make sure this function gets called on page load with the help of the SharePoint in-built _spBodyOnLoadFunctionNames.push function

Following is the result of this approach

When both checkboxes are checked

When only the approved checkbox is checked

When only the Not Approved checkbox is checked

When both the checkboxes are unchecked

SharePoint 2007: State Machine Workflows

noreply@blogger.com (Rahul) — Sat, 10 Jul 2010 15:57:00 +0000

Scenario: I have a SharePoint list ‘Knowledge Links’ which is open to all users. I wish to create a simple approval workflow wherein whenever a new ‘Knowledge Link’ is submitted by any user, a task would be created and assigned to a group ‘KT_Approvers’.

An Admin would review the link submitted. If he is satisfied with the link provided, he would mark the link as 100% complete by editing the task created for the same link.
Once the task is marked as 100% complete, the workflow will change the Status of the ‘Knowledge Link’ item as ‘Approved’ and the link can then be used in various views throughout the site. If the task is partially complete, the workflow will set the status to ‘Approval in Progress’.

1. Create a WSP Solution. Make sure you select ‘.NET Framework 3.0’

2. Add a ‘State Machine Workflow with Feature’ item to the solution.

Note that Workflow features can be defined only at the site collection (i.e. Scope = Site)

3. The workflow solution requires the following three DLLs as reference which may not be present by default..

4. Add the three workflow DLLs to be required by the solution if they are not present by default.

a. These DLLS can be found at the following location

C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\

b. To be able to refer to these DLLs , the solution should be using the .NET framework 3.0 or above. In other cases, you may find the following error in the workflow designer file.

c. The error can be removed by simply editing the csproj file as follows.

Close the Visual Studio solution currently in use and then Open the .csproj file present in the solution folder (shown below) in Notepad.

Find the following line.

Replace the value inside the <ProjectTypeGuids> tag with the following line.

{F8810EC1-6754-47FC-A15F-DFABD2E3FA90};{14822709-B5A1-4724-98CA-57A101D1B079};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}

and re-open the solution in Visual Studio.

5. Now, add a new feature (a simple feature with receiver) which will associate this workflow to a list. Also called as Stapling feature. Stapling feature can be kept at the scope Web if required.

6. Remove the following lines from the elements.xml file in the workflow feature.

7. Enter the following code in the Feature Activated event of the Stapling feature.

public override void FeatureActivated(SPFeatureReceiverProperties properties)

{

SPList myList = null; // List to associate workflow to

string myListName = null; // My list name

SPList historyList = null; // Workflow history list

SPList taskList = null; // Workflow tasks list

string workflowTemplateGuid = null; // Workflow template Guid

SPWorkflowTemplate workflowTemplate = null; // Workflow template

SPWorkflowAssociation workflowAssociation = null; // Workflow association

string workflowAssocName = null; // Workflow association name

SPWeb spWeb = SPContext.Current.Web;

myListName = "Knowledge Links";

workflowAssocName = "KTStateMachine";

myList = spWeb.Lists[myListName];

workflowTemplate = spWeb.WorkflowTemplates.GetTemplateByName("KTStateMachine", System.Globalization.CultureInfo.CurrentCulture);

try

{

historyList = spWeb.Lists["KT History"];

}

catch (ArgumentException exc)

{

// Create workflow history list

Guid listGuid = spWeb.Lists.Add("KT History", "", SPListTemplateType.WorkflowHistory);

historyList = spWeb.Lists[listGuid];

historyList.Hidden = true;

historyList.Update();

}

try

{

taskList = spWeb.Lists["Workflow Tasks"];

}

catch (ArgumentException exc)

{

// Create workflow tasks list

Guid listGuid = spWeb.Lists.Add("Workflow Tasks", "", SPListTemplateType.Tasks);

taskList = spWeb.Lists[listGuid];

taskList.Hidden = true;

taskList.Update();

}

// Allow unsafe updates on web

spWeb.AllowUnsafeUpdates = true;

try

{

// Create workflow association

if (null != workflowTemplate)

workflowAssociation = SPWorkflowAssociation.CreateListAssociation(workflowTemplate, workflowAssocName, taskList, historyList);

// Set workflow parameters

workflowAssociation.AllowManual = false;

workflowAssociation.AutoStartCreate = true;

workflowAssociation.AutoStartChange = false;

// Add workflow association to my list

if (null != myList)

{

SPWorkflowAssociation oldAssociation = myList.WorkflowAssociations.GetAssociationByName(workflowAssocName, System.Globalization.CultureInfo.CurrentCulture);

if (null != oldAssociation)

{

SPSecurity.RunWithElevatedPrivileges(delegate()

{

myList.RemoveWorkflowAssociation(oldAssociation);

});

}

myList.AddWorkflowAssociation(workflowAssociation);

}

// Enable workflow

workflowAssociation.Enabled = true;

}

finally

{

spWeb.AllowUnsafeUpdates = false;

}

}

Verify you made the following changes

a. Enter the correct list name on which the workflow is going to be associated

b. Enter the workflow association name. You can find it in the elements.xml file of the Workflow feature

8. Go to the designer of the workflow created.

9. Rename the event driven activity created by default to ‘InitialStateActivity’

10. Double click on the activity to see the activity design view as shown

11. Rename the first event created by default to onKnowledgeLinkSubmitted

12. In the properties window, besides ‘Invoked’, define a new function ‘onLinkSubmitted_Invoked’ by simply typing the name there and press enter. An event handler will automatically be created. This is the event where you can initialize any class variables you might have created with the properties of the item which triggered this workflow. (E.g. title).

13. Add the code given below in this event.

private void onLinkSubmitted_Invoked(object sender, ExternalDataEventArgs e)

{

title = workflowProperties.Item["Title"].ToString();

}

14. Add a Create Task activity

15. Rename it to createApprovalTask

16. Enter a new correlation token ‘taskToken’. Set its owner activity name to ‘KTStateMachine’

17. Define an event ‘createApprovalTask_Invoked’ for method invoking

18. Bind the Taskid property to a new member (property) by clicking at the button given alongside. Select the options and enter values as shown below later.

19. Similarly, bind the TaskProperties to a new member (field).

20. Enter the following code in ‘createApprovalTask_Invoked’

private void createApprovalTask_Invoked(object sender, EventArgs e)

{

TaskId = Guid.NewGuid();

TaskProperties.TaskType = 1;

TaskProperties.Title = title;

TaskProperties.StartDate = DateTime.Today;

TaskProperties.PercentComplete = 0.0f;

TaskProperties.AssignedTo = "KT_Approvers";

}

21. Go to the designer view and click on the ‘KTStateMachine’ link in the top navigation.

22. Add four more states as shown below and rename them appropriately.

23. Now double click the Initial state Activity and add a Set State activity as shown

24. The workflow should look like this now from the main design view

25. In the State KTLinkTaskFormed, add an activity onKTLinkTaskFormed.

26. Inside this activity, add an onTaskChanged box and set its properties as given below.

27. Bind the AfterProperties to a new property as shown below

Do exactly the same for Before Properties.

28. Set its correlation token to taskToken and Owner Activity Name to KTStateMachine

29. Define the Invoked event ‘onLinkSubmitted_Invoked’

30. Bind the Task ID to the existing member tasked created before

31. The properties should like these

32. Create a IfElse Activity block

33. In the properties of the IfElseBranch Activity , set the properties as shown below

a. The condition isApproved will create a function in code which returns true if that condition is satisfied

b. Do the same for the other IfElseBranch Activity.

34. Add the following code inside isApproved & isRejected function

private void isApproved(object sender, ConditionalEventArgs e)

{

if(onTaskChanged_AfterProperties.PercentComplete == 1.0f)

e.Result = true;

}

private void isRejected(object sender, ConditionalEventArgs e)

{

e.Result = true;

}

35. Under each IfElseBranch activity, add a Set State Activity which will send the workflow to the appropriate state as shown below.

36. Go to the main workflow design view. You should see it as show below.

37. Add two state initiation activities, one each inside KTLinkApproved and KTLinkRejected.

38. Rename them as stateInitializationApproved and stateInitializationRejected

39. Go inside ‘stateInitializationApproved’ activity

a. Add an ‘Update Task’ activity and set its properties as shown

b. Note: The TaskId property here should be bind to the existing member TaskId.

The TaskProperties property here should be bind to a new property ‘updateTaskonApproved_TaskProperties1’ as shown in the figure below.

c. Also add a Set State event taking the state to ‘KTLinkReviewComplete’ state

40. Go inside ‘stateInitializationRejected’ activity and repeat the steps

41. Right Click the ‘KTLinkReviewComplete’ state and set it as completed state.

42. Finally the workflow should look like this.

43. Copy the following code in the update task events generated in the code file

private void updateTaskAfterApproved(object sender, EventArgs e)

{

workflowProperties.Item["Status"] = "Approved";

workflowProperties.Item.Update();

}

private void updateTaskAfterRejected(object sender, EventArgs e)

{

workflowProperties.Item["Status"] = "Approval In Progress";

workflowProperties.Item.Update();

}

44. Build and deploy the solution.

45. Go to the Site collection features and activate the ‘KTStateMachine’ feature.

46. Go to the Site features and activate the ‘StaplingFeature’ .

47. Go to the ‘Knowledge Links’ list in your site.

48. Create a new item as shown below.

49. On creation, we can see the workflow being ‘In Progress’.

50. You will see a workflow task item being created in the ‘Workflow Tasks’ list as shown below.

51. Edit the item, and make its percent complete to 100%.

52. Now go back to the ‘Knowledge Links’ list. The item will have its ‘Status’ column marked as ‘Approved’

53. Note that if the task is changed but not marked as complete, the item will have the ‘Status’ column populated as ‘Approval in Progress’

There are many many more things to explore in SharePoint State Machine workflows. Will update the blog soon......

JS Lint Addin for Visual Studio

noreply@blogger.com (Rahul) — Wed, 09 Jun 2010 15:26:00 +0000

A simple way to catch JSlint warnings and errors during development.

Integrate this addin in Visual Studio and you can easily fix them while building the solution.

http://jslint.codeplex.com/

About me

noreply@blogger.com (Rahul) — Sun, 09 May 2010 03:40:00 +0000

Rahul Vartak.
Software Developer
MOSS 2007 / 2010, ASP.NET, HTML, JavaScript, Oracle, SQL, SSIS
Experience: 3+ Years