Additional data sources for dimension values
Additional data sources for dimension values
Sometimes dimension values must be taken not from the data table, but from another table. For example, in the Capital Budget by Program by Month view, the data table (Budget Items) might not contain records for all months in the desired year-month range. In this case, the cross-tab will only display months for which budget items exist. This will confuse users who expect to see all month columns.
To provide dimension values from another table:
Step 1: Add the 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.
- If the cross-table data needs to be restricted, add the restriction to the dimension data source.
<!-- Retrieves all months for 2008 and 2009 from afm_cal_dates. Months are used as dimension values for the main data source. -->
<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 type="sql"
sql="${sql.yearMonthOf('wr.date_requested')} >= '2008-01'
AND ${sql.yearMonthOf('wr.date_requested')} <= '2009-12'">
</restriction>
</dataSource>
Step 2: 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. -->
<dataSource type="grouping" id="crossTableByMonthAndStatus_dataDS">
<table name="wr"/>
<!-- Grouping fields (dimensions). -->
<field name="status" groupBy="true"/>
<field name="month" groupBy="true" dataType="text" formula="month"
baseField="wr.date_requested"/>
<!-- Calculated fields (measures). -->
<field name="total_requests" dataType="integer" formula="count"
baseField="wr.wr_id">
<title>Work requests</title>
</field>
<sortField name="status"/>
<sortField name="month"/>
<restriction type="sql"
sql="${sql.yearMonthOf('wr.date_requested')} >= '2008-01'
AND ${sql.yearMonthOf('wr.date_requested')} <= '2009-12'">
</restriction>
</dataSource>
Step 3 : Specify the dimension data source in the panel:
<!-- Cross-tab panel references both data sources:
monthDS for the second dimension;
dataDS for the first dimension and all measures. -->
<panel type="crossTable" id="crossTableByMonthAndStatus_table"
dataSource="crossTableByMonthAndStatus_dataDS"
columnDimensionDataSource="crossTableByMonthAndStatus_monthDS">
</panel>
You can define data sources either for the row dimension (first grouping field) or for the column dimension (second grouping field), but not for both. You specify them in the panel using the
rowDimensionDataSource
and
columnDimensionDataSource
attributes.
Examples: