How to dynamically assign a source table for report data

Ignition
1

I have a simple SQL script to find the time stamps of some data when their respective integer value is 1. The source database's name is updated weekly with that new date in its name. The parent query finds this name from a list of database names. Since the database's name changes, I need to somehow dynamically change the child SQL query to account for that. Since you can only pass "values" into queries and not "query strings" in the parameter section, how should I approach this issue? The simple code screenshot is below...

Screenshot 2025-02-11 104336
Screenshot 2025-02-11 1043361251×427 33.9 KB

2

I think what you are calling "source database's name" is actually a table name. Have you tried using FROM QUOTENAME(?) I haven't tested that, but it may work.

Alternatively, to get variable table names, you could dynamically create your query using a script instead of using the built-in query datasource type. Maybe this example below can help you. It has tagid and tablename as parameters (as well as a dynamic database name if your application calls for it). Warning: Be sure to protect yourself from SQL Injections.

Note that if you are trying to get data from the Ignition Historian this way, I'd recommend using the built-in system functions and loop through your data.

def updateData(data, sample):
	#SQL Query
	sql = """DECLARE @tablename as VARCHAR(50), @tagid as BIGINT;
	SET @tablename = ?;
	SET @tagid = ?;
	
	SELECT t_stamp
	FROM @tablename
	WHERE tagid = @tagid) 
	"""
	
	data['YourDataKey'] = system.db.runPrepQuery(sql,args=[data['tablename'],data['tagid']],database=data['variabledatabase'])
2 Likes
3

Consider NOT trying to directly query the historian's data partitions. Use a history query on the tag of interest and returning "As stored" data. Set a reasonable start timestamp. In your script data source following, discard the rows that don't have value==1.

2 Likes
4

This seems a strange setup. Most of us would have one database / table and, if necessary, add a column to store the weekly name. It would simplify the data storage and retrieval as well as making monthly, annual, reports or comparisons much easier. The tables should have appropriate indexes to match likely WHERE clauses in the SELECT queries.

5

Thanks everyone!