Report Help Basic SQL Query/SQL Query

Ignition
1

Hi

Im trying to build a report to get the value form a selectable Column Between StartDate and End Date

Im using Ignition 7.9
My Database is a MariaDB

My Table look like this
MyTable_ndx INT
ColumnName1 FLOAT
ColumnName2 FLOAT
ColumnName3 FLOAT
t_stamp DATETIME

Since i have way more that 3 column Id like to have a single query that use a parameter instead of the Column Name. The StartDate and EndDate need to be parameter too.
i’ve try both SQL and Basic SQL and im having an issue with Both.

The report using the Basic SQL Query seem to work fine in the report preview but when i run it in a Report Viewer in a Window in Preview Mode the report remain empty im pretty sure it has to do with the date formating

SELECT
MyTable_ndx as ‘Index’,
t_stamp as ‘Time’,
{ColumnNameParameter} as ‘ColumnName’
FROM MyTable
WHERE (t_stamp BETWEEN ‘{StartDate}’ AND ‘{EndDate}’)

i have bind start and end date in the report viewer using the binding type Expression to something like this
dateFormat({StartDateTag}, “yyyy-MM-dd HH:mm:ss”) but my report still come out empty showing the date formated like this “Apr 3,2017”. There really seem to be an issue whit the date formating in the report viewer

I’ve also try The SQL Query and it seem to work properly but since i cant bind a parameter to a column name i would have to build a report for each Column which im trying to avoid. And while the report viewer show the correct value from the database the startdate and Enddate text field still show the date formated like “Apr 3,2017”.

My SQL Query is
SELECT
MyTable_ndx as ‘Index’,
t_stamp as ‘Time’,
ColumnName1 as ‘ColumnName’
FROM MyTable
WHERE (t_stamp BETWEEN ? AND ?)

With Parameter 1 and 2 being {StartDate} and {EndDate}

So my main question would be Why is the report viewer not using the date formated with the format i’ve bind to it?

Do you have any advice that would avoid me to create a report for every single column of my table?

Thanks

2

A Basic SQL Query will allow you to parametize column names using syntax like this:
SELECT {ParameterReference} FROM MyTable

You will also need to use the dateFormat function of the expression language so that the StartDate and EndDate parameters are inserted into the query using the correct syntax for that database. This will look something like this:
dateFormat({StartDate}, “MM-dd-yyyy HH:mm:ss”)

You will have to define these formatted dates as separate parameters below the existing StartDate and EndDate parameters passed into the report, then reference them in your query.

Lastly, in text boxes of the report designer (including table fields), there is a date format property, which determines the way that dates are actually displayed.

3

Well Thanks!

Adding 2 parameter {FormatedStartDate} {FormatedEndDate} and using them in the query instead of {StartDate} {EndDate} fixed the date formating issue in the report.

Im not sure i understand why this is working, I guess {StartDate} and {EndDate} being default parameter, can’t be formated the way i need them.

1 Like
4

The StartDate and EndDate parameters point to Date objects. In most queries in Ignition, these get nicely coerced to syntactically correct strings, but in reporting they do not, so you must create the string representation yourself.

5

I have this same problem. Is this a bug? Because if I hard code the output of those parameters into my nested query they work fine, If I make my SQL query have parameters from the named query they work fine.

But if I use the report's parameters everything fails apart. the "jan" error comes up

What I have tried:

report prams set up:
Start date(end is the same but uses now()):

dateFormat(dateArithmetic(now(), -30, 'days'), 'yyyy-MM-dd HH:mm:ss')
image

Source on how i found to set this up:
Working with Dates

I tried making them strings as well and still get a fail

When that did not work I took off date format there and tried to format it in the nested SQL call all failed:
toDate()
dateFormat()
Convert('date',)
CAST({ChartStart} AS DATETIME)

I tried to do

AND datColumn BETWEEN ? AND
?
But since this is a nested Basic SQL query in the report the boxes do not show up for me to put the report parameters in.
For fun I tired this and failed:

AND datColumn BETWEEN {?} AND
{?}

I have look at all the following documentation but can not find any other examples on why the SQL query does not except the reports date formatted parameters or how to deal with them:
Report Parameters
Report prams video in the university
Ignition User Manual 7.9 ... Report Data SQL Query

Edit: Instead of a Basic SQL queries used a SQL query. I was able to use the ? in the SQL call

AND datColumn BETWEEN ? AND ?

but I get this error now:

ERROR: class java.lang.String cannot be cast to class java.util.Date (java.lang.String and java.util.Date are in module java.base of loader 'bootstrap')

I also tried to play around with the parameters making them strings, date, getting rid of the dateformt etc. On day 3 of trying to solve this with no luck.

6

dateFormat() returns a string. Don't do that. You need to supply a date object for date parameters.

Use ? without curly braces to pass parameters in a report's normal SQL query. Basic queries need curly braces, but those convert to strings, which screws up dates.

3 Likes
7

Final output. I tried this on day one before I posted an no clue why it is working now and did not work 3 days ago. ignition reset 2 hour timer was running so I know it was not that:

Reprot pram set up:
image

query:

image
image901×691 10.9 KB

Edit: I tired everything to recreate the issue I was having and can not seem to replicate it. So I am leaving my above post, there is a lot of good information there, but I just can not say what the root problem was for me. That said I am glad it is fixed now.