Reporting Question

Ignition
1

Hello,

I know this may be a simple and silly question. This is my first time working with reporting.

I’m working with the new reporting module. I’ve created a table and brought my data in using an SQL query. I’ve also created a simple window and added the report viewer component and a date range selector. I bound the report viewer start and end date parameters to the date range selectors start and end date properties.

Is there something in the report builder that I’m missing that is causing the data not to show according to the date range selected?

Thank you for any help that anyone can provide.

2

Is your SQL query that gets the data referencing the start and end date in your date range selector? You need to add this to your query.

3

It does not. I will make sure to add a where clause for that. Thank you for your help!!

4

Ok so now I have a new issue.
Here is the query:

[color=#0000FF]SELECT[/color]
power_output.*
[color=#0000FF]FROM [/color]
power_output
[color=#0000FF]WHERE [/color]
t_stamp >= {StartDate}
[color=#0000FF]AND [/color]
t_stamp < {EndDate}

I get an error:
Incorrect syntax near ‘Jan’

I don’t understand why it is converting it to that if the table has it as ‘2016-01-11’.

Again thank you for any help that anyone can provide.

5

Probably need single quotes around the dates

SELECT * FROM power_output WHERE t_stamp >= '{StartDate}' AND t_stamp < '{EndDate}'

6

Thank you for your response Pat,
but I’ve tried that and it returns Incorrect syntax near ‘Mon Jan 11 00:00:00 EST 2016’. I’m really confused as to what it’s doing.

7

If also tried

SELECT power_output.*
FROM power_output
WHERE t_stamp BETWEEN '{StartDate}'AND '{EndDate}'
8

Hi Jason,

I just put together a Report datasource using a Basic SQL Query that looks like:

SELECT * FROM sqlt_data_1_2015_06 WHERE t_stamp < '{StartDate}'

where the sqlt_data is just a random history table. It resolved how I would expect it to, without error. Same if I add the AND clause. Does your query work ok with a single WHERE?

What happens if you create a new parameter named “TestDate”, of type ‘Date’ and contents of ‘now()’ and try a simple query of select * from <tablename> where t_stamp < '{TestDate}' ?

9

I’ve run into this a few times – the conversion of dates to strings as they are embedded in the query uses the platform’s date conversion preferences. That isn’t acceptable to all database types. I’ve typically created custom string properties for each date and manually formatted them for the database. Then included the string properties into the query.
In scripting you can use ‘Prep’ queries to have JDBC do the conversions for you in a database-independent way. This isn’t available in query bindings (yet?).

10

Thank you for all your help.

I ended up using the SQL Query instead of the Basic SQL Query and it worked just fine.

11

I would like to know what you ended up doing to get this to work or if it was just changing from basic query to SQL Query. I have been trying to get this to work for hours with the properties bound and being called in SQL Query…I have no errors but the data does not update?

12

Hi pturmel,

I created data in report, and see data in report designer. But when I link to data viewer page with dateRange component, I change dateRange component on data viewer, but report did not change. Do you know why.

Thanks

13

Not off the top of my head, no. Inspect all of your bindings to make sure the date range is being passed to the report. And that the SQL in the report is using the start and end date parameters.