Reporting - Usage of start date and end date parameters

Ignition
1

Hi,

I have created a report, which has to get the historical data from database, based on start date and end date. I have used Basic SQL query for this as given below.

SELECT transaction_group_table.[Turbine_1_ Power_Output],
  transaction_group_table.[Turbine_2_ Power_Output,
  Format(transaction_group_table.[t_stamp],'yyyy-MM-dd') as t_stamp	 
FROM transaction_group_table
where transaction_group_table.[t_stamp] between {StartDate} and {EndDate}

I have formatted the startdate and enddate parameters in this format “yyyy-MM-dd”.

I’m not getting the results using the above query. What am I missing. Please help.

2

[quote=“sudhamoorthy”]Hi,

I have created a report, which has to get the historical data from database, based on start date and end date. I have used Basic SQL query for this as given below.

SELECT transaction_group_table.[Turbine_1_ Power_Output],
  transaction_group_table.[Turbine_2_ Power_Output,
  Format(transaction_group_table.[t_stamp],'yyyy-MM-dd') as t_stamp	 
FROM transaction_group_table
where transaction_group_table.[t_stamp] between {StartDate} and {EndDate}

I have formatted the startdate and enddate parameters in this format “yyyy-MM-dd”.

I’m not getting the results using the above query. What am I missing. Please help.[/quote]

I have found with the new reporting module that I get better results if I create a WhereClause parameter and pass string values into report.

look at this thread and scroll to bottom inductiveautomation.com/forum/v … 72&t=15446

1 Like
3

In general, when you are using parameters in a reporting query, you should use the regular SQL Query, not the Basic SQL Query. In the regular SQL Query, replace {StartDate} and {EndDate} with question marks, and you will get some expression editor boxes at the bottom of your screen. In those editors, you can enter any valid expression, including {StartDate}

4 Likes
4

Thanks a lot for your help, Kathy. That solved my issue. :slight_smile:

1 Like
5

Hi Kathy,

I did the same way but data do not show on table of report

date
date1466×803 59.2 KB

6

Check the Preview panel to see if you are getting any data back with your query. (The XML on the right pane of that panel). If you are, then it’s an issue with your table – you might not have associated the data source with the table correctly