crosstab newby question

[dtrobert]

 Hi,

 I am trying to make a cross tab (based upon an Excel pivot table) which shows phone models by location.   My sql collects all phones and their location and I've created a cross tab using the wizard with 

 

row label = site name (aka devicepool)

colum name = model_name

data = count of the device_id

The first problem is that, the device_id are strings so count somehow just returned one of the device ids for all entries.  The element type was actually set to string for a count.  

I then modified the 'measure' (looks like just a variable to me) and changed the type to java.lang.Integer and now at least it's reporting what seem to be counts.  The problem is that the counts do not change from row to row.  

Any help is greatly appreciated. 

Thanks

Code:

<?xml version="1.0" encoding="UTF-8"  ?><!-- Created with iReport - A designer for JasperReports --><!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd"><jasperReport		 name="DeviceSummary2"		 columnCount="1"		 printOrder="Vertical"		 orientation="Landscape"		 pageWidth="792"		 pageHeight="612"		 columnWidth="792"		 columnSpacing="0"		 leftMargin="0"		 rightMargin="0"		 topMargin="0"		 bottomMargin="0"		 whenNoDataType="AllSectionsNoDetail"		 isTitleNewPage="false"		 isSummaryNewPage="false">	<property name="ireport.scriptlethandling" value="0" />	<property name="ireport.encoding" value="UTF-8" />	<import value="java.util.*" />	<import value="net.sf.jasperreports.engine.*" />	<import value="net.sf.jasperreports.engine.data.*" />	<reportFont name="Verdana_Normal" isDefault="true" fontName="Verdana" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>	<reportFont name="Verdana_Bold" isDefault="false" fontName="Verdana" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica-Bold" pdfEncoding="Cp1252" isPdfEmbedded="false"/>	<queryString><![CDATA[select dp.name as devicepool, d.* from device dinner join device_pool dp on d.device_pool_id = d.device_pool_idorder by dp.name]]></queryString>	<field name="devicepool" class="java.lang.String"/>	<field name="device_id" class="java.lang.String"/>	<field name="name" class="java.lang.String"/>	<field name="ip_address" class="java.lang.String"/>	<field name="authentication_mode" class="java.lang.String"/>	<field name="authentication_string" class="java.lang.String"/>	<field name="auto_select_line_enable" class="java.lang.String"/>	<field name="build_in_bridge" class="java.lang.String"/>	<field name="certificate_operation" class="java.lang.String"/>	<field name="cti_enabled" class="java.lang.Boolean"/>	<field name="date_discovered" class="java.sql.Timestamp"/>	<field name="description" class="java.lang.String"/>	<field name="device_protocol" class="java.lang.String"/>	<field name="device_security_mode" class="java.lang.String"/>	<field name="disable_speaker" class="java.lang.Boolean"/>	<field name="disable_speaker_and_headset" class="java.lang.Boolean"/>	<field name="display_idle_timeout" class="java.lang.String"/>	<field name="extension_mobility" class="java.lang.Boolean"/>	<field name="forwarding_delay" class="java.lang.String"/>	<field name="garp" class="java.lang.String"/>	<field name="http_supported" class="java.lang.Boolean"/>	<field name="ignore_presentation_indicators" class="java.lang.Boolean"/>	<field name="key_size" class="java.lang.Integer"/>	<field name="last_registration_date" class="java.sql.Timestamp"/>	<field name="login_duration" class="java.lang.String"/>	<field name="login_time" class="java.lang.String"/>	<field name="login_user_id" class="java.lang.String"/>	<field name="mlpp_domain_id" class="java.lang.Integer"/>	<field name="mlpp_indication_status" class="java.lang.String"/>	<field name="mlpp_preemption" class="java.lang.String"/>	<field name="model_number" class="java.lang.Integer"/>	<field name="model_name" class="java.lang.String"/>	<field name="network_locale" class="java.lang.String"/>	<field name="network_moh_audio_source" class="java.lang.String"/>	<field name="number_of_add_on_modules" class="java.lang.Integer"/>	<field name="operation_completes_time" class="java.lang.String"/>	<field name="packet_capture_duration" class="java.lang.String"/>	<field name="packet_capture_mode" class="java.lang.String"/>	<field name="pc_port" class="java.lang.String"/>	<field name="phone" class="java.lang.Boolean"/>	<field name="phone_load" class="java.lang.String"/>	<field name="primary_call_manager" class="java.lang.String"/>	<field name="privacy" class="java.lang.String"/>	<field name="registration_state" class="java.lang.Integer"/>	<field name="retry_video_call_as_audio" class="java.lang.Boolean"/>	<field name="rtp_info_supported" class="java.lang.Boolean"/>	<field name="settings_access" class="java.lang.String"/>	<field name="span_to_pc_port" class="java.lang.String"/>	<field name="subnet_mask" class="java.lang.String"/>	<field name="user_locale" class="java.lang.String"/>	<field name="user_moh_audio_source" class="java.lang.String"/>	<field name="video_capability" class="java.lang.String"/>	<field name="voice_vlan_access" class="java.lang.String"/>	<field name="web_access" class="java.lang.String"/>	<field name="web_access_disabled" class="java.lang.Boolean"/>	<field name="web_info_extracted" class="java.lang.Boolean"/>	<field name="xml_supported" class="java.lang.Boolean"/>	<field name="directory_services_url_1" class="java.lang.String"/>	<field name="directory_services_url_2" class="java.lang.String"/>	<field name="voice_mail_url_1" class="java.lang.String"/>	<field name="voice_mail_url_2" class="java.lang.String"/>	<field name="idle_url" class="java.lang.String"/>	<field name="idle_timeout" class="java.lang.String"/>	<field name="proxy_url" class="java.lang.String"/>	<field name="authentication_url" class="java.lang.String"/>	<field name="e911_location" class="java.lang.String"/>	<field name="phone_user_name" class="java.lang.String"/>	<field name="device_mobility_mode" class="java.lang.String"/>	<field name="logged_into_hunt_group" class="java.lang.Boolean"/>	<field name="last_status_update_time" class="java.sql.Timestamp"/>	<field name="phone_web_fetch_required" class="java.lang.Boolean"/>	<field name="aar_calling_search_space_id" class="java.lang.String"/>	<field name="calling_search_space_id" class="java.lang.String"/>	<field name="device_pool_id" class="java.lang.String"/>	<field name="location_id" class="java.lang.String"/>	<field name="network_segment_id" class="java.lang.String"/>	<field name="softkey_template_id" class="java.lang.String"/>	<field name="media_resource_group_list_id" class="java.lang.String"/>	<field name="phone_template_id" class="java.lang.String"/>	<field name="phone_web_info_id" class="java.lang.String"/>	<field name="common_profile_id" class="java.lang.String"/>	<field name="aar_group_id" class="java.lang.String"/>		<background>			<band height="0"  isSplitAllowed="true" >			</band>		</background>		<title>			<band height="0"  isSplitAllowed="true" >			</band>		</title>		<pageHeader>			<band height="0"  isSplitAllowed="true" >			</band>		</pageHeader>		<columnHeader>			<band height="0"  isSplitAllowed="true" >			</band>		</columnHeader>		<detail>			<band height="0"  isSplitAllowed="false" >			</band>		</detail>		<columnFooter>			<band height="0"  isSplitAllowed="true" >			</band>		</columnFooter>		<pageFooter>			<band height="0"  isSplitAllowed="true" >			</band>		</pageFooter>		<summary>			<band height="200"  isSplitAllowed="true" >				<crosstab >					<reportElement						x="0"						y="0"						width="792"						height="200"						key="crosstab-1"/>					<crosstabHeaderCell>						<cellContents mode="Transparent">					<box></box>						</cellContents>					</crosstabHeaderCell>					<rowGroup name="devicepool" width="100" totalPosition="End">						<bucket>							<bucketExpression class="java.lang.String"><![CDATA[$F{devicepool}]]></bucketExpression>						</bucket>						<crosstabRowHeader>						<cellContents mode="Transparent">					<box></box>				<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >					<reportElement						x="0"						y="0"						width="100"						height="30"						key="textField"/>					<box></box>					<textElement textAlignment="Center" verticalAlignment="Middle">						<font/>					</textElement>				<textFieldExpression   class="java.lang.String"><![CDATA[$V{devicepool}]]></textFieldExpression>				</textField>						</cellContents>						</crosstabRowHeader>						<crosstabTotalRowHeader>						<cellContents mode="Transparent">					<box></box>				<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >					<reportElement						x="0"						y="0"						width="100"						height="30"						key="textField"/>					<box></box>					<textElement textAlignment="Center" verticalAlignment="Middle">						<font/>					</textElement>				<textFieldExpression   class="java.lang.String"><![CDATA["devicepool total"]]></textFieldExpression>				</textField>						</cellContents>						</crosstabTotalRowHeader>					</rowGroup>					<columnGroup name="model_name" height="25" totalPosition="End" headerPosition="Center">						<bucket>							<bucketExpression class="java.lang.String"><![CDATA[$F{model_name}]]></bucketExpression>						</bucket>						<crosstabColumnHeader>						<cellContents mode="Transparent">					<box></box>				<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >					<reportElement						x="0"						y="0"						width="50"						height="25"						key="textField"/>					<box></box>					<textElement textAlignment="Center" verticalAlignment="Middle">						<font/>					</textElement>				<textFieldExpression   class="java.lang.String"><![CDATA[$V{model_name}]]></textFieldExpression>				</textField>						</cellContents>						</crosstabColumnHeader>						<crosstabTotalColumnHeader>						<cellContents mode="Transparent">					<box></box>				<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >					<reportElement						x="0"						y="0"						width="50"						height="25"						key="textField"/>					<box></box>					<textElement textAlignment="Center" verticalAlignment="Middle">						<font/>					</textElement>				<textFieldExpression   class="java.lang.String"><![CDATA["model_name total"]]></textFieldExpression>				</textField>						</cellContents>						</crosstabTotalColumnHeader>					</columnGroup>					<measure name="name_Count" class="java.lang.Integer" calculation="Count">						<measureExpression><![CDATA[$F{name}]]></measureExpression>					</measure>					<crosstabCell width="50" height="30">						<cellContents mode="Transparent">					<box></box>				<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >					<reportElement						x="0"						y="0"						width="50"						height="30"						key="textField"/>					<box></box>					<textElement textAlignment="Center" verticalAlignment="Middle">						<font/>					</textElement>				<textFieldExpression   class="java.lang.Integer"><![CDATA[$V{name_Count}]]></textFieldExpression>				</textField>						</cellContents>					</crosstabCell>					<crosstabCell width="50" height="30" columnTotalGroup="model_name">						<cellContents mode="Transparent">					<box></box>				<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >					<reportElement						x="0"						y="0"						width="50"						height="30"						key="textField"/>					<box></box>					<textElement textAlignment="Center" verticalAlignment="Middle">						<font/>					</textElement>				<textFieldExpression   class="java.lang.Integer"><![CDATA[$V{name_Count}]]></textFieldExpression>				</textField>						</cellContents>					</crosstabCell>					<crosstabCell width="50" height="30" rowTotalGroup="devicepool">						<cellContents mode="Transparent">					<box></box>				<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >					<reportElement						x="0"						y="0"						width="50"						height="30"						key="textField"/>					<box></box>					<textElement textAlignment="Center" verticalAlignment="Middle">						<font/>					</textElement>				<textFieldExpression   class="java.lang.Integer"><![CDATA[$V{name_Count}]]></textFieldExpression>				</textField>						</cellContents>					</crosstabCell>					<crosstabCell width="50" height="30" rowTotalGroup="devicepool" columnTotalGroup="model_name">						<cellContents mode="Transparent">					<box></box>				<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >					<reportElement						x="0"						y="0"						width="50"						height="30"						key="textField"/>					<box></box>					<textElement textAlignment="Center" verticalAlignment="Middle">						<font/>					</textElement>				<textFieldExpression   class="java.lang.Integer"><![CDATA[$V{name_Count}]]></textFieldExpression>				</textField>						</cellContents>					</crosstabCell>					<whenNoDataCell>						<cellContents mode="Transparent">					<box></box>						</cellContents>					</whenNoDataCell>					</crosstab>			</band>		</summary></jasperReport>

[cbarlow3]

I'm imagining your result set (not counting additional fields in your query that don't appear in the cross tab) to look like the cells I have below, where device_id is a string, but it's also guaranteed to be unique (I only have to COUNT them, not SUM them), and you're trying to build a crosstab that counts how many rows in the result set have various combinations of devicepool/modelname.  If that's accurate, I have a screenshot of my output, and I've also pasted my jrxml (ignore my SQL command, naturally):

 

device_id	devicepool	modelname
0001	Pool 1	Model A
0002	Pool 1	Model C
0003	Pool 4	Model B
0004	Pool 2	Model A
0005	Pool 1	Model C
0006	Pool 4	Model B

Code:

Post Edited by cbarlow3 at 10/22/2010 21:05

[dtrobert]

Ugh, looks like my query was wrong on the join. Thanks for your help.

 

Now that I have my report working generally, I do have one follow-up question. I have 2 groups for columns with totals at the end of each row. What I would like is a total for each group1 column. For example:

 

group1(column)=model

group2(column)=state

group1(row)=devicepool

 

my report has

 

devicepool $V{model} total

$V{state}

$V{devicepool} $V{count} total

 

a phone can be in one of several states. What I'm looking for is the total number of phones per model, something like

 

 

devicepool model1 model2 total

st1 st2 st3 tot st2 st3 tot

mydp1 15 3 4 22 4 3 7 total

 

do you know how/if this is possible?

 

 

[cbarlow3]

I haven't tried it in iReport, but I think what you're describing is like a 'GROUP BY CUBE' clause in SQL, where you're looking for a summary (SUM, AVG, etc.) for each combination of various field values.  If you're looking to show the details in iReport but also show subtotals for each combination (and maybe even "rollup" totals if one of these fields is considered subordinate to another, like city is to state), then I don't know how you do that.

Carl

[dtrobert]

Hmm, could be. I was hoping it was just say adding another total variable after my state variable since I just want to sum up the counts for all states for a particular model+devicepool.