Additional Per-Dimension Fields
Additional Per-Dimension Fields
You can also display additional fields per each dimension value. These fields come from the dimension data source, not from the main data source. For example, a view might need to display founding sources and the following calculated values for each funding source:
- Fund Total the total amount of money that each fund has.
- Available Funds : how much of the total fund is available to still be allocated.
- Capital Allocated : how much has been allocated to capital projects for each year.
- Expense Allocated : how much has been allocated to expenses each year.
To provide additional per-dimension fields from another table:
Step 1 : Add the first dimension data source to the view. This can be a regular or a grouping data source.
- Select one field that will be used as a dimension value, and name it so that it matches the grouping field in the main data source.
- You can only use one grouping field.
- If the cross-table data needs to be restricted, add the restriction to the dimension data source.
Step 2: Add visible fields that you want to be displayed for the first dimension.
<dataSource id="crossTableByFundSource_fundingSourcesDS" type="grouping">
<!-- Join projfunds and funding tables to calculate available funds. -->
<table name="projfunds" role="main"/>
<table name="funding" role="standard"/>
<!-- Group by Funding Source. -->
<field name="fund_id" table="projfunds" groupBy="true"/>
<!-- Additional calculated values displayed for each funding source. -->
<field name="amt_total" dataType="number"
baseField="funding.amt_total" formula="min" decimals="0"/>
<field name="funds_avail" dataType="number" decimals="0">
<title>Available Funds</title>
<sql dialect="generic">
MIN(funding.amt_total) -
SUM(projfunds.amount_cap + projfunds.amount_exp)
</sql>
</field>
<sortField name="fund_id" table="projfunds"/>
</dataSource>
Step 3 : Define the main data source as usual.
- If the cross-table data needs to be restricted, add the same restriction to the dimension data source – otherwise the totals and the data will not match.
<!-- Main data source. -->
<table name="projfunds" role="main"/>
<table name="funding" role="standard"/>
<!-- Two grouping fields is required for 2D cross-tab. -->
<field name="fund_id" table="projfunds" groupBy="true"/>
<field name="fiscal_year" table="projfunds" groupBy="true"/>
<!-- Calculated values (measures) for 2D cross-tab. -->
<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>
Step 4: Specify the dimension data source in the panel:
<!-- Cross-tab panel references both data sources:
fundingSourcesDS for the first dimension;
fundsAllocatedDS for the second dimension and all measures. -->
<panel type="crossTable" id="crossTableByDepartment_table"
dataSource="crossTableByFundSource_fundsAllocatedDS"
rowDimensionDataSource="crossTableByFundSource_fundingSourcesDS">
</panel>
Examples: