Excel Filter in Grid

[Oka Sugandi]

Dear Syncfusion,
I am implementing Grid with Excel Filter, and I have some troubles listed below:
1. I have 100.000+ data, in my experience, Excel Filter will get the whole data in order to know what is the options of the specific column... Is it possible to let Excel Filter know the options so it won't query the whole data?
2. I am aware that it has built in TextFilter and NumberFilter, does it also have Date Filter, to filter specific range of date?

Thank you.

[Prasanna Kumar Viswanathan]

Hi Sugandi, 

Thanks for contacting Syncfusion support. 

Queries	Response
“I have 100.000+ data, in my experience, Excel Filter will get the whole data in order to know what is the options of the specific column... Is it possible to let Excel Filter know the options so it won't query the whole data?”	If you do not need to display the whole data in excel filter, use maxFilterChoices API of filterSettings. In the attached sample we mentioned 200 in maxFilterChoices so it shows 200 data in excel filer instead of whole data.   Find the code example:   $("#Grid").ejGrid({               dataSource : ej.DataManager({                 url: "/Grid/DataSource",                 adaptor : "UrlAdaptor"             }),                allowPaging: true,                isResponsive: true,                allowFiltering: true,                filterSettings: { filterType: "excel", maxFilterChoices: 200 },                columns: [                  -----------------------                   ]            });  Refer to the Help document for maxFilterChoices.  https://help.syncfusion.com/api/js/ejgrid#members:filtersettings-maxfilterchoices
“I am aware that it has built in TextFilter and NumberFilter, does it also have Date Filter, to filter specific range of date?”	Yes, we have built in DateFilter for Date column to filter the specific range of date.   Find the screenshot:

Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/SyncfusionMvcApplication1211978543353-585077400.zip 

Regards, 

Prasanna Kumar N.S.V 

 

[Oka Sugandi]

Thank you Prasanna for the help.

I am still struggling to cope with the problem...

1. About option of Excel Filter. If I specifiy a number in maxFilterChoices, let say 1,000. The options is only shown if exists in the first 1,000 data... I have 100,000+ data, and in a particular column (let say 'Stage'), the first (let say) 10,000 data has same value 'Init', in fact the stage option has 5 value. I am unable to make Excel Filter show the 5 option.

2. I am unable to make Excel Filter show "Date Filter". It always show "Text Filter" although the data has been formatted as standard date... Any additional method to make it happen?

Thank you very much for the help provided.

[Prasanna Kumar Viswanathan]

Hi Sugandi, 

Queries	Response
“I have 100,000+ data, and in a particular column (let say 'Stage'), the first (let say) 10,000 data has same value 'Init', in fact the stage option has 5 value. I am unable to make Excel Filter show the 5 option.”	In excel filter it will show only unique values even if we bind 50000 records in grid. So, if you have same value for 100,000 records then it will display only one record in excel filter dialog. By default the excel filter will show only 1000 records if we bind 100,000 records in Grid     If you need to view other data use search option in excel filter. In search box type the particular value and click search icon we will get the other data in excel filter.
“I am unable to make Excel Filter show "Date Filter". It always show "Text Filter" although the data has been formatted as standard date... Any additional method to make it happen?”	We suspect that you have render the string value in Grid for the date column. So, we suggest you to mention the type as date for that particular column.   Find the code example:   columns: [                  -----                  { field: "OrderDate", headerText: "Order Date", width: 80, format: "{0:dd/MM/yyyy}", type : "date", textAlign: ej.TextAlign.Right },                                     ]  If you still face the same issue. Please share the following details.  1. Code example of a Grid.   2. Share the first record of data that you have bounded to the Grid.  3. Essential Studio Version details.  4. If possible, replicate the issue in the attached sample.

Regards, 

Prasanna Kumar N.S.V