I have a vision window that allows the user to select filters for running reports. Some of the selections are the "from and through" type filters (like dates) and these can be handled via report parameters, but other selections are a list of strings that appear in a SQL IN clause. I can't figure out how to pass that list as a parameter to named query. Is there a way to do this?
Ideally, to simplify things and to match the logic I use in another development platform, I would like to dynamically generate a SQL WHERE clause based on the user selections and and pass the where clause as a parameter or dynamically update the where clause of a data source of a named query. The benefit of this approach is that the named query does not need static parameters(the filters I have often involve up to 20 or more data columns).
I also thought of creating the SQL in scripting and run the SQL to build a table that is used as the data source of a report, but this will require a lot of work for each specific report. I am hoping for a more generic approach.
Hopefully someone can steer me in the right direction here.
If using a named query- you need to do it as a query string parameter, not a value parameter.
To generate your IN clause from the gui - I would opt for a table component that allows the user to select multiple rows, on property change detect the selectedRow component changing, in there is your opportunity to then create your string for SQL IN clause. I would write that to a custom property on the table. Then bind whatever you need to that cusom property.
I know some people do to the report style where you feed in the full raw dataset - and this would allow you to dynamically make the report based on anything. But I dislike it because then to regenrate a report I need to regenrate a dataset manually and to me it violates encapuslation a bit.
My suggestion - in your report have a param that accepts a string list of (I assume) the ID’s you want to include in your report. Then you can have a scripting data source where you can dynamically make your Query using that list of id’s for your IN statement, put it as a datakey and reference in your report. So going back to my first suggestion - then youre report viewer component could have it’s param bind to your tables custom property.
If you really want to go the route for dynamic filtering of many/few columns, I would at the least make it a function like
def getReportDataset(filters):
"""
Args:
filters: dictionary like
{"columnName":(operator, value)
where operator can be like ">=", "IN", "=="
"""
# construct query and return dataset
The above is a quick first draft. Probably a better way. You also know your system better than I do. Maybe there’s only a handful of cases you don’t need to be so generic and can use just a few kwargs that only are used if they are not None.
Concur. Don't use a named query for this. Use a prep query, and put it in a project library script that you only run from the gateway. Use a gateway message handler to run it when a Vision component needs the result. Run it from a scripted data source in reports.
When constructing your dynamic SQL, use ? substitutions for all of the elements you wish in the IN clause.
I didn't see the 'script' data source on the report (didn't look very hard, did I?), I will be able to use a gateway script along with a stored procedure or prep query statement, this should work great for me. Thanks to you both!
You have to add a datasource and you can select script. But either way works. Definitely will want to script the query if you have 20+ optional filters thats your pain point and scripting is your best bet.