2

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

  • can you share the screenshot of error? – Mohit Rane Jul 4 at 13:35
  • @MohitRane, added the error screenshot, pls have a look – jafar pinjar Jul 4 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 – Mohit Rane Jul 4 at 13:42
  • @MohitRane, No same error after that also, cleared cache and checked it – jafar pinjar Jul 4 at 13:45
  • 1
    @MohitRane, I am not using custom table, using sales_order_item table for loading collection – jafar pinjar Jul 4 at 13:58
3
+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.

  • This code i tried but not worked.. – jafar pinjar Jul 15 at 10:57
  • May I know the version of your Magento2? – Kartik Maniyar Jul 15 at 10:58
  • version is 2.2.2 – jafar pinjar Jul 15 at 11:06
  • Hello @karthik, is there any possibility to add the customer address to grid, at least country? – jafar pinjar Jul 24 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. – Kartik Maniyar Jul 24 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>
  • hi @RkRathod, After adding these lines, it is not showing any filter option to filter – jafar pinjar Jul 15 at 6:18
  • follow command s:s:d or clean chache – Rk Rathod Jul 15 at 6:27
  • Yes, I run those commands @Rathod.. – jafar pinjar Jul 15 at 6:28
  • i m already do this in sales order grid using this code – Rk Rathod Jul 15 at 6:29
  • please attch full code of this file – Rk Rathod Jul 15 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>
  • yes tried your code, its not working. "Something went wrong " error – jafar pinjar Jul 8 at 13:17
  • same error again, Grid is not loading – jafar pinjar Jul 8 at 13:20
  • updated the script please check now – nikunj Jul 8 at 13:20
  • It should be created_at is it? updated but still same issue, cleared cache and checked it "Bad Request" – jafar pinjar Jul 8 at 13:21
  • can you please check your model and field name for created at – nikunj Jul 8 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>
  • same error. nothing changed – jafar pinjar Jul 4 at 13:48
  • @jafarpinjar: after above changes, have you clear cache? – Balwant Singh Jul 4 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 – jafar pinjar Jul 4 at 13:53
  • @jafarpinjar you mean now error is coming diffrent one to previous, right? – Balwant Singh Jul 5 at 5:33
  • @jafarpinjar: check one more thing your custom column 'created_at' should not unique key in your custom table. – Balwant Singh Jul 5 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>
  • Hello Mohit, used this code, its not working on page load itself error message showing like above, i cleared cache and checked it. – jafar pinjar Jul 8 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> – Mohit Rane Jul 8 at 6:37
  • yes same error again, gird not loading on page load.. – jafar pinjar Jul 8 at 7:15
  • I think some error in xml code, – jafar pinjar Jul 8 at 7:31
  • can you share your listing.xml file ? – Mohit Rane Jul 8 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.