#486 SQL dashboards limitations

[Denis]

SQL dashboards are fast and use very little memory compared to OQL based ones, but they currently have some critical limitations:

- the name of the SQL table is in clear in the query. The dashboard will not work if the application is installed with a DB prefix.

- the query is not "contextual" (i.e. does not take into account the fact that an Organization is selected), whereas other dashboards are context based.

[Denis]

• status changed from new to closed
• resolution set to fixed

Fix committed as revision [1729] in trunk, merged as [1730] in the 1.2 branch

- In the SQL query put $DB_PREFIX$ in front of each SQL table name.
- define "context" parameter(s) and put $CONDITION(param_name,sql_column_name)$ in SQL query where appropriate to limit your query depending on the context.

Example:

Displays the list of UserRequest opened in the last 45 days, grouped by the day of the month. Supports drill-down and organization's context

<sqlblock>
    <sql>SELECT date_format(start_date, '%d') AS Date, count(*) AS Count FROM $DB_PREFIX$ticket as ticket JOIN $DB_PREFIX$ticket_response AS tr ON ticket.id = tr.id WHERE DATE_SUB(NOW(), INTERVAL 45 DAY) &lt; start_date AND finalclass = 'UserRequest' AND $CONDITION(param1, tr.org_id)$ GROUP BY date_format(start_date, '%d')</sql>
    <type>table</type>
    <title>UserRequest:Overview-Title</title>
    <parameter>
        <name>param1</name>
        <type>context</type>
        <mapping>org_id</mapping>
    </parameter>
    <column>
        <name>Date</name>
        <label>UserRequest:Overview-Date</label>
        <drilldown></drilldown>
    </column>
    <column>
        <name>Count</name>
        <label>UserRequest:Overview-Count</label>
        <drilldown>SELECT UserRequest WHERE DATE_FORMAT(start_date, '%d') = :Date</drilldown>
    </column>
</sqlblock>