Need help on dd-Mon-yyyy- Jasper studio

anisur.76 · October 5, 2017

Dear Jasper Report experts,

I need to set hard code DD-MON-YYYY in report report for DatePicker but my actual data in DD-MON-YYYY hh24:mi:ss - just need to trim hh24:mi:ss in jrxml file and run report.How to do this ? my sql command tested on sql developer below which works and like to place this query in jasper report/jrxml file not sure how? how to twick paramDate?

alter session set nls_date_format = 'DD-MON-YYYY';

SELECT * from TRHH where trunc(REQUEST_DATE)=to_date('24-AUG-2017','dd-Mon-yyyy')

<?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="3 HOST REF Count By Status." pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isFloatColumnFooter="true" uuid="08493364-0f31-43a9-880e-d7774a013b75">
 <property name="com.jaspersoft.studio.data.defaultdataadapter" value="mbsource"/>
 <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
 <property name="com.jaspersoft.studio.property.dataset.dialog.DatasetDialog.sash.w1" value="483"/>
 <property name="com.jaspersoft.studio.property.dataset.dialog.DatasetDialog.sash.w2" value="503"/>
 <property name="ireport.jasperserver.url" value="http://localhost:6080/jasperserver/"/>
 <property name="ireport.jasperserver.user" value="jasperadmin"/>
 <property name="ireport.jasperserver.report.resource" value="/BMREPORT/IB_HOST_REF_Count_By_Status_files/main_jrxml"/>
 <property name="ireport.jasperserver.reportUnit" value="/BMREPORT/IB_HOST_REF_Count_By_Status"/>
 <parameter name="paramDate" class="java.util.Date">
 <defaultValueExpression><![CDATA["2017-02-28"]]></defaultValueExpression>
 </parameter>
 <queryString>
 <![CDATA[sELECT

COALESCE(TRHH.TXN_STATUS,CAST('NO_STATUS' AS NVARCHAR2(50))) AS txnstatus,
count(nvl(TRHH.HOST_REF_NO,0)) AS refcount
FROM TRHH
LEFT JOIN TRHE ON TRHH.REQ_ID=TRHE.REQ_ID
WHERE TRHH.REQUEST_DATE=$P{paramDate}

GROUP BY TRHH.TXN_STATUS]]>
 </queryString>
 <field name="txnstatus" class="java.lang.String"/>
 <field name="refcount" class="java.lang.Long"/>
 <variable name="var_refcount " class="java.lang.Integer" resetType="Column" calculation="Sum">
 <variableExpression><![CDATA[$F{refcount}]]></variableExpression>
 </variable>
 <background>
 <band splitType="Stretch"/>
 </background>
 <title>
 <band height="387" splitType="Stretch">
 <textField>
 <reportElement x="180" y="0" width="375" height="82" forecolor="#FF0000" uuid="b20feb46-6af0-433d-a5f2-e099aeb8a79f"/>
 <box leftPadding="20"/>
 <textElement textAlignment="Left" verticalAlignment="Middle">
 
 </textElement>
 <textFieldExpression><![CDATA["HOST REF Count By Status"]]></textFieldExpression>
 </textField>
 <textField isStretchWithOverflow="true" pattern="MMMM d, yyyy" isBlankWhenNull="true">
 <reportElement x="360" y="60" width="189" height="30" uuid="47ec5089-99fe-4167-838c-f55e6872234c"/>
 <textElement verticalAlignment="Middle">
 
 </textElement>
 <textFieldExpression><![CDATA[$P{paramDate}]]></textFieldExpression>
 </textField>
 <textField isStretchWithOverflow="true" isBlankWhenNull="true">
 <reportElement x="180" y="60" width="184" height="30" uuid="523c3b5c-a5bd-421b-bb46-13de2a41b1ec">
 <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
 <property name="com.jaspersoft.studio.unit.width" value="pixel"/>
 </reportElement>
 <box leftPadding="20"/>
 <textElement textAlignment="Left" verticalAlignment="Middle">
 
 </textElement>
 <textFieldExpression><![CDATA["Transaction Request Date:"]]></textFieldExpression>
 </textField>
 <image>
 <reportElement x="0" y="0" width="180" height="112" uuid="6655d209-7311-4780-ae36-88ea949ae5eb"/>
 <imageExpression><![CDATA["repo:/T24_To_IB/Images/ABC.jpg"]]></imageExpression>
 </image>
 <barChart>
 <chart isShowLegend="false" evaluationTime="Report">
 <reportElement x="0" y="130" width="555" height="238" uuid="89287e42-9b99-4638-a5d1-29d73ea103b7"/>
 <chartTitle/>
 <chartSubtitle/>
 <chartLegend/>
 </chart>
 <categoryDataset>
 <categorySeries>
 <seriesExpression><![CDATA["TXN Status"]]></seriesExpression>
 <categoryExpression><![CDATA[$F{txnstatus}]]></categoryExpression>
 <valueExpression><![CDATA[$F{refcount}]]></valueExpression>
 <itemHyperlink hyperlinkType="ReportExecution" hyperlinkTarget="Blank">
 <hyperlinkParameter name="_report">
 <hyperlinkParameterExpression><![CDATA["/T24_To_IB/Resources/Drill_Down_Detailed_Reports/IB_Host_Ref_Count_By_Status_Detail_Report"]]></hyperlinkParameterExpression>
 </hyperlinkParameter>
 <hyperlinkParameter name="paramDate">
 <hyperlinkParameterExpression><![CDATA[$P{paramDate}]]></hyperlinkParameterExpression>
 </hyperlinkParameter>
 <hyperlinkParameter name="paramStatus">
 <hyperlinkParameterExpression><![CDATA[$F{txnstatus}]]></hyperlinkParameterExpression>
 </hyperlinkParameter>
 </itemHyperlink>
 </categorySeries>
 </categoryDataset>
 <barPlot>
 <plot>
 <seriesColor seriesOrder="0" color="#FF0000"/>
 </plot>
 <itemLabel/>
 <categoryAxisLabelExpression><![CDATA["Transaction Status"]]></categoryAxisLabelExpression>
 <categoryAxisFormat>
 <axisFormat labelColor="#000000" tickLabelColor="#000000" axisLineColor="#000000">
 <labelFont>
 
 </labelFont>
 <tickLabelFont>
 
 </tickLabelFont>
 </axisFormat>
 </categoryAxisFormat>
 <valueAxisLabelExpression><![CDATA["HOST REF Count"]]></valueAxisLabelExpression>
 <valueAxisFormat>
 <axisFormat labelColor="#000000" tickLabelColor="#000000" axisLineColor="#000000">
 <labelFont>
 
 </labelFont>
 </axisFormat>
 </valueAxisFormat>
 </barPlot>
 </barChart>
 </band>
 </title>
 <columnHeader>
 <band height="25" splitType="Stretch">
 <staticText>
 <reportElement mode="Opaque" x="92" y="0" width="218" height="25" forecolor="#000000" backcolor="#BFE1FF" uuid="1bcffa68-285f-425d-b512-1d50aa38fc6b">
 <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
 </reportElement>
 <box leftPadding="10">
 <pen lineWidth="0.5" lineColor="#A6A1A1"/>
 </box>
 <textElement textAlignment="Left" verticalAlignment="Middle">
 
 </textElement>
 <text><![CDATA[TXN Status]]></text>
 </staticText>
 <staticText>
 <reportElement mode="Opaque" x="310" y="0" width="245" height="25" forecolor="#000000" backcolor="#BFE1FF" uuid="4c76398a-5bf9-43fa-b23d-bbfe23b429de"/>
 <box rightPadding="10">
 <pen lineWidth="0.5" lineColor="#A6A1A1"/>
 </box>
 <textElement textAlignment="Right" verticalAlignment="Middle">
 
 </textElement>
 <text><![CDATA[HOST REF Count]]></text>
 </staticText>
 <staticText>
 <reportElement mode="Opaque" x="1" y="0" width="91" height="25" forecolor="#000000" backcolor="#BFE1FF" uuid="f807fd7e-9e4b-4401-823b-708d20298162">
 <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
 </reportElement>
 <box leftPadding="10">
 <pen lineWidth="0.5" lineColor="#A6A1A1"/>
 </box>
 <textElement textAlignment="Left" verticalAlignment="Middle">
 
 </textElement>
 <text><![CDATA[s.NO]]></text>
 </staticText>
 </band>
 </columnHeader>
 <detail>
 <band height="25" splitType="Stretch">
 <textField isStretchWithOverflow="true" isBlankWhenNull="true">
 <reportElement stretchType="RelativeToTallestObject" x="92" y="0" width="218" height="25" forecolor="#000000" uuid="a9ca539a-0d5b-4529-af29-e630dadfe9a7">
 <property name="com.jaspersoft.studio.unit.y" value="pixel"/>
 <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
 </reportElement>
 <box leftPadding="10">
 <pen lineWidth="0.5" lineColor="#A6A1A1"/>
 </box>
 <textElement textAlignment="Left" verticalAlignment="Middle">
 
 </textElement>
 <textFieldExpression><![CDATA[$F{txnstatus}]]></textFieldExpression>
 </textField>
 <textField isStretchWithOverflow="true" isBlankWhenNull="true">
 <reportElement x="310" y="0" width="245" height="25" uuid="6b44e9b7-2991-495a-94ac-32b6db444bad"/>
 <box rightPadding="10">
 <pen lineWidth="0.5" lineColor="#A6A1A1"/>
 </box>
 <textElement textAlignment="Right" verticalAlignment="Middle">
 
 </textElement>
 <textFieldExpression><![CDATA[$F{refcount}]]></textFieldExpression>
 </textField>
 <textField>
 <reportElement x="0" y="0" width="92" height="25" uuid="421e4bb2-dacd-4c2d-b30a-599b7c586b56"/>
 <box>
 <pen lineWidth="0.5" lineColor="#A6A1A1"/>
 </box>
 <textElement textAlignment="Center" verticalAlignment="Middle">
 
 </textElement>
 <textFieldExpression><![CDATA[$V{REPORT_COUNT}]]></textFieldExpression>
 </textField>
 </band>
 </detail>
 <columnFooter>
 <band height="20" splitType="Stretch">
 <textField isStretchWithOverflow="true" isBlankWhenNull="true">
 <reportElement mode="Opaque" x="310" y="0" width="245" height="20" backcolor="#BFE1FF" uuid="9d5d2591-07b7-49d5-bd20-3bb0460849b8"/>
 <box rightPadding="10">
 <pen lineWidth="0.5" lineColor="#A6A1A1"/>
 </box>
 <textElement textAlignment="Right" verticalAlignment="Middle">
 
 </textElement>
 <textFieldExpression><![CDATA[$V{var_refcount }]]></textFieldExpression>
 </textField>
 <staticText>
 <reportElement mode="Opaque" x="0" y="0" width="310" height="20" backcolor="#BFE1FF" uuid="3179d00d-fec6-45d0-979b-e808df1ff638">
 <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
 </reportElement>
 <box leftPadding="10">
 <pen lineWidth="0.5" lineColor="#A6A1A1"/>
 </box>
 <textElement textAlignment="Center" verticalAlignment="Middle">
 
 </textElement>
 <text><![CDATA[Total REF Count]]></text>
 </staticText>
 </band>
 </columnFooter>
 <pageFooter>
 <band height="15" splitType="Stretch">
 <textField>
 <reportElement x="455" y="0" width="100" height="15" forecolor="#787575" uuid="15336332-68d9-4de0-aecd-7ec274a94c30"/>
 <box rightPadding="5"/>
 <textElement textAlignment="Right" verticalAlignment="Middle">
 
 </textElement>
 <textFieldExpression><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression>
 </textField>
 <textField pattern="MMMM d, yyyy">
 <reportElement x="220" y="0" width="230" height="15" forecolor="#787575" uuid="3db13342-7e04-4385-ace0-052df7803757"/>
 <textElement verticalAlignment="Middle">
 
 </textElement>
 <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
 </textField>
 <textField>
 <reportElement x="0" y="0" width="220" height="15" forecolor="#787575" uuid="8a282459-6ad8-4ea6-8a1b-16f9611f9f7a"/>
 <textElement verticalAlignment="Middle">
 
 </textElement>
 <textFieldExpression><![CDATA["Confidential @ abc Analytics"]]></textFieldExpression>
 </textField>
 </band>
 </pageFooter>
</jasperReport>

<?xml version="1.0" encoding="UTF-8"?>

anisur.76 · October 5, 2017

Look moderator to prioritize this call so any community member can help me faster. Regards

anisur.76 · October 5, 2017

Additional info

alter session set nls_date_format = 'DD-MON-YYYY';

SELECT req_id , request_date from TRHH where trunc(REQUEST_DATE)=to_date('24-AUG-2017','dd-Mon-yyyy')
sql output

req_id request_date
1670532   24-AUG-2017
1601550   24-AUG-2017
1669029   24-AUG-2017
1669039   24-AUG-2017
1670112   24-AUG-2017

<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>

alter session set nls_date_format = 'DD-MON-YYYY hh24:mi:ss';

SELECT req_id , request_date from TRHH where trunc(REQUEST_DATE)=to_date('24-AUG-2017','dd-Mon-yyyy')

sql output
req_id    request_date
1670532   24-AUG-2017 12:30:48
1601550   24-AUG-2017 12:01:05
1669029   24-AUG-2017 17:30:50
1669039   24-AUG-2017 09:39:58
1670112   24-AUG-2017 17:36:23

Peñarandix85 · October 9, 2017

Good morning,

In one of my reports i got date field as 10-09-2017 08:19:00 when executing the query but when i print it in jasper studio i use DATEFORMAT($F{date},"dd/MM/yyyy kk:mm") inside the text field.

Take a look here:

https://community.jaspersoft.com/wiki/date-and-time-patterns

Hope this helps!!

Sign In

Need help on dd-Mon-yyyy- Jasper studio

Recommended Posts

anisur.76

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

anisur.76

anisur.76

Peñarandix85

Create an account or sign in to comment

Create an account

Sign in

Activity

Products

Explore