4

I have created the custom sales report using ui component.

Grid is loading correctly. I have added the date range filter as like below.

<column name="created_at" class="Magento\Ui\Component\Listing\Columns\Date"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">dateRange</item> <item name="component" xsi:type="string">Magento_Ui/js/grid/columns/date</item> <item name="dataType" xsi:type="string">date</item> <item name="label" xsi:type="string" translate="true">Created At</item> <item name="dateFormat" xsi:type="string">Y-MM-dd HH:mm:ss</item> </item> </argument> </column>

Here is the screenshot of error.

enter image description here

In the filter section when the from and To date selected and applied, I am getting Bad request error.

This is the error in Request

 {error: "UI component could not be rendered because of system exception", errorcode: "23000" }

error: "UI component could not be rendered because of system exception" errorcode: "23000"

Can anyone check where i am wrong and help me to resolve this error. The answers provided below all i tried but no solution for it.

Update:

Complete code of my listing file

<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd"> <!-- Integration --> <argument name="data" xsi:type="array"> <item name="js_config" xsi:type="array"> <!-- we define a provider --> <item name="provider" xsi:type="string">catalogreport_manage_listing.catalogreport_manage_listing_data_source</item> <!-- same string as above --> <item name="deps" xsi:type="string">catalogreport_manage_listing.catalogreport_manage_listing_data_source</item> </item> <!-- define column type --> <item name="spinner" xsi:type="string">catalogreport_manage_columns</item> </argument> <!-- Data source --> <dataSource name="catalogreport_manage_listing_data_source"> <argument name="dataProvider" xsi:type="configurableObject"> <argument name="class" xsi:type="string">ManageGridDataProvider</argument> <!-- Data provider class --> <argument name="name" xsi:type="string">catalogreport_manage_listing_data_source</argument> <!-- provider defined above --> <argument name="primaryFieldName" xsi:type="string">item_id</argument> <!-- Primary key --> <argument name="requestFieldName" xsi:type="string">id</argument> <!-- URL name parameter --> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="component" xsi:type="string">Magento_Ui/js/grid/provider</item> <item name="update_url" xsi:type="url" path="mui/index/render"/> <item name="storageConfig" xsi:type="array"> <!-- Primary key column name --> <item name="indexField" xsi:type="string">item_id</item> </item> </item> </argument> </argument> </dataSource> <!-- Container Listing Top --> <container name="listing_top"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="template" xsi:type="string">ui/grid/toolbar</item> </item> </argument> <!-- Button to manage views --> <bookmark name="bookmarks"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="component" xsi:type="string">Magento_Ui/js/grid/controls/bookmarks/bookmarks</item> <item name="displayArea" xsi:type="string">dataGridActions</item> <item name="storageConfig" xsi:type="array"> <item name="saveUrl" xsi:type="url" path="mui/bookmark/save"/> <item name="deleteUrl" xsi:type="url" path="mui/bookmark/delete"/> <item name="namespace" xsi:type="string">jobs_department_listing</item> </item> </item> </argument> </bookmark> <!-- Button to manage columns --> <container name="columns_controls"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="columnsData" xsi:type="array"> <item name="provider" xsi:type="string">catalogreport_manage_listing.catalogreport_manage_listing.catalogreport_manage_columns</item> </item> <item name="component" xsi:type="string">Magento_Ui/js/grid/controls/columns</item> <item name="displayArea" xsi:type="string">dataGridActions</item> </item> </argument> </container> <!-- Filters --> <filters name="listing_filters"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="storageConfig" xsi:type="array"> <item name="provider" xsi:type="string">catalogreport_manage_listing.catalogreport_manage_listing.listing_top.bookmarks</item> <item name="namespace" xsi:type="string">current.filters</item> </item> <item name="childDefaults" xsi:type="array"> <item name="provider" xsi:type="string">catalogreport_manage_listing.catalogreport_manage_listing.listing_top.listing_filters</item> <item name="imports" xsi:type="array"> <item name="visible" xsi:type="string">catalogreport_manage_listing.catalogreport_manage_listing.listing_top.bookmarks:current.columns.${ $.index }.visible</item> </item> </item> </item> </argument> <!-- Department ID Filter --> <!-- Department name Filter --> <!-- <filterInput name="sku"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="dataScope" xsi:type="string">name</item> <item name="label" xsi:type="string" translate="true">SKU</item> </item> </argument> </filterInput> --> </filters> <!-- Paging --> <paging name="listing_paging"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="storageConfig" xsi:type="array"> <item name="provider" xsi:type="string">catalogreport_manage_listing.catalogreport_manage_listing.listing_top.bookmarks</item> <item name="namespace" xsi:type="string">current.paging</item> </item> <item name="selectProvider" xsi:type="string">catalogreport_manage_listing.catalogreport_manage_listing.catalogreport_manage_columns.ids</item> <item name="displayArea" xsi:type="string">bottom</item> </item> </argument> </paging> <exportButton class="Magento\Ui\Component\ExportButton"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="options" xsi:type="array"> <item name="cvs" xsi:type="array"> <item name="value" xsi:type="string">csv</item> <item name="label" xsi:type="string" translate="true">CSV</item> <item name="url" xsi:type="string">catalogreport/export/gridToCsv</item> </item> <item name="xml" xsi:type="array"> <item name="value" xsi:type="string">xml</item> <item name="label" xsi:type="string" translate="true">Excel XML</item> <item name="url" xsi:type="string">catalogreport/export/gridToXml</item> <!-- <item name="componentDisabled" xsi:type="string">true</item> --> </item> </item> </item> </argument> </exportButton> </container> <!-- Columns --> <columns name="catalogreport_manage_columns"> <!-- <selectionsColumn name="ids"> <settings> <indexField>item_id</indexField> </settings> </selectionsColumn> --> <!-- Name Column --> <column name="increment_id" class="Twofoursevencommerce\Catalogreport\Ui\Component\Listing\Column\Quantity"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">true</item> <item name="label" xsi:type="string" translate="true">Order number</item> </item> </argument> </column> <column name="sku"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">false</item> <item name="editor" xsi:type="array"> <item name="editorType" xsi:type="string">text</item> <item name="validation" xsi:type="array"> <item name="required-entry" xsi:type="boolean">true</item> </item> </item> <item name="label" xsi:type="string" translate="true">SKU</item> </item> </argument> </column> <!-- new code --> <column name="created_at" class="Magento\Ui\Component\Listing\Columns\Date"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="editor" xsi:type="array"> <item name="editorType" xsi:type="string">date</item> </item> <item name="visible" xsi:type="boolean">true</item> <item name="filter" xsi:type="string">date</item> <item name="component" xsi:type="string">Magento_Ui/js/grid/columns/date</item> <item name="dataType" xsi:type="string">date</item> <item name="label" xsi:type="string" translate="true">Created At</item> <item name="dateFormat" xsi:type="string">MMM dd, YYYY</item> //set date format as per your requirement </item> </argument> </column> <!-- new code --> <column name="status" class="Twofoursevencommerce\Catalogreport\Ui\Component\Listing\Column\Quantity"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">false</item> <item name="label" xsi:type="string" translate="true">Status</item> </item> </argument> </column> <column name="name" class="Twofoursevencommerce\Catalogreport\Ui\Component\Listing\Column\Quantity"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">false</item> <item name="label" xsi:type="string" translate="true">Product name</item> </item> </argument> </column> <column name="qty_ordered" class="Twofoursevencommerce\Catalogreport\Ui\Component\Listing\Column\Quantity"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">false</item> <item name="label" xsi:type="string" translate="true">Quantity</item> </item> </argument> </column> <column name="shipping_amount" class="Twofoursevencommerce\Catalogreport\Ui\Component\Listing\Column\Quantity"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">false</item> <item name="label" xsi:type="string" translate="true">Shipping amount</item> </item> </argument> </column> <column name="discount_code" class="Twofoursevencommerce\Catalogreport\Ui\Component\Listing\Column\Quantity"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">false</item> <item name="label" xsi:type="string" translate="true">Discount code</item> </item> </argument> </column> <column name="discount_amount" class="Twofoursevencommerce\Catalogreport\Ui\Component\Listing\Column\Quantity"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">false</item> <item name="label" xsi:type="string" translate="true">Discount</item> </item> </argument> </column> <column name="price" class="Twofoursevencommerce\Catalogreport\Ui\Component\Listing\Column\Quantity"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">false</item> <item name="label" xsi:type="string" translate="true">Price</item> </item> </argument> </column> <column name="row_total" class="Twofoursevencommerce\Catalogreport\Ui\Component\Listing\Column\Quantity"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">false</item> <item name="label" xsi:type="string" translate="true">Row total</item> </item> </argument> </column> <column name="grand_total" class="Twofoursevencommerce\Catalogreport\Ui\Component\Listing\Column\Quantity"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">false</item> <item name="label" xsi:type="string" translate="true">Grand total</item> </item> </argument> </column> </columns>

Thanks in Advance

Improve this question
19
  • can you share the screenshot of error? Commented Jul 4, 2019 at 13:35
  • @MohitRane, added the error screenshot, pls have a look Commented Jul 4, 2019 at 13:37
  • check removing <item name="dateFormat" xsi:type="string">Y-MM-dd HH:mm:ss</item> this code and check if everything is working Commented Jul 4, 2019 at 13:42
  • @MohitRane, No same error after that also, cleared cache and checked it Commented Jul 4, 2019 at 13:45
  • 1
    @MohitRane, I am not using custom table, using sales_order_item table for loading collection Commented Jul 4, 2019 at 13:58

6 Answers 6

Reset to default
8
+50

Please replace your code with below given code for Date Field(created_at) only.

<column name="created_at"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">dateRange</item> <item name="editor" xsi:type="string">text</item> <item name="label" xsi:type="string" translate="true">Created At</item> <item name="sortOrder" xsi:type="number">60</item> </item> </argument> </column>

Update your Twofoursevencommerce\Catalogreport\Model\Resource\Products\Collection.php file. And Add below given code in the _initSelect() method before return $this.

$this->addFilterToMap('created_at', 'main_table.created_at');

And then just flush the caches and refresh your grid page.

Improve this answer
20
  • This code i tried but not worked.. Commented Jul 15, 2019 at 10:57
  • May I know the version of your Magento2? Commented Jul 15, 2019 at 10:58
  • version is 2.2.2 Commented Jul 15, 2019 at 11:06
  • Hello @karthik, is there any possibility to add the customer address to grid, at least country? Commented Jul 24, 2019 at 8:24
  • Hello @jafarpinjar, It is possible but there are 2 address types (Billing and Shipping) and you will have to confirm which address you want to show over there. Commented Jul 24, 2019 at 12:16
2

Try This :-

<column name="created_at" class="Magento\Ui\Component\Listing\Columns\Date"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="editor" xsi:type="array"> <item name="editorType" xsi:type="string">date</item> </item> <item name="visible" xsi:type="boolean">true</item> <item name="filter" xsi:type="string">date</item> <item name="component" xsi:type="string">Magento_Ui/js/grid/columns/date</item> <item name="dataType" xsi:type="string">date</item> <item name="label" xsi:type="string" translate="true">Created At</item> <item name="dateFormat" xsi:type="string">MMM dd, YYYY</item> //set date format as per your requirement </item> </argument> </column>
Improve this answer
12
  • hi @RkRathod, After adding these lines, it is not showing any filter option to filter Commented Jul 15, 2019 at 6:18
  • follow command s:s:d or clean chache Commented Jul 15, 2019 at 6:27
  • Yes, I run those commands @Rathod.. Commented Jul 15, 2019 at 6:28
  • i m already do this in sales order grid using this code Commented Jul 15, 2019 at 6:29
  • please attch full code of this file Commented Jul 15, 2019 at 6:30
1

can you please try this code 

 <column name="created_on"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">dateRange</item> <item name="sorting" xsi:type="string">asc</item> <item name="label" xsi:type="string" translate="true">Created On</item> </item> <item name="sortOrder" xsi:type="number">2</item> </argument> </column>
Improve this answer
13
  • yes tried your code, its not working. "Something went wrong " error Commented Jul 8, 2019 at 13:17
  • same error again, Grid is not loading Commented Jul 8, 2019 at 13:20
  • updated the script please check now Commented Jul 8, 2019 at 13:20
  • It should be created_at is it? updated but still same issue, cleared cache and checked it "Bad Request" Commented Jul 8, 2019 at 13:21
  • can you please check your model and field name for created at Commented Jul 8, 2019 at 13:23
0

Try below code

<item name="dateFormat" xsi:type="string">MMM dd, yyyy, h:mm:ss a</item>

Instead of 

 <item name="dateFormat" xsi:type="string">Y-MM-dd HH:mm:ss</item>
Improve this answer
6
  • same error. nothing changed Commented Jul 4, 2019 at 13:48
  • @jafarpinjar: after above changes, have you clear cache? Commented Jul 4, 2019 at 13:51
  • yes, did the cache flush and checked it, I have one exception, Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous at Commented Jul 4, 2019 at 13:53
  • @jafarpinjar you mean now error is coming diffrent one to previous, right? Commented Jul 5, 2019 at 5:33
  • @jafarpinjar: check one more thing your custom column 'created_at' should not unique key in your custom table. Commented Jul 5, 2019 at 5:45
0

EDIT

You can solve this by using addFilterToMap()

Add this code in your Collection.php file

protected function _construct() { parent::_construct(); $this->addFilterToMap( 'created_at', 'sales_order_item.created_at' ); }

This code is working properly in my module

<column name="created_at" class="Magento\Ui\Component\Listing\Columns\Date"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">dateRange</item> <item name="sorting" xsi:type="string">desc</item> <item name="sortable" xsi:type="boolean">true</item> <item name="dataType" xsi:type="string">date</item> <item name="label" xsi:type="string" translate="true">Date</item> <item name="dateFormat" xsi:type="string">Y-MM-dd HH:mm:ss</item> <item name="sortOrder" xsi:type="number">1</item> </item> </argument> </column>

You can try removing this line

<item name="component" xsi:type="string">Magento_Ui/js/grid/columns/date</item>
Improve this answer
12
  • Hello Mohit, used this code, its not working on page load itself error message showing like above, i cleared cache and checked it. Commented Jul 8, 2019 at 6:30
  • Try this code <column name="created_at" class="Magento\Ui\Component\Listing\Columns\Date" component="Magento_Ui/js/grid/columns/date"> <settings> <filter>dateRange</filter> <dataType>date</dataType> <label translate="true">Created at</label> <sorting>desc</sorting> </settings> </column> Commented Jul 8, 2019 at 6:37
  • yes same error again, gird not loading on page load.. Commented Jul 8, 2019 at 7:15
  • I think some error in xml code, Commented Jul 8, 2019 at 7:31
  • can you share your listing.xml file ? Commented Jul 8, 2019 at 7:33
0

try with below code for UI Component

<column name="created_at" class="Magento\Ui\Component\Listing\Columns\Date"> <argument name="data" xsi:type="array"> <item name="config" xsi:type="array"> <item name="filter" xsi:type="string">dateRange</item> <item name="sorting" xsi:type="string">desc</item> <item name="sortable" xsi:type="boolean">true</item> <item name="dataType" xsi:type="string">date</item> <item name="label" xsi:type="string" translate="true">Date</item> <item name="dateFormat" xsi:type="string">Y-MM-dd HH:mm:ss</item> <item name="sortOrder" xsi:type="number">1</item> </item> </argument> </column>

please comment below line

<item name="component" xsi:type="string">Magento_Ui/js/grid/columns/date</item>

than update your Collection.php

protected function _construct() { parent::_construct(); $this->addFilterToMap( 'created_at', 'sales_order_item.created_at' ); }

I hope its work for you.

Improve this answer
4
  • hello @Anas, you mean here? Model/Resource/Products/Grid/Collection.php Commented Jul 12, 2019 at 6:34
  • yes, Note : try with your filter attributes. I mention here for just demo which done and working from my side. Commented Jul 12, 2019 at 6:36
  • Its not working bro.. Commented Jul 12, 2019 at 7:55
  • please refer this url - github.com/magento/magento2/issues/1724 , may be its very useful to you. Commented Jul 12, 2019 at 9:04

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.