Custom Query

Your model contains a great deal of information on both project design and project management, and a major benefit of the Chart feature is providing the facility to search through your model and extract specific aspects of that information using your own SQL Query, within the Chart element.

Access

Select a Chart element on a diagram or in the Project Browser, then use any of the methods outlined here to open the Chart element's 'Properties' dialog.

On the Chart element's 'Properties' dialog, click on the 'Source' tab, then on the 'Custom SQL' child tab.

Ribbon	Design > Element > Properties > Properties Dialog > Source > Custom SQL
Context Menu	Right-click on element \|Properties \| Source\| Custom SQL
Keyboard Shortcuts	Alt+Enter \| Source \| Custom SQL
Other	Double-click on element \| Source\| Custom SQL

Extract Information Using SQL Query

Field/Button	Action	See also
panel	Type in your SQL Query. The field provides the facilities of the Common Code Editor, such as Intelli-sense. The Select statement must include the alias 'Series'. For Heat Maps and Bar Charts you also have the option of grouping the results using the alias 'GroupName'. For example: Select t_object.Status AS Series, t_object.Author AS GroupName from t_object Using SQL, you can also use the 'ChartValue' alias to reflect the actual value of an instance, as captured in a Tagged Value. With the dialog fields, two instances of a property such as Cost have an equal value of 1, whereas the SQL can capture the value of Cost being, say, 6 and 1 and so represent the first instance with a Heat Map cell that is 6 times larger than the second. This is an example of such a SQL statement: select t_object.Name AS Series, Packages.Name AS GroupName, aColor.[Value] AS Color, aSize.[Value] AS ChartValue from t_object, t_package RootPackage, t_package Packages, t_objectproperties aColor, t_objectproperties aSize This statement can also be used for Bar Charts, except that the 'Color' alias is ignored; the 'Color' alias applies only to Heat Maps. Additionally, you can also use #<macro>#s as string replacers in any WHERE statements in your query, as for other SQL Search Queries.	Create Search Definitions Code Editor Functions
OK	Click on this button to execute the query, close the dialog and generate the Chart.
Cancel	Click on this button to abort the changes and close the dialog.

Field/Button

Action

See also

panel

Type in your SQL Query. The field provides the facilities of the Common Code Editor, such as Intelli-sense.

The Select statement must include the alias 'Series'. For Heat Maps and Bar Charts you also have the option of grouping the results using the alias 'GroupName'. For example:

Select t_object.Status AS Series, t_object.Author AS GroupName from t_object

Using SQL, you can also use the 'ChartValue' alias to reflect the actual value of an instance, as captured in a Tagged Value. With the dialog fields, two instances of a property such as Cost have an equal value of 1, whereas the SQL can capture the value of Cost being, say, 6 and 1 and so represent the first instance with a Heat Map cell that is 6 times larger than the second. This is an example of such a SQL statement:

select t_object.Name AS Series, Packages.Name AS GroupName, aColor.[Value] AS Color, aSize.[Value] AS ChartValue from t_object, t_package

RootPackage, t_package Packages, t_objectproperties aColor, t_objectproperties aSize

This statement can also be used for Bar Charts, except that the 'Color' alias is ignored; the 'Color' alias applies only to Heat Maps.

Additionally, you can also use #<macro>#s as string replacers in any WHERE statements in your query, as for other SQL Search Queries.

Create Search Definitions Code Editor Functions

Click on this button to execute the query, close the dialog and generate the Chart.

Cancel

Click on this button to abort the changes and close the dialog.

Learn more