Parameter Issue: Not Showing All Date Data

This will require Builder privileges for setup and implementation.

Issue:

Using an Input Parameter with an Initial Value of the macro ${currdate} in a chart or dashboard does not show all of the date data that exists in the data source. When you use Chart Data Diagnostics you notice that the defined Custom Query is:

SELECT col1, col2,
FROM tablename
WHERE datefield <= ${param:theDate}

The Input Parameter ${param:theDate} has an Initial Value of ${currdate}. You also notice in the Executed Custom Query that the date value in the query that gets sent to the database is:

SELECT col1, col2,
FROM tablename
WHERE datefield <= {ts '2012-01-01'}

This does not include the time stamp. Since your data source contains a time stamp some data is not returned.  By default this {ts '2012-01-01'} has the timestamp of 00:00:00. This will not take into account any time period after midnight on that particular date.

Resolution:

Set the Input Parameter Initial Value to: ${currdate,"yyyy-MM-dd '23:59:59'"}

You will notice now the Executed Custom Query that the date value in the query that gets sent to the database is:

SELECT col1, col2,
FROM tablename
WHERE datefield <= {ts '2012-01-01 23:59:59.000'}

The result set for the chart will now include any data that is prior to 23:59:59 on the current date. An Input Parameter Initial Value of ${currdate,"yyyy-MM-dd '00:00:00'"} or any other date can also be used.

For More Information:

• iDashboards Builder Manual 13. Input Parameters
• Charts: Formatting Parameters
• Creating Cascading Input Parameters
• Input Parameters 11:05

If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.