Skip Navigation LinksHome / Community & Support / Code Library / ASP.NET and ASP.NET AJAX > General and Integration Projects > Excel-like Auto-filter with RadGrid, RadMenu and RadAjax

Answered Excel-like Auto-filter with RadGrid, RadMenu and RadAjax

Feed from this thread
  • Telerik Admin Master avatar

    Posted on Jul 17, 2007 (permalink)

    Requirements
    RadControls version

    RadGrid, RadMenu, RadAjax
    or Telerik.Web.UI 2007.3.12.18+
    .NET version

    2.x
    Visual Studio version

    2005
    programming language

    VB.NET, Javascript
    browser support

    all browsers supported by RadControls

     
  • PROJECT DESCRIPTION
    The demo presents how to implement Excel-like Auto-filter with distinct filter values and All option having RadMenu as context menu for the RadGrid column headers. The RadContextMenu items are created on demand (when right-clicking an arbitrary grid header) to reflect the filter options in the column chosen by the user. We also store the X/Y coordinates of the client RadContextMenu object to display it on the specified location when the ajax request generating the menu items finishes execution.

    Finally, the ItemClick event of RadMenu is wired to modify the SELECT query and filter the data in RadGrid according to the user preferences. The grid and menu are ajaxified via RadAjaxManager to make asynchronous calls to the server and enrich the user experience.

      Protected Sub RadAjaxManager1_AjaxRequest(ByVal sender As Object, ByVal e As AjaxRequestEventArgs) Handles RadAjaxManager1.AjaxRequest     
            Dim arguments As String() = e.Argument.Split(":")     
            Dim columnToFilter As GridColumn = CType(RadGrid1.MasterTableView.AutoGeneratedColumns(Integer.Parse(arguments(1))), GridColumn)     
        
            BindFilterMenuRecords(columnToFilter.UniqueName)     
        End Sub    
        Protected Sub BindFilterMenuRecords(ByVal columnName As String)     
        
            Dim filterMenuData As DataTable = GetDataTable("SELECT DISTINCT TOP 40 " & columnName & " FROM Customers")     
            Dim row As DataRow     
            Dim menuItem As RadMenuItem     
        
            RadMenu1.Items.Clear()     
        
            Dim rootItem As RadMenuItem = New RadMenuItem("Show filter options")     
            rootItem.GroupSettings.Flow = ItemFlow.Vertical     
            rootItem.GroupSettings.Height = Unit.Pixel(300)     
        
            RadMenu1.Items.Add(rootItem)     
        
            For Each row In filterMenuData.Rows     
                menuItem = New RadMenuItem()     
                menuItem.Text = row(columnName).ToString()     
                menuItem.Value = columnName     
                rootItem.Items.Add(menuItem)     
            Next    
        
            rootItem.Items.Insert(0, New RadMenuItem("All"))     
        
            RadAjaxManager1.ResponseScripts.Add("DisplayFilterMenu(this)")     
        End Sub    
        Public Function GetDataTable(ByVal query As String) As DataTable     
            Dim connection1 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & System.Web.HttpContext.Current.Server.MapPath("~/Grid/Data/Access/NWind.mdb"))     
            Dim adapter1 As New OleDbDataAdapter     
            adapter1.SelectCommand = New OleDbCommand(query, connection1)     
            Dim table1 As New DataTable     
            connection1.Open()     
            Try    
                adapter1.Fill(table1)     
            Finally    
                connection1.Close()     
            End Try    
            Return table1     
        End Function    
        Protected Sub RadMenu1_ItemClick(ByVal sender As Object, ByVal e As RadMenuEventArgs) Handles RadMenu1.ItemClick     
            If (e.Item.Text = "All") Then    
                RadGrid1.MasterTableView.FilterExpression = String.Empty     
                RadGrid1.MasterTableView.Rebind()     
            ElseIf (Not e.Item.Text = "Show filter options")    
                Dim columnUniqueName As String = e.Item.Value     
                AccessDataSource1.SelectCommand = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, PostalCode FROM Customers WHERE " & columnUniqueName & " = '" & e.Item.Text & "'"    
                RadGrid1.MasterTableView.Rebind()     
            End If    
        End Sub    
     

      

                <telerik:RadCodeBlock ID="RadCodeBlock1" runat="server">  
     
                    <script type="text/javascript">  
               
                     var contextMenuX = 0;  
                     var contextMenuY = 0;  
                       
                     function ColumnContextMenu(sender, eventArgs)  
                     {  
                        var menu = $find("<%= RadMenu1.ClientID %>");  
                        var columnIndex = eventArgs.get_gridColumn().get_element().cellIndex;  
                        contextMenuX = eventArgs.get_domEvent().clientX;  
                        contextMenuY = eventArgs.get_domEvent().clientY;  
                          
                        $find("<%= RadAjaxManager1.ClientID %>").ajaxRequest("ColumnIndex:" + columnIndex);  
                          
                        eventArgs.get_domEvent().cancelBubble = true;  
                        eventArgs.get_domEvent().returnValue = false;  
     
                        if (eventArgs.get_domEvent().preventDefault())  
                        {  
                           eventArgs.get_domEvent().preventDefault();  
                        }  
                     }  
                     function DisplayFilterMenu(e)  
                     {  
                        var menu = $find("<%= RadMenu1.ClientID %>");  
                        menu.showAt(contextMenuX, contextMenuY);  
                     }  
                    </script>  
     
                </telerik:RadCodeBlock> 


                <asp:ScriptManager ID="ScriptManager1" runat="server" /> 
                <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">  
                    <AjaxSettings> 
                        <telerik:AjaxSetting AjaxControlID="RadMenu1">  
                            <UpdatedControls> 
                                <telerik:AjaxUpdatedControl ControlID="RadGrid1" LoadingPanelID="AjaxLoadingPanel1" /> 
                                <telerik:AjaxUpdatedControl ControlID="RadMenu1" /> 
                            </UpdatedControls> 
                        </telerik:AjaxSetting> 
                        <telerik:AjaxSetting AjaxControlID="RadAjaxManager1">  
                            <UpdatedControls> 
                                <telerik:AjaxUpdatedControl ControlID="RadMenu1" /> 
                                <telerik:AjaxUpdatedControl ControlID="RadGrid1" LoadingPanelID="AjaxLoadingPanel1" /> 
                            </UpdatedControls> 
                        </telerik:AjaxSetting> 
                    </AjaxSettings> 
                </telerik:RadAjaxManager> 
                <telerik:RadAjaxLoadingPanel ID="AjaxLoadingPanel1" runat="server" Height="75px" 
                    Width="75px" Transparency="25">  
                    <img alt="Loading..." src='<%= RadAjaxLoadingPanel.GetWebResourceUrl(Page, "Telerik.Web.UI.Skins.Default.Ajax.loading.gif") %>' 
                        style="border: 0;" /> 
                </telerik:RadAjaxLoadingPanel> 
                <br /> 
                Right-click grid column header to choose filter menu options  
                <br /> 
                <telerik:RadGrid ID="RadGrid1" DataSourceID="AccessDataSource1" Skin="Vista" runat="server" 
                    GridLines="None" Width="95%" AllowPaging="true" PageSize="25">  
                    <MasterTableView Width="100%" DataKeyNames="CustomerID" /> 
                    <ClientSettings AllowColumnHide="true">  
                        <ClientEvents OnColumnContextMenu="ColumnContextMenu" /> 
                    </ClientSettings> 
                    <PagerStyle Mode="NextPrevAndNumeric" /> 
                </telerik:RadGrid> 
                <br /> 
                <asp:AccessDataSource ID="AccessDataSource1" DataFile="~/App_Data/Nwind.mdb" SelectCommand="SELECT TOP 40 CustomerID, CompanyName, ContactName, ContactTitle, Address, PostalCode FROM Customers" 
                    runat="server"></asp:AccessDataSource> 
                <br /> 
                <telerik:RadContextMenu ID="RadMenu1" runat="server" Skin="Outlook" /> 






           <script type="text/javascript">  
               
             var contextMenuX = 0;  
             var contextMenuY = 0;  
               
             function ColumnContextMenu(columnIndex, e)  
             {  
                var menu = <%= RadMenu1.ClientID %>;  
                  
                contextMenuX = menu.MouseEventX(e);  
                contextMenuY = menu.MouseEventY(e);  
                  
                window["<%= RadAjaxManager1.ClientID %>"].AjaxRequest("ColumnIndex:" + columnIndex);  
                  
                e.cancelBubble = true;  
                e.returnValue = false;  
     
                if (e.stopPropagation)  
                {  
                   e.stopPropagation();  
                   e.preventDefault();  
                }  
                  
             }  
             function DisplayFilterMenu(e)  
             {  
                var menu = <%= RadMenu1.ClientID %>;  
                menu.ShowAt(contextMenuX, contextMenuY);  
             }  
          </script> 

                Right-click grid column header to choose filter menu options  
                <br /> 
                <radG:RadGrid ID="RadGrid1" DataSourceID="AccessDataSource1" Skin="Mac" runat="server" 
                    GridLines="None" Width="95%" AllowPaging="true" PageSize="25">  
                    <MasterTableView Width="100%" DataKeyNames="CustomerID" /> 
                    <ClientSettings AllowColumnHide="true">  
                        <ClientEvents OnColumnContextMenu="ColumnContextMenu" /> 
                    </ClientSettings> 
                    <PagerStyle Mode="NextPrevAndNumeric" /> 
                </radG:RadGrid> 
                <br /> 
                <asp:AccessDataSource ID="AccessDataSource1" DataFile="~/Grid/Data/Access/Nwind.mdb" 
                    SelectCommand="SELECT TOP 40 CustomerID, CompanyName, ContactName, ContactTitle, Address, PostalCode FROM Customers" 
                    runat="server">     
                </asp:AccessDataSource> 
                <br /> 
                <radM:RadMenu ID="RadMenu1" IsContext="True" runat="server" Skin="Outlook" ContextMenuElementID="none" /> 
                <radA:RadAjaxManager ID="RadAjaxManager1" runat="server">  
                    <AjaxSettings> 
                        <radA:AjaxSetting AjaxControlID="RadMenu1">  
                            <UpdatedControls> 
                                <radA:AjaxUpdatedControl ControlID="RadGrid1" LoadingPanelID="AjaxLoadingPanel1" /> 
                                <radA:AjaxUpdatedControl ControlID="RadMenu1" /> 
                            </UpdatedControls> 
                        </radA:AjaxSetting> 
                        <radA:AjaxSetting AjaxControlID="RadAjaxManager1">  
                            <UpdatedControls> 
                                <radA:AjaxUpdatedControl ControlID="RadMenu1" /> 
                                <radA:AjaxUpdatedControl ControlID="RadGrid1" LoadingPanelID="AjaxLoadingPanel1" /> 
                            </UpdatedControls> 
                        </radA:AjaxSetting> 
                    </AjaxSettings> 
                </radA:RadAjaxManager> 
                <radA:AjaxLoadingPanel ID="AjaxLoadingPanel1" runat="server" Height="75px" Width="75px" 
                 Transparency="25"><asp:Image id="Image1" runat="server" AlternateText="Loading..." ImageUrl="~/RadControls/Ajax/Skins/Default/Loading.gif"></asp:Image> </radA:AjaxLoadingPanel> 

  • Answer JC avatar

    Posted on Jan 6, 2009 (permalink)

    Is there an updated version of this example?  This one doesn't run no matter how many modifications I've tried.
    I've got it to the point I can build and run it but when I right click on a column header I get:
    "Microsoft JScript runtime error: 'null' is null or not an object"

  • Sebastian Sebastian admin's avatar

    Posted on Jan 7, 2009 (permalink)

    Hello AyaNova,

    I tested both demo versions (ASP.NET and ASP.NET AJAX) using the latest versions of the controls and they worked without issues on my machine. If you experience issues on your end, please isolate a stripped working version of your project and send it enclosed to a formal support ticket. We will test/debug it locally and will get around to you with our findings.

    Best regards,
    Sebastian
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.

    • Back to Top

    Skip Navigation LinksHome / Community & Support / Code Library / ASP.NET and ASP.NET AJAX > General and Integration Projects > Excel-like Auto-filter with RadGrid, RadMenu and RadAjax