Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[EDM] Generated invalid JSON for .schema file when having ReportEntity #3739

Closed
iliyan-velichkov opened this issue Apr 5, 2024 · 5 comments
Closed

[EDM] Generated invalid JSON for .schema file when having ReportEntity #3739

iliyan-velichkov opened this issue Apr 5, 2024 · 5 comments
Assignees
@ThuF
Labels
bug component-database component-template database edm
Projects
Backlog
Milestone
11.0.0

Comments

@iliyan-velichkov
Copy link
Contributor

iliyan-velichkov commented Apr 5, 2024 •
edited by ThuF

Edm
image

Generated schema json

{
    "schema": {
        "structures": [
            {
                "name": "ORDERS",
                "type": "TABLE",
                "columns": [
                    {
                        "type": "INTEGER",
                        "length": 0,
                        "primaryKey": true,
                        "identity": true,
                        "nullable": true,
                        "name": "ORDERS_ID"
                    },
                    {
                        "type": "TIMESTAMP",
                        "length": 20,
                        "precision": "NaN",
                        "scale": "NaN",
                        "nullable": true,
                        "name": "ORDERS_DATE"
                    },
                    {
                        "type": "INTEGER",
                        "length": 0,
                        "precision": "NaN",
                        "scale": "NaN",
                        "nullable": true,
                        "name": "ORDERS_SHOP"
                    },
                    {
                        "type": "DOUBLE",
                        "length": 20,
                        "precision": "NaN",
                        "scale": "NaN",
                        "nullable": true,
                        "name": "ORDERS_TOTAL"
                    }
                ]
            },
            {
                "name": "SHOP",
                "type": "TABLE",
                "columns": [
                    {
                        "type": "INTEGER",
                        "length": 0,
                        "primaryKey": true,
                        "identity": true,
                        "nullable": true,
                        "name": "SHOP_ID"
                    },
                    {
                        "type": "VARCHAR",
                        "length": 20,
                        "nullable": true,
                        "name": "SHOP_NAME"
                    }
                ]
            },
            {
                "name": "ORDERSREPORTFILTER",
                "type": "TABLE",
                "columns": [
                    {
                        "type": "VARCHAR",
                        "length": 20,
                        "primaryKey": true,
                        "precision": "NaN",
                        "scale": "NaN",
                        "nullable": true,
                        "name": "ORDERSREPORTFILTER_REPORT"
                    },
                    {
                        "type": "INTEGER",
                        "length": 0,
                        "nullable": true,
                        "name": "ORDERSREPORTFILTER_ORDERS"
                    },
                    {
                        "type": "DATE",
                        "length": 20,
                        "precision": "NaN",
                        "scale": "NaN",
                        "nullable": true,
                        "name": "ORDERSREPORTFILTER_STARTPERIOD"
                    },
                    {
                        "type": "DATE",
                        "length": 20,
                        "precision": "NaN",
                        "scale": "NaN",
                        "nullable": true,
                        "name": "ORDERSREPORTFILTER_ENDPERIOD"
                    }
                ]
            },
            {
                "name": "Orders_Shop",
                "type": "foreignKey",
                "table": "ORDERS",
                "constraintName": "Orders_Shop",
                "columns": "ORDERS_SHOP",
                "referencedTable": "SHOP",
                "referencedColumns": "SHOP_ID"
            },
            {
                "name": "OrdersReportFilter_OrdersReportBar",
                "type": "foreignKey",
                "table": "ORDERSREPORTFILTER",
                "constraintName": "OrdersReportFilter_OrdersReportBar",
                "columns": "ORDERSREPORTFILTER_REPORT",
            },
            {
                "name": "OrdersReportFilter_Orders",
                "type": "foreignKey",
                "table": "ORDERSREPORTFILTER",
                "constraintName": "OrdersReportFilter_Orders",
                "columns": "ORDERSREPORTFILTER_ORDERS",
                "referencedTable": "ORDERS",
                "referencedColumns": "ORDERS_ID"
            }
        ]
    },
    "datasource": "DefaultDB"
}
image
@iliyan-velichkov iliyan-velichkov mentioned this issue Apr 5, 2024
Closed
@ThuF ThuF self-assigned this Apr 5, 2024
@ThuF ThuF added this to To do in Backlog via automation Apr 5, 2024
@ThuF ThuF added this to the 11.0.0 milestone Apr 5, 2024
@ThuF ThuF changed the title Generated invalid JSON for .schema file when having ReportEntity in edm [EDM] Generated invalid JSON for .schema file when having ReportEntity Apr 5, 2024
@iliyan-velichkov iliyan-velichkov mentioned this issue Apr 5, 2024
Closed
@ThuF
Copy link
Contributor

ThuF commented Apr 23, 2024 •
edited

There are few issues with the model:

  • OrdersReportFilter entity type was set to Primary Entity instead of Filter Entity
  • The Orders entity type was set to Primary Entity instead of Dependant Entity
  • The Date property of the Orders entity should be of type DATE instead of TIMESTAMP (this is important for the grouping of the orders in the report later, as otherwise, they should have the same time to be grouped)
  • The Orders UI layout type was set to Manage Master Entities instead of Manage Detail Entitis
  • The connection between the Orders and the Shops should be of type Composition and Cardinality of one-to-many
  • The Orders property in the OrdersReportFilter seems redundant
  • The connection between the OrdersReportFilter and the OrdersReportBar should be of type Association and Cardinality of one-to-one
  • The widget type of the StartPeriod and EndPeriod properties in theOrdersReportFilter were set to Text Box instead of Date Picker
  • The data queries in the OrdersReport were updated as follows:

Databse count:

select count(*) from
(
    select o.ORDERS_DATE as "OrderDate", sum(o.ORDERS_TOTAL) as "Total"
    from ${tablePrefix}ORDERS o
    join "${tablePrefix}SHOP" s on o.ORDERS_SHOP = s.SHOP_ID
    ${
        filter.Shop && filter.StartPeriod && filter.EndPeriod ?
            `where s.SHOP_ID = ? and o.ORDERS_DATE >= ? and o.ORDERS_DATE <= ?`
        :
        filter.Shop && filter.StartPeriod ?
            `where s.SHOP_ID = ? and o.ORDERS_DATE >= ?`
        :
        filter.Shop && filter.EndPeriod ?
            `where s.SHOP_ID = ? and o.ORDERS_DATE <= ?`
        :
        filter.StartPeriod && filter.EndPeriod ?
            `where o.ORDERS_DATE >= ? and o.ORDERS_DATE <= ? `
        :
        filter.Shop ?
            `where s.SHOP_ID = ?`
        :
        filter.StartPeriod ?
            `where o.ORDERS_DATE >= ?`
        :
        filter.EndPeriod ?
            `where o.ORDERS_DATE <= ?`
        :
            ''
    }
    group by s.SHOP_NAME, o.ORDERS_DATE
)

Databse query:

select o.ORDERS_DATE as "OrderDate", sum(o.ORDERS_TOTAL) as "Total"
from ${tablePrefix}ORDERS o
join "${tablePrefix}SHOP" s on o.ORDERS_SHOP = s.SHOP_ID
${
    filter.Shop && filter.StartPeriod && filter.EndPeriod ?
        `where s.SHOP_ID = ? and o.ORDERS_DATE >= ? and o.ORDERS_DATE <= ?`
    :
    filter.Shop && filter.StartPeriod ?
        `where s.SHOP_ID = ? and o.ORDERS_DATE >= ?`
    :
    filter.Shop && filter.EndPeriod ?
        `where s.SHOP_ID = ? and o.ORDERS_DATE <= ?`
    :
    filter.StartPeriod && filter.EndPeriod ?
        `where o.ORDERS_DATE >= ? and o.ORDERS_DATE <= ? `
    :
    filter.Shop ?
        `where s.SHOP_ID = ?`
    :
    filter.StartPeriod ?
        `where o.ORDERS_DATE >= ?`
    :
    filter.EndPeriod ?
        `where o.ORDERS_DATE <= ?`
    :
        ''
}
group by s.SHOP_NAME, o.ORDERS_DATE
${
    filter["$limit"] && filter["$offset"] ?
        'limit ? offset ?'
    :
    filter["$limit"] ?
        'limit ?'
    :
    filter["$offset"] ?
        'offset ?'
    :
        ''
}

Once the issues are fixed, the generation is executed successfuly.

Recommendation:

  • Use the dropdown for the correct entity types, so that you'll get the correct coloring

Here is the updated model:

<model>
 <entities>
  <entity name="Orders" dataName="ORDERS" dataCount="SELECT COUNT(*) AS COUNT FROM &quot;${tablePrefix}ORDERS&quot;" dataQuery="" type="DEPENDENT" title="Orders" caption="Manage entity Orders" tooltip="Orders" icon="/services/web/resources/unicons/file.svg" menuKey="orders" menuLabel="Orders" menuIndex="100" layoutType="MANAGE_DETAILS" navigationPath="/Home" perspectiveName="Orders" perspectiveLabel="Orders" perspectiveIcon="/services/web/resources/unicons/box.svg" perspectiveOrder="100" perspectiveRole="">
    <property name="Id" isRequiredProperty="false" isCalculatedProperty="false" calculatedPropertyExpressionCreate="" calculatedPropertyExpressionUpdate="" dataName="ORDERS_ID" dataType="INTEGER" dataLength="0" dataNullable="true" dataPrimaryKey="true" dataAutoIncrement="true" dataUnique="false" widgetType="TEXTBOX" widgetSize="" widgetLength="20" widgetIsMajor="true"></property>
    <property name="Date" isRequiredProperty="false" isCalculatedProperty="false" calculatedPropertyExpressionCreate="" calculatedPropertyExpressionUpdate="" dataName="ORDERS_DATE" dataType="DATE" dataLength="20" dataNullable="true" dataPrimaryKey="false" dataAutoIncrement="false" dataUnique="false" dataPrecision="NaN" dataScale="NaN" widgetType="DATE" widgetSize="" widgetLength="20" widgetIsMajor="true"></property>
    <property name="Shop" isRequiredProperty="true" isCalculatedProperty="false" calculatedPropertyExpressionCreate="" calculatedPropertyExpressionUpdate="" dataName="ORDERS_SHOP" dataType="INTEGER" dataLength="0" dataNullable="true" dataPrimaryKey="false" dataAutoIncrement="false" dataUnique="false" dataPrecision="NaN" dataScale="NaN" relationshipType="COMPOSITION" relationshipCardinality="1_n" relationshipName="ShopOrders" widgetType="DROPDOWN" widgetSize="" widgetLength="20" widgetIsMajor="true" widgetDropDownKey="Id" widgetDropDownValue="Name"></property>
    <property name="Total" isRequiredProperty="true" isCalculatedProperty="false" calculatedPropertyExpressionCreate="" calculatedPropertyExpressionUpdate="" dataName="ORDERS_TOTAL" dataType="DOUBLE" dataLength="20" dataNullable="true" dataPrimaryKey="false" dataAutoIncrement="false" dataUnique="false" dataPrecision="NaN" dataScale="NaN" widgetType="TEXTBOX" widgetSize="" widgetLength="20" widgetIsMajor="true"></property>
  </entity>
  <entity name="Shop" dataName="SHOP" dataCount="SELECT COUNT(*) AS COUNT FROM &quot;${tablePrefix}SHOP&quot;" dataQuery="" type="PRIMARY" title="Shop" caption="Manage entity Shop" tooltip="Shop" icon="/services/web/resources/unicons/file.svg" menuKey="shop" menuLabel="Shop" menuIndex="100" layoutType="MANAGE_MASTER" navigationPath="/Home" perspectiveName="Orders" perspectiveLabel="Orders" perspectiveIcon="/services/web/resources/unicons/box.svg" perspectiveOrder="100" perspectiveRole="">
    <property name="Id" isRequiredProperty="false" isCalculatedProperty="false" calculatedPropertyExpressionCreate="" calculatedPropertyExpressionUpdate="" dataName="SHOP_ID" dataType="INTEGER" dataLength="0" dataNullable="true" dataPrimaryKey="true" dataAutoIncrement="true" dataUnique="false" widgetType="TEXTBOX" widgetSize="" widgetLength="20" widgetIsMajor="true"></property>
    <property name="Name" isRequiredProperty="false" isCalculatedProperty="false" calculatedPropertyExpressionCreate="" calculatedPropertyExpressionUpdate="" dataName="SHOP_NAME" dataType="VARCHAR" dataLength="20" dataNullable="true" dataPrimaryKey="false" dataAutoIncrement="false" dataUnique="false" widgetType="TEXTBOX" widgetSize="" widgetLength="20" widgetIsMajor="true"></property>
  </entity>
  <relation name="Orders_Shop" type="relation" entity="Orders" relationName="Orders_Shop" relationshipEntityPerspectiveName="Orders" relationshipEntityPerspectiveLabel="Entities" property="Shop" referenced="Shop" referencedProperty="Id">
  </relation>
  <entity name="OrdersReportFilter" dataName="ORDERSREPORTFILTER" dataCount="SELECT COUNT(*) AS COUNT FROM &quot;${tablePrefix}ORDERSREPORTFILTER&quot;" dataQuery="" type="FILTER" title="OrdersReportFilter" caption="Manage entity OrdersReportFilter" tooltip="OrdersReportFilter" icon="/services/web/resources/unicons/file.svg" menuKey="ordersreportfilter" menuLabel="OrdersReportFilter" menuIndex="100" layoutType="MANAGE" navigationPath="/Home" perspectiveName="Reports" perspectiveLabel="Reports" perspectiveIcon="/services/web/resources/unicons/chart-line.svg" perspectiveOrder="200" perspectiveRole="">
    <property name="Report" isRequiredProperty="false" isCalculatedProperty="false" calculatedPropertyExpressionCreate="" calculatedPropertyExpressionUpdate="" dataName="ORDERSREPORTFILTER_REPORT" dataType="VARCHAR" dataLength="20" dataNullable="true" dataPrimaryKey="true" dataAutoIncrement="false" dataUnique="false" dataPrecision="NaN" dataScale="NaN" relationshipType="ASSOCIATION" relationshipCardinality="1_1" relationshipName="Filter" widgetType="TEXTBOX" widgetSize="" widgetLength="20" widgetIsMajor="true"></property>
    <property name="Shop" isRequiredProperty="false" isCalculatedProperty="false" calculatedPropertyExpressionCreate="" calculatedPropertyExpressionUpdate="" dataName="ORDERSREPORTFILTER_SHOP" dataType="INTEGER" dataLength="0" dataNullable="true" dataPrimaryKey="false" dataAutoIncrement="false" dataUnique="false" dataPrecision="NaN" dataScale="NaN" widgetType="DROPDOWN" widgetSize="" widgetLength="20" widgetIsMajor="true" widgetDropDownKey="Id" widgetDropDownValue="Name"></property>
    <property name="StartPeriod" isRequiredProperty="false" isCalculatedProperty="false" calculatedPropertyExpressionCreate="" calculatedPropertyExpressionUpdate="" dataName="ORDERSREPORTFILTER_STARTPERIOD" dataType="DATE" dataLength="20" dataNullable="true" dataPrimaryKey="false" dataAutoIncrement="false" dataUnique="false" dataPrecision="NaN" dataScale="NaN" widgetType="DATE" widgetSize="" widgetLength="20" widgetIsMajor="true"></property>
    <property name="EndPeriod" isRequiredProperty="false" isCalculatedProperty="false" calculatedPropertyExpressionCreate="" calculatedPropertyExpressionUpdate="" dataName="ORDERSREPORTFILTER_ENDPERIOD" dataType="DATE" dataLength="20" dataNullable="true" dataPrimaryKey="false" dataAutoIncrement="false" dataUnique="false" dataPrecision="NaN" dataScale="NaN" widgetType="DATE" widgetSize="" widgetLength="20" widgetIsMajor="true"></property>
  </entity>
  <entity name="OrdersReportBar" dataName="ORDERSREPORTBAR" dataCount="select count(*) from
(
    select o.ORDERS_DATE as &quot;OrderDate&quot;, sum(o.ORDERS_TOTAL) as &quot;Total&quot;
    from ${tablePrefix}ORDERS o
    join &quot;${tablePrefix}SHOP&quot; s on o.ORDERS_SHOP = s.SHOP_ID
    ${
        filter.Shop &amp;&amp; filter.StartPeriod &amp;&amp; filter.EndPeriod ?
            &#x60;where s.SHOP_ID = ? and o.ORDERS_DATE &gt;= ? and o.ORDERS_DATE &lt;= ?&#x60;
        :
        filter.Shop &amp;&amp; filter.StartPeriod ?
            &#x60;where s.SHOP_ID = ? and o.ORDERS_DATE &gt;= ?&#x60;
        :
        filter.Shop &amp;&amp; filter.EndPeriod ?
            &#x60;where s.SHOP_ID = ? and o.ORDERS_DATE &lt;= ?&#x60;
        :
        filter.StartPeriod &amp;&amp; filter.EndPeriod ?
            &#x60;where o.ORDERS_DATE &gt;= ? and o.ORDERS_DATE &lt;= ? &#x60;
        :
        filter.Shop ?
            &#x60;where s.SHOP_ID = ?&#x60;
        :
        filter.StartPeriod ?
            &#x60;where o.ORDERS_DATE &gt;= ?&#x60;
        :
        filter.EndPeriod ?
            &#x60;where o.ORDERS_DATE &lt;= ?&#x60;
        :
            &#x27;&#x27;
    }
    group by s.SHOP_NAME, o.ORDERS_DATE
)" dataQuery="select o.ORDERS_DATE as &quot;OrderDate&quot;, sum(o.ORDERS_TOTAL) as &quot;Total&quot;
from ${tablePrefix}ORDERS o
join &quot;${tablePrefix}SHOP&quot; s on o.ORDERS_SHOP = s.SHOP_ID
${
    filter.Shop &amp;&amp; filter.StartPeriod &amp;&amp; filter.EndPeriod ?
        &#x60;where s.SHOP_ID = ? and o.ORDERS_DATE &gt;= ? and o.ORDERS_DATE &lt;= ?&#x60;
    :
    filter.Shop &amp;&amp; filter.StartPeriod ?
        &#x60;where s.SHOP_ID = ? and o.ORDERS_DATE &gt;= ?&#x60;
    :
    filter.Shop &amp;&amp; filter.EndPeriod ?
        &#x60;where s.SHOP_ID = ? and o.ORDERS_DATE &lt;= ?&#x60;
    :
    filter.StartPeriod &amp;&amp; filter.EndPeriod ?
        &#x60;where o.ORDERS_DATE &gt;= ? and o.ORDERS_DATE &lt;= ? &#x60;
    :
    filter.Shop ?
        &#x60;where s.SHOP_ID = ?&#x60;
    :
    filter.StartPeriod ?
        &#x60;where o.ORDERS_DATE &gt;= ?&#x60;
    :
    filter.EndPeriod ?
        &#x60;where o.ORDERS_DATE &lt;= ?&#x60;
    :
        &#x27;&#x27;
}
group by s.SHOP_NAME, o.ORDERS_DATE
${
    filter[&quot;$limit&quot;] &amp;&amp; filter[&quot;$offset&quot;] ?
        &#x27;limit ? offset ?&#x27;
    :
    filter[&quot;$limit&quot;] ?
        &#x27;limit ?&#x27;
    :
    filter[&quot;$offset&quot;] ?
        &#x27;offset ?&#x27;
    :
        &#x27;&#x27;
}" type="REPORT" title="OrdersReportBar" caption="Manage entity OrdersReportBar" tooltip="OrdersReportBar" icon="/services/web/resources/unicons/file.svg" menuKey="ordersreportbar" menuLabel="OrdersReportBar" menuIndex="100" layoutType="REPORT_BAR" navigationPath="/Home" perspectiveName="Reports" perspectiveLabel="Reports" perspectiveIcon="/services/web/resources/unicons/chart-line.svg" perspectiveOrder="200" perspectiveRole="">
    <property name="OrderDate" isRequiredProperty="false" isCalculatedProperty="false" calculatedPropertyExpressionCreate="" calculatedPropertyExpressionUpdate="" dataName="ORDERSREPORTBAR_ORDERDATE" dataType="DATE" dataLength="20" dataNullable="true" dataPrimaryKey="true" dataAutoIncrement="false" dataUnique="false" dataPrecision="NaN" dataScale="NaN" widgetType="DATE" widgetSize="" widgetLength="20" widgetIsMajor="true"></property>
    <property name="Total" isRequiredProperty="false" isCalculatedProperty="false" calculatedPropertyExpressionCreate="" calculatedPropertyExpressionUpdate="" dataName="ORDERS_TOTAL" dataType="DOUBLE" dataLength="20" dataNullable="true" dataPrimaryKey="false" dataAutoIncrement="false" dataUnique="false" dataPrecision="NaN" dataScale="NaN" widgetType="TEXTBOX" widgetSize="" widgetLength="20" widgetIsMajor="true"></property>
  </entity>
  <relation name="OrdersReportFilter_OrdersReportBar" type="relation" entity="OrdersReportFilter" relationName="OrdersReportFilter_OrdersReportBar" relationshipEntityPerspectiveName="Reports" relationshipEntityPerspectiveLabel="Entities" property="Report" referenced="OrdersReportBar" referencedProperty="OrderDate">
  </relation>
  <relation name="OrdersReportFilter_Shop" type="relation" entity="OrdersReportFilter" relationName="OrdersReportFilter_Shop" relationshipEntityPerspectiveName="Orders" relationshipEntityPerspectiveLabel="Entities" property="Shop" referenced="Shop" referencedProperty="Id">
  </relation>
 </entities>
 <perspectives>
  <perspective><name>Orders</name><label>Orders</label><icon>/services/web/resources/unicons/box.svg</icon><order>100</order><role></role></perspective>
  <perspective><name>Reports</name><label>Reports</label><icon>/services/web/resources/unicons/chart-line.svg</icon><order>200</order><role></role></perspective>
 </perspectives>
 <navigations>
 </navigations>
 <mxGraphModel><root><mxCell id="0"/><mxCell id="1" parent="0"/><mxCell id="f05dab8e-2bed-3dbf-c598-a43c0db267c7" style="dependent" parent="1" vertex="1"><Entity name="Orders" dataName="ORDERS" dataCount="SELECT COUNT(*) AS COUNT FROM &quot;${tablePrefix}ORDERS&quot;" title="Orders" caption="Manage entity Orders" tooltip="Orders" menuLabel="Orders" layoutType="MANAGE_DETAILS" perspectiveName="Orders" type="Entity" menuKey="orders" disableGeneration="false" entityType="DEPENDENT" as="value"/><mxGeometry x="50" y="40" width="200" height="132" as="geometry"><mxRectangle width="200" height="28" as="alternateBounds"/></mxGeometry></mxCell><mxCell id="b6c286fd-5aab-efc3-281a-578848601675" parent="f05dab8e-2bed-3dbf-c598-a43c0db267c7" vertex="1" connectable="0"><Property name="Id" dataType="INTEGER" dataLength="0" dataPrimaryKey="true" dataAutoIncrement="true" dataName="ORDERS_ID" as="value"/><mxGeometry y="28" width="200" height="26" as="geometry"/></mxCell><mxCell id="5ef0987d-b249-3d49-d920-746beba58501" parent="f05dab8e-2bed-3dbf-c598-a43c0db267c7" vertex="1" connectable="0"><Property name="Date" dataName="ORDERS_DATE" dataType="DATE" dataPrecision="NaN" dataScale="NaN" widgetType="DATE" as="value"/><mxGeometry y="54" width="200" height="26" as="geometry"/></mxCell><mxCell id="5d783e08-db6a-caf9-d626-dca0f299cbff" parent="f05dab8e-2bed-3dbf-c598-a43c0db267c7" vertex="1" connectable="0"><Property name="Shop" isRequiredProperty="true" dataName="ORDERS_SHOP" dataType="INTEGER" dataLength="0" dataPrecision="NaN" dataScale="NaN" widgetType="DROPDOWN" widgetDropDownKey="Id" widgetDropDownValue="Name" relationshipName="ShopOrders" relationshipType="COMPOSITION" relationshipCardinality="1_n" as="value"/><mxGeometry y="80" width="200" height="26" as="geometry"/></mxCell><mxCell id="ff6291a9-c6d9-6b8d-8446-917b51143827" parent="f05dab8e-2bed-3dbf-c598-a43c0db267c7" vertex="1" connectable="0"><Property name="Total" isRequiredProperty="true" dataName="ORDERS_TOTAL" dataType="DOUBLE" dataPrecision="NaN" dataScale="NaN" as="value"/><mxGeometry y="106" width="200" height="26" as="geometry"/></mxCell><mxCell id="efb6aa26-c2f9-97a8-5326-30fcf797f4cc" style="entity" parent="1" vertex="1"><Entity name="Shop" dataName="SHOP" dataCount="SELECT COUNT(*) AS COUNT FROM &quot;${tablePrefix}SHOP&quot;" title="Shop" caption="Manage entity Shop" tooltip="Shop" menuLabel="Shop" layoutType="MANAGE_MASTER" perspectiveName="Orders" type="Entity" menuKey="shop" disableGeneration="false" as="value"/><mxGeometry x="390" y="40" width="200" height="80" as="geometry"><mxRectangle width="200" height="28" as="alternateBounds"/></mxGeometry></mxCell><mxCell id="2c5c3d11-7167-1f55-7040-8e5ad6df59af" parent="efb6aa26-c2f9-97a8-5326-30fcf797f4cc" vertex="1" connectable="0"><Property name="Id" dataType="INTEGER" dataLength="0" dataPrimaryKey="true" dataAutoIncrement="true" dataName="SHOP_ID" as="value"/><mxGeometry y="28" width="200" height="26" as="geometry"/></mxCell><mxCell id="cdaf46d6-6a72-0ce2-a38e-f408faab3e66" parent="efb6aa26-c2f9-97a8-5326-30fcf797f4cc" vertex="1" connectable="0"><Property name="Name" dataName="SHOP_NAME" as="value"/><mxGeometry y="54" width="200" height="26" as="geometry"/></mxCell><mxCell id="b44f8321-2177-60cf-d4c2-5683ff079a15" parent="1" source="5d783e08-db6a-caf9-d626-dca0f299cbff" target="2c5c3d11-7167-1f55-7040-8e5ad6df59af" edge="1"><Connector name="ShopOrders" as="value"/><mxGeometry relative="1" as="geometry"/></mxCell><mxCell id="8465de1c-9a39-75e1-5a52-fa88969e58ad" style="filter" parent="1" vertex="1"><Entity name="OrdersReportFilter" dataName="ORDERSREPORTFILTER" dataCount="SELECT COUNT(*) AS COUNT FROM &quot;${tablePrefix}ORDERSREPORTFILTER&quot;" title="OrdersReportFilter" caption="Manage entity OrdersReportFilter" tooltip="OrdersReportFilter" menuLabel="OrdersReportFilter" perspectiveName="Reports" type="Entity" menuKey="ordersreportfilter" disableGeneration="false" entityType="FILTER" as="value"/><mxGeometry x="230" y="360" width="200" height="132" as="geometry"><mxRectangle width="200" height="28" as="alternateBounds"/></mxGeometry></mxCell><mxCell id="f17feb4d-355b-30d2-4dee-6f13c44d2379" parent="8465de1c-9a39-75e1-5a52-fa88969e58ad" vertex="1" connectable="0"><Property name="Report" dataName="ORDERSREPORTFILTER_REPORT" dataPrimaryKey="true" dataPrecision="NaN" dataScale="NaN" relationshipType="ASSOCIATION" relationshipCardinality="1_1" relationshipName="Filter" as="value"/><mxGeometry y="28" width="200" height="26" as="geometry"/></mxCell><mxCell id="5f5d66f9-396d-56f9-2b54-349a5edfe699" parent="8465de1c-9a39-75e1-5a52-fa88969e58ad" vertex="1" connectable="0"><Property name="Shop" dataName="ORDERSREPORTFILTER_SHOP" dataType="INTEGER" dataLength="0" dataPrecision="NaN" dataScale="NaN" widgetType="DROPDOWN" widgetDropDownKey="Id" widgetDropDownValue="Name" as="value"/><mxGeometry y="54" width="200" height="26" as="geometry"/></mxCell><mxCell id="6917102f-a585-7655-faa2-1a6c298abeed" parent="8465de1c-9a39-75e1-5a52-fa88969e58ad" vertex="1" connectable="0"><Property name="StartPeriod" dataName="ORDERSREPORTFILTER_STARTPERIOD" dataType="DATE" dataPrecision="NaN" dataScale="NaN" widgetType="DATE" as="value"/><mxGeometry y="80" width="200" height="26" as="geometry"/></mxCell><mxCell id="d207ba02-4027-8377-8316-102209b0d195" parent="8465de1c-9a39-75e1-5a52-fa88969e58ad" vertex="1" connectable="0"><Property name="EndPeriod" dataName="ORDERSREPORTFILTER_ENDPERIOD" dataType="DATE" dataPrecision="NaN" dataScale="NaN" widgetType="DATE" as="value"/><mxGeometry y="106" width="200" height="26" as="geometry"/></mxCell><mxCell id="16310741-2b44-ff04-b35c-651c78585fc0" style="report" parent="1" vertex="1"><Entity name="OrdersReportBar" entityType="REPORT" dataName="ORDERSREPORTBAR" dataQuery="select o.ORDERS_DATE as &quot;OrderDate&quot;, sum(o.ORDERS_TOTAL) as &quot;Total&quot;&#10;from ${tablePrefix}ORDERS o&#10;join &quot;${tablePrefix}SHOP&quot; s on o.ORDERS_SHOP = s.SHOP_ID&#10;${&#10;    filter.Shop &amp;&amp; filter.StartPeriod &amp;&amp; filter.EndPeriod ?&#10;        `where s.SHOP_ID = ? and o.ORDERS_DATE &gt;= ? and o.ORDERS_DATE &lt;= ?`&#10;    :&#10;    filter.Shop &amp;&amp; filter.StartPeriod ?&#10;        `where s.SHOP_ID = ? and o.ORDERS_DATE &gt;= ?`&#10;    :&#10;    filter.Shop &amp;&amp; filter.EndPeriod ?&#10;        `where s.SHOP_ID = ? and o.ORDERS_DATE &lt;= ?`&#10;    :&#10;    filter.StartPeriod &amp;&amp; filter.EndPeriod ?&#10;        `where o.ORDERS_DATE &gt;= ? and o.ORDERS_DATE &lt;= ? `&#10;    :&#10;    filter.Shop ?&#10;        `where s.SHOP_ID = ?`&#10;    :&#10;    filter.StartPeriod ?&#10;        `where o.ORDERS_DATE &gt;= ?`&#10;    :&#10;    filter.EndPeriod ?&#10;        `where o.ORDERS_DATE &lt;= ?`&#10;    :&#10;        ''&#10;}&#10;group by s.SHOP_NAME, o.ORDERS_DATE&#10;${&#10;    filter[&quot;$limit&quot;] &amp;&amp; filter[&quot;$offset&quot;] ?&#10;        'limit ? offset ?'&#10;    :&#10;    filter[&quot;$limit&quot;] ?&#10;        'limit ?'&#10;    :&#10;    filter[&quot;$offset&quot;] ?&#10;        'offset ?'&#10;    :&#10;        ''&#10;}" dataCount="select count(*) from&#10;(&#10;    select o.ORDERS_DATE as &quot;OrderDate&quot;, sum(o.ORDERS_TOTAL) as &quot;Total&quot;&#10;    from ${tablePrefix}ORDERS o&#10;    join &quot;${tablePrefix}SHOP&quot; s on o.ORDERS_SHOP = s.SHOP_ID&#10;    ${&#10;        filter.Shop &amp;&amp; filter.StartPeriod &amp;&amp; filter.EndPeriod ?&#10;            `where s.SHOP_ID = ? and o.ORDERS_DATE &gt;= ? and o.ORDERS_DATE &lt;= ?`&#10;        :&#10;        filter.Shop &amp;&amp; filter.StartPeriod ?&#10;            `where s.SHOP_ID = ? and o.ORDERS_DATE &gt;= ?`&#10;        :&#10;        filter.Shop &amp;&amp; filter.EndPeriod ?&#10;            `where s.SHOP_ID = ? and o.ORDERS_DATE &lt;= ?`&#10;        :&#10;        filter.StartPeriod &amp;&amp; filter.EndPeriod ?&#10;            `where o.ORDERS_DATE &gt;= ? and o.ORDERS_DATE &lt;= ? `&#10;        :&#10;        filter.Shop ?&#10;            `where s.SHOP_ID = ?`&#10;        :&#10;        filter.StartPeriod ?&#10;            `where o.ORDERS_DATE &gt;= ?`&#10;        :&#10;        filter.EndPeriod ?&#10;            `where o.ORDERS_DATE &lt;= ?`&#10;        :&#10;            ''&#10;    }&#10;    group by s.SHOP_NAME, o.ORDERS_DATE&#10;)" title="OrdersReportBar" caption="Manage entity OrdersReportBar" tooltip="OrdersReportBar" menuLabel="OrdersReportBar" layoutType="REPORT_BAR" perspectiveName="Reports" type="Entity" disableGeneration="false" menuKey="ordersreportbar" as="value"><Array as="referenceProjections"/></Entity><mxGeometry x="280" y="210" width="200" height="80" as="geometry"><mxRectangle width="200" height="28" as="alternateBounds"/></mxGeometry></mxCell><mxCell id="82e5ce93-b11d-0697-91c3-ff440bbcf456" parent="16310741-2b44-ff04-b35c-651c78585fc0" vertex="1" connectable="0"><Property name="OrderDate" dataPrimaryKey="true" dataName="ORDERSREPORTBAR_ORDERDATE" dataPrecision="NaN" dataScale="NaN" dataType="DATE" widgetType="DATE" as="value"/><mxGeometry y="28" width="200" height="26" as="geometry"/></mxCell><mxCell id="6fdf30e0-5b29-0225-fab1-259cad1a0c19" parent="16310741-2b44-ff04-b35c-651c78585fc0" vertex="1" connectable="0"><Property name="Total" dataName="ORDERS_TOTAL" dataType="DOUBLE" dataPrecision="NaN" dataScale="NaN" as="value"/><mxGeometry y="54" width="200" height="26" as="geometry"/></mxCell><mxCell id="944d983b-ae96-a9fd-e885-73a45fc86833" parent="1" source="f17feb4d-355b-30d2-4dee-6f13c44d2379" target="82e5ce93-b11d-0697-91c3-ff440bbcf456" edge="1"><Connector name="Filter" as="value"/><mxGeometry relative="1" as="geometry"/></mxCell><mxCell id="6c1f1a33-cc24-cdfd-ed87-3c9b01847640" parent="1" source="5f5d66f9-396d-56f9-2b54-349a5edfe699" target="2c5c3d11-7167-1f55-7040-8e5ad6df59af" edge="1"><mxGeometry relative="1" as="geometry"/></mxCell></root></mxGraphModel>
</model>

@ThuF ThuF closed this as completed Apr 23, 2024
Backlog automation moved this from To do to Done Apr 23, 2024
@iliyan-velichkov
Copy link
Contributor Author

iliyan-velichkov commented Apr 26, 2024 •
edited

@ThuF I update the model with some of your suggestions
I have a question regarding - The Date property of the Orders entity should be of type DATE instead of TIMESTAMP (this is important for the grouping of the orders in the report later, as otherwise, they should have the same time to be grouped) ---> ok, but in the real world scenario orders are made in a specific time not just date. Do we have a solution for this scenario? You cannot tell the customer to update the model so that the charts can work.

Updated model commit - iliyan-velichkov/dirigible-charts@0ed3d59

@iliyan-velichkov
Copy link
Contributor Author

iliyan-velichkov commented Apr 26, 2024 •
edited

@ThuF and one more question - I have grouping by shop and date (group by s.SHOP_NAME, o.ORDERS_DATE) but in the chart, I don't see grouping by shop (I guess because it is not added to the select)
image

If I add , s.SHOP_NAME as "ShopName" to the select, the chart looks like that:
image
It is absolutely not clear what is visualized.

  • different colors are not used for the different shops
  • x axis for 26.04 is duplicated
  • shop names are not displayed
  • the legend is incorrect

Backlog automation moved this from Done to In progress Apr 26, 2024
@iliyan-velichkov
Copy link
Contributor Author

@ThuF could you please check my comments?

@ThuF
Copy link
Contributor

ThuF commented Apr 30, 2024 •
edited

@ThuF and one more question - I have grouping by shop and date (group by s.SHOP_NAME, o.ORDERS_DATE) but in the chart, I don't see grouping by shop (I guess because it is not added to the select) image

If I add , s.SHOP_NAME as "ShopName" to the select, the chart looks like that: image It is absolutely not clear what is visualized.

  • different colors are not used for the different shops
  • x axis for 26.04 is duplicated
  • shop names are not displayed
  • the legend is incorrect

That's currently not possible and out of scope with the current implementation. The Chart.js is expecting set of numeric values in order to display bar/line/donought, etc. Meaning that currently you can't have a query that groups by shop and by date at the same time. As a workaround you could do the grouping by dates, and in the Filter you can select the desired shop.

I'm closing this issue as it was originally about an incorrect EDM model. Simplified reporting capabilities would be introduced with:

@ThuF ThuF closed this as completed Apr 30, 2024
Backlog automation moved this from In progress to Done Apr 30, 2024
@delchev delchev moved this from Done to 11.x in Backlog May 12, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@ThuF ThuF

Labels
bug component-database component-template database edm
Projects
Backlog
11.x
Milestone
11.0.0
Development

No branches or pull requests
2 participants
@ThuF @iliyan-velichkov