ktalarico Posted February 16, 2015 Posted February 16, 2015 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 Posted February 16, 2015 Author Posted February 16, 2015 Hmmm, when I uploaded to the server, I did not get null. It only happens when I run directly from iReport/JasperSoft Studio.
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