GetAnalysis Script Examples

From QPR ProcessAnalyzer Wiki
Revision as of 06:32, 20 June 2016 by TeeHiet (talk | contribs) (403051)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

This page contains script examples for the GetAnalysis script command.

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
Retrieved from "https://wiki.onqpr.com/pa/index.php?title=GetAnalysis_Script_Examples&oldid=2882"