Reporting - Usage of start date and end date parameters

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=“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

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}

2 Likes

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

2 Likes

Hi Kathy,

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

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