Cross-Reference TablesΒΆ

Cross-reference is the type of a report that shows a matrix of XYZ relations, where X and Y are groupping fields and Z is a field with a summary value of relation between X and Y.

Z field could be just the first/once value of a relation, or could be an aggregation, like COUNT, DISTINCT COUNT, SUM, MAX, MIN, AVERAGE or a CONCATENATION, depending on its data type.

So, just to keep the things clear:

  • X = row_attribute = is the row groupper attribute
  • Y = col_attribute = is the column groupper attribute
  • Z = cell = is the relation between X and Y. It is an aggregation or concatenation

Example:

# We have a list of "Cities" in "States" where someones are the "Capitals" and
# others are not. Additionally, there are their respective "Government Types",
# "Populations" and "Areas"

from geraldo import Report, ObjectValue, Label, ReportBand, SystemField,\
    BAND_WIDTH, FIELD_ACTION_COUNT, DetailBand, ManyElements, CROSS_COLS
from geraldo.cross_reference import CrossReferenceMatrix
from geraldo.utils import cm, TA_RIGHT, TA_CENTER

class MyReport(Report):
    title = 'Population per state and their capitais/other cities'

    class band_page_header(ReportBand):
        height = 1.7*cm
        elements = [
            SystemField(expression='%(report_title)s', top=0.1*cm, left=0, width=BAND_WIDTH,
                style={'fontName': 'Helvetica', 'fontSize': 14, 'alignment': TA_CENTER}),
            Label(text='Capital', width=2.5*cm, top=1*cm),
            ManyElements(
                element_class=Label,
                count=CROSS_COLS,
                start_left=4*cm,
                width=2*cm,
                top=1*cm,
                text=CROSS_COLS,
                ),
            Label(text='Average', left=17*cm, width=3.5*cm, top=1*cm),
        ]
        borders = {'bottom': True}

    class band_detail(DetailBand):
        height=0.6*cm
        elements = [
            ObjectValue(attribute_name='row', width=2.5*cm),
            ManyElements( # Make one ObjectValue for each column and shows the max aggregation
                element_class=ObjectValue,
                count=CROSS_COLS,
                start_left=4*cm,
                width=2*cm,
                attribute_name=CROSS_COLS,
                get_value=lambda self, inst: inst.sum('population', self.attribute_name),
                ),
            ObjectValue( # Calculates the average of this row
                attribute_name='row',
                left=17*cm,
                width=3.5*cm,
                get_value=lambda inst: inst.avg('population'),
                ),
        ]

    class band_summary(ReportBand):
        height = 0.5*cm
        elements = [
            Label(text='Totals', width=2.5*cm),
            ManyElements(
                element_class=ObjectValue,
                count=CROSS_COLS,
                start_left=4*cm,
                width=2*cm,
                attribute_name=CROSS_COLS,
                get_value=lambda self, inst: inst.matrix.sum('population', col=self.attribute_name),
                ),
            ObjectValue(
                attribute_name='row',
                left=17*cm,
                width=3.5*cm,
                get_value=lambda inst: inst.matrix.avg('population'),
                ),
        ]
        borders = {'top': True}

    class band_page_footer(ReportBand):
        height = 0.5*cm
        elements = [
            Label(text='Created with Geraldo Reports', top=0.1*cm, left=0),
            SystemField(expression='Page # %(page_number)d of %(page_count)d', top=0.1*cm,
                width=BAND_WIDTH, style={'alignment': TA_RIGHT}),
        ]
        borders = {'top': True}

cities = [
    {'city': 'New York City', 'state': 'NY', 'capital': False, 'population': 8363710, 'area': 468.9, 'government': 'Mayor'},
    {'city': 'Albany', 'state': 'NY', 'capital': True, 'population': 95658, 'area': 21.8, 'government': 'Mayor'},
    {'city': 'Austin', 'state': 'TX', 'capital': True, 'population': 757688, 'area': 296.2, 'government': 'Council-manager'},
    {'city': 'Dallas', 'state': 'TX', 'capital': False, 'population': 1279910, 'area': 385.0, 'government': 'Council-manager'},
    {'city': 'Houston', 'state': 'TX', 'capital': False, 'population': 2242193, 'area': 601.7, 'government': 'Mayor-council'},
    {'city': 'San Francisco', 'state': 'CA', 'capital': False, 'population': 808976, 'area': 231.92, 'government': 'Mayor-council'},
    {'city': 'Los Angeles', 'state': 'CA', 'capital': False, 'population': 3833995, 'area': 498.3, 'government': 'Mayor-council'},
    {'city': 'Sacramento', 'state': 'CA', 'capital': True, 'population': 463794, 'area': 99.2, 'government': 'Mayor-council'},
    {'city': 'Seattle', 'state': 'WA', 'capital': False, 'population': 602000, 'area': 142.5, 'government': 'Mayor-council'},
]

cross = CrossReferenceMatrix(
    objects_list=cities,
    rows_attribute='capital',
    cols_attribute='state',
)

if __name__ == '__main__':
    # Generating the result report
    report = MyReport(queryset=cross)

    # PDF generation
    import os
    cur_dir = os.path.dirname(os.path.abspath(__file__))
    from geraldo.generators import PDFGenerator
    report.generate_by(PDFGenerator, filename=os.path.join(cur_dir, 'output/cross-reference-table.pdf'))

The Result