Crosstab measure displays null

[ktalarico]

I have a row group called question with a bucket expression equal to $F{question} + $F{sort_order}.  I only want to display the text of $F{question}, not $F{sort_order}; so I created a measure called questionmeasure with a value expression equal to $F{question}.  In questionheader, I used questionmeasure in the textfield expression.  It is displaying null.

Basically, I want to use a measure expression where I would normally use a row group expression.

Below is jrxml:

<?xml version="1.0" encoding="UTF-8"?><jasperReportxmlns="http://jasperreports.sourceforge.net/jasperreports"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreportshttp://jasperreports.sourceforge.net/xsd/jasperreport.xsd"name="QuestionnaireResponseSummary" language="groovy" pageWidth="1350"pageHeight="1000" whenNoDataType="NoDataSection" columnWidth="1310"leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"isIgnorePagination="true">    <property name="ireport.zoom" value="1.0"/>    <property name="ireport.x" value="0"/>    <property name="ireport.y" value="0"/>    <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>    <style name="Crosstab Data Text" hAlign="Center"/>    <parameter name="START DATE" class="java.util.Date">        <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression>    </parameter>    <parameter name="END DATE" class="java.util.Date">        <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression>    </parameter>    <parameter name="KIOSK GROUP" class="java.util.List">        <property name="parameter_type" value="multiselect"/>      <property name="query" value="select distinct name from (selectkg.name from kiosk_group kg union select string_value fromsrv_instance_context_variable_map where property='kioskGroup')t where name not ilike'ZZ%'order by name"/>        <defaultValueExpression><![CDATA[java.util.Arrays.asList(new String[" "])]]></defaultValueExpression>    </parameter>    <parameter name="QUESTIONNAIRE NAME" class="java.lang.String">        <property name="parameter_type" value="select"/>      <property name="query" value="select distinct sd.display_name asdisplayName from srv_survey_definition sd where sd.active = true orderby sd.display_name"/>        <parameterDescription><![CDATA[]]></parameterDescription>        <defaultValueExpression><![CDATA[]]></defaultValueExpression>    </parameter>    <queryString>        <![CDATA[with recursive survey_items(sort_order,kioskgroup,response_id,item_id,item_subclass,question)as (        select        ARRAY[nde.page_indx,itm.survey_item_indx] as sort_order,        sicm.string_value,        resp.id,        itm.id,        itm.subclass,        sltm.language_map_element::text as question        from srv_survey_item itm         join srv_transition_node nde on nde.id = itm.page_id         join srv_survey_definition sd on sd.id = nde.survey_definition_id         join srv_survey_response resp on resp.active_survey_definition_id = sd.id and resp.start_survey_date is not null         join srv_survey_instance ssi on (ssi.survey_response_id = resp.id )         join srv_instance_context_variable_map sicm on (sicm.survey_instance_id = ssi.id and sicm.property like 'kioskGroup')         join srv_language_text slt on slt.id = itm.main_text         join srv_language_text_language_map sltm on sltm.srv_language_text_id = slt.id         where itm.subclass <> 'DESCRIPTIVE_TEXT'       and resp.start_survey_date between to_timestamp($P{STARTDATE},'YYYY-MM-DD') and (to_timestamp($P{END DATE}, 'YYYY-MM-DD') +interval '1 day' )         and resp.start_survey_date is not null       and split_part(sd.survey_name,'_OUTDATED',1) IN (select survey_namefrom srv_survey_definition def where def.display_name = $P{QUESTIONNAIRENAME})         and $X{IN, sicm.string_value,KIOSK GROUP}         and sicm.string_value not ilike 'ZZ%'         and resp.id not in(           select ssr.id           from  srv_survey_response ssr           join srv_survey_instance si1 on ssr.id = si1.survey_response_id           where (si1.exit_type is null and ssr.completed =false))    union        select            sort_order || coalesce(itm.survey_item_indx,0) || coalesce(itm.group_idx,0) || coalesce(itm.group_question_idx,0),            si.kioskgroup,            si.response_id,            itm.id,            itm.subclass,            question ||  E'n' || sltm.language_map_element::text        from            srv_survey_item itm            join survey_items si on itm.item_group_id = si.item_id or itm.question_group_id = si.item_id            join srv_language_text slt on slt.id = itm.main_text            join srv_language_text_language_map sltm on sltm.srv_language_text_id = slt.id),survey_responses as(selectsi.sort_order,si.kioskgroup,si.response_id,si.item_id,si.item_subclass,si.question,case when si.item_subclass='QUESTION_MULTIPLE_CHOICE' then sltm.language_map_element     when si.item_subclass='QUESTION_MATRIX' then sltm.language_map_element || ' : ' || sltm2.language_map_element     when si.item_subclass in ('QUESTION_TEXT','QUESTION_DATE') then 'n/a' end as choice,CASE when si.item_subclass='QUESTION_MULTIPLE_CHOICE' then        (select count(*) from srv_survey_answer ans         join srv_multiple_choice_answer_choice_join mChoice on ans.id = mChoice.matrix_part_answer_id         where ans.question = si.item_id and mChoice.choice_id=slt.id and ans.response_id=si.response_id)     when si.item_subclass='QUESTION_MATRIX' then        (select count(*) from srv_survey_answer ans         join srv_survey_answer_part_answers pa on pa.srv_survey_answer_id = ans.id         join srv_survey_answer partans on pa.part_answers_id = partans.id         join srv_multiple_choice_answer_choice_join mChoice on partans.id = mChoice.matrix_part_answer_id       where ans.question = si.item_id  and mChoice.choice_id=slt2.id andpartans.matrix_part=slt.id and ans.response_id=si.response_id)     when si.item_subclass = 'QUESTION_TEXT' then        (select count(*) from srv_survey_answer ans where ans.question = si.item_id and ans.response_id = si.response_id         and ans.value is not null and ans.value <> '')     when si.item_subclass = 'QUESTION_DATE' then        (select count(*) from srv_survey_answer ans where ans.question = si.item_id and ans.response_id = si.response_id         and ans.date_value is not null)     end as num_chosen,slt.choice_indx as multiple_choice_sort,slt.part_indx as matrix_part_sort,slt2.choice_indx as matrix_choice_sort from survey_items sileft join srv_language_text slt onCASE when si.item_subclass='QUESTION_MULTIPLE_CHOICE' then slt.multiple_choice_question_id = si.item_id      when si.item_subclass='QUESTION_MATRIX' then slt.matrix_question_id = si.item_id endleft join srv_language_text_language_map sltm on sltm.srv_language_text_id = slt.id--This extra join is so we can combine each matrix part with each choiceleft join srv_language_text slt2 on slt.matrix_question_id = slt2.multiple_choice_question_idleft join srv_language_text_language_map sltm2 on slt2.id = sltm2.srv_language_text_idwhere si.item_subclass not in ('SURVEY_ITEM_GROUP','QUESTION_GROUP')order by sort_order, multiple_choice_sort, matrix_part_sort, matrix_choice_sort)selectsort_order,multiple_choice_sort,matrix_part_sort,matrix_choice_sort,question,choice,kioskgroup,count (distinct response_id) as num_responses,sum(num_chosen) as num_chosenfrom survey_responsesgroup by sort_order, multiple_choice_sort, matrix_part_sort, matrix_choice_sort, kioskgroup, question, choiceorder by sort_order, multiple_choice_sort, matrix_part_sort, matrix_choice_sort, kioskgroup]]>    </queryString>    <field name="sort_order" class="java.sql.Array"/>    <field name="multiple_choice_sort" class="java.lang.Integer"/>    <field name="matrix_part_sort" class="java.lang.Integer"/>    <field name="matrix_choice_sort" class="java.lang.Integer"/>    <field name="question" class="java.lang.String"/>    <field name="choice" class="java.lang.String"/>    <field name="kioskgroup" class="java.lang.String"/>    <field name="num_responses" class="java.lang.Long"/>    <field name="num_chosen" class="java.math.BigDecimal"/>    <variable name="PercentAnswered" class="java.lang.Double">      <variableExpression><![CDATA[$F{num_chosen} == 0 ||$F{num_responses} == 0 ? 0 :($F{num_chosen}/$F{num_responses})]]></variableExpression>    </variable>    <background>        <band splitType="Stretch"/>    </background>    <title>        <band height="82">            <staticText>                <reportElement x="0" y="0" width="1276" height="36"/>                <textElement textAlignment="Left" verticalAlignment="Middle">                    <font size="24" isBold="true"/>                </textElement>                <text><![CDATA[Questionnaire Response Summary]]></text>            </staticText>            <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true">                <reportElement x="0" y="36" width="1276" height="21"/>                <textElement textAlignment="Left" verticalAlignment="Middle">                    <font size="12"/>                </textElement>              <textFieldExpression class="java.lang.String"><![CDATA["Date: "+ new SimpleDateFormat("MM/dd/yyyy").format($P{START DATE}) + " to " +new SimpleDateFormat("MM/dd/yyyy").format($P{ENDDATE})]]></textFieldExpression>            </textField>            <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true">                <reportElement x="0" y="57" width="1260" height="25"/>                <box leftPadding="0" bottomPadding="2"/>                <textElement verticalAlignment="Middle">                    <font size="12"/>                </textElement>              <textFieldExpressionclass="java.lang.String"><![CDATA["Questionnaire Name:"+$P{QUESTIONNAIRE NAME}]]></textFieldExpression>            </textField>        </band>    </title>    <pageFooter>        <band height="20">            <textField pattern="MM/dd/yyyy h.mm a" isBlankWhenNull="true">                <reportElement x="0" y="0" width="1276" height="20"/>                <textElement verticalAlignment="Middle">                    <font size="10"/>                </textElement>              <textFieldExpression class="java.lang.String"><![CDATA[newSimpleDateFormat("MM/dd/yyyy h:mm a").format(newjava.util.Date())]]></textFieldExpression>            </textField>        </band>    </pageFooter>    <summary>        <band height="320" splitType="Stretch">            <staticText>              <reportElement positionType="Float" mode="Transparent" x="0" y="97"width="1276" height="223" backcolor="#FFFFCC"/>                <textElement>                    <font size="10"/>                </textElement>              <text><![CDATA[Report Overview: Provides _______. Reportexcludes all kiosk groups that start with 'ZZ' or 'zz' .Parameters: Start Date, End Date, Kiosk Group, Questionnaire Name.Data DefinitionsKiosk Group: Kiosk group selected in parameters.Questionnaire Name: Name of questionnaire selected in parameters.Question:Answer:Number Chosen out of Total Number of Responses:%Answered:]]></text>            </staticText>            <crosstab>                <reportElement x="0" y="0" width="1310" height="320"/>                <crosstabDataset isDataPreSorted="true"/>                <rowGroup name="question" width="70">                    <bucket>                      <bucketExpressionclass="java.lang.String"><![CDATA[$F{question} +$F{sort_order}]]></bucketExpression>                    </bucket>                    <crosstabRowHeader>                        <cellContents backcolor="#F0F8FF" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                            <textField isStretchWithOverflow="true">                              <reportElement style="Crosstab Data Text"stretchType="RelativeToTallestObject" x="0" y="0" width="70"height="25"/>                                <textElement/>                              <textFieldExpressionclass="java.lang.String"><![CDATA[$V{questionmeasure}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabRowHeader>                    <crosstabTotalRowHeader>                        <cellContents/>                    </crosstabTotalRowHeader>                </rowGroup>                <rowGroup name="choice" width="100">                    <bucket>                        <bucketExpression class="java.lang.String"><![CDATA[$F{choice}]]></bucketExpression>                    </bucket>                    <crosstabRowHeader>                        <cellContents>                            <textField>                                <reportElement style="Crosstab Data Text" x="0" y="0" width="100" height="25"/>                                <textElement/>                              <textFieldExpressionclass="java.lang.String"><![CDATA[$V{choice}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabRowHeader>                    <crosstabTotalRowHeader>                        <cellContents/>                    </crosstabTotalRowHeader>                </rowGroup>                <columnGroup name="kioskgroup" height="30" totalPosition="End">                    <bucket>                      <bucketExpressionclass="java.lang.String"><![CDATA[$F{kioskgroup}]]></bucketExpression>                    </bucket>                    <crosstabColumnHeader>                        <cellContents backcolor="#F0F8FF" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                            <textField>                                <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="30"/>                                <textElement/>                              <textFieldExpressionclass="java.lang.String"><![CDATA[$V{kioskgroup}]]></textFieldExpression>                            </textField>                        </cellContents>                    </crosstabColumnHeader>                    <crosstabTotalColumnHeader>                        <cellContents backcolor="#BFE1FF" mode="Opaque">                            <box>                                <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                            </box>                            <staticText>                                <reportElement x="0" y="0" width="50" height="30"/>                                <textElement textAlignment="Center" verticalAlignment="Middle"/>                                <text><![CDATA[Total kioskgroup]]></text>                            </staticText>                        </cellContents>                    </crosstabTotalColumnHeader>                </columnGroup>                <measure name="num_chosenMeasure" class="java.math.BigDecimal" calculation="Sum">                    <measureExpression><![CDATA[$F{num_chosen}]]></measureExpression>                </measure>                <measure name="questionmeasure" class="java.lang.String">                    <measureExpression><![CDATA[$F{question}]]></measureExpression>                </measure>                <crosstabCell width="50" height="25">                    <cellContents>                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField>                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>                            <textElement/>                          <textFieldExpressionclass="java.math.BigDecimal"><![CDATA[$V{num_chosenMeasure}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell height="25" rowTotalGroup="question">                    <cellContents backcolor="#BFE1FF" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField>                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>                            <textElement/>                          <textFieldExpressionclass="java.math.BigDecimal"><![CDATA[$V{num_chosenMeasure}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell width="50" columnTotalGroup="kioskgroup">                    <cellContents backcolor="#BFE1FF" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField>                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>                            <textElement/>                          <textFieldExpressionclass="java.math.BigDecimal"><![CDATA[$V{num_chosenMeasure}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell rowTotalGroup="question" columnTotalGroup="kioskgroup">                    <cellContents backcolor="#BFE1FF" mode="Opaque">                        <box>                            <pen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>                        </box>                        <textField>                            <reportElement style="Crosstab Data Text" x="0" y="0" width="50" height="25"/>                            <textElement/>                          <textFieldExpressionclass="java.math.BigDecimal"><![CDATA[$V{num_chosenMeasure}]]></textFieldExpression>                        </textField>                    </cellContents>                </crosstabCell>                <crosstabCell rowTotalGroup="choice">                    <cellContents/>                </crosstabCell>                <crosstabCell rowTotalGroup="choice" columnTotalGroup="kioskgroup">                    <cellContents/>                </crosstabCell>            </crosstab>        </band>    </summary>    <noData>        <band height="406">            <staticText>                <reportElement x="0" y="136" width="1276" height="20"/>                <box>                    <topPen lineWidth="1.0"/>                    <leftPen lineWidth="1.0"/>                    <bottomPen lineWidth="1.0"/>                    <rightPen lineWidth="1.0"/>                </box>                <textElement textAlignment="Center" verticalAlignment="Middle">                    <font size="12" isBold="false"/>                </textElement>                <text><![CDATA[No Records Found]]></text>            </staticText>            <staticText>                <reportElement x="0" y="10" width="1276" height="36"/>                <textElement textAlignment="Left" verticalAlignment="Middle">                    <font size="24" isBold="true"/>                </textElement>                <text><![CDATA[Questionnaire Response Summary]]></text>            </staticText>            <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true">                <reportElement x="0" y="46" width="1276" height="20"/>                <textElement textAlignment="Left" verticalAlignment="Middle">                    <font size="12"/>                </textElement>              <textFieldExpression class="java.lang.String"><![CDATA["Date: "+ new SimpleDateFormat("MM/dd/yyyy").format($P{START DATE}) + " to " +new SimpleDateFormat("MM/dd/yyyy").format($P{ENDDATE})]]></textFieldExpression>            </textField>            <staticText>              <reportElement positionType="Float" mode="Transparent" x="0" y="156"width="1276" height="230" backcolor="#FFFFCC"/>                <textElement>                    <font size="10"/>                </textElement>              <text><![CDATA[Report Overview: Provides metrics on initiatedand completed questionnaires. Report excludes all kiosk groups thatstart with 'ZZ' or 'zz'.Parameters: Start Date, End Date, Kiosk Group, Questionnaire Name, Length of Time.Data DefinitionsKiosk Group: Kiosk group selected in parameters.Questionnaire Name: Name of questionnaire selected in parameters.Questionnaires Started: Number of times questionnaire  is started.PatientsStarted: For sign-in survey, number of unique patients who started atleast one questionnaire. For non-sign-in and anonymous survey, number oftimes questionnaire is started.Questionnaires Completed: Number of times Questionnaire is completed.PatientsCompleted: For sign-in survey, number of unique patients who completedat least one questionnaire. For non-sign-in and anonymous survey, numberof times questionnaire is completed.% Completed Questionnaires: Number of completed questionnaires divided by started questionnaires.CompletedQuestionnaires Average Time: For completed questionnaires, total timespent on questionnaires divided by number of completed questionnaires.AbandonedQuestionnaires Average Time: For questionnaires started but notcompleted, total time spent on questionnaires divided by number ofquestionnaires that were started but not completed.AllQuestionnaires Average Time: For all questionnaires that were started,total time spent on questionnaires divided by number of startedquestionnaires.]]></text>            </staticText>            <textField pattern="MM/dd/yyyy h.mm a" isBlankWhenNull="true">                <reportElement x="0" y="386" width="1276" height="20"/>                <textElement verticalAlignment="Middle">                    <font size="10"/>                </textElement>              <textFieldExpression class="java.lang.String"><![CDATA[newSimpleDateFormat("MM/dd/yyyy h:mm a").format(newjava.util.Date())]]></textFieldExpression>            </textField>            <staticText>                <reportElement stretchType="RelativeToBandHeight" x="565" y="66" width="215" height="30"/>                <box bottomPadding="2" rightPadding="2">                    <topPen lineWidth="1.0"/>                    <leftPen lineWidth="1.0"/>                    <bottomPen lineWidth="1.0"/>                    <rightPen lineWidth="1.0"/>                </box>                <textElement textAlignment="Center" verticalAlignment="Bottom">                    <font size="12" isBold="true"/>                </textElement>                <text><![CDATA[Completed]]></text>            </staticText>            <staticText>                <reportElement stretchType="RelativeToBandHeight" x="0" y="66" width="150" height="70"/>                <box leftPadding="2" bottomPadding="2">                    <topPen lineWidth="1.0"/>                    <leftPen lineWidth="1.0"/>                    <bottomPen lineWidth="1.0"/>                    <rightPen lineWidth="1.0"/>                </box>                <textElement textAlignment="Left" verticalAlignment="Bottom">                    <font size="12" isBold="true"/>                </textElement>                <text><![CDATA[Kiosk Group]]></text>            </staticText>            <staticText>                <reportElement x="565" y="96" width="125" height="40"/>                <box bottomPadding="2" rightPadding="2">                    <topPen lineWidth="1.0"/>                    <leftPen lineWidth="1.0"/>                    <bottomPen lineWidth="1.0"/>                    <rightPen lineWidth="1.0"/>                </box>                <textElement textAlignment="Right" verticalAlignment="Bottom">                    <font size="12" isBold="true"/>                </textElement>                <text><![CDATA[Questionnaires]]></text>            </staticText>            <staticText>                <reportElement stretchType="RelativeToBandHeight" x="350" y="66" width="215" height="30"/>                <box bottomPadding="2" rightPadding="2">                    <topPen lineWidth="1.0"/>                    <leftPen lineWidth="1.0"/>                    <bottomPen lineWidth="1.0"/>                    <rightPen lineWidth="1.0"/>                </box>                <textElement textAlignment="Center" verticalAlignment="Bottom">                    <font size="12" isBold="true"/>                </textElement>                <text><![CDATA[started]]></text>            </staticText>            <staticText>                <reportElement stretchType="RelativeToBandHeight" x="150" y="66" width="200" height="70"/>                <box leftPadding="2" bottomPadding="2">                    <topPen lineWidth="1.0"/>                    <leftPen lineWidth="1.0"/>                    <bottomPen lineWidth="1.0"/>                    <rightPen lineWidth="1.0"/>                </box>                <textElement textAlignment="Left" verticalAlignment="Bottom">                    <font size="12" isBold="true"/>                </textElement>                <text><![CDATA[Questionnaire Name]]></text>            </staticText>            <staticText>                <reportElement stretchType="RelativeToBandHeight" x="690" y="96" width="90" height="40"/>                <box bottomPadding="2" rightPadding="2">                    <topPen lineWidth="1.0"/>                    <leftPen lineWidth="1.0"/>                    <bottomPen lineWidth="1.0"/>                    <rightPen lineWidth="1.0"/>                </box>                <textElement textAlignment="Right" verticalAlignment="Bottom">                    <font size="12" isBold="true"/>                </textElement>                <text><![CDATA[Patients]]></text>            </staticText>            <staticText>                <reportElement stretchType="RelativeToBandHeight" x="350" y="96" width="125" height="40"/>                <box bottomPadding="2" rightPadding="2">                    <topPen lineWidth="1.0"/>                    <leftPen lineWidth="1.0"/>                    <bottomPen lineWidth="1.0"/>                    <rightPen lineWidth="1.0"/>                </box>                <textElement textAlignment="Right" verticalAlignment="Bottom">                    <font size="12" isBold="true"/>                </textElement>                <text><![CDATA[Questionnaires]]></text>            </staticText>            <staticText>                <reportElement stretchType="RelativeToBandHeight" x="475" y="96" width="90" height="40"/>                <box bottomPadding="2" rightPadding="2">                    <topPen lineWidth="1.0"/>                    <leftPen lineWidth="1.0"/>                    <bottomPen lineWidth="1.0"/>                    <rightPen lineWidth="1.0"/>                </box>                <textElement textAlignment="Right" verticalAlignment="Bottom">                    <font size="12" isBold="true"/>                </textElement>                <text><![CDATA[Patients]]></text>            </staticText>        </band>    </noData></jasperReport>[/code]

[ktalarico]

Hmmm, when I uploaded to the server, I did not get null.  It only happens when I run directly from iReport/JasperSoft Studio.