JS Grid excel-like filtering server side

[Pawel Klimczyk]

I want to have values in excel-like filtering provided from server.
I tried to use this: http://js.syncfusion.com/demos/web/#!/azure/grid/filtering/filteringmenu
but don't understand how should that work with DataMamager property passed to my Data method on server-side. All values in DataManager are empty/null.
I'm using UrlAdaptor.

Here is the client-side code:

$(function () {
var dataManager = ej.DataManager({ url: "Data", adaptor: new ej.UrlAdaptor() });

$("#Grid").ejGrid({
toolbarSettings: { showToolbar: true, toolbarItems: ["search"] },
dataSource: dataManager,
allowPaging: true,
allowSearching: true,
allowTextWrap: true,
allowSorting: true,
enableHeaderHover: true,
allowFiltering: true,
//filterSettings: { filterType: "menu" },
filterSettings: { filterType: "excel" },
textWrapSettings: { wrapMode: "both" },
sortSettings: { sortedColumns: [{ field: "CreateDate", direction: "descending" }] },
columns: [
{ field: "Source", width: 120 },
{ field: "CreateDate", headerText: "Archived", format: "{0:yyyy/MM/dd HH:mm:ss}", width: 140 },
{ field: "Payload", allowSorting: false }]
});
});


And server-side code:
public JsonResult Data(DataManager dm)
{
string searchCriteria = String.Empty;
string sortField = "createDate";
string sortOrder = "desc";

if (dm.Search != null)
searchCriteria = String.Join(" ", dm.Search.Select(s => s.Key));

if (dm.Sorted != null && dm.Sorted.Count > 0)
{
sortField = dm.Sorted[0].Name;
sortOrder = dm.Sorted[0].Direction;
}

var records = _service.GetList(searchCriteria, dm.Skip, dm.Take, sortField, sortOrder);

DataResult result = new DataResult();
result.result = records.Items;

result.count = records.TotalItemCount;

return JsonResult.Create(result);
}


What I want to achive is that "Source" excel-like folter will contain all distinct values from database, even if only 10 rows are displayed on the client-side. So user can filter on "source" that is not displayed in current view.

[Seeni Sakthi Kumar Seeni Raj]

Hi Pawel,

We are able to reproduce the your problem.

You have used the Skip and Take of DataManager in the all the instance of Grid actions, which is cause for the problem. DataManager send Skip and Take values as zero and checkboxes will not render at your case while opening the excel filter.

To render the checkboxes, avoid the use of Skip and Take while both of them are zero. Refer to the following code example.

namespace MvcApplication66.Controllers

{

    public class HomeController : Controller

    {

        public ActionResult Index()

        {

            return View();

        }

        public ActionResult DataSource(DataManager dm)

        {

            IEnumerable DataSource = new NorthwindDataContext().OrdersViews.ToList();

            DataOperations operation = new DataOperations();

            DataResult result = new DataResult();

            if (dm.Search != null)

                DataSource = operation.PerformSearching(DataSource, dm.Search);

            if (dm.Where != null)

                DataSource = operation.PerformWhereFilter(DataSource, dm.Where, dm.Where[0].Condition);

            result.count = DataSource.AsQueryable().Count();

            if (dm.Skip != null && dm.Skip != 0)//skiped while rendering checkbox

                DataSource = operation.PerformSkip(DataSource, dm.Skip);

            if (dm.Take != null && dm.Take != 0)//skiped while rendering checkbox

                DataSource = operation.PerformTake(DataSource, dm.Take);

            result.result = DataSource;//passed the whole dataSource directly for checkbox rendering

            return Json(result, JsonRequestBehavior.AllowGet);

        }

        public class DataResult

        {

            public IEnumerable result { get; set; }

            public int count { get; set; }

            public IEnumerable aggregate { get; set; }

        }

    }
}

We have also prepared a sample with the provided code example that can be downloaded from the following location.

Sample: http://www.syncfusion.com/downloads/support/forum/123658/ze/ejGrid_Filtering-841702121

To perform server-side operations, DataManager provides DataOperation class. Refer to the following KB.

https://www.syncfusion.com/kb/4300/server-side-api-for-datamanager-operations

Regards,
Seeni Sakthi Kumar S.

[Pawel Klimczyk]

Hi!

Thanks for reply.

In your example where is

            public IEnumerable aggregate { get; set; }

used ?

[Seeni Sakthi Kumar Seeni Raj]

Hi Pawel,

aggregate is extra property of DataResult class which we have not used it in our sample. Refer to the modified code example.

namespace MvcApplication66.Controllers

{

    public class HomeController : Controller

    {

        public ActionResult Index()

        {

            return View();

        }

        public ActionResult DataSource(DataManager dm)

        {

            IEnumerable DataSource = new NorthwindDataContext().OrdersViews.ToList();

            DataOperations operation = new DataOperations();

            DataResult result = new DataResult();

            if (dm.Search != null)

                DataSource = operation.PerformSearching(DataSource, dm.Search);

            if (dm.Where != null)

                DataSource = operation.PerformWhereFilter(DataSource, dm.Where, dm.Where[0].Condition);

            result.count = DataSource.AsQueryable().Count();

            if (dm.Skip != null && dm.Skip != 0)//skiped while rendering checkbox

                DataSource = operation.PerformSkip(DataSource, dm.Skip);

            if (dm.Take != null && dm.Take != 0)//skiped while rendering checkbox

                DataSource = operation.PerformTake(DataSource, dm.Take);

            result.result = DataSource;//passed the whole dataSource directly for checkbox rendering

            return Json(result, JsonRequestBehavior.AllowGet);

        }

        public class DataResult

        {

            public IEnumerable result { get; set; }

            public int count { get; set; }

        }

    }
}

We may also return the data as an object without the need of the class “DataResult”

        public ActionResult DataSource(DataManager dm)

        {

            IEnumerable result = null;

            int count = 0;

            IEnumerable DataSource = new NorthwindDataContext().OrdersViews.ToList();

            DataOperations operation = new DataOperations();

            if (dm.Search != null)

                DataSource = operation.PerformSearching(DataSource, dm.Search);

            if (dm.Where != null)

                DataSource = operation.PerformWhereFilter(DataSource, dm.Where, dm.Where[0].Condition);

            count = DataSource.AsQueryable().Count();

            if (dm.Skip != null && dm.Skip != 0)//skiped while rendering checkbox

                DataSource = operation.PerformSkip(DataSource, dm.Skip);

            if (dm.Take != null && dm.Take != 0)//skiped while rendering checkbox

                DataSource = operation.PerformTake(DataSource, dm.Take);

            result = DataSource;//passed the whole dataSource directly for checkbox rendering

            return Json(new { result = result, count = count }, JsonRequestBehavior.AllowGet); //result and count pair defined in a new obj instead of using DataResult Class
        }

We have also modified the sample that can be downloaded from the following location.

Sample: http://www.syncfusion.com/downloads/support/forum/123658/ze/ejGrid_filter640726526.zip
 
Regards,
Seeni Sakthi Kumar S.