RunQuery Script Examples: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
Line 1: Line 1:
This page contains script examples for the [[SQL Scripting Commands#--.23RunQuery|RunQuery]] script command.
This page contains script examples for the [[SQL Scripting Commands#--.23RunQuery|RunQuery]] script command.


Following command runs an expression query (dimensioning by ''Company Code'' attribute and calculate cases counts for each) and stores results to a temporary table ''MyTempTable'' in the scripting sandbox:
Following command runs an expression query (dimensioning by ''Company Code'' attribute and calculate cases count for each) and stores results to a temporary table ''MyTempTable'' in the scripting sandbox:
<pre>
<pre>
(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'TargetTable', '#MyTempTable') UNION ALL

Revision as of 22:34, 10 November 2021

This page contains script examples for the RunQuery script command.

Following command runs an expression query (dimensioning by Company Code attribute and calculate cases count for each) and stores results to a temporary table MyTempTable in the scripting sandbox:

(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'Configuration', '
{
	"Dimensions": [
		{
			"Name": "dimension0",
			"Expression": "Attribute(\"Company Code\")"
		}
	],
	"Values": [
		{
			"Name": "measure0",
			"Expression": "Count(_)",
			"AggregationExpression": "Sum(_)"
		}
	],
	"Ordering": [
		{
			"Name": "measure0",
			"Direction": "Descending"
		}
	],
	"AggregateOthers": true,
	"MaximumRowCount": 20,
	"EnableResultCaching": true,
	"ModelId": 140559
}
')
--#RunQuery

Following command runs an expression query (list cases data) and stores results to a datatable MyDataTable in project id 123:

(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'ProjectId', '123') UNION ALL
(SELECT 'Configuration', '
{
	"Dimensions": null,
	"Values": [
		{
			"Name": "dimension0",
			"Expression": "Name"
		},
		{
			"Name": "dimension1",
			"Expression": "StartTime"
		},
		{
			"Name": "dimension2",
			"Expression": "EndTime"
		},
		{
			"Name": "dimension3",
			"Expression": "Duration?.TotalDays"
		},
		{
			"Name": "dimension4",
			"Expression": "LastEvent.TypeName"
		},
		{
			"Name": "dimension5",
			"Expression": "Variation.EventTypeCount"
		},
		{
			"Name": "dimension6",
			"Expression": "Variation.UniqueEventTypeCount"
		}
	],
	"AggregateOthers": false,
	"MaximumRowCount": 200,
	"Root": "Cases",
	"ModelId": 140559
}
')
--#RunQuery