Birt Report runtime column sorting, how to avoid multiple database calls

suhailmirs

<p>Hi,</p><p> </p><p>I am using below script on "<strong>initialize</strong>" of my report and on each column header I am setting <strong>Hyperlink </strong> location as "<strong>targetURL +"table_name.columnName</strong>";"</p><p> </p><p>The runtime column sorting is working fine however each time I click column header it makes database call again and fetch data from database. I am new to BIRT can anyone help me how to avoid multiple database calls on clicking column header to sort the data?</p><p> </p><p> </p><p><span style="font-size:12px;">dataSetName = "Data_Set_Name";
sortCol  = reportContext.getHttpServletRequest().getParameter("sortCol");
sortDir = reportContext.getHttpServletRequest().getParameter("sortDir");
currentURL = reportContext.getHttpServletRequest().getRequestURL() + "?" +
reportContext.getHttpServletRequest().getQueryString();
sortClause = "";
targetURL  = "";
abbrSortCol = "";

if (sortDir != null)
{
  if (sortDir.indexOf("ASC") != -1)
sortDir = "DESC";
  else
  sortDir = "ASC";
}

else
 sortDir = "ASC";

if (sortCol != null && sortCol.length != 0)
{
  sortClause = " ORDER BY " + sortCol + " " + sortDir;
 
 lastIndex = sortCol.lastIndexOf(".");
 
  if (lastIndex == -1)
  abbrSortCol = sortCol;
   
  else
  abbrSortCol = sortCol.substring(lastIndex + 1);
}

reportContext.getReportRunnable().getDesignInstance().
getDataSet(dataSetName).queryText += sortClause;
 
if (currentURL.indexOf("__sorting=") != -1 )
  targetURL = currentURL.substring(0, currentURL.indexOf("__sorting") - 1);
else
targetURL = currentURL;
 
targetURL = targetURL + "&__sorting=true&sortDir=" + sortDir + "&sortCol=";</span></p>

kclark

<p>Without using the commercial viewer I think your best bet might be to generate rptdocuments and use those as the data source.  It will still "query" the rptdocument, but it's a cached version of the data in your db.  Then you'd need to write a Java class to recreate the rptdocuments.  You could do it for every n times your report is viewed or have it based on time.</p>