Difference between revisions of "QPR Reporting Expression"

From Mea Wiki
Jump to navigation Jump to search
m (Ollvihe moved page Generic Web Service Query to Generic Web Service Datasource without leaving a redirect)
Line 2: Line 2:
  
 
== Examples ==
 
== Examples ==
The following query will count how many scorecards there are in each QPR Metrics model:
+
The following query counts how many scorecards there are in each QPR Metrics model:
  
 
<pre>
 
<pre>
Line 10: Line 10:
 
</pre>
 
</pre>
  
The following query will return 20 process element instances which have the greatest width in the flowchart.
+
The following query will return 20 process element instances which have the greatest width in the flowchart. Note that running the following query may take time, if there are lot of process models and elements inside them.
 +
 
 
<pre>
 
<pre>
 
instances=From('[PG].models.subobjects.instances', '', '', 'name,id', '')
 
instances=From('[PG].models.subobjects.instances', '', '', 'name,id', '')
Line 16: Line 17:
 
sortedData=SortBy([getWidth], 'elementWidth desc')
 
sortedData=SortBy([getWidth], 'elementWidth desc')
 
getMax20=Where(AddColumn([sortedData], 'rowordernumber', '[rowordernumber]'), '[rowordernumber] < 20')
 
getMax20=Where(AddColumn([sortedData], 'rowordernumber', '[rowordernumber]'), '[rowordernumber] < 20')
 +
</pre>
 +
 +
The following query is an example of joining two datasets. It returns a line for each user-group assignment.
 +
 +
<pre>
 +
users=From('[UM].users', '', '', 'name(as="username"),fullname,email,id(as="userid"),ingroups', '')
 +
groups=From('[UM].groups', '', '', 'name(as="groupname"),id(as="groupid")', '')
 +
joined=RemoveColumns(LeftJoin([users], [groups], 'Contains([ingroups], [groupid])'), Array('ingroups'))
 
</pre>
 
</pre>
 
[[Category: QPR UI]]
 
[[Category: QPR UI]]

Revision as of 14:29, 19 November 2017

When querying the Generic Web Service, use the QPR Reporting Expressions.

Examples

The following query counts how many scorecards there are in each QPR Metrics model:

scorecards=From('[SC].models.scorecard', '', '', 'model.name(as="modelname"),id')
countSums=SortBy(GroupBy([scorecards], Array('modelname'), Array('numberOfScorecards'), Array('ArraySize([id])')), 'numberOfScorecards DESC')
sortedResult=SortBy([countSums], 'numberOfScorecards DESC')

The following query will return 20 process element instances which have the greatest width in the flowchart. Note that running the following query may take time, if there are lot of process models and elements inside them.

instances=From('[PG].models.subobjects.instances', '', '', 'name,id', '')
getWidth=AddColumn(AddColumn([instances], 'elementInstanceId', 'InstanceIdFromFullId([id])'), 'elementWidth', 'ConvertToDouble(ItemAt(SubAttributesAsArray([id], \'graphicalproperties\', \'width\', \'[instanceid]=[elementInstanceId]\'), 0))')
sortedData=SortBy([getWidth], 'elementWidth desc')
getMax20=Where(AddColumn([sortedData], 'rowordernumber', '[rowordernumber]'), '[rowordernumber] < 20')

The following query is an example of joining two datasets. It returns a line for each user-group assignment.

users=From('[UM].users', '', '', 'name(as="username"),fullname,email,id(as="userid"),ingroups', '')
groups=From('[UM].groups', '', '', 'name(as="groupname"),id(as="groupid")', '')
joined=RemoveColumns(LeftJoin([users], [groups], 'Contains([ingroups], [groupid])'), Array('ingroups'))