GetAnalysis Script Examples: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
 
(18 intermediate revisions by the same user not shown)
Line 1: Line 1:
This page contains script examples for the [[QPR ProcessAnalyzer Scripting Commands#--.23GetAnalysis|GetAnalysis]] script command.
This page contains script examples for the [[SQL Scripting Commands#--.23GetAnalysis|GetAnalysis]] script command.


The following example runs the KPI analysis.
This example runs an [[Web_API:_Expression/query|expression analysis]].
<pre>
<pre>
(SELECT 'AnalysisType', '33') UNION ALL
(SELECT 'AnalysisType', '33') UNION ALL
Line 11: Line 11:
"Dimensions": [
"Dimensions": [
{
{
"name": "Month",
"Name": "Date",
"expression": "Duration.ToInteger(TotalDays/30)"
"Expression": "Duration.TotalDays"
"NumberPrecision": 0
}
}
],
],
"Values": [
"Values": [
{
{
"name": "Count",
"Name": "Count",
"expression": "Count(_)"
"Expression": "Count(_)"
}
}
],
],
"Ordering": [
"Ordering": [
{
{
"name": "Month",
"Name": "Date",
"Direction": "Ascending"
"Direction": "Ascending"
}
}
]
]
}'
}
)
')
--#GetAnalysis
--#GetAnalysis
</pre>
</pre>


Cases list:
<pre>
<pre>
(SELECT 'AnalysisType', '33') UNION ALL
(SELECT 'AnalysisType', '33') UNION ALL
(SELECT 'ModelId', '123') UNION ALL
(SELECT 'Show', 'True') UNION ALL
(SELECT 'Show', 'True') UNION ALL
(SELECT 'Configuration', '
(SELECT 'Configuration', '
{
{
"Root": "DatatableById(49).DataFrame.Where(Column(\"DELIVERY MODE\")==\"Truck\")",
"Root": "Cases",
}'
"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"
}
],
"MaximumRowCount": 200
}
')
--#GetAnalysis
--#GetAnalysis
</pre>
</pre>


The following example will get a Case analysis and open that analysis with "Case Analysis From Script" as the title on a sheet named "Case Analysis Sheet".
This example runs an expression analysis with a root expression only.
<pre>
<pre>
(SELECT 'AnalysisType', '5') UNION ALL
(SELECT 'AnalysisType', '33') UNION ALL
(SELECT 'ProjectName', '<ProjectName>') UNION ALL
(SELECT 'Show', 'True') UNION ALL
(SELECT 'ModelName', '<ModelName>') UNION ALL
(SELECT 'Configuration', '
(SELECT 'MaximumCount', '10') UNION ALL
{
(SELECT 'Title', 'Case Analysis From Script') UNION ALL
"Root": "DatatableById(49).DataFrame.Where(Column(\"DELIVERY MODE\")==\"Truck\")",
(SELECT 'SheetName', 'Case Analysis Sheet') UNION ALL
}
(SELECT 'Show', 'True')
')
--#GetAnalysis
--#GetAnalysis
</pre>
</pre>


The following example will get an Event analysis, open that analysis with "Analysis Title" as the title on a sheet named "Example Sheet Name", and store the Event analysis results to the "#ExampleTable" data table.
<pre>
(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '3') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'Title', 'Analysis Title') UNION ALL
(SELECT 'SheetName', 'Excel Sheet Name') UNION ALL
(SELECT 'TargetTable', '#ExampleTable')
--#GetAnalysis
</pre>
The following example will get a Duration analysis and open that analysis with "Duration Analysis From Script" as the title on a sheet named "Duration Analysis Sheet".
<pre>
(SELECT 'AnalysisType', '9') UNION ALL
(SELECT 'ProjectName', '<ProjectName>') UNION ALL
(SELECT 'ModelName', '<ModelName>') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'Granularity', '1') UNION ALL
(SELECT 'Title', 'Duration Analysis From Script') UNION ALL
(SELECT 'SheetName', 'Duration Analysis Sheet') UNION ALL
(SELECT 'Show', 'True')
--#GetAnalysis
</pre>
The following example will load data from the "ExampleTable" data table in the "ExampleProject" project and put that data into the "CSV1" table.  
The following example will load data from the "ExampleTable" data table in the "ExampleProject" project and put that data into the "CSV1" table.  
<pre>
<pre>
Line 86: Line 97:
(SELECT 'DataTableName', 'ExampleTable') UNION ALL  
(SELECT 'DataTableName', 'ExampleTable') UNION ALL  
(SELECT 'TargetTable', '#CSV1')  
(SELECT 'TargetTable', '#CSV1')  
--#GetAnalysis
</pre>
The following example will get a Model Report analysis, store the analysis results to a temporary table called "#ModelResult" and show the results on an Excel sheet. This table contains the same information as is visible in the Project Workspace. For explanations of the columns, see [[Project Workspace in QPR ProcessAnalyzer Excel Client#Models|Models]].
<pre>
(SELECT 'AnalysisType', '21') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'TargetTable', '#ModelResult')
--#GetAnalysis
</pre>
The following example will get a Project Report analysis, store the analysis results to a temporary table called "#ProjectResult" and show the results on an Excel sheet. This table contains the same information as is visible in the Project Workspace. For explanations of the columns, see [[Project Workspace in QPR ProcessAnalyzer Excel Client#Models|Models]].
<pre>
(SELECT 'AnalysisType', '22') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'TargetTable', '#ProjectResult')
--#GetAnalysis
</pre>
The following example will get a Data Table Report analysis, store the analysis results to a temporary table called "#DataTableResult" and show the results on an Excel sheet. This table contains the same information as is visible in the Project Workspace. For explanations of the columns, see [[Project Workspace in QPR ProcessAnalyzer Excel Client#Data_Tables|Data Tables]].
<pre>
(SELECT 'AnalysisType', '23') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'TargetTable', '#DataTableResult')
--#GetAnalysis
</pre>
The following example will get a Script Report analysis, store the analysis results to a temporary table called "#ScriptResult" and show the results on an Excel sheet. This table contains the same information as is visible in the Manage Scripts dialog. For explanations of the columns, see [[Manage Scripts in QPR ProcessAnalyzer Excel Client|Manage Scripts]].
<pre>
(SELECT 'AnalysisType', '24') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'TargetTable', '#ScriptResult')
--#GetAnalysis
</pre>
The following example will create a pivot table based on the analysis data.
<pre>
(SELECT 'AnalysisType', '5') UNION ALL
(SELECT 'SelectedCaseAttributes', '*') UNION ALL
(SELECT 'FilterId', CAST(@_FilterId AS NVARCHAR)) UNION ALL
(SELECT 'Show', 'TRUE') UNION ALL
(SELECT 'Title', 'Title for the case analysis') UNION ALL
(SELECT 'SheetName', 'sheet name for CA') UNION ALL
(SELECT 'PivotSheetName', 'sheet name for pivot') UNION ALL
(SELECT 'PivotTitle', 'Pivot report title name') UNION ALL
(SELECT 'PivotRows', 'Account Manager') UNION ALL
(SELECT 'PivotColumns', 'Region') UNION ALL
(SELECT 'PivotValues', 'Duration Days')
--#GetAnalysis
</pre>
The following example will get a Path Analysis and put it on a sheet named "Path Sheet".
<pre>
(SELECT 'AnalysisType', '3') UNION ALL
(SELECT 'ProjectId', '1') UNION ALL
(SELECT 'ModelId', '1') UNION ALL
(SELECT 'IncludeDurations', 'TRUE') UNION ALL
(SELECT 'DurationType', '0') UNION ALL
(SELECT 'CostType', '0') UNION ALL
(SELECT 'ShowCostForEventType', 'TRUE') UNION ALL
(SELECT 'ShowAmountForFlow', 'TRUE') UNION ALL
(SELECT 'ShowAmountForEventType', 'FALSE') UNION ALL
(SELECT 'Direction', '1') UNION ALL
(SELECT 'PathType', '1') UNION ALL
(SELECT 'TreeExpansionDepth', '-1') UNION ALL
(SELECT 'MaxChildNodeCount', '0') UNION ALL
(SELECT 'AbsolutePath', '0') UNION ALL
(SELECT 'RootEventType', '14') UNION ALL
(SELECT 'Title', 'Path Analysis from Script') UNION ALL
(SELECT 'SheetName', 'Path Sheet') UNION ALL
(SELECT 'Show', 'True')
--#GetAnalysis
</pre>
The following example will get a Flowchart Analysis and put it on a sheet named "Flowchart Sheet".
<pre>
(SELECT 'AnalysisType', '0') UNION ALL
(SELECT 'ProjectName', '<Project Name>') UNION ALL
(SELECT 'ModelName', '<Model Name>') UNION ALL
(SELECT 'CostType', '1') UNION ALL
(SELECT 'IncludeDurations', 'TRUE') UNION ALL
(SELECT 'DurationType', '0') UNION ALL
(SELECT 'Title', 'Flowchart Analysis from Script') UNION ALL
(SELECT 'SheetName', 'Flowchart Sheet') UNION ALL
(SELECT 'Show', 'True')
--#GetAnalysis
</pre>
The following example will get a Variation Analysis as a chart and put it on a sheet named "Variation Sheet".
<pre>
(SELECT 'AnalysisType', '1') UNION ALL
(SELECT 'ProjectId', '<Project Id>') UNION ALL
(SELECT 'ModelId', '<Model Id>') UNION ALL
(SELECT 'ShowOthersGroup', 'TRUE') UNION ALL
(SELECT 'Values', '1') UNION ALL
(SELECT 'CostType', '1') UNION ALL
(SELECT 'IncludeDurations', 'TRUE') UNION ALL
(SELECT 'DurationType', '0') UNION ALL
(SELECT 'Title', 'Variation Chart from Script') UNION ALL
(SELECT 'SheetName', 'Variation Sheet') UNION ALL
(SELECT 'Show', 'True')
--#GetAnalysis
</pre>
The following example will get a Variation Analysis as a table and put it on a sheet named "Variation Table Sheet".
<pre>
(SELECT 'AnalysisType', '8') UNION ALL
(SELECT 'ProjectId', '1') UNION ALL
(SELECT 'ModelId', '1') UNION ALL
(SELECT 'MaximumCount', '10') UNION ALL
(SELECT 'GroupBy', '0') UNION ALL
(SELECT 'MaximumCount', '10') UNION ALL
(SELECT 'Title', 'Variation Analysis from Script') UNION ALL
(SELECT 'SheetName', 'Variation Table Sheet') UNION ALL
(SELECT 'Show', 'TRUE')
--#GetAnalysis
</pre>
The following example will get an Event Type Analysis as a table and put it on a sheet named "Event Type Table Sheet".
<pre>
(SELECT 'AnalysisType', '7') UNION ALL
(SELECT 'ProjectId', '1') UNION ALL
(SELECT 'ModelId', '1') UNION ALL
(SELECT 'MaximumCount', '1000') UNION ALL
(SELECT 'ShowRelativeStart', 'TRUE') UNION ALL
(SELECT 'ConfidencePercentage', '50') UNION ALL
(SELECT 'MinTransitionUsagePercentage', '0.05') UNION ALL
(SELECT 'Title', 'Event Type Analysis from Script') UNION ALL
(SELECT 'SheetName', 'Event Type Table Sheet') UNION ALL
(SELECT 'Show', 'TRUE')
--#GetAnalysis
</pre>
The following example will get an Event Type Analysis as a chart and put it on a sheet named "Event Type Chart Sheet".
<pre>
(SELECT 'AnalysisType', '4') UNION ALL
(SELECT 'ProjectId', '1') UNION ALL
(SELECT 'ModelId', '1') UNION ALL
(SELECT 'MaximumCount', '1000') UNION ALL
(SELECT 'ShowRelativeStart', 'FALSE') UNION ALL
(SELECT 'ConfidencePercentage', '50') UNION ALL
(SELECT 'MinTransitionUsagePercentage', '0.05') UNION ALL
(SELECT 'Title', 'Event Type Analysis from Script') UNION ALL
(SELECT 'SheetName', 'Event Type Chart Sheet') UNION ALL
(SELECT 'Show', 'True')
--#GetAnalysis
</pre>
The following example will get a Profiling Analysis with the "Account Manager" attribute selected, and put the analysis on a sheet named "Profiling Sheet".
<pre>
(SELECT 'AnalysisType', '10') UNION ALL
(SELECT 'ProjectId', '1') UNION ALL
(SELECT 'ModelId', '1') UNION ALL
(SELECT 'MaximumCount', '100') UNION ALL
(SELECT 'SelectedAttributeType', '5') UNION ALL
(SELECT 'AttributeName', 'Account Manager') UNION ALL
(SELECT 'Title', 'Profiling Analysis from Script') UNION ALL
(SELECT 'SheetName', 'Profiling Sheet') UNION ALL
(SELECT 'Show', 'TRUE')
--#GetAnalysis
</pre>
The following example will get a Flow Analysis and put it on a sheet named "Flow Sheet".
<pre>
(SELECT 'AnalysisType', '13') UNION ALL
(SELECT 'ProjectId', '1') UNION ALL
(SELECT 'ModelId', '1') UNION ALL
(SELECT 'MaximumCount', '100') UNION ALL
(SELECT 'IncludeDurations', 'TRUE') UNION ALL
(SELECT 'CostType', '1') UNION ALL
(SELECT 'Title', 'Flow Analysis from Script') UNION ALL
(SELECT 'SheetName', 'Flow Sheet') UNION ALL
(SELECT 'Show', 'TRUE')
--#GetAnalysis
</pre>
The following example will get an Influence Analysis based on the cases going through the "Customer pick-up" event type and put it on a sheet named "Influence Sheet".
<pre>
(SELECT 'AnalysisType', '14') UNION ALL
(SELECT 'ProjectId', '1') UNION ALL
(SELECT 'ModelId', '1') UNION ALL
(SELECT 'MaximumCount', '100') UNION ALL
(SELECT 'IncludeDurations', 'TRUE') UNION ALL
(SELECT 'DurationType', '0') UNION ALL
(SELECT 'DurationWeightedByCost', 'FALSE') UNION ALL
(SELECT 'SelectedActivities', '3') UNION ALL
(SELECT 'PrettySelection', 'Event Types=Customer pick-up') UNION ALL
(SELECT 'Title', 'Influence Analysis from Script') UNION ALL
(SELECT 'SheetName', 'Influence Sheet') UNION ALL
(SELECT 'Show', 'TRUE')
--#GetAnalysis
--#GetAnalysis
</pre>
</pre>
Line 286: Line 108:
--#GetAnalysis
--#GetAnalysis
</pre>
</pre>
The following example will create a new sheet containing Event Type Trends by months.
<pre>
(SELECT 'AnalysisType', '26') UNION ALL
(SELECT 'TrendPeriodLevel', 'Month') UNION ALL
(SELECT 'FilterId', CAST(@_FilterId AS NVARCHAR)) UNION ALL
(SELECT 'Show', 'TRUE') UNION ALL
(SELECT 'Title', 'Title for the Event Type Trend Analysis') UNION ALL
(SELECT 'SheetName', 'ShowA26Result')
--#GetAnalysis
</pre>
The following example will create a new sheet containing Event Type Trends showing the top 5 event types for three quarters.
<pre>
(SELECT 'AnalysisType', '26') UNION ALL
(SELECT 'TrendPeriodLevel', 'Quarter') UNION ALL
(SELECT 'TrendMaximumCount', '3') UNION ALL
(SELECT 'MaximumCount', '5') UNION ALL
(SELECT 'FilterId', CAST(@_FilterId AS NVARCHAR)) UNION ALL
(SELECT 'Show', 'TRUE') UNION ALL
(SELECT 'Title', 'Title for the Event Type Trend Analysis in the Table Mode') UNION ALL
(SELECT 'SheetName', 'ShowA26Result')
--#GetAnalysis
</pre>
The following example gets the Event Type Trends by months, puts the trend table to a temporary table, and shows the trend table on a new sheet.
<pre>
(SELECT 'AnalysisType', '26') UNION ALL
(SELECT 'TrendPeriodLevel', 'Month') UNION ALL
(SELECT 'FilterId', CAST(@_FilterId AS NVARCHAR)) UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis
SELECT * FROM #AnalysisResult;
(SELECT 'Title', 'Report1') UNION ALL
(SELECT 'SheetName', 'A26Report') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
</pre>
The following example will get the Profiling Analysis Trend table for the "Product Group" case attribute ("'SelectedAttributeType', '6'") using a case attribute to define the time stamps of the occurrences ("'TrendCaseTimeStampType', 'CaseCustomAttributeValue'" and "'TrendSelectedTimeStampAttribute', '10'") on a monthly basis ("'TrendPeriodLevel', 'Month'"). It then puts the trend table to a temporary table, and then shows the trend table on a new sheet.
<pre>
(SELECT 'AnalysisType', '28') UNION ALL
(SELECT 'TrendPeriodLevel', 'Month') UNION ALL
(SELECT 'SelectedAttributeType', '6') UNION ALL
(SELECT 'TrendCaseTimeStampType', 'CaseCustomAttributeValue') UNION ALL'
(SELECT 'TrendSelectedTimeStampAttribute', '10') UNION ALL
(SELECT 'FilterId', CAST(@_FilterId AS NVARCHAR)) UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis
SELECT * FROM #AnalysisResult;
(SELECT 'Title', 'Report1') UNION ALL
(SELECT 'SheetName', 'A28Report') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
</pre>
The following example will put the Flowchart analysis into a target table, parse the XML from the target table, and then show the activities, transitions, etc. on their separate sheets. Note that when IncludeLayout is "True", the layout (activity boxes, connectors, and text boxes) are parsed like they are shown in QPR ProcessAnalyzer Excel client. When IncludeLayout is "False", the result will only have activities and transitions.
<pre>
(SELECT 'AnalysisType', '0') UNION ALL
(SELECT 'FilterId', cast(@_FilterId as nvarchar(100))) UNION ALL
(SELECT 'TargetTable', '#AnalysisResult') UNION ALL
(SELECT 'IncludeLayout', 'True') UNION ALL
(SELECT 'ComparisonBenchmark', 'False')
--#GetAnalysis
DECLARE @XML xml;
SELECT @XML = [Flowchart Analysis Xml] FROM #AnalysisResult;
-- Activities
;WITH XMLNAMESPACES(DEFAULT 'http://www.qpr.com/ns/', 'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d4p1)
SELECT Activity.N.value('Id[1]', 'BIGINT') as ACT_ID,
      Activity.N.value('ModelId[1]', 'BIGINT') as ACT_MODEL_ID,
                                Activity.N.value('Name[1]', 'NVARCHAR(4000)') as ACT_NAME,
      Activity.N.value('CaseCost[1]', 'FLOAT') as ACT_CASE_COST,
                                Activity.N.value('EventCost[1]', 'FLOAT') as ACT_EVENT_COST,
                                Activity.N.value('PathIdentifier[1]', 'NVARCHAR(4000)') as ACT_PATH_IDENTIFIER,
Activity.N.value('Count[1]', 'NVARCHAR(4000)') as ACT_COUNT,
Activity.N.value('UniqueCount[1]', 'NVARCHAR(4000)') as ACT_UNIQUE_COUNT,
                                PAR_FILTER_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as FILTER_ID,
                                PAR_CA_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as CA_ID
INTO #Activities
FROM @XML.nodes('ProcessAnalysis/ChildAnalyses/GenericAnalysis,ProcessAnalysis') as Analysis(N)
                                                          CROSS APPLY Analysis.N.nodes('ActivitiesByStartCount/Activity') as Activity(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''FilterId'']/parent::d4p1:KeyValueOfstringstring') as PAR_FILTER_ID(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''SelectedCaseAttributeValues'']/parent::d4p1:KeyValueOfstringstring') as PAR_CA_ID(N);
--Transitions
; WITH XMLNAMESPACES(DEFAULT 'http://www.qpr.com/ns/', 'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d4p1)
SELECT Transition.N.value('ToId[1]', 'BIGINT') as TR_TO_ID,
      Transition.N.value('FromId[1]', 'BIGINT') as TR_FROM_ID,
                                Transition.N.value('Duration[1]', 'FLOAT') as TR_DURATION,
      Transition.N.value('CaseCost[1]', 'FLOAT') as TR_CASE_COST,
                                Transition.N.value('EventCost[1]', 'FLOAT') as TR_EVENT_COST,
                                Transition.N.value('TotalCount[1]', 'INT') as TR_TOTAL_COUNT,
                                Transition.N.value('UniqueCount[1]', 'INT') as TR_UNIQUE_COUNT,
                                PAR_FILTER_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as FILTER_ID,
                                PAR_CA_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as CA_ID
INTO #Transitions
FROM @XML.nodes('ProcessAnalysis/ChildAnalyses/GenericAnalysis,ProcessAnalysis') as Analysis(N)
                                                          CROSS APPLY Analysis.N.nodes('Transitions/Transition') as Transition(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''FilterId'']/parent::d4p1:KeyValueOfstringstring') as PAR_FILTER_ID(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''SelectedCaseAttributeValues'']/parent::d4p1:KeyValueOfstringstring') as PAR_CA_ID(N);
--Layout activity boxes
; WITH XMLNAMESPACES(DEFAULT 'http://www.qpr.com/ns/', 'http://www.w3.org/2001/XMLSchema-instance' as i, 'http://schemas.datacontract.org/2004/07/System.Drawing' as d5p1,
'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d4p1)
SELECT Shape.N.value('Id[1]', 'BIGINT') as ACT_BOX_ID,
                                                          Shape.N.value('Text[1]', 'NVARCHAR(MAX)') as ACT_BOX_TEXT,
                                                          Shape.N.value('Alignment[1]', 'NVARCHAR(MAX)') as ACT_BOX_ALIGNMENT,
                                                          Shape.N.value('FontSize[1]', 'FLOAT') as ACT_BOX_FONT_SIZE,
                                                          Shape.N.value('LineWeight[1]', 'FLOAT') as ACT_BOX_LINE_WEIGHT,
                                                          Shape.N.value('VAlignment[1]', 'NVARCHAR(MAX)') as ACT_BOX_VALIGNMENT,
                                                          Rectangle.N.value('d5p1:height[1]', 'FLOAT') as ACT_BOX_RECT_HEIGHT,
                                                          Rectangle.N.value('d5p1:width[1]', 'FLOAT') as ACT_BOX_RECT_WIDTH,
                                                          Rectangle.N.value('d5p1:x[1]', 'FLOAT') as ACT_BOX_RECT_X,
                                                          Rectangle.N.value('d5p1:y[1]', 'FLOAT') as ACT_BOX_RECT_Y,
                                                          PAR_FILTER_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as FILTER_ID,
                                                          PAR_CA_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as CA_ID
INTO #Layout_ActivityBoxes
FROM @XML.nodes('ProcessAnalysis/ChildAnalyses/GenericAnalysis,ProcessAnalysis') as Analysis(N)
                            CROSS APPLY Analysis.N.nodes('Layout/Shapes/Shape') as Shape(N)
                            CROSS APPLY Shape.N.nodes('Rectangle') as Rectangle(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''FilterId'']/parent::d4p1:KeyValueOfstringstring') as PAR_FILTER_ID(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''SelectedCaseAttributeValues'']/parent::d4p1:KeyValueOfstringstring') as PAR_CA_ID(N)
WHERE Shape.N.value('@i:type', 'NVARCHAR(MAX)') = 'ActivityBox';
--Layout text boxes
; WITH XMLNAMESPACES(DEFAULT 'http://www.qpr.com/ns/', 'http://www.w3.org/2001/XMLSchema-instance' as i, 'http://schemas.datacontract.org/2004/07/System.Drawing' as d7p1,
'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d4p1)
SELECT Shape.N.value('Text[1]', 'NVARCHAR(MAX)') as TEXT_BOX_TEXT,
                            Shape.N.value('Alignment[1]', 'NVARCHAR(MAX)') as TEXT_BOX_ALIGNMENT,
                            Shape.N.value('FontSize[1]', 'FLOAT') as TEXT_BOX_FONT_SIZE,
                            Shape.N.value('LineWeight[1]', 'FLOAT') as TEXT_BOX_LINE_WEIGHT,
                            Shape.N.value('VAlignment[1]', 'NVARCHAR(MAX)') as TEXT_BOX_VALIGNMENT,
                            Rectangle.N.value('d7p1:height[1]', 'FLOAT') as TEXT_BOX_RECT_HEIGHT,
                            Rectangle.N.value('d7p1:width[1]', 'FLOAT') as TEXT_BOX_RECT_WIDTH,
                            Rectangle.N.value('d7p1:x[1]', 'FLOAT') as TEXT_BOX_RECT_X,
                            Rectangle.N.value('d7p1:y[1]', 'FLOAT') as TEXT_BOX_RECT_Y,
                            PAR_FILTER_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as FILTER_ID,
                            PAR_CA_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as CA_ID
INTO #Layout_TextBoxes
FROM @XML.nodes('ProcessAnalysis/ChildAnalyses/GenericAnalysis,ProcessAnalysis') as Analysis(N)
                            CROSS APPLY Analysis.N.nodes('Layout/Shapes/Shape/ChildShapes/Shape') as Shape(N)
                            CROSS APPLY Shape.N.nodes('Rectangle') as Rectangle(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''FilterId'']/parent::d4p1:KeyValueOfstringstring') as PAR_FILTER_ID(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''SelectedCaseAttributeValues'']/parent::d4p1:KeyValueOfstringstring') as PAR_CA_ID(N)
WHERE Shape.N.value('@i:type', 'NVARCHAR(MAX)') = 'TextBox';
--Layout straight connectors
;WITH XMLNAMESPACES(DEFAULT 'http://www.qpr.com/ns/', 'http://www.w3.org/2001/XMLSchema-instance' as i, 'http://schemas.datacontract.org/2004/07/System.Drawing' as d5p1,
'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d4p1)
SELECT Shape.N.value('FromId[1]', 'BIGINT') as SC_FROM_ID,
                            Shape.N.value('ToId[1]', 'BIGINT') as SC_TO_ID,
                            Shape.N.value('LineWeight[1]', 'FLOAT') as SC_LINE_WEIGHT,
                            PositionFrom.N.value('d5p1:x[1]', 'FLOAT') as SC_FROM_X,
                            PositionFrom.N.value('d5p1:y[1]', 'FLOAT') as SC_FROM_Y,
                            PositionTo.N.value('d5p1:x[1]', 'FLOAT') as SC_TO_X,
                            PositionTo.N.value('d5p1:y[1]', 'FLOAT') as SC_TO_Y,
                            PAR_FILTER_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as FILTER_ID,
                            PAR_CA_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as CA_ID
INTO #Layout_StraightConnector
FROM @XML.nodes('ProcessAnalysis/ChildAnalyses/GenericAnalysis,ProcessAnalysis') as Analysis(N)
                            CROSS APPLY Analysis.N.nodes('Layout/Shapes/Shape') as Shape(N)
                            CROSS APPLY Shape.N.nodes('From') as PositionFrom(N)
                            CROSS APPLY Shape.N.nodes('To') as PositionTo(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''FilterId'']/parent::d4p1:KeyValueOfstringstring') as PAR_FILTER_ID(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''SelectedCaseAttributeValues'']/parent::d4p1:KeyValueOfstringstring') as PAR_CA_ID(N)
WHERE Shape.N.value('@i:type', 'NVARCHAR(MAX)') = 'StraightConnector';
--Layout curved connectors
;WITH XMLNAMESPACES(DEFAULT 'http://www.qpr.com/ns/', 'http://www.w3.org/2001/XMLSchema-instance' as i, 'http://schemas.datacontract.org/2004/07/System.Drawing' as d5p1,
'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d4p1)
SELECT Shape.N.value('FromId[1]', 'BIGINT') as CC_FROM_ID,
                            Shape.N.value('ToId[1]', 'BIGINT') as CC_TO_ID,
                            Shape.N.value('LineWeight[1]', 'FLOAT') as CC_LINE_WEIGHT,
                            Points.N.value('d5p1:x[1]', 'FLOAT') as CC_POINT_X,
                            Points.N.value('d5p1:y[1]', 'FLOAT') as CC_POINT_Y,
                            PAR_FILTER_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as FILTER_ID,
                            PAR_CA_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as CA_ID
INTO #Layout_CurvedConnector
FROM @XML.nodes('ProcessAnalysis/ChildAnalyses/GenericAnalysis,ProcessAnalysis') as Analysis(N)
                            CROSS APPLY Analysis.N.nodes('Layout/Shapes/Shape') as Shape(N)
                            CROSS APPLY Shape.N.nodes('Points/d5p1:PointF') as Points(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''FilterId'']/parent::d4p1:KeyValueOfstringstring') as PAR_FILTER_ID(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''SelectedCaseAttributeValues'']/parent::d4p1:KeyValueOfstringstring') as PAR_CA_ID(N)
WHERE Shape.N.value('@i:type', 'NVARCHAR(MAX)') = 'CurvedConnector';
SELECT * FROM #Activities;
(SELECT 'Title', 'Activities') UNION ALL
(SELECT 'SheetName', 'Activities') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
SELECT * FROM #Transitions;
(SELECT 'Title', 'Transitions') UNION ALL
(SELECT 'SheetName', 'Transitions') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
SELECT * FROM #Layout_ActivityBoxes;
(SELECT 'Title', 'Layout_ActivityBoxes') UNION ALL
(SELECT 'SheetName', 'Layout_ActivityBoxes') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
SELECT * FROM #Layout_TextBoxes;
(SELECT 'Title', 'Layout_TextBoxes') UNION ALL
(SELECT 'SheetName', 'Layout_TextBoxes') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
SELECT * FROM #Layout_StraightConnector;
(SELECT 'Title', 'Layout_StraightConnector') UNION ALL
(SELECT 'SheetName', 'Layout_StraightConnector') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
SELECT * FROM #Layout_CurvedConnector;
(SELECT 'Title', 'Layout_CurvedConnector') UNION ALL
(SELECT 'SheetName', 'Layout_CurvedConnector') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
</pre>
The following example will put the Path analysis into a target table, parse the XML from the target table, and then show the activities, transitions, etc. on their separate sheets. Note that when IncludeLayout is "True", the layout (activity boxes, connectors, and text boxes) are parsed like they are shown in QPR ProcessAnalyzer Excel client. When IncludeLayout is "False", the result will only have activities and transitions.
<pre>
(SELECT 'AnalysisType', '3') UNION ALL
(SELECT 'FilterId', cast(@_FilterId as nvarchar(100))) UNION ALL
(SELECT 'TargetTable', '#AnalysisResult') UNION ALL
(SELECT 'IncludeLayout', 'True') UNION ALL
(SELECT 'RootEventType', '6304')
--#GetAnalysis
DECLARE @XML xml;
SELECT @XML = [Path Analysis Xml] FROM #AnalysisResult;
-- Activities
;WITH XMLNAMESPACES(DEFAULT 'http://www.qpr.com/ns/', 'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d4p1)
SELECT Activity.N.value('Id[1]', 'BIGINT') as ACT_ID,
      Activity.N.value('ModelId[1]', 'BIGINT') as ACT_MODEL_ID,
                                Activity.N.value('Name[1]', 'NVARCHAR(4000)') as ACT_NAME,
      Activity.N.value('CaseCost[1]', 'FLOAT') as ACT_CASE_COST,
                                Activity.N.value('EventCost[1]', 'FLOAT') as ACT_EVENT_COST,
                                Activity.N.value('PathIdentifier[1]', 'NVARCHAR(4000)') as ACT_PATH_IDENTIFIER,
Activity.N.value('Count[1]', 'NVARCHAR(4000)') as ACT_COUNT,
Activity.N.value('UniqueCount[1]', 'NVARCHAR(4000)') as ACT_UNIQUE_COUNT,
                                PAR_FILTER_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as FILTER_ID,
                                PAR_CA_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as CA_ID
INTO #Activities
FROM @XML.nodes('PathAnalysis/ChildAnalyses/GenericAnalysis,PathAnalysis') as Analysis(N)
                                                          CROSS APPLY Analysis.N.nodes('ActivitiesByStartCount/Activity') as Activity(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''FilterId'']/parent::d4p1:KeyValueOfstringstring') as PAR_FILTER_ID(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''SelectedCaseAttributeValues'']/parent::d4p1:KeyValueOfstringstring') as PAR_CA_ID(N);
--Transitions
; WITH XMLNAMESPACES(DEFAULT 'http://www.qpr.com/ns/', 'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d4p1)
SELECT Transition.N.value('ToId[1]', 'BIGINT') as TR_TO_ID,
      Transition.N.value('FromId[1]', 'BIGINT') as TR_FROM_ID,
                                Transition.N.value('Duration[1]', 'FLOAT') as TR_DURATION,
      Transition.N.value('CaseCost[1]', 'FLOAT') as TR_CASE_COST,
                                Transition.N.value('EventCost[1]', 'FLOAT') as TR_EVENT_COST,
                                Transition.N.value('Occurrence[1]', 'INT') as TR_OCCURRENCE,
                                Transition.N.value('TotalCount[1]', 'INT') as TR_TOTAL_COUNT,
                                Transition.N.value('UniqueCount[1]', 'INT') as TR_UNIQUE_COUNT,
                                PAR_FILTER_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as FILTER_ID,
                                PAR_CA_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as CA_ID
INTO #Transitions
FROM @XML.nodes('PathAnalysis/ChildAnalyses/GenericAnalysis,PathAnalysis') as Analysis(N)
                                                          CROSS APPLY Analysis.N.nodes('Transitions/Transition') as Transition(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''FilterId'']/parent::d4p1:KeyValueOfstringstring') as PAR_FILTER_ID(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''SelectedCaseAttributeValues'']/parent::d4p1:KeyValueOfstringstring') as PAR_CA_ID(N);
--Layout activity boxes
; WITH XMLNAMESPACES(DEFAULT 'http://www.qpr.com/ns/', 'http://www.w3.org/2001/XMLSchema-instance' as i, 'http://schemas.datacontract.org/2004/07/System.Drawing' as d5p1,
'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d4p1)
SELECT Shape.N.value('Id[1]', 'BIGINT') as ACT_BOX_ID,
                                                          Shape.N.value('Text[1]', 'NVARCHAR(MAX)') as ACT_BOX_TEXT,
                                                          Shape.N.value('Alignment[1]', 'NVARCHAR(MAX)') as ACT_BOX_ALIGNMENT,
                                                          Shape.N.value('FontSize[1]', 'FLOAT') as ACT_BOX_FONT_SIZE,
                                                          Shape.N.value('LineWeight[1]', 'FLOAT') as ACT_BOX_LINE_WEIGHT,
                                                          Shape.N.value('VAlignment[1]', 'NVARCHAR(MAX)') as ACT_BOX_VALIGNMENT,
                                                          Rectangle.N.value('d5p1:height[1]', 'FLOAT') as ACT_BOX_RECT_HEIGHT,
                                                          Rectangle.N.value('d5p1:width[1]', 'FLOAT') as ACT_BOX_RECT_WIDTH,
                                                          Rectangle.N.value('d5p1:x[1]', 'FLOAT') as ACT_BOX_RECT_X,
                                                          Rectangle.N.value('d5p1:y[1]', 'FLOAT') as ACT_BOX_RECT_Y,
                                                          PAR_FILTER_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as FILTER_ID,
                                                          PAR_CA_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as CA_ID
INTO #Layout_ActivityBoxes
FROM @XML.nodes('PathAnalysis/ChildAnalyses/GenericAnalysis,PathAnalysis') as Analysis(N)
                            CROSS APPLY Analysis.N.nodes('Layout/Shapes/Shape') as Shape(N)
                            CROSS APPLY Shape.N.nodes('Rectangle') as Rectangle(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''FilterId'']/parent::d4p1:KeyValueOfstringstring') as PAR_FILTER_ID(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''SelectedCaseAttributeValues'']/parent::d4p1:KeyValueOfstringstring') as PAR_CA_ID(N)
WHERE Shape.N.value('@i:type', 'NVARCHAR(MAX)') = 'ActivityBox';
--Layout text boxes
; WITH XMLNAMESPACES(DEFAULT 'http://www.qpr.com/ns/', 'http://www.w3.org/2001/XMLSchema-instance' as i, 'http://schemas.datacontract.org/2004/07/System.Drawing' as d7p1,
'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d4p1)
SELECT Shape.N.value('Text[1]', 'NVARCHAR(MAX)') as TEXT_BOX_TEXT,
                            Shape.N.value('Alignment[1]', 'NVARCHAR(MAX)') as TEXT_BOX_ALIGNMENT,
                            Shape.N.value('FontSize[1]', 'FLOAT') as TEXT_BOX_FONT_SIZE,
                            Shape.N.value('LineWeight[1]', 'FLOAT') as TEXT_BOX_LINE_WEIGHT,
                            Shape.N.value('VAlignment[1]', 'NVARCHAR(MAX)') as TEXT_BOX_VALIGNMENT,
                            Rectangle.N.value('d7p1:height[1]', 'FLOAT') as TEXT_BOX_RECT_HEIGHT,
                            Rectangle.N.value('d7p1:width[1]', 'FLOAT') as TEXT_BOX_RECT_WIDTH,
                            Rectangle.N.value('d7p1:x[1]', 'FLOAT') as TEXT_BOX_RECT_X,
                            Rectangle.N.value('d7p1:y[1]', 'FLOAT') as TEXT_BOX_RECT_Y,
                            PAR_FILTER_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as FILTER_ID,
                            PAR_CA_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as CA_ID
INTO #Layout_TextBoxes
FROM @XML.nodes('PathAnalysis/ChildAnalyses/GenericAnalysis,PathAnalysis') as Analysis(N)
                            CROSS APPLY Analysis.N.nodes('Layout/Shapes/Shape/ChildShapes/Shape') as Shape(N)
                            CROSS APPLY Shape.N.nodes('Rectangle') as Rectangle(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''FilterId'']/parent::d4p1:KeyValueOfstringstring') as PAR_FILTER_ID(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''SelectedCaseAttributeValues'']/parent::d4p1:KeyValueOfstringstring') as PAR_CA_ID(N)
WHERE Shape.N.value('@i:type', 'NVARCHAR(MAX)') = 'TextBox';
--Layout straight connectors
;WITH XMLNAMESPACES(DEFAULT 'http://www.qpr.com/ns/', 'http://www.w3.org/2001/XMLSchema-instance' as i, 'http://schemas.datacontract.org/2004/07/System.Drawing' as d5p1,
'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d4p1)
SELECT Shape.N.value('FromId[1]', 'BIGINT') as SC_FROM_ID,
                            Shape.N.value('ToId[1]', 'BIGINT') as SC_TO_ID,
                            Shape.N.value('LineWeight[1]', 'FLOAT') as SC_LINE_WEIGHT,
                            PositionFrom.N.value('d5p1:x[1]', 'FLOAT') as SC_FROM_X,
                            PositionFrom.N.value('d5p1:y[1]', 'FLOAT') as SC_FROM_Y,
                            PositionTo.N.value('d5p1:x[1]', 'FLOAT') as SC_TO_X,
                            PositionTo.N.value('d5p1:y[1]', 'FLOAT') as SC_TO_Y,
                            PAR_FILTER_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as FILTER_ID,
                            PAR_CA_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as CA_ID
INTO #Layout_StraightConnector
FROM @XML.nodes('PathAnalysis/ChildAnalyses/GenericAnalysis,PathAnalysis') as Analysis(N)
                            CROSS APPLY Analysis.N.nodes('Layout/Shapes/Shape') as Shape(N)
                            CROSS APPLY Shape.N.nodes('From') as PositionFrom(N)
                            CROSS APPLY Shape.N.nodes('To') as PositionTo(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''FilterId'']/parent::d4p1:KeyValueOfstringstring') as PAR_FILTER_ID(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''SelectedCaseAttributeValues'']/parent::d4p1:KeyValueOfstringstring') as PAR_CA_ID(N)
WHERE Shape.N.value('@i:type', 'NVARCHAR(MAX)') = 'StraightConnector';
--Layout curved connectors
;WITH XMLNAMESPACES(DEFAULT 'http://www.qpr.com/ns/', 'http://www.w3.org/2001/XMLSchema-instance' as i, 'http://schemas.datacontract.org/2004/07/System.Drawing' as d7p1,
'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d4p1)
SELECT Shape.N.value('FromId[1]', 'BIGINT') as CC_FROM_ID,
                            Shape.N.value('ToId[1]', 'BIGINT') as CC_TO_ID,
                            Shape.N.value('LineWeight[1]', 'FLOAT') as CC_LINE_WEIGHT,
                            Points.N.value('d7p1:x[1]', 'FLOAT') as CC_POINT_X,
                            Points.N.value('d7p1:y[1]', 'FLOAT') as CC_POINT_Y,
                            PAR_FILTER_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as FILTER_ID,
                            PAR_CA_ID.N.value('d4p1:Value[1]', 'NVARCHAR(4000)') as CA_ID
INTO #Layout_CurvedConnector
FROM @XML.nodes('PathAnalysis/ChildAnalyses/GenericAnalysis,PathAnalysis') as Analysis(N)
                            CROSS APPLY Analysis.N.nodes('Layout/Shapes/Shape') as Shape(N)
                            CROSS APPLY Shape.N.nodes('Points/d7p1:PointF') as Points(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''FilterId'']/parent::d4p1:KeyValueOfstringstring') as PAR_FILTER_ID(N)
                            OUTER APPLY Analysis.N.nodes('Parameters/d4p1:KeyValueOfstringstring/d4p1:Key[1][child::text()=''SelectedCaseAttributeValues'']/parent::d4p1:KeyValueOfstringstring') as PAR_CA_ID(N)
WHERE Shape.N.value('@i:type', 'NVARCHAR(MAX)') = 'CurvedConnector';
SELECT * FROM #Activities;
(SELECT 'Title', 'Activities') UNION ALL
(SELECT 'SheetName', 'Activities') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
SELECT * FROM #Transitions;
(SELECT 'Title', 'Transitions') UNION ALL
(SELECT 'SheetName', 'Transitions') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
SELECT * FROM #Layout_ActivityBoxes;
(SELECT 'Title', 'Layout_ActivityBoxes') UNION ALL
(SELECT 'SheetName', 'Layout_ActivityBoxes') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
SELECT * FROM #Layout_TextBoxes;
(SELECT 'Title', 'Layout_TextBoxes') UNION ALL
(SELECT 'SheetName', 'Layout_TextBoxes') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
SELECT * FROM #Layout_StraightConnector;
(SELECT 'Title', 'Layout_StraightConnector') UNION ALL
(SELECT 'SheetName', 'Layout_StraightConnector') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
SELECT * FROM #Layout_CurvedConnector;
(SELECT 'Title', 'Layout_CurvedConnector') UNION ALL
(SELECT 'SheetName', 'Layout_CurvedConnector') UNION ALL
(SELECT 'MaximumCount', '0')
--#ShowReport
</pre>
The following example will get all filters available to the user and list the filters with "Filters" as the title on a sheet named "Filter Report Sheet"
<pre>
(SELECT 'AnalysisType', '30') UNION ALL
(SELECT 'Title', 'Filters') UNION ALL
(SELECT 'SheetName', 'Filter Report Sheet') UNION ALL
(SELECT 'Show', 'True')
--#GetAnalysis
</pre>
The following example will get all filters from the model specified by ModelId and list the filters with "Filters" as the title on a sheet named "Filter Report Sheet"
<pre>
(SELECT 'AnalysisType', '30') UNION ALL
(SELECT 'ModelId', '1') UNION ALL
(SELECT 'Title', 'Filters') UNION ALL
(SELECT 'SheetName', 'Filter Report Sheet') UNION ALL
(SELECT 'Show', 'True')
--#GetAnalysis
</pre>
The following example will get all filter rules of all filters available to the user and list the filter rules with "Filter Rules" as the title on a sheet named "Filter Rule Report Sheet"
<pre>
(SELECT 'AnalysisType', '31') UNION ALL
(SELECT 'Title', 'Filter Rules') UNION ALL
(SELECT 'SheetName', 'Filter Rule Report Sheet') UNION ALL
(SELECT 'Show', 'True')
--#GetAnalysis
</pre>
The following example will get all filter rules of the filter specified by FilterId and list the filter rules and their pretty names and IDs with "Filter Rules" as the title on a sheet named "Filter Rule Report Sheet"
<pre>
(SELECT 'AnalysisType', '31') UNION ALL
(SELECT 'FilterId', '123') UNION ALL
(SELECT 'IncludeFullParameters', 'TRUE') UNION ALL
(SELECT 'Title', 'Filter Rules') UNION ALL
(SELECT 'SheetName', 'Filter Rule Report Sheet') UNION ALL
(SELECT 'Show', 'True')
--#GetAnalysis
</pre>
The following example will get all filter rules of the model specified by ModelId and list the filter rules and their pretty names and IDs with "Filter Rules" as the title on a sheet named "Filter Rule Report Sheet"
<pre>
(SELECT 'AnalysisType', '31') UNION ALL
(SELECT 'ModelId', '123') UNION ALL
(SELECT 'IncludeFullParameters', 'TRUE') UNION ALL
(SELECT 'Title', 'Filter Rules') UNION ALL
(SELECT 'SheetName', 'Filter Rule Report Sheet') UNION ALL
(SELECT 'Show', 'True')
--#GetAnalysis
</pre>
The following example will get script log report for the scripts with ids "123" and "456" that have been started between 14th March 2016 02:20 and 18th March 2016 02:10. It will put the information to the "#ScriptReport" temporary table.
<pre>
(SELECT 'AnalysisType', '32') UNION ALL
(SELECT 'ScriptId', '123,456') UNION ALL
(SELECT 'MinStartTime', '2016-03-14T02:10:00') UNION ALL
(SELECT 'MaxStartTime', '2016-03-18T02:10:00') UNION ALL
(SELECT 'TargetTable', '#ScriptReport')
--#GetAnalysis
</pre>
The following example will get script log report for all scripts available for the user that have been started on 14th March 2016. It will put the information to the "#ScriptReport" temporary table.
<pre>
(SELECT 'AnalysisType', '32') UNION ALL
(SELECT 'MinStartTime', '2016-03-14T00:00:00') UNION ALL
(SELECT 'MaxStartTime', '2016-03-14T23:59:59') UNION ALL
(SELECT 'TargetTable', '#ScriptReport')
--#GetAnalysis
</pre>
The following example will get operation log report for 14th March 2016. It will put the information to the "#OperationReport" temporary table. It will then also show the contents of that table.
<pre>
(SELECT 'AnalysisType', '12') UNION ALL
(SELECT 'MinStartTime', '2016-03-14T00:00:00') UNION ALL
(SELECT 'MaxStartTime', '2016-03-14T23:59:59') UNION ALL
(SELECT 'MaxTextLength', '12') UNION ALL
(SELECT 'TargetTable', '#OperationReport')
--#GetAnalysis
(SELECT * FROM #OperationReport)
(SELECT 'Title', 'Operation Log Report')
--#ShowReport
</pre>
[[Category:Examples]]

Latest revision as of 14:21, 24 January 2024

This page contains script examples for the GetAnalysis script command.

This example runs an expression analysis.

(SELECT 'AnalysisType', '33') UNION ALL
(SELECT 'ModelId', '123') UNION ALL
(SELECT 'Show', 'True') UNION ALL
(SELECT 'Configuration', '
{
	"Root": "Cases",
	"Dimensions": [
		{
			"Name": "Date",
			"Expression": "Duration.TotalDays"
			"NumberPrecision": 0
		}
	],
	"Values": [
		{
			"Name": "Count",
			"Expression": "Count(_)"
		}
	],
	"Ordering": [
		{
			"Name": "Date",
			"Direction": "Ascending"
		}
	]
}
')
--#GetAnalysis

Cases list:

(SELECT 'AnalysisType', '33') UNION ALL
(SELECT 'ModelId', '123') UNION ALL
(SELECT 'Show', 'True') UNION ALL
(SELECT 'Configuration', '
{
	"Root": "Cases",
	"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"
		}
	],
	"MaximumRowCount": 200
}
')
--#GetAnalysis

This example runs an expression analysis with a root expression only.

(SELECT 'AnalysisType', '33') UNION ALL
(SELECT 'Show', 'True') UNION ALL
(SELECT 'Configuration', '
{
	"Root": "DatatableById(49).DataFrame.Where(Column(\"DELIVERY MODE\")==\"Truck\")",
}
')
--#GetAnalysis

The following example will load data from the "ExampleTable" data table in the "ExampleProject" project and put that data into the "CSV1" table.

(SELECT 'AnalysisType', '18') UNION ALL 
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'DataTableName', 'ExampleTable') UNION ALL 
(SELECT 'TargetTable', '#CSV1') 
--#GetAnalysis

The following example will run the script with ID "34" and returns the "ShowScript34Result" sheet that the script with ID "34" creates.

(SELECT 'AnalysisType', '25') UNION ALL
(SELECT 'Show', 'TRUE') UNION ALL
(SELECT 'SelectedAnalysisResult', 'ShowScript34Result') UNION ALL
(SELECT 'ScriptId', '34') 
--#GetAnalysis