CA Service Management

Hi Team,

Please help me for below query in jaspersoft, below query working on direct DB connection

Select group_id,(Select Count(ref_num) from call_req as a where status = 'CL')as C1,(Select Count(ref_num)from call_req as b
where status = 'CLREQ')as C2 from call_req as o group by group_id

But its not working when running from query builder in jasper studio, i am using in below form 

Please help.

Hi Team,

Any help would be must appreciated, please suggest. 

Below query works using SDM JDBC driver.

Select group_name,
sum(case when status = 'CL' then 1 else 0 end) as SumCL,
sum(case when status = 'CLREQ' then 1 else 0 end) as SumCLREQ
from cr
group by group_name

If you need to connect to MDB directly you should be able to modify above SQL to use correct table/column names.

Thanks SteveTroy, it was really helpful. excuse me for late response.

In addition to above, can you please give me high level idea, how i can show percentage of resolved call or closed calls in next column with the help of jasperstudio, not using sql query builder, any idea.

As of now through query builder, i have total of all call (SumAll), total of Close Call (SumCL), Total of Resolved Call (SumCLREQ), now i want to show (SumCLREQ/SumAll) in another field of report.

Please help.

Thanks.

Currently i am using below expression on text field, 

</textField>
<textField evaluationTime="Report" pattern="#,##0.##%" isBlankWhenNull="false">
<reportElement x="309" y="0" width="70" height="30" uuid="27b62aa8-5686-4074-a742-38668ddcb408"/>
<textElement textAlignment="Center"/>
<textFieldExpression><![CDATA[$F{SumCLREQ}/$F{SumAll}]]></textFieldExpression>
</textField>

but its showing only 0% in all rows.

Please help.

Thanks

Any idea from anyone , please suggest.

Try evaluationTime="Now"

If that doesn't work attach your jrxml and I can take a look

Thanks SteveTroy

Please check below report 

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.0.final using JasperReports Library version 6.3.0 -->
<!-- 2018-08-23T01:46:04 -->
<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="CowiseCount" pageWidth="880" pageHeight="842" columnWidth="840" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="8b5661c7-861b-4f58-af60-df55da34b00d">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="sdm_ds"/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/"/>
<property name="ireport.jasperserver.user" value="superuser"/>
<property name="ireport.jasperserver.report.resource" value="/organizations/servicemanagement/capability/reports/CA_Service_Desk/CireCle_Wise_Report/COWise.jrxml_files/CowiseCount.jrxml"/>
<property name="ireport.jasperserver.reportUnit" value="/organizations/servicemanagement/capability/reports/CA_Service_Desk/CireCle_Wise_Report/COWise.jrxml"/>
<property name="com.jaspersoft.studio.unit." value="pixel"/>
<property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/>
<property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/>
<property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/>
<property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/>
<property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/>
<property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/>
<property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/>
<property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/>
<parameter name="p_finalstartdate" class="java.lang.String"/>
<parameter name="p_finalenddate" class="java.lang.String">
<parameterDescription><![CDATA[]]></parameterDescription>
</parameter>
<queryString>
<![CDATA[Select test, Count (ref_num) as SumAL,
sum(case when active = 1 and (group_name LIKE 'NE_%' or group_name IS NULL) then 1 else 0 end) as SumCO,
sum(case when active = 0 and (group_name LIKE 'NE_%' or group_name IS NULL) then 1 else 0 end) as SumCORES,
sum(case when active = 1 and group_name LIKE 'ZE_%'then 1 else 0 end) as SumZO,
sum(case when active = 0 and group_name LIKE 'ZE_%' then 1 else 0 end) as SumZORES,
sum(case when active = 1 and (group_name NOT LIKE 'NE_%' or group_name NOT LIKE 'ZE_%') then 1 else 0 end) as SumHO,
sum(case when active = 0 and (group_name NOT LIKE 'NE_%' or group_name NOT LIKE 'ZE_%') then 1 else 0 end) as SumHORES
from cr
WHERE type = 'I' and open_date >= $P!{p_finalstartdate} and open_date <= $P!{p_finalenddate} and test IS NOT NULL
group by test]]>
</queryString>
<field name="test" class="java.lang.String"/>
<field name="SumAL" class="java.lang.Integer"/>
<field name="SumCO" class="java.lang.Integer"/>
<field name="SumCORES" class="java.lang.Integer"/>
<field name="SumZO" class="java.lang.Integer"/>
<field name="SumZORES" class="java.lang.Integer"/>
<field name="SumHO" class="java.lang.Integer"/>
<field name="SumHORES" class="java.lang.Integer"/>
<background>
<band splitType="Stretch"/>
</background>
<columnHeader>
<band height="15" splitType="Stretch">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.FreeLayout"/>
<staticText>
<reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="0" y="-19" width="71" height="29" forecolor="#FFFFFF" backcolor="#0F52BA" uuid="f0c7675a-810f-49c7-a7e7-8b40c95f067d"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Test]]></text>
</staticText>
<staticText>
<reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="529" y="-19" width="100" height="29" forecolor="#FFFFFF" backcolor="#0F52BA" uuid="961e3c60-b8a1-4e17-85d8-55d340949ccd"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[T6]]></text>
</staticText>
<staticText>
<reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="71" y="-19" width="78" height="29" forecolor="#FFFFFF" backcolor="#0F52BA" uuid="f04f089f-ea80-4ea2-b532-c9fd9b890023"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Total Calls Opened between]]></text>
</staticText>
<staticText>
<reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="229" y="-19" width="80" height="29" forecolor="#FFFFFF" backcolor="#0F52BA" uuid="c741c2a6-1bd2-433e-89c6-77bb440ea82e"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[T2]]></text>
</staticText>
<staticText>
<reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="149" y="-19" width="80" height="29" forecolor="#FFFFFF" backcolor="#0F52BA" uuid="e4d357b5-a4d1-4380-afef-67b753605915"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[T1]]></text>
</staticText>
<staticText>
<reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="309" y="-19" width="60" height="29" forecolor="#FFFFFF" backcolor="#0F52BA" uuid="0b8c38e7-ba75-44b7-9462-bf09b36cabf5"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[T3]]></text>
</staticText>
<staticText>
<reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="369" y="-19" width="100" height="29" forecolor="#FFFFFF" backcolor="#0F52BA" uuid="f705eb96-90b4-41e8-9064-a663dbb99433"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[T4]]></text>
</staticText>
<staticText>
<reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="469" y="-19" width="60" height="29" forecolor="#FFFFFF" backcolor="#0F52BA" uuid="36993c66-01ea-4a33-93cf-3312f2be5ab6"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[T5]]></text>
</staticText>
<staticText>
<reportElement stretchType="RelativeToBandHeight" mode="Opaque" x="630" y="-19" width="100" height="29" forecolor="#FFFFFF" backcolor="#0F52BA" uuid="9c74b46d-79fe-4856-8dec-f6a79abed7da"/>
<box>
<topPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
<rightPen lineWidth="1.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="10" isBold="true"/>
</textElement>
<text><![CDATA[Percent1]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="45" splitType="Stretch">
<textField>
<reportElement key="" x="5" y="0" width="71" height="30" uuid="20e11c0a-3998-4587-85d9-ae3edd6cafb0"/>
<textFieldExpression><![CDATA[$F{test}]]></textFieldExpression>
</textField>
<textField>
<reportElement key="" x="81" y="0" width="68" height="30" uuid="b813313a-826f-4953-98f8-a906b57db06d"/>
<textFieldExpression><![CDATA[$F{SumAL}]]></textFieldExpression>
</textField>
<textField>
<reportElement key="" x="156" y="0" width="73" height="30" uuid="66dccb94-66f5-4b69-b2f3-3371c60fc511"/>
<textFieldExpression><![CDATA[$F{SumCO}]]></textFieldExpression>
</textField>
<textField>
<reportElement key="" x="231" y="0" width="78" height="30" uuid="7313fa67-278d-4783-9323-2ab5f124583d"/>
<textFieldExpression><![CDATA[$F{SumCORES}]]></textFieldExpression>
</textField>
<textField>
<reportElement key="" x="314" y="0" width="55" height="30" uuid="34c7f68f-fcb1-4716-88cb-d4ba4cd0f8c4"/>
<textFieldExpression><![CDATA[$F{SumZO}]]></textFieldExpression>
</textField>
<textField>
<reportElement key="" x="380" y="0" width="69" height="30" uuid="e2be9516-b6a2-491d-80f5-673464416b85"/>
<textFieldExpression><![CDATA[$F{SumZORES}]]></textFieldExpression>
</textField>
<textField>
<reportElement key="" x="469" y="0" width="60" height="30" uuid="ab24a8d0-88a0-4b16-b5d9-ad900498e478"/>
<textFieldExpression><![CDATA[$F{SumHO}]]></textFieldExpression>
</textField>
<textField>
<reportElement key="" x="532" y="0" width="94" height="30" uuid="2d813afa-b0eb-41f7-acc7-808caf467d44"/>
<textFieldExpression><![CDATA[$F{SumHORES}]]></textFieldExpression>
</textField>
<textField pattern="#,##0.##%">
<reportElement x="640" y="0" width="100" height="30" uuid="9d78237f-3ec7-412f-a6c3-681955f332d0"/>
<textFieldExpression><![CDATA[$F{SumCORES}/$F{SumAL}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>

Tried with evaluationTime="Now", but still it is not working.

change the class of your field names to java.lang.Double instead of java.lang.Integer 

<field name="SumAL" class="java.lang.Double"/>
<field name="SumCO" class="java.lang.Double"/>
<field name="SumCORES" class="java.lang.Double"/>
<field name="SumZO" class="java.lang.Double"/>
<field name="SumZORES" class="java.lang.Double"/>
<field name="SumHO" class="java.lang.Double"/>
<field name="SumHORES" class="java.lang.Long"/>

Thanks SteveTroy , let me try and get back to you.

Thanks SteveTroy, its working now. Appreciate your prompt support.