Getting row or column values from another datasource
Getting row or column values from another datasource
1D - rows
In 1D cross-tables, row values can be retrieved from a separate data source. This is useful when the main data source cannot provide all possible row values. For example, if the main data source displays work requests grouped by department, the cross-table will only display departments for which work requests do exist. To display all departments, you can use a second data source based on the Department table.
When a second data source is used, the cross-table will display any visible fields defined in it. For example, you can display the Name and Group Area fields per department.
<dataSource id="crossTableByDepartment_dpDS">
<table name="dp"/>
<field name="dp_id"/>
<field name="name"/>
<field name="area_gp"/>
<!-- Display only one division. -->
<restriction type="parsed">
<clause table="dp" name="dv_id" value="FACILITIES"/>
</restriction>
</dataSource>
<dataSource type="grouping" id="crossTableByDepartment_dataDS">
<table name="rm"/>
<field name="dp_id" groupBy="true"/>
<field name="total_rooms" dataType="integer" formula="count" baseField="rm.rm_id">
<title>Total Rooms</title>
</field>
<restriction type="parsed">
<clause table="dp" name="dv_id" value="FACILITIES"/>
</restriction>
</dataSource>
<panel type="crossTable" id="crossTableByDepartment_table2" layoutRegion="row1col2"
dataSource="crossTableByDepartment_dataDS"
rowDimensionDataSource="crossTableByDepartment_dpDS">
Taken with modification from : ab-products/solutions/parts/cross-table/ab-ex-crosstable-by-department.axvw
2D - rows
2D cross-tables also support the same features for row values. In the example below, Fund Total comes directly from the Funding Source table.
<dataSource id="crossTableByFundSource_fundingSourcesAndFieldsDS" type="grouping">
<table name="projfunds" role="main"/>
<table name="funding" role="standard"/>
<field name="fund_id" table="projfunds" groupBy="true"/>
<field name="amt_total" dataType="number" baseField="funding.amt_total" formula="min" decimals="0"/>
<sortField name="fund_id" table="projfunds"/>
</dataSource>
<dataSource id="crossTableByFundSource_fundsAllocatedDS" type="grouping">
<table name="projfunds" role="main"/>
<table name="funding" role="standard"/>
<field name="fund_id" table="projfunds" groupBy="true"/>
<field name="fiscal_year" table="projfunds" groupBy="true"/>
<field name="amount_cap" dataType="number" decimals="0" baseField="projfunds.amount_cap" formula="sum">
<title>Capital Allocated</title>
</field>
<field name="amount_exp" dataType="number" decimals="0" baseField="projfunds.amount_exp" formula="sum">
<title>Expense Allocated</title>
</field>
<sortField name="fund_id" table="projfunds"/>
<sortField name="fiscal_year" table="projfunds"/>
</dataSource>
<panel type="crossTable" id="crossTableByDepartment_table2" layoutRegion="row2col1"
dataSource="crossTableByFundSource_fundsAllocatedDS"
rowDimensionDataSource="crossTableByFundSource_fundingSourcesAndFieldsDS">
<title>Show all funding sources, and additional data per funding source</title>
</panel>
Taken with modification from : ab-products/solutions/parts/cross-table/ab-ex-crosstable-by-fund-source-and-year.axvw
2D - columns
2D cross-tables also allow column values to be retrieved from a separate data source. This is useful to display year or month columns that are based on the
afm_cal_dates
table.
<dataSource type="grouping" id="crossTableByMonthAndStatus_monthDS">
<table name="afm_cal_dates"/>
<field name="month" groupBy="true" dataType="text" formula="month" baseField="afm_cal_dates.cal_date">
<title>Month</title>
</field>
<sortField name="month"/>
<!-- Restriction on months to display. -->
<restriction type="sql" sql="${sql.yearMonthOf('afm_cal_dates.cal_date')} >= '2008-01'
AND ${sql.yearMonthOf('afm_cal_dates.cal_date')} <= '2008-12'"></restriction>
</dataSource>
<dataSource type="grouping" id="crossTableByMonthAndStatus_dataDS">
<table name="wr"/>
<field name="status" groupBy="true"/>
<field name="month" groupBy="true" dataType="text" formula="month" baseField="wr.date_requested"/>
<field name="total_requests" dataType="integer" formula="count" baseField="wr.wr_id">
<title>Work requests</title>
</field>
<!-- Sort fields are required and must match the grouping fields by either name or baseField. -->
<sortField name="status"/>
<sortField name="month"/>
<restriction type="sql" sql="${sql.yearMonthOf('wr.date_requested')} >= '2008-01'
AND ${sql.yearMonthOf('wr.date_requested')} <= '2008-12'"></restriction>
</dataSource>
<panel type="crossTable" id="crossTableByMonthAndStatus_table2" layoutRegion="row2col1"
dataSource="crossTableByMonthAndStatus_dataDS"
columnDimensionDataSource="crossTableByMonthAndStatus_monthDS">
<title>Show all months</title>
</panel>
Taken with modification from : ab-products/solutions/parts/cross-table/ab-ex-crosstable-by-month-and-status.axvw
2D - limitations
- No additional fields can be displayed for columns
- You can use a separate data source either for rows or for columns, but not for both