Friday 14 December 2007

Telerik RadGrid - Creating A Grouped Grid that allows sorting and editing

Today, I wanted to get the Telerik RadGrid (http://www.telerik.com/products/aspnet/controls/grid/overview.aspx) 5.0 edit functionality working in conjunction with its grouping functionality.

Background: I have been temporarily seconded from the Lend Lease Property Pipeline project onto a high-profile BI project that has several dashboards for KPIs used by management (hosted within MOSS 2007 - parameters are passed into my page via Request.Params). I have a few management-style pages that need to be created - but they have to be editable so this excludes Reporting Services from the equation.

One of the requirements of this particular page was to have automatic grouping of risks and opportunities into different sections of the grid. However, when I set up the grid my code triggered the edit event, I get the following error:

[InvalidConstraintException: Cannot have 0 columns.]

Google only returned 2 results on this error - and they didn't give me a direct solution. After looking through the Telerik site, I found a few clues which pointed to the fact I had to have both the "Select Fields" and "GroupByFields" Set in the grid. Here is my current (unfinished) version of the page) which show that magic combination of code hacks and declarative settings to get the grid to work in all its Databound AJAX-enabled editable groupable glory!


Code:





<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="RiskOpportunity.aspx.cs"
Inherits="ddkonline.MRP.Client.RiskOpportunity" %>


<%@ Register Assembly="Microsoft.Practices.Web.UI.WebControls" Namespace="Microsoft.Practices.Web.UI.WebControls"
TagPrefix="pp" %>

<%@ Register Assembly="RadGrid.Net2" Namespace="Telerik.WebControls" TagPrefix="radG" %>
<%@ Register Assembly="RadCalendar.Net2" Namespace="Telerik.WebControls" TagPrefix="radCln" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Risks and Opportunities</title>
</head>
<body>
<form id="form1" runat="server">
<pp:ObjectContainerDataSource ID="OdsRiskOpportunityDataSource" runat="server" DataObjectTypeName="ddkonline.MRP.Service.Persistence.Entity.ODS_Risk_Opportunity"
OnInserted="OdsRiskOpportunityDataSource_Inserted" OnUpdated="OdsRiskOpportunityDataSource_Updated"
OnSelecting="OdsRiskOpportunityDataSource_Selecting" />
<asp:LinqDataSource ID="BusinessUnitDataSource" runat="server" ContextTypeName="ddkonline.MRP.Service.Persistence.Entity.MRPDataContext"
OrderBy="Sort_Order" TableName="Dim_Business_Hierarchy_PCs" Where="ETL_Region == @ETL_Region &amp;&amp; Member_Level == @Member_Level"
OnSelecting="BusinessUnitDataSource_Selecting">
<WhereParameters>
<asp:Parameter Name="ETL_Region" Type="String" />
<asp:Parameter DefaultValue="&quot;3&quot;" Name="Member_Level" Type="Int32" />
</WhereParameters>
</asp:LinqDataSource>
<asp:LinqDataSource ID="ReportingCurrencyDataSource" runat="server" ContextTypeName="ddkonline.MRP.Service.Persistence.Entity.MRPDataContext"
OrderBy="Currency_Name" Select="new (Currency_Name, Currency_Code, Reporting_Currency_SKEY)"
TableName="Dim_Reporting_Currencies">
</asp:LinqDataSource>
<asp:LinqDataSource ID="RiskConsequenceDataSource" runat="server" ContextTypeName="ddkonline.MRP.Service.Persistence.Entity.MRPDataContext"
OrderBy="Sort_Order" Select="new (Lookup_SKEY, Lookup_Value)" TableName="Dim_Lookups"
Where="Type == @Type">
<WhereParameters>
<asp:Parameter DefaultValue="RiskConsequence" Name="Type" Type="String" />
</WhereParameters>
</asp:LinqDataSource>
<asp:LinqDataSource ID="StatusDataSource" runat="server" ContextTypeName="ddkonline.MRP.Service.Persistence.Entity.MRPDataContext"
OrderBy="Sort_Order" Select="new (Lookup_SKEY, Lookup_Value)" TableName="Dim_Lookups"
Where="Type == @Type">
<WhereParameters>
<asp:Parameter DefaultValue="Status" Name="Type" Type="String" />
</WhereParameters>
</asp:LinqDataSource>
<%--<asp:LinqDataSource ID="peopleDataSource" runat="server"
ContextTypeName="ddkonline.MRP.Service.Persistence.Entity.MRPDataContext"
OrderBy="ETL_Extract_User" Select="new (People_SKEY, ETL_Extract_User)"
TableName="Dim_People">
</asp:LinqDataSource>--%>
<div>
<radG:RadGrid ID="BusinessRiskGrid" ShowGroupPanel="False" runat="server" AutoGenerateColumns="False"
DataSourceID="OdsRiskOpportunityDataSource" GridLines="None" OnItemCommand="BusinessRiskGrid_ItemCommand"
Skin="Windows" AllowSorting="True" OnItemDataBound="BusinessRiskGrid_ItemDataBound"
GroupingEnabled="true" EnableAJAX="true" EnableAJAXLoadingTemplate="true" LoadingTemplateTransparency="25">
<ExportSettings>
<Pdf PageWidth="8.5in" PageHeight="11in" PageTopMargin="" PageBottomMargin="" PageLeftMargin=""
PageRightMargin="" PageHeaderMargin="" PageFooterMargin=""></Pdf>
</ExportSettings>
<MasterTableView AllowAutomaticInserts="True" AllowAutomaticUpdates="True" CommandItemDisplay="Bottom"
DataSourceID="OdsRiskOpportunityDataSource" EditMode="InPlace" DataKeyNames="Id">
<GroupByExpressions>
<radG:GridGroupByExpression>
<SelectFields>
<radG:GridGroupByField FieldAlias="IsRisk" FieldName="IsRisk"></radG:GridGroupByField>
</SelectFields>
<GroupByFields>
<radG:GridGroupByField FieldName="IsRisk"></radG:GridGroupByField>
</GroupByFields>
</radG:GridGroupByExpression>
</GroupByExpressions>
<CommandItemTemplate>
<table width="100%">
<tr>
<td width="50%">
<asp:LinkButton ID="AddNewLink" runat="server" CommandName="InitInsert">
<img style="border:0px" alt="" src="Image/Insert.gif" /> Add new Record</asp:LinkButton>
</td>
<td width="50%" align="right">
<asp:HyperLink ID="PrintLink" runat="server" NavigateUrl="http://172.24.27.157/ReportserverDEV?%2fBLL+MRP%2fBusiness+Plan+Status&amp;period=20071101&amp;hierarchy=838&amp;businessArea=6">
<img style="border:0px" alt="" src="Image/Insert.gif"/> Print
</asp:HyperLink>
</td>
</tr>
</table>
</CommandItemTemplate>
<Columns>
<%--Issue Detail --%>
<radG:GridTemplateColumn DataField="IssueDetail" HeaderText="Issue Details" SortExpression="IssueDetail"
UniqueName="IssueDetail" ItemStyle-Width="5pc">
<EditItemTemplate>
<asp:TextBox ID="IssueDetailTextBox" runat="server" Text='<%# Bind("IssueDetail")%>' />
<span style="color: Red"></span>
<asp:RequiredFieldValidator ID="IssueDetailRequiredFieldValidator" ControlToValidate="IssueDetailTextBox"
Display="Dynamic" ErrorMessage="* This field is required" runat="server" />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="IssueDetailLabel" runat="server"><%# Eval("IssueDetail")%></asp:Label>
</ItemTemplate>
</radG:GridTemplateColumn>
<%--Business Unit--%>
<%--
<radG:GridDropDownColumn DataField="BusinessUnitId" DataSourceID="BusinessUnitDataSource"
HeaderText="Business Unit" ListTextField="Lookup_Value" ListValueField="BusinessUnitId"
UniqueName="BusinessUnitId">
</radG:GridDropDownColumn>
--%>
<%--Reporting Currency --%>
<radG:GridDropDownColumn DataField="Reporting_Currency_SKEY" DataSourceID="ReportingCurrencyDataSource"
ItemStyle-Width="5pc" HeaderText="Reporting Currency" ListTextField="Currency_Name"
ListValueField="Reporting_Currency_SKEY" UniqueName="Reporting_Currency_SKEY"
Groupable="True" GroupByExpression="Reporting_Currency_SKEY Group By Reporting_Currency_SKEY">
</radG:GridDropDownColumn>
<%--PBT--%>
<radG:GridTemplateColumn DataField="PBT" HeaderText="PBT" UniqueName="PBT" SortExpression="PBT"
ItemStyle-Width="1pc">
<EditItemTemplate>
<asp:TextBox ID="PBTTextBox" runat="server" Text='<%# Bind("PBT", "{0:N1}")%>' />
<span style="color: Red"></span>
<asp:RequiredFieldValidator ID="PBTRequiredFieldValidator" ControlToValidate="PBTTextBox"
ErrorMessage="* This field is required" runat="server" Display="Dynamic" />
<asp:RegularExpressionValidator ID="PBTRegularExpressionValidator" ControlToValidate="PBTTextBox"
ValidationExpression="-{0,1}\d*\.{0,1}\d{0,1}" ErrorMessage="* You must enter a valid value (1 decimal place)"
runat="server" Display="Dynamic" />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="PBTLabel" runat="server"><%# Eval("PBT", "{0:N1}")%></asp:Label>
</ItemTemplate>
</radG:GridTemplateColumn>
<%--PAT--%>
<radG:GridTemplateColumn DataField="PAT" HeaderText="PAT" UniqueName="PAT" SortExpression="PAT"
ItemStyle-Width="1pc">
<EditItemTemplate>
<asp:TextBox ID="PATTextBox" runat="server" Text='<%# Bind("PAT", "{0:N1}")%>' />
<span style="color: Red"></span>
<asp:RequiredFieldValidator ID="PATRequiredFieldValidator" ControlToValidate="PATTextBox"
ErrorMessage="* This field is required" runat="server" Display="Dynamic" />
<asp:RegularExpressionValidator ID="PATRegularExpressionValidator" ControlToValidate="PATTextBox"
ValidationExpression="-{0,1}\d*\.{0,1}\d{0,1}" ErrorMessage="* You must enter a valid value (1 decimal place)"
runat="server" Display="Dynamic" />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="PATLabel" runat="server"><%# Eval("PAT","{0:N1}")%></asp:Label>
</ItemTemplate>
</radG:GridTemplateColumn>
<%--Probability--%>
<radG:GridTemplateColumn DataField="Probability" HeaderText="% Probability" UniqueName="Probability"
SortExpression="Probability" ItemStyle-Width="5pc">
<EditItemTemplate>
<asp:TextBox ID="ProbabilityTextBox" runat="server" Text='<%# Bind("Probability")%>' />
<span style="color: Red"></span>
<asp:RequiredFieldValidator ID="ProbabilityRequiredFieldValidator" ControlToValidate="ProbabilityTextBox"
ErrorMessage="* This field is required" runat="server" Display="Dynamic" />
<asp:RegularExpressionValidator ID="ProbabilityRegularExpressionValidator" ControlToValidate="ProbabilityTextBox"
ValidationExpression="-{0,1}\d*\.{0,1}\d{0,1}" ErrorMessage="* You must enter a numeric value" runat="server"
Display="Dynamic" />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="ProbabilityLabel" runat="server"><%# Eval("Probability", "{0}%")%></asp:Label>
</ItemTemplate>
</radG:GridTemplateColumn>
<%--Commentary--%>
<radG:GridTemplateColumn DataField="Commentary" HeaderText="Commentary" SortExpression="Commentary"
UniqueName="Commentary" ItemStyle-Width="5pc">
<EditItemTemplate>
<asp:TextBox ID="CommentaryTextBox" runat="server" Text='<%# Bind("Commentary")%>' />
<span style="color: Red"></span>
<asp:RequiredFieldValidator ID="CommentaryRequiredFieldValidator" ControlToValidate="CommentaryTextBox"
Display="Dynamic" ErrorMessage="* This field is required" runat="server" />
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="CommentaryLabel" runat="server"><%# Eval("Commentary")%></asp:Label>
</ItemTemplate>
</radG:GridTemplateColumn>
<%--Person Responsible --%>
<radG:GridTemplateColumn DataField="Person_Responsible_Id" UniqueName="PersonResponsible"
HeaderText="Person Responsible" SortExpression="Person_Responsible_Id">
<EditItemTemplate>
<asp:DropDownList ID="PersonResponsible" runat="server" SelectedValue='<%# Bind("Person_Responsible_Id") %>'>
<asp:ListItem Value="0" Text="" />
<asp:ListItem Value="1" Text="David Klein" />
<asp:ListItem Value="2" Text="Andrew Muller" />
<asp:ListItem Value="3" Text="Damien Herslet" />
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="dtLabel1" runat="server" Text='<%# Eval("PersonName") %>' />
</ItemTemplate>
</radG:GridTemplateColumn>
<radG:GridTemplateColumn DataField="ResolutionDate" DataType="System.DateTime" HeaderText="Resolution Date"
SortExpression="ResolutionDate" UniqueName="ResolutionDate" ForceExtractValue="Always">
<EditItemTemplate>
<radCln:RadDatePicker ID="ResolutionDateDatePicker" runat="server" DbSelectedDate='<%# Bind("ResolutionDate") %>'>
</radCln:RadDatePicker>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="ResolutionDateLabel" runat="server"><%# Eval("ResolutionDate", "{0:d}")%></asp:Label>
</ItemTemplate>
</radG:GridTemplateColumn>
<radG:GridTemplateColumn DataField="Status_Lookup_SKEY" UniqueName="Status" HeaderText="Status"
SortExpression="Status_Lookup_SKEY">
<EditItemTemplate>
<asp:DropDownList ID="Status" runat="server" DataSourceID="StatusDataSource" DataTextField="Lookup_Value"
DataValueField="Lookup_SKEY" SelectedValue='<%# Bind("Status_Lookup_SKEY") %>'>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Image ID="Image1" runat="server" ImageAlign="Middle" ImageUrl='<%# Eval("StatusImageURL") %>' />
</ItemTemplate>
</radG:GridTemplateColumn>
<radG:GridTemplateColumn DataField="Period_Date_SKEY" HeaderText="Period_Date_SKEY"
UniqueName="Period_Date_SKEY" Visible="false">
<EditItemTemplate>
<asp:Label ID="PeriodDateLabel" runat="server" Text='<%# Bind("Period_Date_SKEY")%>' />
</EditItemTemplate>
</radG:GridTemplateColumn>
<radG:GridTemplateColumn DataField="IsRisk" HeaderText="IsRisk" UniqueName="IsRisk"
Groupable="True" GroupByExpression="IsRisk Group By IsRisk" Visible="False">
<ItemTemplate>
<asp:Label ID="IsRiskLabel" runat="server" Text='<%# Eval("IsRisk")%>' />
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="IsRiskLabel" runat="server" Text='<%# Eval("IsRisk")%>' />
</EditItemTemplate>
</radG:GridTemplateColumn>
<radG:GridEditCommandColumn UniqueName="Edit" ButtonType="ImageButton">
</radG:GridEditCommandColumn>
</Columns>
</MasterTableView>
<ClientSettings AllowDragToGroup="True">
<Resizing AllowColumnResize="True" EnableRealTimeResize="True" />
</ClientSettings>
</radG:RadGrid>
</div>
</form>
</body>
</html>




using System;
using System.Collections.Specialized;
using ddkonline.MRP.Service;
using ddkonline.MRP.Service.Persistence.Entity;
using Microsoft.Practices.Web.UI.WebControls;
using Telerik.WebControls;
using ddkonline.MRP.Global;

namespace ddkonline.MRP.Client
{
/// <summary>
/// Risk Management page
/// </summary>
public partial class RiskOpportunity : BasePage
{
protected void Page_Load(object sender, EventArgs e)
{
}

/// <summary>
/// Defaults the newly inserted items
/// </summary>
/// <param name="sender">Default event sender</param>
/// <param name="e">Grid command event arguments</param>
protected void BusinessRiskGrid_ItemCommand(object sender, GridCommandEventArgs e)
{
//// Default values for the items to insert in grid
//// http://www.telerik.com/community/forums/thread/b311D-emgdc.aspx
if (e.CommandName == RadGrid.InitInsertCommandName)
{
e.Canceled = true;
ListDictionary newValues = new ListDictionary();
newValues["IssueDetail"] = string.Empty;
newValues["Commentary"] = string.Empty;
newValues["BusinessUnitId"] = 871;
newValues["Reporting_Currency_SKEY"] = 0;
newValues["PBT"] = 0;
newValues["PAT"] = 0;
newValues["Probability"] = 0;
newValues["CreatedOn"] = DateTime.Now;
newValues["ModifiedOn"] = DateTime.Now;
newValues["ResolutionDate"] = null;
newValues["Person_Responsible_Id"] = 0;
newValues["Period_Date_SKEY"] = Controller.Current_Period;
newValues["Status_Lookup_SKEY"] = Constant.ON_TRACK;
e.Item.OwnerTableView.InsertItem(newValues);
}
}
/// <summary>
/// Handles the insert of an Item and Saves via partial method in the LINQ Entity (via a Service wrapper class)
/// </summary>
/// <param name="sender">Default event sender</param>
/// <param name="e">Object container event arguments</param>
protected void OdsRiskOpportunityDataSource_Inserted(
object sender, ObjectContainerDataSourceStatusEventArgs e)
{
ODS_Risk_Opportunity RiskOpportunity = (ODS_Risk_Opportunity)e.Instance;

// Set audit attributes
RiskOpportunity.CreatedOn = DateTime.Now;
RiskOpportunity.CreatedBy = Context.User.Identity.Name;
RiskOpportunity.ModifiedOn = DateTime.Now;
RiskOpportunity.ModifiedBy = Context.User.Identity.Name;
RiskOpportunity.Original_Risk_Opportunity_Id = 0;
//RiskOpportunity.Business_Area_Lookup_SKEY = GetBusinessAreaId(Controller.Current_BusinessArea);
RiskOpportunity.Business_Hierarchy_PC_SKEY = Controller.Current_BusinessHierarchy;
RiskOpportunityService.Save(RiskOpportunity);
}

/// <summary>
/// Handles the update of an Item
/// </summary>
/// <param name="sender">Default event sender</param>
/// <param name="e">Object container event arguments</param>
protected void OdsRiskOpportunityDataSource_Updated(
object sender, ObjectContainerDataSourceStatusEventArgs e)
{
ODS_Risk_Opportunity RiskOpportunity = (ODS_Risk_Opportunity)e.Instance;
// Set audit attributes
RiskOpportunity.ModifiedOn = DateTime.Now;
RiskOpportunity.ModifiedBy = Context.User.Identity.Name;
//RiskOpportunity.Business_Area_Lookup_SKEY = GetBusinessAreaId(Controller.Current_BusinessArea);
RiskOpportunity.Business_Hierarchy_PC_SKEY = Controller.Current_BusinessHierarchy;
RiskOpportunityService.Save(RiskOpportunity); ;
}

protected void OdsRiskOpportunityDataSource_Selecting(object sender, ObjectContainerDataSourceSelectingEventArgs e)
{
OdsRiskOpportunityDataSource.DataSource = RiskOpportunityService.GetAllFiltered(Controller.Current_BusinessHierarchy, Controller.Current_BusinessArea, Controller.Current_Period);
}

protected void BusinessUnitDataSource_Selecting(object sender, System.Web.UI.WebControls.LinqDataSourceSelectEventArgs e)
{
e.WhereParameters["ETL_Region"] = Controller.Current_BusinessHierarchy;
//e.SelectParameters. ["ETL_Region"]
//BusinessUnitDataSource.SelectParameters. = Controller.Current_BusinessHierarchy;
}

protected void BusinessRiskGrid_ItemDataBound(object sender, GridItemEventArgs e)
{
//Render Correct Header (RISK/OPPORTUNITY) on ItemDataBound of Telerik Grid.
if (e.Item is GridGroupHeaderItem)
{
GridGroupHeaderItem item = (GridGroupHeaderItem)e.Item;
System.Data.DataRowView groupDataRow = (System.Data.DataRowView)e.Item.DataItem;
item.DataCell.Text = Boolean.Parse(groupDataRow["IsRisk"].ToString()) ? "RISKS (enter -ve)" : "OPPORTUNITIES (enter +ve)";

//Calculations
//item.DataCell.Text += ((System.Decimal)groupDataRow["total"] / (int.Parse(groupDataRow["count"].ToString()))).ToString();
}
}
}
}