Group One Value by Two Criteria in a Chart
HTML Chart Control
Group One Value by Two Criteria in a Chart
Technologies > User Interface Add-Ins > HTML Chart Views > / Column Chart with One Value Grouped by Two Criteria
In this example, total room area has been calculated for rooms grouped by Department and then by Room Standard.
Two grouping data sources are necessary for this view. The first data source groups by Division Id. The second data source groups by Room Standard. Notice the second data source's parameter “summaryValueForThisGroup”. The
first data source query value is placed into this parameter. The value is then used by means of a binding expression in the second data sources's restriction clause. Further discussion of the use of the
parameters
object in binding expressions can be found under
Data Binding
.
<view version="2.0">
<!-- data source for the grouping axis -->
<dataSource id="chartOneValMultCriteria_ds_groupingAxis" type="grouping">
<table name="rm"/>
<field table="rm" name="chart_dv_and_dp" dataType="string" groupBy="true">
<title>Division and Department</title>
<sql dialect="generic">RTRIM(dv_id)${sql.concat}'-'${sql.concat}RTRIM(dp_id)</sql>
<restriction type="sql" sql="dp_id IN ('ENGINEERING', 'MARKETING', 'MANAGEMENT')"/>
</field>
</dataSource>
<!-- data sources for two data axis -->
<dataSource id="chartOneValMultCriteria_ds_dataAxis" type="grouping">
<table name="rm"/>
<field table="rm" name="rm_std" groupBy="true"/>
<field name="total_area" formula="sum" baseField="rm.area" dataType="number" size="6" decimals="0">
<title>Total Area</title>
</field>
<sortField name="rm_std" ascending="true"/>
<parameter name="summaryValueForThisGroup" dataType="text" value=""/>
<restriction type="sql"
sql="RTRIM(dv_id)${sql.concat}'-'${sql.concat}RTRIM(dp_id) = ${parameters['summaryValueForThisGroup']}
AND rm_std IN ('CONFERENCE','WKSTA-TECH', 'WKSTA-B', 'OFF-A', 'OFF-B')"/>
</dataSource>
<panel id="chartOneValMultCriteria_chart" type="htmlChart" controlType="columnChart"
dataSource="chartOneValMultCriteria_ds_dataAxis" legendLocation="right" showLegendAsPopUp="false">
<title>Total Area by Standard by Department</title>
<groupingAxis table="rm" field="chart_dv_and_dp" dataSource="chartOneValMultCriteria_ds_groupingAxis">
<title>Division and Department</title>
</groupingAxis>
<!-- secondaryGroupingAxis must have the same dataSource as all dataAxis -->
<secondaryGroupingAxis table="rm" field="rm_std">
<title>Room Standards</title>
</secondaryGroupingAxis>
<!-- if there is no dataSource attribute setting-up, it will inherit the panel's dataSource -->
<dataAxis table="rm" field="total_area">
<title>Total Area</title>
</dataAxis>
</panel>
</view>
Example view : http://localhost:8080/archibus/schema/ab-products/solutions/parts/htmlchart/ab-ex-htmlchart-one-value-mult-criteria.axvw