GetAnalysis Script Examples: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
No edit summary |
||
| Line 11: | Line 11: | ||
"Dimensions": [ | "Dimensions": [ | ||
{ | { | ||
"name": " | "name": "Date", | ||
"expression": "Duration.ToInteger( | "expression": "Duration.TotalDays.ToInteger(_)" | ||
} | } | ||
], | ], | ||
| Line 23: | Line 23: | ||
"Ordering": [ | "Ordering": [ | ||
{ | { | ||
"name": " | "name": "Date", | ||
"Direction": "Ascending" | "Direction": "Ascending" | ||
} | } | ||
Revision as of 11:18, 19 January 2020
This page contains script examples for the GetAnalysis script command.
The following example runs the KPI 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.ToInteger(_)"
}
],
"Values": [
{
"name": "Count",
"expression": "Count(_)"
}
],
"Ordering": [
{
"name": "Date",
"Direction": "Ascending"
}
]
}'
)
--#GetAnalysis
(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 get a Case analysis and open that analysis with "Case Analysis From Script" as the title on a sheet named "Case Analysis Sheet".
(SELECT 'AnalysisType', '5') UNION ALL (SELECT 'ProjectName', '<ProjectName>') UNION ALL (SELECT 'ModelName', '<ModelName>') UNION ALL (SELECT 'MaximumCount', '10') UNION ALL (SELECT 'Title', 'Case Analysis From Script') UNION ALL (SELECT 'SheetName', 'Case Analysis Sheet') UNION ALL (SELECT 'Show', 'True') --#GetAnalysis
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.
(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
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".
(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
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 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 Models.
(SELECT 'AnalysisType', '21') UNION ALL (SELECT 'Show', '1') UNION ALL (SELECT 'TargetTable', '#ModelResult') --#GetAnalysis
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 Models.
(SELECT 'AnalysisType', '22') UNION ALL (SELECT 'Show', '1') UNION ALL (SELECT 'TargetTable', '#ProjectResult') --#GetAnalysis
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 Data Tables.
(SELECT 'AnalysisType', '23') UNION ALL (SELECT 'Show', '1') UNION ALL (SELECT 'TargetTable', '#DataTableResult') --#GetAnalysis
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.
(SELECT 'AnalysisType', '24') UNION ALL (SELECT 'Show', '1') UNION ALL (SELECT 'TargetTable', '#ScriptResult') --#GetAnalysis
The following example will create a pivot table based on the analysis data.
(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
The following example will get a Path Analysis and put it on a sheet named "Path Sheet".
(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
The following example will get a Flowchart Analysis and put it on a sheet named "Flowchart Sheet".
(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
The following example will get a Variation Analysis as a chart and put it on a sheet named "Variation Sheet".
(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
The following example will get a Variation Analysis as a table and put it on a sheet named "Variation Table Sheet".
(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
The following example will get an Event Type Analysis as a table and put it on a sheet named "Event Type Table Sheet".
(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
The following example will get an Event Type Analysis as a chart and put it on a sheet named "Event Type Chart Sheet".
(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
The following example will get a Profiling Analysis with the "Account Manager" attribute selected, and put the analysis on a sheet named "Profiling Sheet".
(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
The following example will get a Flow Analysis and put it on a sheet named "Flow Sheet".
(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
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".
(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
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
The following example will create a new sheet containing Event Type Trends by months.
(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
The following example will create a new sheet containing Event Type Trends showing the top 5 event types for three quarters.
(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
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.
(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
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.
(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
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.
(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
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.
(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
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"
(SELECT 'AnalysisType', '30') UNION ALL (SELECT 'Title', 'Filters') UNION ALL (SELECT 'SheetName', 'Filter Report Sheet') UNION ALL (SELECT 'Show', 'True') --#GetAnalysis
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"
(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
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"
(SELECT 'AnalysisType', '31') UNION ALL (SELECT 'Title', 'Filter Rules') UNION ALL (SELECT 'SheetName', 'Filter Rule Report Sheet') UNION ALL (SELECT 'Show', 'True') --#GetAnalysis
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"
(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
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"
(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
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.
(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
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.
(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
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.
(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