Interactive Sorting and Dataset Sorting

[velayutham.palaniappan]

I have a Jaspersoft report built using Jaspersoft Studio. The report has a table which reads data from a SQL dataset. To sort the data, I have used the "Sort Field" option in the Dataset.

When I run the report from the server, it allows me to interactively sort the table by clicking on the column names. The data is first sorted based on the "Sort Field" and only on top of it the "Interactive Sorting" is working. I would want to override the "Sort Fields" by "Interactive Sort". is there a way to do it?

[reportdev]

Try this way: 

Create a boolean parameter in the report and send in Order by clause using that boolean.

If the boolean is set to true, then it will allow custom sort order. If set to false, then it will do sort from table element.

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.4.2.final using JasperReports Library version 6.4.1  --><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="customSort"              pageWidth="842" pageHeight="595" orientation="Landscape"              columnWidth="802"              leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"              uuid="0e689ce3-d367-4825-985c-78a782d64436">    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>    <style name="Table_TH" mode="Opaque" backcolor="#F0F8FF">        <box>            <pen       lineWidth="0.5" lineColor="#000000"/>            <topPen    lineWidth="0.5" lineColor="#000000"/>            <leftPen   lineWidth="0.5" lineColor="#000000"/>            <bottomPen lineWidth="0.5" lineColor="#000000"/>            <rightPen  lineWidth="0.5" lineColor="#000000"/>        </box>    </style>    <style name="Table_CH" mode="Opaque" backcolor="#BFE1FF">        <box>            <pen       lineWidth="0.5" lineColor="#000000"/>            <topPen    lineWidth="0.5" lineColor="#000000"/>            <leftPen   lineWidth="0.5" lineColor="#000000"/>            <bottomPen lineWidth="0.5" lineColor="#000000"/>            <rightPen  lineWidth="0.5" lineColor="#000000"/>        </box>    </style>    <style name="Table_TD" mode="Opaque" backcolor="#FFFFFF">        <box>            <pen       lineWidth="0.5" lineColor="#000000"/>            <topPen    lineWidth="0.5" lineColor="#000000"/>            <leftPen   lineWidth="0.5" lineColor="#000000"/>            <bottomPen lineWidth="0.5" lineColor="#000000"/>            <rightPen  lineWidth="0.5" lineColor="#000000"/>        </box>    </style>    <subDataset name="newData" uuid="a11458d4-7475-4b3a-9ba0-21a7bb4cd1b1">        <parameter name="customSort" class="java.lang.Boolean"/>        <parameter name="sortOrder" class="java.lang.String">            <defaultValueExpression>                <![CDATA[$P{customSort} ?  " Order by business_date desc" : " "]]>            </defaultValueExpression>        </parameter>        <queryString language="SQL">            <![CDATA[select * from (select  trunc(sysdate+1) + level -1 as business_date, ('ID'|| level ) as IDfrom    dualconnect by sysdate + level -1 < sysdate + 30 )$P!{sortOrder}]]>        </queryString>        <field name="BUSINESS_DATE" class="java.sql.Timestamp"/>        <field name="ID" class="java.lang.String"/>    </subDataset>    <parameter name="customSort" class="java.lang.Boolean"/>    <queryString>        <![CDATA[select * from dual]]>    </queryString>    <field name="DUMMY" class="java.lang.String"/>    <background>        <band splitType="Stretch"/>    </background>    <summary>        <band height="40" splitType="Stretch">            <componentElement>                <reportElement x="0" y="0" width="802" height="40"                               uuid="808644a4-e0b1-4e78-a553-4502e987c8f8">                    <property name="com.jaspersoft.studio.layout"                              value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>                    <property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/>                    <property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/>                    <property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/>                </reportElement>                <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components"                          xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">                    <datasetRun subDataset="newData" uuid="5c268e98-d70c-4264-83a0-c645fa1a07f5">                        <datasetParameter name="customSort">                            <datasetParameterExpression>                                <![CDATA[$P{customSort}]]>                            </datasetParameterExpression>                        </datasetParameter>                        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>                    </datasetRun>                    <jr:column width="180" uuid="96299c1f-63e8-4dc4-a191-7138dd8d9860">                        <property name="com.jaspersoft.studio.unit.width"  value="pixel"/>                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                        <jr:columnHeader style="Table_CH" height="20">                            <property name="com.jaspersoft.studio.unit.width"  value="px"/>                            <property name="com.jaspersoft.studio.unit.height" value="px"/>                            <staticText>                                <reportElement x="0" y="0" width="180" height="20"                                               uuid="0be05bd5-da0d-41fe-b829-96eb3bf62100"/>                                <text><![CDATA[bUSINESS_DATE]]></text>                            </staticText>                        </jr:columnHeader>                        <jr:detailCell style="Table_TD" height="20">                            <property name="com.jaspersoft.studio.unit.height" value="px"/>                            <textField>                                <reportElement x="0" y="0" width="180" height="20"                                               uuid="edcf3142-7ea3-4d5b-9480-942ac1ddf374"/>                                <textFieldExpression>                                    <![CDATA[$F{BUSINESS_DATE}]]>                                </textFieldExpression>                            </textField>                        </jr:detailCell>                    </jr:column>                    <jr:column width="100" uuid="5c9e03a3-fbd7-4808-8c1c-d0705ddfb5ae">                        <jr:columnHeader style="Table_CH" height="20">                            <property name="com.jaspersoft.studio.unit.height" value="px"/>                            <staticText>                                <reportElement x="0" y="0" width="100" height="20"                                               uuid="80a6ca5c-c77c-4b00-a610-3925829ce71e"/>                                <text><![CDATA[iD]]></text>                            </staticText>                        </jr:columnHeader>                        <jr:detailCell style="Table_TD" height="20">                            <property name="com.jaspersoft.studio.unit.height" value="px"/>                            <textField>                                <reportElement x="0" y="0" width="100" height="20"                                               uuid="466c1b18-f818-4d02-ad23-5868a3a79a99"/>                                <textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>                            </textField>                        </jr:detailCell>                    </jr:column>                </jr:table>            </componentElement>        </band>    </summary></jasperReport>[/code]

 

[mlocklin]

I'm getting my data from a jasper parameter I passed in along with an ordered list of sortfields. I have my column headers in the main report, but my data in a sub report.

Is there a way to transform my headers into links that toggle ascending/descending of the sortfields and the order?