I want to run a report, within a certain date (Date Range) and also with a condition (Radio Button).
The query is something like:
select this,that,other from table where date >= start and date <= end
here’s the condition: if one radio button is depressed, the query needs to have ‘AND number = 12345’
There is a MySQL IF statement, but that only returns a scalar, not a complete query.
When setting up the data for the Report Designer (or any control), there is an option for Expression and an option for SQL Query. Is there some way to make a hybrid, so FPMI will do a little processing (IF statement) and also SQL. Perhaps there could be a Query command in the function list for the Expression page or the ability to interpret expression syntax in the SQL Query page.
Another way I saw to do this was to have a separate button called ‘Run Report.’
report.data = toDataSet(runQuery) would retrieve the data.
The main snag here seems to be the date format for the Date Range. Using a Message Box, I ran the query and it was in a format like ‘Jan 01 00:00:00 CST 2008.’ There is a MySQL str_to_date function that allows one to specify the format, but unfortunately it doesn’t have the ‘CST’ or timezone option. When using the ‘SQL Binding’ option for the Report Control, this is automagically handled, and the select works. Am I missing something here? Is there a way to adjust the date format so it returns something proper to MySQL?
Thanks for the help,
Matt