RunQuery Script Examples

This page contains script examples for the RunQuery script command. Note that single quotes (') in the Configuration parameter needs to be escaped by using two single quotes (usually expression queries don't contain single quotes).

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