[SPRAND]

Hi,
I have a requirement, on which I have to work very urgently.

I have a Report. The report has a table. The table has 10 columns. The report has 3 input Report parameters,
based on which the report runs. I have provided the implementation so far. I am using the Scripted datasource
My requirement is, once the report is rendered, The user should be able to sort the report by clicking on the
table columns. So far, I have implemented the full report except the sorting functionality.

Can any one please tell me how to implement the dynamic sorting functionality in BIRT Report tables..?

Thanks in advance....

--Sunil

[mwilliams]

The way to do this without using the Interactive Viewer, from the commercial version, will be to create a drill through on the header label of your column. When you click on this label, you'll pass a value through a hidden report parameter, in your report that you'll use to sort your table based on this field. Let me know if you have questions.

[SPRAND]

Can you please elaborate the solution?

How to give label value to report parameter?
I can give my rptdesign file here.

This report takes 'project' as input, then runs the report.
It has 2 report parameters(project, sortkey(hidden, here is where I want the column label to come))
I am using scripted datasource.

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.21" id="1">
<property name="comments">Copyright © 2011 Vue Technology</property>
<property name="createdBy">Eclipse BIRT Designer Version 2.6.0.v20100531 Build &lt;2.6.0.v20100609-1613></property>
<html-property name="description">Emp Report</html-property>
<simple-property-list name="includeResource">
<value>resources</value>
</simple-property-list>
<property name="units">in</property>
<property name="theme">vue_library.defaultTheme</property>
<text-property name="displayName">Emp Report</text-property>
<property name="iconFile">/templates/blank_report.gif</property>
<property name="layoutPreference">auto layout</property>
<list-property name="libraries">
<structure>
<property name="fileName">vue_library.rptlibrary</property>
<property name="namespace">vue_library</property>
</structure>
</list-property>
<parameters>
<scalar-parameter name="project" id="1215">
<text-property name="promptText">Enter Project</text-property>
<property name="valueType">static</property>
<property name="dataType">string</property>
<property name="distinct">true</property>
<property name="paramType">simple</property>
<property name="controlType">text-box</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
</scalar-parameter>
<scalar-parameter name="sortkey" id="1217">
<property name="hidden">true</property>
<property name="valueType">static</property>
<property name="isRequired">false</property>
<property name="dataType">string</property>
<property name="distinct">true</property>
<property name="paramType">simple</property>
<property name="controlType">text-box</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
</scalar-parameter>
</parameters>
<data-sources>
<script-data-source name="Data Source" id="1210"/>
</data-sources>
<data-sets>
<script-data-set name="Data Set" id="1211">
<list-property name="resultSetHints">
<structure>
<property name="position">0</property>
<property name="name">id</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">1</property>
<property name="name">name</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">project</property>
<property name="dataType">string</property>
</structure>
</list-property>
<list-property name="columnHints">
<structure>
<property name="columnName">id</property>
</structure>
<structure>
<property name="columnName">name</property>
</structure>
<structure>
<property name="columnName">project</property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">id</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">name</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">project</property>
<property name="dataType">string</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source</property>
<method name="open"><![CDATA[readerClass = new Packages.com.tyco.trafint.data.EmpDataSource();
data = readerClass.getReportData(params["project"].value);
totalrows = data.size();
currentrow = 0;]]></method>
<method name="fetch"><![CDATA[if( currentrow >= totalrows )
{
return( false );
}
var dataRow = data.get(currentrow);

row["id"]=dataRow[0];
row["name"]=dataRow[1];
row["project"]=dataRow[2];

currentrow = currentrow + 1;
return ( true );]]></method>
</script-data-set>
</data-sets>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<property name="orientation">landscape</property>
<property name="topMargin">0.5in</property>
<property name="leftMargin">0.5in</property>
<property name="bottomMargin">0.5in</property>
<property name="rightMargin">0.3in</property>
<property name="headerHeight">0in</property>
<property name="footerHeight">0in</property>
</simple-master-page>
</page-setup>
<body>
<table name="reportTable" id="8">
<list-property name="userProperties">
<structure>
<property name="name">ShowChartsOnlyOnFirstPage</property>
<property name="type">string</property>
</structure>
<structure>
<property name="name">DrillDownReports</property>
<property name="type">string</property>
</structure>
<structure>
<property name="name">ReportTableHeaderRowIndex</property>
<property name="type">integer</property>
</structure>
</list-property>
<property name="width">100%</property>
<property name="dataSet">Data Set</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">id</property>
<expression name="expression" type="javascript">dataSetRow["id"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">name</property>
<expression name="expression" type="javascript">dataSetRow["name"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">project</property>
<expression name="expression" type="javascript">dataSetRow["project"]</expression>
<property name="dataType">string</property>
</structure>
</list-property>
<property name="pageBreakInterval">50</property>
<column id="1136">
<property name="width">33%</property>
</column>
<column id="1155">
<property name="width">33%</property>
</column>
<column id="1184">
<property name="width">34%</property>
</column>
<header>
<row id="145">
<cell id="146">
<property name="colSpan">3</property>
<property name="rowSpan">1</property>
<grid id="151">
<property name="width">100%</property>
<column id="152"/>
<column id="153"/>
<row id="154">
<cell id="155">
<text id="1209">
<property name="fontFamily">"Arial"</property>
<property name="fontSize">12pt</property>
<property name="fontWeight">bold</property>
<property name="color">#800000</property>
<property name="contentType">html</property>
<text-property name="content"><![CDATA[<center>This report is used to test Column Sorting in BIRT</center>
<BR>]]></text-property>
</text>
</cell>
<cell id="157"/>
</row>
</grid>
</cell>
</row>
<row id="1205">
<property name="repeatable">false</property>
<cell id="1206">
<property name="colSpan">3</property>
<property name="rowSpan">1</property>
<property name="paddingTop">1pt</property>
<property name="paddingLeft">1pt</property>
<property name="paddingBottom">1pt</property>
<property name="paddingRight">1pt</property>
<property name="textAlign">center</property>
<text-data id="1216">
<property name="fontFamily">"Arial"</property>
<property name="color">#808080</property>
<expression name="valueExpr">"Search Results for: Project: "+params["project"].value</expression>
<property name="contentType">html</property>
</text-data>
</cell>
</row>
<row id="9">
<cell id="1133">
<property name="style">vue-table-header</property>
<label id="1185">
<text-property name="text">ID</text-property>
</label>
</cell>
<cell id="1152">
<property name="style">vue-table-header</property>
<label id="1186">
<text-property name="text">Name</text-property>
</label>
</cell>
<cell id="1181">
<property name="style">vue-table-header</property>
<label id="1187">
<text-property name="text">Project</text-property>
</label>
</cell>
</row>
</header>
<detail>
<row id="32">
<list-property name="highlightRules">
<structure>
<property name="operator">eq</property>
<property name="backgroundColor">#F5F5E8</property>
<expression name="testExpr">row["__rownum"]%2</expression>
<simple-property-list name="value1">
<value>0</value>
</simple-property-list>
</structure>
</list-property>
<cell id="1134">
<data id="1212">
<property name="whiteSpace">nowrap</property>
<property name="resultSetColumn">id</property>
</data>
</cell>
<cell id="1153">
<data id="1213">
<property name="whiteSpace">nowrap</property>
<property name="resultSetColumn">name</property>
</data>
</cell>
<cell id="1182">
<data id="1214">
<property name="whiteSpace">nowrap</property>
<property name="resultSetColumn">project</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="55">
<cell id="1135"/>
<cell id="1154"/>
<cell id="1183"/>
</row>
</footer>
</table>
</body>
</report>


Above is my report. I have 3 columns in my table. They are ID, Name, Project.

As you said to create a drill-through on the header label of the column, I can do the below code for that.
(This code is not there in the above report).

<cell id="14">
<label id="15">
<text-property name="text">ID</text-property>
<list-property name="action">
<structure>
<property name="linkType">drill-through</property>
<property name="reportName">employee.rptdesign</property>
<property name="targetWindow">_self</property>
<property name="targetFileType">report-design</property>
</structure>
</list-property>
</label>
</cell>
<cell id="14">
<label id="15">
<text-property name="text">Name</text-property>
<list-property name="action">
<structure>
<property name="linkType">drill-through</property>
<property name="reportName">employee.rptdesign</property>
<property name="targetWindow">_self</property>
<property name="targetFileType">report-design</property>
</structure>
</list-property>
</label>
</cell>
<cell id="14">
<label id="15">
<text-property name="text">Project</text-property>
<list-property name="action">
<structure>
<property name="linkType">drill-through</property>
<property name="reportName">employee.rptdesign</property>
<property name="targetWindow">_self</property>
<property name="targetFileType">report-design</property>
</structure>
</list-property>
</label>
</cell>

But how to write code to pass the column label to the report parameter?

Thanks in advance....

--Sunil

[mwilliams]

Take a look at this report. A project report parameter is used to filter the dataSet. A hidden sort key parameter is used to set the sorting on the table. Depending on which label you click on, a different value is passed to the hidden parameter to sort the table by a different field. Let me know if you have questions.

Attached File(s)

•  DynamicSort.rptdesign (13.97K)
  Number of downloads: 26

[SPRAND]

can you please suggest the solution?

My Report has input report parameter, based on which my report runs.

When I click on the column header, the report has to be re-run with sort key(column name).

I am using scripted datasource.

I have seen one example report on column sorting on the web. But that report is using JDBC datasource and it doesn't have any input report parameters. It has a plain SQL Query. Every time a click on the column label, that will be appended to the order by clause in the query. This example is not solving my problem.

In my requirement, every time there is a click on my column, there should be a way to pass the column label to the method, which I have defined in the data source class and also the report parameter to be passed.

Thanks in advance.

[mwilliams]

In the example I just attached (35 minutes ago), the column name and the Project are both passed to the report with report parameters, so they would both be available for you to pass to you method. Take a look at the report parameters in the report and look at the hyperlinks on the labels, where the values are passed.

[SPRAND]

Thanks for the solution.

It worked fine when I run the report(click on column headers) in Eclipse BIRT Viewer.

But, It failed when I run(click on column headers) from JBoss 4.2.3 server.

I got the following error when I try to sort the columns(click on the column headers)

There is no report design object available.
org.eclipse.birt.report.exception.ViewerException: There is no report design object available.

at org.eclipse.birt.report.context.ViewerAttributeBean.__init(ViewerAttributeBean.java:226)

at org.eclipse.birt.report.context.BaseAttributeBean.init(BaseAttributeBean.java:233)

at org.eclipse.birt.report.context.ViewerAttributeBean.<init>(ViewerAttributeBean.java:118)

at org.eclipse.birt.report.context.BirtContext.__init(BirtContext.java:44)

at org.eclipse.birt.report.context.BaseContext.<init>(BaseContext.java:69)

at org.eclipse.birt.report.context.BirtContext.<init>(BirtContext.java:30)

at org.eclipse.birt.report.servlet.ViewerServlet.__getContext(ViewerServlet.java:150)

at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doGet(BirtSoapMessageDispatcherServlet.java:151)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)

at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.eclipse.birt.report.filter.ViewerFilter.doFilter(ViewerFilter.java:68)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)

at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:182)

at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)

at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)

at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)

at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)

at java.lang.Thread.run(Thread.java:722)

[mwilliams]

So, it runs the first time, but then, doesn't run when you click on a header? Do you place your reports in a folder, when you deploy? If so, you'll need to handle that in your design. If you put your reports into a "reports" folder, in your deployment, it's best to create that folder in your workspace and place all your report in there, so there are no differences between your design and deployment setup.

[SPRAND]

When I deployed it in Tomcat 7, It is working fine.

In JBoss, it is still not working.

Thank you very much for your help.

This approach works in only single direction(ascending order).

Can you please tell me the approach on how to perform sort in both directions(ascending, descending)?
I mean, the sort direction should keep on changing for every click on the column header.

Thanks in advance.

[mwilliams]

I have JBoss 5.0.1 and I didn't have an issue on there. That's why I figured it must be a file path issue. If it runs the first time, but not on a click of a label, it's probably most definitely that. Instead of using drill through, you could use URI and build your URL to link to the report again, with the new parameters. I'll take a look at the report and get the asc/desc set up. I'll post the example in here when finished.

[mwilliams]

Sorry for the delay. This report handles the Ascending vs Descending issue. Hope it helps.

Attached File(s)

•  DynamicSort.rptdesign (15.89K)
  Number of downloads: 31