Ignition 8 Reporting - Component binding to SQL query

Ignition
1

I have a reporting sql query that saves as a PDF via a button on screen(Vision); the time range is changed by component on screen as well. I want to implement a dropdown to say either sort by ascending or descending order. I can build the query manually but I can not pass the option in from the window the same way I am doing the StartDate/EndDate. The SQL query doesnt like any form of “asc” or “desc” that I try to pass to it. Please help, much appreciated.

2

Please show your query.

Ascending vs Descending in a query’s ORDER BY clause would be considered part of the query structure, not data. You must use curly-brace string substitution, not a ‘?’ placeholder, for structure.

3

I tried this and I still have an error message along the bottom when I go to the preview tab.

image
image694×265 26.9 KB

Order_Input has a default value of 1 and here is the logic to evaluate it.

image

4

Is your query a ‘SQL Query’ or a ‘Basic SQL Query’ type? Only the latter allows query structure subsitution, I believe.

From some google, it seems like a CASE statement might be valid for the ORDER BY clause; something like

ORDER BY
  CASE WHEN 'ASC' THEN SortColumn ELSE '' END ASC,
  CASE WHEN 'DESC' THEN SortColumn ELSE '' END DESC

Ramifications for performance may be dramatic, and in general this isn’t a “recommended” approach.

5

Another possibility would be to always query ascending, but use a script data source to reverse it conditionally.

6

it is a SQL query and the start/end dates are working fine.

7

Or you could convert to a Named Query–those permit both types of substitution.