<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="ProductsReport" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="782" leftMargin="30" rightMargin="30" topMargin="30" bottomMargin="30">
	<style name="Sans_Normal" isDefault="true" fontName="DejaVu Sans" fontSize="8" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
	<style name="Sans_Bold" isDefault="false" fontName="DejaVu Sans" fontSize="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false"/>
	<subDataset name="Customer_Quantity">
		<parameter name="CityParam" class="java.lang.String"/>
		<queryString>
			<![CDATA[SELECT product.name, positions.quantity,
					   address.firstname, address.lastname
				FROM product, positions, address, document
				WHERE product.id = positions.productid AND
					  address.id = document.addressid AND
					  document.id = positions.documentid AND
					  address.city=$P{CityParam}]]>
		</queryString>
		<field name="Name" class="java.lang.String"/>
		<field name="Quantity" class="java.lang.Integer"/>
		<field name="FirstName" class="java.lang.String"/>
		<field name="LastName" class="java.lang.String"/>
	</subDataset>
	<queryString>
		<![CDATA[SELECT city FROM address GROUP BY city ORDER BY city]]>
	</queryString>
	<field name="City" class="java.lang.String"/>
	<detail>
		<band height="90">
			<textField>
				<reportElement style="Sans_Bold" x="0" y="0" width="500" height="15"/>
				<textElement>
					<font size="10"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["Number of orders by product and customer in " + $F{City}]]></textFieldExpression>
			</textField>
			<crosstab isRepeatColumnHeaders="false">
				<reportElement x="0" y="20" width="782" height="70"/>
				<crosstabParameter name="CityParam">
					<parameterValueExpression><![CDATA[$F{City}]]></parameterValueExpression>
				</crosstabParameter>
				<crosstabDataset>
					<dataset>
						<datasetRun subDataset="Customer_Quantity">
							<datasetParameter name="CityParam">
								<datasetParameterExpression><![CDATA[$F{City}]]></datasetParameterExpression>
							</datasetParameter>
						</datasetRun>
					</dataset>
				</crosstabDataset>
				<rowGroup name="NameGroup" width="80" totalPosition="End">
					<bucket>
						<bucketExpression class="java.lang.String"><![CDATA[$F{FirstName} + " " + $F{LastName}]]></bucketExpression>
					</bucket>
					<crosstabRowHeader>
						<cellContents>
							<box>
								<pen lineWidth="0.5"/>
							</box>
							<textField>
								<reportElement style="Sans_Bold" x="5" y="0" width="75" height="10"/>
								<textElement/>
								<textFieldExpression class="java.lang.String"><![CDATA[$V{NameGroup}]]></textFieldExpression>
							</textField>
						</cellContents>
					</crosstabRowHeader>
					<crosstabTotalRowHeader>
						<cellContents backcolor="#E0FFFF" mode="Opaque">
							<box>
								<pen lineWidth="0.5"/>
							</box>
							<staticText>
								<reportElement style="Sans_Bold" x="5" y="0" width="75" height="10"/>
								<textElement/>
								<text><![CDATA[TOTAL]]></text>
							</staticText>
						</cellContents>
					</crosstabTotalRowHeader>
				</rowGroup>
				<columnGroup name="ProductGroup" height="50" totalPosition="End" headerPosition="Stretch">
					<bucket>
						<bucketExpression class="java.lang.String"><![CDATA[$F{Name}]]></bucketExpression>
					</bucket>
					<crosstabColumnHeader>
						<cellContents>
							<box>
								<pen lineWidth="0.5"/>
							</box>
							<textField>
								<reportElement style="Sans_Bold" x="0" y="0" width="20" height="50"/>
								<textElement textAlignment="Center" verticalAlignment="Middle" rotation="Left"/>
								<textFieldExpression class="java.lang.String"><![CDATA[$V{ProductGroup}]]></textFieldExpression>
							</textField>
						</cellContents>
					</crosstabColumnHeader>
					<crosstabTotalColumnHeader>
						<cellContents backcolor="#FFFF60" mode="Opaque">
							<box>
								<pen lineWidth="0.5"/>
							</box>
							<staticText>
								<reportElement style="Sans_Bold" x="0" y="0" width="30" height="50"/>
								<textElement textAlignment="Center" verticalAlignment="Middle"/>
								<text><![CDATA[TOTAL]]></text>
							</staticText>
						</cellContents>
					</crosstabTotalColumnHeader>
				</columnGroup>
				<measure name="QuantityMesure" class="java.lang.Integer" calculation="Sum">
					<measureExpression><![CDATA[$F{Quantity}]]></measureExpression>
				</measure>
				<crosstabCell width="20" height="10">
					<cellContents backcolor="#FFFFFF" mode="Opaque">
						<box>
							<pen lineWidth="0.5"/>
						</box>
						<textField>
							<reportElement x="0" y="0" width="20" height="10"/>
							<textElement textAlignment="Center" verticalAlignment="Middle"/>
							<textFieldExpression class="java.lang.Integer"><![CDATA[$V{QuantityMesure}]]></textFieldExpression>
						</textField>
					</cellContents>
				</crosstabCell>
				<crosstabCell width="30" height="10" columnTotalGroup="ProductGroup">
					<cellContents backcolor="#FFFF60" mode="Opaque">
						<box>
							<pen lineWidth="0.5"/>
						</box>
						<textField>
							<reportElement x="0" y="0" width="30" height="10"/>
							<textElement textAlignment="Center" verticalAlignment="Middle"/>
							<textFieldExpression class="java.lang.Integer"><![CDATA[$V{QuantityMesure}]]></textFieldExpression>
						</textField>
					</cellContents>
				</crosstabCell>
				<crosstabCell width="20" height="10" rowTotalGroup="NameGroup">
					<cellContents backcolor="#E0FFFF" mode="Opaque">
						<box>
							<pen lineWidth="0.5"/>
						</box>
						<textField>
							<reportElement x="0" y="0" width="20" height="10"/>
							<textElement textAlignment="Center" verticalAlignment="Middle"/>
							<textFieldExpression class="java.lang.Integer"><![CDATA[$V{QuantityMesure}]]></textFieldExpression>
						</textField>
					</cellContents>
				</crosstabCell>
				<crosstabCell width="30" height="10" rowTotalGroup="NameGroup" columnTotalGroup="ProductGroup">
					<cellContents backcolor="#C0C0C0" mode="Opaque">
						<box>
							<pen lineWidth="0.5"/>
						</box>
						<textField>
							<reportElement style="Sans_Bold" x="0" y="0" width="30" height="10"/>
							<textElement textAlignment="Center" verticalAlignment="Middle"/>
							<textFieldExpression class="java.lang.Integer"><![CDATA[$V{QuantityMesure}]]></textFieldExpression>
						</textField>
					</cellContents>
				</crosstabCell>
				<whenNoDataCell>
					<cellContents>
						<staticText>
							<reportElement x="0" y="0" width="250" height="25"/>
							<textElement/>
							<text><![CDATA[No data]]></text>
						</staticText>
					</cellContents>
				</whenNoDataCell>
			</crosstab>
		</band>
	</detail>
</jasperReport>
