dtrobert Posted October 22, 2010 Share Posted October 22, 2010 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_namedata = count of the device_idThe 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. ThanksCode:<?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> Link to comment Share on other sites More sharing options...
cbarlow3 Posted October 22, 2010 Share Posted October 22, 2010 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 Link to comment Share on other sites More sharing options...
dtrobert Posted October 26, 2010 Author Share Posted October 26, 2010 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)=modelgroup2(column)=stategroup1(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 totmydp1 15 3 4 22 4 3 7 total do you know how/if this is possible? Link to comment Share on other sites More sharing options...
cbarlow3 Posted October 26, 2010 Share Posted October 26, 2010 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 Link to comment Share on other sites More sharing options...
dtrobert Posted October 26, 2010 Author Share Posted October 26, 2010 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now