Using 7.9.21.
I am trying to make a report with the following functionality -
if no users are provided, I run the report data as usual, but if users are provided, I only grab rows relating to said users.
I have users as a single column dataset with test like so -
This default expression works I can see it as a preview on the report if dragged and dropped.
My SQL looks like
SELECT [idx], [DPD_Group], [DPD_Project], [Project], [Project_Status], [Hot_Topic], [timeStamp], [userid]
FROM dpd.project_status_table
WHERE [last_curator_review_tstamp] BETWEEN :startDate AND :endDate
AND [Hot_Topic] = :hotTopic
AND [assetType] = :assetType
AND (:dpdProject=-1 OR [DPD_Project]=:dpdProject)
AND (:userIdLength=0 OR [userid] IN ({users}))
order by 'Project', 'timeStamp' DESC
My datasource is a script
assets = ['Milestones', 'Hot Topics', 'Emerging Issues']
for asset in assets:
userStr = ", ".join(["'%s'"%(user) for (user,) in system.dataset.toPyDataSet(data['users'])])
params = {'startDate':data['StartDate'], 'endDate':data['EndDate'], 'assetType':asset, 'dpdProject':data['dpdProject'], 'userIdLength':data['users'].rowCount, 'users':userStr}
data[asset] = system.db.runNamedQuery('Report/reportData', params)
When I go to preview my report I get this error -
WARN: Error invoking script.Traceback (most recent call last):
File "<function:updateData>", line 6, in updateData
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:354)
at sun.reflect.GeneratedMethodAccessor87.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
java.lang.NullPointerException: java.lang.NullPointerException
Where line 6 is where I call my named query.
This didn't seem very helpful to me so I tried running this named query in script console with the following -
users = system.dataset.toDataSet(["Users"],[["User"],["Curator"]])
data ={
"EndDate":system.date.now(),
"StartDate":system.date.addDays(system.date.now(),-7),
"dpdProject":-1,
"users":users
}
assets = ['Milestones', 'Hot Topics', 'Emerging Issues']
for asset in assets:
userStr = ", ".join(["'%s'"%(user) for (user,) in system.dataset.toPyDataSet(data['users'])])
print userStr
params = {'startDate':data['StartDate'], 'endDate':data['EndDate'], 'assetType':asset, 'dpdProject':data['dpdProject'], 'userIdLength':data['users'].rowCount, 'users':userStr}
print params
data[asset] = system.db.runNamedQuery('DPD Weeklies Report V2', params)
print data
The script console worked.
'User', 'Curator'
{'endDate': Wed Apr 03 10:16:27 EDT 2024, 'users': u"'User', 'Curator'", 'assetType': 'Milestones', 'userIdLength': 2, 'startDate': Wed Mar 27 10:16:27 EDT 2024, 'dpdProject': -1}
{'EndDate': Wed Apr 03 10:23:47 EDT 2024, 'Emerging Issues': Dataset [0R ? 8C], 'users': Dataset [2R ? 1C], 'StartDate': Wed Mar 27 10:23:47 EDT 2024, 'Milestones': Dataset [0R ? 8C], 'Hot Topics': Dataset [0R ? 8C], 'dpdProject': -1}
and this worked
Then I tested my named query directly and that does seem to work though
I can't tell what these error indicates so I don't see what I am doing wrong especially since the SQL is valid when tested directly. What is going on?