Use script from project library in report datasource

Ignition
1

I'm using Ignition 8.1.7. I have a complicated script in my script library that returns a dataset. I need to use this dataset in a report. When I run the script in a script data source, it does not show up on the data source list in the design view of the report.

def updateData(data, sample):
	"""
	This function has the opportunity to add additional data into the report's
	data map.

	Arguments:
		data: This is a map, whose keys are report data keys and values should
		      be sequences, maps, scalar values, or datasets to provide information to
		      the report.
		sample: A flag that will be True if the report data being gathered is
		        for a preview of the report. Use to avoid slow queries and calculations
		        to keep previews quick.
	"""
	
	tableDataset = emissions.SO2report(startDate, endDate)
	data['Table'] = tableDataset

When I run this script in the script console, it works.

startDate = '12/01/2023'
endDate = '12/31/2023'

print emissions.SO2report(startDate, endDate)

I think it either has something to do with the date format or the reporting module can't connect to the project script library. Any suggestions?

2

Can you share the actual code in the project script?

Have you looked in the gateway's console for any errors?

1 Like
3

Did you try with the same hardcoded dates as in your console example, to make sure ?
That should at least tell you if there's an issue with the dates.

Side note: I'd suggest using actual date objects in scripts, not their string representations.

4 Likes
4

Here is the project script:

def getChargeTons(startDate, endDate):

	query = 'Charging/select_ChargeTotals'
	params = {'startDate': startDate, 'endDate': endDate}
	ChargeTonsDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	ChargeTons = ChargeTonsDB.getValueAt(0, 'totaltons')
	
	return ChargeTons
	
def getEF(stack, endDate):
	
	query = 'StackTest/select_TestBeforeDate'
	params = {'stackID': stack, 'chemicalID': 1, 'endDate': endDate}
	EFDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	EF = EFDB.getValueAt(0, 'amount')

	return EF

def getSCslagcharge(endDate):

	query = 'SulferContent/select_SulferRollingAvgbySample'
	params = {'sampleID': 5, 'endDate': endDate}
	SCslagchargeDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	SCslagcharge = SCslagchargeDB.getValueAt(0, 'avg_sulfer')/100
	
	return SCslagcharge
	
def getSCcokecharge(endDate):

	query = 'SulferContent/select_SulferRollingAvgbySample'
	params = {'sampleID': 4, 'endDate': endDate}
	SCcokechargeDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	SCcokecharge = SCcokechargeDB.getValueAt(0, 'avg_sulfer')/100

	return SCcokecharge
	
def getSCcoketest(stack, endDate):

	query = 'StackTest/select_TestBeforeDate'
	params = {'stackID': stack,'chemicalID': 2, 'endDate': endDate}
	SCcoketestDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	SCcoketest  = (SCcoketestDB.getValueAt(0, 'amount'))/100
	
	return SCcoketest
	
def getSCslagtest(stack, endDate):
				
	query = 'StackTest/select_TestBeforeDate'
	params = {'stackID': stack,'chemicalID': 3, 'endDate': endDate}
	SCslagtestDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	SCslagtest = (SCslagtestDB.getValueAt(0, 'amount'))/100
	
	return SCslagtest
							
def getTcoketest(stack, endDate):
	
	query = 'StackTest/select_TestBeforeDate'
	params = {'stackID': stack,'chemicalID': 4, 'endDate': endDate}
	TcoketestDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	Tcoketest = TcoketestDB.getValueAt(0, 'amount')
	
	return Tcoketest

def getTslagtest(stack, endDate):
		
	query = 'StackTest/select_TestBeforeDate'
	params = {'stackID': stack,'chemicalID': 5, 'endDate': endDate}
	TslagtestDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	Tslagtest = TslagtestDB.getValueAt(0, 'amount')
	
	return Tslagtest
	
def getARsulfurcontentcoke(stack, startDate, endDate):
	
	SCcoketest = getSCcoketest(stack, endDate)
	SCcokecharge = getSCcokecharge(endDate)
	
	if SCcoketest > 0:
		return SCcokecharge/SCcoketest
	else:
		return 0
		
def getARsulfurcontentslag(stack, startDate, endDate):
				
	SCslagtest = getSCslagtest(stack, endDate)
	SCslagcharge = getSCslagcharge(endDate)

	if SCslagtest > 0:
		return SCslagcharge/SCslagtest
	else:
		return 0		
		
def getChargeTotals(startDate, endDate):

	query = 'Charging/select_PoundsPerMonthbyMaterial'
	params = {'startDate': startDate, 'endDate': endDate}
	ChargeTotalsDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	ChargeTotals = ChargeTotalsDB.getValueAt(0, 'total_pounds')

	return ChargeTotals

def getSTinputsulfermass(stack, startDate, endDate):

	SCslagtest = getSCslagtest(stack, endDate)
	SCcoketest = getSCcoketest(stack, endDate)

	Tslagtest = getTslagtest(stack, endDate)
	Tcoketest = getTcoketest(stack, endDate)
	
	STinputsulfermass = (SCslagtest * Tslagtest) + (SCcoketest * Tcoketest)
	
	return STinputsulfermass

def getWSOflyashsulfercontent(endDate):

	query = 'SulferContent/select_SulferRollingAvgbySample'
	params = {'sampleID': 2, 'endDate': endDate}
	WSOflyashsulfercontentDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	WSOflyashsulfercontent = (WSOflyashsulfercontentDB.getValueAt(0, 'avg_sulfer'))/100
	
	return WSOflyashsulfercontent

def getWSOslagwastesulfercontent(endDate):
	
	query = 'SulferContent/select_SulferRollingAvgbySample'
	params = {'sampleID': 3, 'endDate': endDate}
	WSOslagwastesulfercontentDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	WSOslagwastesulfercontent = (WSOslagwastesulfercontentDB.getValueAt(0, 'avg_sulfer'))/100
	
	return WSOslagwastesulfercontent

def getWSOwoolsulfercontent(endDate):
		
	query = 'SulferContent/select_SulferRollingAvgbySample'
	params = {'sampleID': 1, 'endDate': endDate}
	WSOwoolsulfercontentDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	WSOwoolsulfercontent = (WSOwoolsulfercontentDB.getValueAt(0, 'avg_sulfer'))/100
	
	return WSOwoolsulfercontent
		
def getWSOflyash(startDate, endDate):

	# get the date and amount of the last flyash bin before the start date
	query = 'Flyash/select_LastWeightBefore'
	params = {'startDate': startDate}
	flyashStartDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	flyashStart = flyashStartDB.getValueAt(0, 'amount')	
	flyashStartDate = flyashStartDB.getValueAt(0, 'date_weighed')	
	#print 'flyashStartDate'
	#print flyashStartDate
	
	# get the date of the next flyash bin after the end date
	query = 'Flyash/select_nextWeightAfter'
	params = {'endDate': endDate}
	flyashEndDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	flyashEnd = flyashEndDB.getValueAt(0, 'amount')	
	flyashEndDate = flyashEndDB.getValueAt(0, 'date_weighed')	
	#print 'flyashEndDate'
	#print flyashEndDate
	
	#get the total flyash between the two dates (these queries doesn't have the date <= but only <, etc)
	query = 'Flyash/select_WasteTotalPounds'
	params =  {'startDate': flyashStartDate, 'endDate' : flyashEndDate}
	flyashTotalDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	flyashTotal = flyashTotalDB.getValueAt(0, 'total')
	#print 'flyashTotal'
	#print flyashTotal
	
	#get the total runtime during that period
	query = 'Cupola/select_CupolaRunTimeTotal'
	params = {'startDate': flyashStartDate, 'endDate' : flyashEndDate}
	flyashRuntimeDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	flyashRuntime = flyashRuntimeDB.getValueAt(0, 'hours')
	#print 'flyashRuntime'
	#print flyashRuntime
	
	#flyash average lbs/hr between the date of the bin before the month and the date of the bin after the month
	if flyashRuntime > 0:
		WSOflyash = (flyashTotal / flyashRuntime)		
	else:
		WSOflyash = 0
	
	return WSOflyash

def getWSOwool(startDate, endDate):	
	
	query = 'Wool/select_WoolBinTotal'
	params = {'startDate': startDate, 'endDate' : endDate}
	woolBinTotalDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	woolBinTotal = woolBinTotalDB.getValueAt(0, 'WoolTotal')	
	
	query = 'Wool/select_BaleTotal'
	params = {'startDate': startDate, 'endDate' : endDate}
	woolBaleTotalDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	woolBaleTotal = woolBaleTotalDB.getValueAt(0, 'WoolTotal')	
			
	query = 'Wool/select_CafcoBagWoolTotal'
	params = {'startDate': startDate, 'endDate' : endDate}
	cafcoWoolTotalDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	cafcoWoolTotal = cafcoWoolTotalDB.getValueAt(0, 'WoolTotal')	
	
	WSOwool = woolBinTotal + cafcoWoolTotal + woolBaleTotal
	
	return WSOwool
							
def getWSOslagwaste(startDate, endDate):

	#initialize the material Total
	materialTotal = 0
	#get total wool and total material to calculate recovery
	WSOwool = getWSOwool(startDate, endDate)
	query = 'Charging/select_PoundsPerMonthbyMaterial'
	params = {'startDate': startDate, 'endDate': endDate}
	ChargeTotalsDB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	
	for row in range(ChargeTotalsDB.rowCount):
		if ChargeTotalsDB.getValueAt(row, 'material') == 'rocks':
			materialTotal = (ChargeTotalsDB.getValueAt(row, 'total_pounds'))/2000
	
	if materialTotal > 0:
		WSOslagwaste = materialTotal - WSOwool
	else:
		WSOslagwaste = 0 
		
	return WSOslagwaste
		
def getMWPWSsulfermass(startDate, endDate):

	WSOflyash = getWSOflyash(startDate, endDate)
	WSOflyashsulfercontent = getWSOflyashsulfercontent(endDate)

	WSOwool = getWSOwool(startDate, endDate)
	WSOwoolsulfercontent = getWSOwoolsulfercontent(endDate)

	WSOslagwaste = getWSOslagwaste(startDate, endDate)
	WSOslagwastesulfercontent = getWSOslagwastesulfercontent(endDate)
	
	MWPWSsulfermass = ((WSOslagwastesulfercontent/100) * WSOslagwaste) + ((WSOwoolsulfercontent/100) * WSOwool) + ((WSOflyashsulfercontent/100) * WSOflyash)
		
	return	MWPWSsulfermass
		
def getH12(startDate, endDate):
	
	query = 'Cupola/select_CupolaRunTimeTotal'
	params = {'startDate': startDate, 'endDate' : endDate}
	H12DB = system.dataset.toPyDataSet(system.db.runNamedQuery(query,params))
	H12 = H12DB.getValueAt(0, 'hours')	

	return H12

def SO2(stack, startDate, endDate):

	Tmaterial = getChargeTons(startDate, endDate)
	EF = getEF(stack, endDate)
	print 'EF'
	print EF
	ARsulfurcontentslag = getARsulfurcontentslag(stack, startDate, endDate)
	print 'ARsulfurcontentslag'
	print ARsulfurcontentslag
	ARsulfurcontentcoke = getARsulfurcontentcoke(stack, startDate, endDate)
	print 'ARsulfurcontentcoke'
	print ARsulfurcontentcoke
	STinputsulfermass = getSTinputsulfermass(stack, startDate, endDate)
	print 'STinputsulfermass'
	print STinputsulfermass
	MWPWSsulfermass = getMWPWSsulfermass(startDate, endDate)
	print 'MWPWSsulfermass'
	print MWPWSsulfermass
	H12 = getH12(startDate, endDate)
	print 'H12'
	print H12
	SO2base =(Tmaterial * EF)
	print 'SO2base'
	print SO2base
	WasteFactorAdjustment = ((STinputsulfermass-MWPWSsulfermass)/STinputsulfermass)
	SO2 = (SO2base + ((SO2base * (ARsulfurcontentslag-1)) + (SO2base * (ARsulfurcontentcoke-1))) * WasteFactorAdjustment)/H12
	print 'SO2'
	print SO2
	
	return SO2

def SO2report(startDate, endDate):
	
	reportDSHeader = ["month", "EFcupola", "EFscreenhouse", "Material", "Hours", "SO2cupola", "SO2screenhouse"]
	reportDS = []
	
	Tmaterial = getChargeTons(startDate, endDate)
	MWPWSsulfermass = getMWPWSsulfermass(startDate, endDate)
	H12 = getH12(startDate, endDate)
	
	stack = 1
	EF_1 = getEF(stack, endDate)
	ARsulfurcontentslag_1 = getARsulfurcontentslag(stack, startDate, endDate) - 1
	ARsulfurcontentcoke_1 = getARsulfurcontentcoke(stack, startDate, endDate) - 1
	STinputsulfermass_1 = getSTinputsulfermass(stack, startDate, endDate)
	SO2base_1 =(Tmaterial * EF_1)
	WasteFactorAdjustment_1 = (STinputsulfermass_1-MWPWSsulfermass)/STinputsulfermass_1
	print 'ARsulfurcontentcoke_1'
	print ARsulfurcontentcoke_1
	SO2_1 = (SO2base_1 + (((SO2base_1 * ARsulfurcontentslag_1) + (SO2base_1 * ARsulfurcontentcoke_1)) * WasteFactorAdjustment_1))/H12
	
	stack = 2
	EF_2 = getEF(stack, endDate)
	ARsulfurcontentslag_2 = getARsulfurcontentslag(stack, startDate, endDate) - 1
	ARsulfurcontentcoke_2 = getARsulfurcontentcoke(stack, startDate, endDate) - 1
	STinputsulfermass_2 = getSTinputsulfermass(stack, startDate, endDate)
	SO2base_2 =(Tmaterial * EF_2)
	WasteFactorAdjustment_2 = ((STinputsulfermass_2-MWPWSsulfermass)/STinputsulfermass_2)
	print 'ARsulfurcontentcoke_2'
	print ARsulfurcontentcoke_2
	SO2_2 = (SO2base_2 + (((SO2base_2 * ARsulfurcontentslag_2) + (SO2base_2 * ARsulfurcontentcoke_2)) * WasteFactorAdjustment_2))/H12	
	
	
	#month = system.date.getMonth(startDate)
	month = "December"
	EFcupola = EF_1
	EFscreenhouse = EF_2
	Material = Tmaterial
	Hours = H12
	SO2cupola = SO2_1
	SO2screenhouse = SO2_2
	
	reportDS.append([month, EFcupola, EFscreenhouse, Material, Hours, SO2cupola, SO2screenhouse])
	
	return system.dataset.toDataSet(reportDSHeader, reportDS)
5

When I use this script, I get a dataset in return.

startDate = system.date.getDate(2023,11,01)
endDate = system.date.getDate(2023,11,31)
print startDate
print endDate

print emissions.SO2report(startDate, endDate)

I don't see any relevant errors in the console

6

All of your runNamedQuery calls supply just query and params. In gateway scope, you also need to include the project name, as documented.

1 Like
7

Here's a script which can be used to determine the scope. In cases where a library script may be called from multiple scopes.

8

When I add the project name I get this error - even in the script console.

Cannot coerce value 'namedQuery' into type: interface java.util.Map

Here is the script in the console. It works fine when I take the project name out.

project = system.project.getProjectName()
query = 'namedQuery'
params = {'startDate': startDate, 'endDate': endDate}
EFDB = system.dataset.toPyDataSet(system.db.runNamedQuery(project, query,params))

I also tried hard coding the project name

project = 'projectName'
query = 'namedQuery'
params = {'startDate': startDate, 'endDate': endDate}
EFDB = system.dataset.toPyDataSet(system.db.runNamedQuery(project, query,params))
9

Testing in the script console is utterly useless and meaningless for this. Reports run in the gateway.

What error(s) are showing up in the gateway log?

10

It gives an out of bounds error on line 160. But when I test the query in the designer with the same dates, I get data.

11

Show the complete error (as preformatted text), and the complete script (so we can find line 160).

12

I fixed the out of bounds error by putting in some error checking. Now the script works in the report, but not in the script console... So I will go with that. Now I just have to figure out why it isn't grabbing the correct data.

I'm still confused why it doesn't work in the script console with the project name in the runNamedQuery, which kind of makes it difficult to use the script console as a code debug tool.

13

The script console runs in designer scope, which is a variant of Vision Client scope. it doesn't work the same as a script running in the gateway. Full stop.

1 Like
14

It's too bad we can't change the scope of the script console. And the system.db.runNamedQuery will not work with the project name in the project scope.

15

The best way to test gateway scoped is to set up a gateway message handler with the function/code you want to test, then call that from the script console using system.util.sendMessage/sendRequest.

If you use sendRequest, you can get a response back (possibly passing data back to inspect).

1 Like