Enable Sorting

Agreed. I'm trying to figure out how to make that happen in the meanime you can create sorting for your reports in the SQL by writing queries that look like:

SELECT * from table 
order by 
  case when :sort = 'col1' then column1
    when :sort = 'col2' then column2
  else create_date end 

THen you make the table headers links to the table with the different sort parameters. reprots/testreport?sort=title

Does this make sense?

Dave

The latest dev versions 7.x-2.x and 6.x-2.x support jquery dataTables plugin to provide javascript based resorting of data. Install jquery dataTables in your sites/all/libraries folder renaming the version specific extracted folder to dataTables so that you see a directory called sites/all/libraries/dataTables/media.

IF your report was named myreport, you could create a myreport.js file as follows:

function myreport() {
  jQuery('table').dataTable();
}

You can also create a report form js file and reference that to embed datatables in multiple reports.

More information on dataTables can be found at http://datatables.net

Hi Dave

Where should the myreport.js file be placed?

I could have datatables js included in report pages via some other non Forena mechanism; however, I'd prefer to keep it bundled consistently with Forena if such a mechanism exists.

For example I can throw the following in a block displayed on the report page :

<?php
   drupal_add_css('sites/all/libraries/datatables/media/css/jquery.dataTables.css', 'file');
   drupal_add_css('sites/all/libraries/datatables/media/css/jquery.dataTables_themeroller.css', 'file');

   drupal_add_js('sites/all/libraries/datatables/media/js/jquery.dataTables.min.js', 'file');
   
    $js = "(function ($) {
                   $(document).ready(function(){
                             $('#content_played_by_user_block > table').dataTable();
                   });
           })(jQuery); ";


     drupal_add_js($js, 'inline');
?>

It proves things are working; however, it's obviously less then ideal.

Thanks in advance.

The myreports.js file should live in the same directory and have the same name as the report (or report form) that you have created. Since writing this I sort of realized that you could (rather than using the myreports function I described) use drupal behaviors as described in:

http://drupal.org/node/171213

So if your report is named davesReallyCoolReport.frx, then you can create a davesReallyCoolReport.js file that contains much of the javascript that you need. Again, you should not be overriding document ready but rather be adding to Drupal Behaviors. In drupal 7 it would look like this.

(function ($) {
  
  // Register the drupal behaviors that get executed on document ready. 
  Drupal.behaviors.forenaReport = {
    attach: function (context, settings) {
       $('#content_played_by_user_block > table').dataTable();
    }
  }
) jQuery

Since you keep needing more and more added libraries. Let me recommend you creating a custom module or alternatively adding the css and js files into your custom theme. Adding js to the theme is described in document that describes the drupal behaviors concept.

If you do a custom module, you could implement hook_forena_parameters_alter to add the css files like this:

function example_forena_parameters_alter($report_name, &$parms) { 
  //drupal_add_css()  and drupal_add_js calls go here. 
}

Know that this is a temporary solution until I build javascript library loading into the the forena form mechanism. (I've just started work on a 7.3 version that will implement these. )

I'll try and roll a youtube video demonstrating this and maybe a sample report as well. Hope this helps.

Library loading code was implemented, and all dataTable plugins can now be used with forena. Youtube video has been published regarding skins and walks through the problem of implemengting datatables with forena.

Is there an frx option to add into the layout that might be able to sort the same data block in a different order?
Tihs would save having to create a new data block just to have a different order.

The two techniques for sorting data blocks are really described in this issue. You do not need to build a different data block, but you can create data blocks that conditionally sort data in one way or another. Some of that is hinted at in earlier comments in this issue, but there have been better tools developed since this issue was closed for doing so.

In particular, the newer SWITCH CASE statement allows you to write data blocks of the form:

select * from mytable
--SWITCH=:sort
--CASE=name
ORDER BY name
--CASE=price
ORDER BY PRICE
--ELSE
ORDER BY somecolumn
--END

Conditional SQL statements are pretty well documented in the forena 7.x-4 current beta.

Once you do this, adding conditional sorting is dependent on the data blocks.
Of course the easiest approach is still to use the dataTables javascript library to add JS sortable columns to the tables. This is also fairly well documented in the 7.x-x current (beta) release.

Extra info about David's latest comment: the conditional SQL docu is located in reports/help.data#conditionalsql . The dataTables docu is at reports/help.setup#datatables and includes a pointer to a video about it also. Note that the beta4 version of the docu still has some glitches that have been resolved in the current dev version already, refer to #2124777 for more details.

Since conditional SQL does not apply to data blocks in XML format, I prefer using the dataTables approach to create sortable table columns for XML data blocks.

Thanks for the suggestions - I was able to use the same datablock twice with different sorts in the same report by passing a different sort parameter directly into the block, using the SWITCH CASE method in this issue and the frx:parameters method here https://www.drupal.org/node/2039039.

e.g. frx:parameters="sort=somesortfield"

Hi,
I've successfully created a forena report with one parameter. My issue is when I open the report I can’t see directly my table. I should select a parameter, submit then my table is browsed.
So how can I let show my table by default? In fact I’m using the parameter as filter criteria.
Thanks in advance.

If I understand you correctly, you want the parameter to have a specific value as its default, so that a report has data? This can be done by editing the parameters of the report and providing a default value.

I'd recommend creating a separate support issue for this as it doesn't seem related to this closed issue.

ok thank you for your reply