Velocity Report with calculated lookup value

Patrick Szabo

Hi all,

we use Jama also for Risk Analysis and just updated to 8.79.1 (self hosted) today. I setup the new lookup matrix feature to calculate P total and the Risk Level. In the previous version, we simply used pickup lists to select the P total and the Risk Level manually.

When I now run our velocity report to export the risk analysis, I only get a number for the calculated lookup value, instead of the text. With just the picklist, the following code worked to get the text value and the associated color:

#set( $prePTotal = "")
	#set( $prePTotal = $velocityReportUtil.getValueForField($r, "p_total", $dateFormat))
		##Get the color it displays in Jama
		###set( $newItem = $contourItemDao.getContourItem($doc.id))
		###set( $customFields = $newItem.getCustomFields())
		#set( $docTypeField = $documentTypeFieldDao.getDocumentTypeFieldByName("p_total", $r.documentType.id))
		#set( $lookupTypeId = $docTypeField.getLookupTypeId())
		#set( $fieldId = $docTypeField.documentField.id)
		#set( $lookupTypeList = [])
		#if( $lookupTypeList.add($lookupTypeId)) #end
		#set( $getLookupIdsReturn = $lookupDao.getLookupIdsByLookupTypes($lookupTypeList))
		#set( $lookupValues = [])
		#foreach( $l in $getLookupIdsReturn)
			#set( $lookup = $lookupDao.getLookup($mathTool.toInteger($l)))
			#if( $lookupValues.add( $lookup)) #end
			##$lookup<br>
			##$lookup.name, $lookup.color, $lookup.description<br>
		#end
		#set( $color = '')
		#foreach( $l in $lookupValues)
			#if( $l.name == $prePTotal)
				#set( $color = $l.color)
				#break
			#end
		#end

Output example:

What would I need to change to get the text and the color of the calculated lookup value?

Thanks!

------------------------------
Patrick
------------------------------

Patrick Szabo

I found now a workaround which works fine, but it is just a workaround. Would be nice if there would be a direct possibility to get the correct values and colors.
Here the code which works for us:

#set( $riskLevel = "")
#set( $riskLevelString = "")
#set( $color = '')
#set( $riskLevel = $velocityReportUtil.getValueForField($r, "estimated_risk_level_lu", $dateFormat))
	
#if( $riskLevel == "892")
	#set( $riskLevelString = "Low")
	#set( $color = '00CC66')
#elseif( $riskLevel == "893")
	#set( $riskLevelString = "Medium")
	#set( $color = 'FF9933')
#elseif( $riskLevel == "894")
	#set( $riskLevelString = "High")
	#set( $color = 'FF0000')
#else
	#set( $riskLevelString = "Undetermined")
	#set( $color = 'FFFFFF')
	#end
<td class=xl88 width=90 rowspan=$riskRowSpan style='background:#$color;mso-pattern:#$color none;align:left;width:68pt'>$!riskLevelString</td>

Fortunately, the numbers (e.g. 892, 893,...) always correlate with the same text. Otherwise this would not work like that.

------------------------------
Patrick
------------------------------
-------------------------------------------
Original Message:
Sent: 12-20-2022 08:23
From: Patrick Szabo
Subject: Velocity Report with calculated lookup value

Hi all,

we use Jama also for Risk Analysis and just updated to 8.79.1 (self hosted) today. I setup the new lookup matrix feature to calculate P total and the Risk Level. In the previous version, we simply used pickup lists to select the P total and the Risk Level manually.

When I now run our velocity report to export the risk analysis, I only get a number for the calculated lookup value, instead of the text. With just the picklist, the following code worked to get the text value and the associated color:

#set( $prePTotal = "")	#set( $prePTotal = $velocityReportUtil.getValueForField($r, "p_total", $dateFormat))		##Get the color it displays in Jama		###set( $newItem = $contourItemDao.getContourItem($doc.id))		###set( $customFields = $newItem.getCustomFields())		#set( $docTypeField = $documentTypeFieldDao.getDocumentTypeFieldByName("p_total", $r.documentType.id))		#set( $lookupTypeId = $docTypeField.getLookupTypeId())		#set( $fieldId = $docTypeField.documentField.id)		#set( $lookupTypeList = [])		#if( $lookupTypeList.add($lookupTypeId)) #end		#set( $getLookupIdsReturn = $lookupDao.getLookupIdsByLookupTypes($lookupTypeList))		#set( $lookupValues = [])		#foreach( $l in $getLookupIdsReturn)			#set( $lookup = $lookupDao.getLookup($mathTool.toInteger($l)))			#if( $lookupValues.add( $lookup)) #end			##$lookup<br>			##$lookup.name, $lookup.color, $lookup.description<br>		#end		#set( $color = '')		#foreach( $l in $lookupValues)			#if( $l.name == $prePTotal)				#set( $color = $l.color)				#break			#end		#end

Output example:

What would I need to change to get the text and the color of the calculated lookup value?

Thanks!

------------------------------
Patrick
------------------------------

Bridget Dowling

Hi

Looks like I am encountering the same issue. Just wondering if anyone has an explanation or understanding of these values. 

I am getting 1786 for Low, 1785 for medium and 1784 for high

Thanks

Bridget

------------------------------
Bridget Dowling
Analog Devices Int
Limerick
------------------------------
-------------------------------------------
Original Message:
Sent: 12-22-2022 23:25
From: Patrick Szabo
Subject: Velocity Report with calculated lookup value

I found now a workaround which works fine, but it is just a workaround. Would be nice if there would be a direct possibility to get the correct values and colors.
Here the code which works for us:

#set( $riskLevel = "")#set( $riskLevelString = "")#set( $color = '')#set( $riskLevel = $velocityReportUtil.getValueForField($r, "estimated_risk_level_lu", $dateFormat))	#if( $riskLevel == "892")	#set( $riskLevelString = "Low")	#set( $color = '00CC66')#elseif( $riskLevel == "893")	#set( $riskLevelString = "Medium")	#set( $color = 'FF9933')#elseif( $riskLevel == "894")	#set( $riskLevelString = "High")	#set( $color = 'FF0000')#else	#set( $riskLevelString = "Undetermined")	#set( $color = 'FFFFFF')	#end<td class=xl88 width=90 rowspan=$riskRowSpan style='background:#$color;mso-pattern:#$color none;align:left;width:68pt'>$!riskLevelString</td>

Fortunately, the numbers (e.g. 892, 893,...) always correlate with the same text. Otherwise this would not work like that.

------------------------------
Patrick
------------------------------

Original Message:
Sent: 12-20-2022 08:23
From: Patrick Szabo
Subject: Velocity Report with calculated lookup value

Hi all,

we use Jama also for Risk Analysis and just updated to 8.79.1 (self hosted) today. I setup the new lookup matrix feature to calculate P total and the Risk Level. In the previous version, we simply used pickup lists to select the P total and the Risk Level manually.

When I now run our velocity report to export the risk analysis, I only get a number for the calculated lookup value, instead of the text. With just the picklist, the following code worked to get the text value and the associated color:

#set( $prePTotal = "")	#set( $prePTotal = $velocityReportUtil.getValueForField($r, "p_total", $dateFormat))		##Get the color it displays in Jama		###set( $newItem = $contourItemDao.getContourItem($doc.id))		###set( $customFields = $newItem.getCustomFields())		#set( $docTypeField = $documentTypeFieldDao.getDocumentTypeFieldByName("p_total", $r.documentType.id))		#set( $lookupTypeId = $docTypeField.getLookupTypeId())		#set( $fieldId = $docTypeField.documentField.id)		#set( $lookupTypeList = [])		#if( $lookupTypeList.add($lookupTypeId)) #end		#set( $getLookupIdsReturn = $lookupDao.getLookupIdsByLookupTypes($lookupTypeList))		#set( $lookupValues = [])		#foreach( $l in $getLookupIdsReturn)			#set( $lookup = $lookupDao.getLookup($mathTool.toInteger($l)))			#if( $lookupValues.add( $lookup)) #end			##$lookup<br>			##$lookup.name, $lookup.color, $lookup.description<br>		#end		#set( $color = '')		#foreach( $l in $lookupValues)			#if( $l.name == $prePTotal)				#set( $color = $l.color)				#break			#end		#end

Output example:

What would I need to change to get the text and the color of the calculated lookup value?

Thanks!

------------------------------
Patrick
------------------------------