I am astounded by Forena Reports and wish to use it to provide a fairly complex report that includes a graph of the data that I can access through views - It is easiest through views since the data is in an entiyform with lots of custom fields (11 join statements are boring to write).

Whilst the documentation tells me I can use it with views there are no instructions on how to use these two together - Rather than simply say "please tell us" - here are some thoughts that have occurred to me as I struggle.

Given a view that displays a table of the data required... when selecting the format 'forena reports' the settings dialog asks for a report name, providing a default of reports/test.

My reports are stored under default/files/reports which is the path given in the Forena Reports settings. If I create a report (with no data source) called for instance "Protective Factors Index" with a machine name of "pfi" this will create a file in default/files/reports/pfi.frx

If I now enter "pfi" into the format settings dialog in views I see a drop-down listing the report, with its machine name followed by the 'human' name, which I then select.

At this point the preview of the view, which was a title followed by a table, changes to title only (no data).

If I go to the report it is blank.

I was expecting that creating a link between the view and the report would set the data source for the report to the output of the view - but not so.

If I click the data button on the report there is no option to select a view, nor can I see any way of creting a data block that is based on a view.

Where am I going wrong?
What is the step by step procedure for using Ferena Reports with Views?

Many thanks for your great contribution.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

mylesorme created an issue. See original summary.

mylesorme’s picture

Issue summary: View changes
metzlerd’s picture

You basically have the right of it. Select a view row style of Forena should allow you to specify the formatting of that view in the forena report. You should not need to include any frx:data or frx:foreach elements in the report. It has been a while since I have used forena in this way so I suppose it is possible that there is a bug. I will try and check this and get back to you. You might try adding a render element to your report to see if it provides clues as to the data that is being passed from views to forena.:

  <div frx:renderer="FrxXML"/> 

Some other ways to use forena:
I totally agree that making 11 joins is not only boring but hard to maintain, but Forena has some other features that make that easy for you. In particular you can write a data blocks that load entities, but still use traditional SQL to determine which rows to load. See the forena's documentation on accessing data for more information. (http://forenasolutions.org/reports/help.data#drupalentities)

This is a far more common way to use forena. Don't get me wrong though. I'm quite willing to get to the bottom of any troubles using views as a forena report.

mylesorme’s picture

@metzlerd - thank you. For now I have resolved my need using a SQL block - drupalentities would be great, not only to limit the joins but also to access field attributes such as the labels that are not reachable with standard SQL. Unfortunately they don't appear to be working - This is an opinion based on playing with the example given (which itself does not return any data). I need to get my application written in something of a hurry, hence I've not explored enough to define the issue - Ferona is a central part of my application - a really great contribution, so I'll be exploring it much more in the future. Thanks again.

aotu’s picture

+1 on this
I'm not able translate Views generated SQL into valid MySQL, and following the same process as above results in both an empty view display and an empty Forena report/report with no valid data source option.
Damn shame :/
Thanks for Forena by the way, super useful

aotu’s picture

OK, well interesting update... I took the Views SQL code and simply threw it into a data.sql file for Forena to try and render. I was surprised to see that it ran, however the output is rather strange.

At a quick glance, can you see why 'node' is rendering in the table, rather than the field output?

IMG1: Forena table
http://i.imgur.com/nIe2Pw0.png

IMG2: Views table
http://imgur.com/gAEkXKm.png

Code: Views SQL:

SELECT node_node_revision.nid AS node_node_revision_nid, node_revision.vid AS vid, node_revision.nid AS node_revision_nid, node_revision.timestamp AS node_revision_timestamp, node_node_revision.type AS node_node_revision_type, field_revision_field_requestor_name.field_requestor_name_value AS field_revision_field_requestor_name_field_requestor_name_val, 'node' AS field_data_field_requestor_node_entity_type, 'node' AS field_revision_field_requestor_name_node_entity_type, 'node' AS field_data_field_representative_name_node_entity_type, 'node' AS field_revision_field_is_year_end_node_entity_type
FROM 
{node_revision} node_revision
LEFT JOIN {node} node_node_revision ON node_revision.nid = node_node_revision.nid
INNER JOIN {field_revision_field_is_year_end} field_revision_field_is_year_end ON node_revision.vid = field_revision_field_is_year_end.revision_id AND (field_revision_field_is_year_end.entity_type = 'node' AND field_revision_field_is_year_end.deleted = '0')
LEFT JOIN {field_revision_field_requestor_name} field_revision_field_requestor_name ON node_revision.vid = field_revision_field_requestor_name.revision_id AND (field_revision_field_requestor_name.entity_type = 'node' AND field_revision_field_requestor_name.deleted = '0')
WHERE (( (node_node_revision.type IN  ('appraisal')) AND (field_revision_field_is_year_end.field_is_year_end_value = 'yes') AND (field_revision_field_requestor_name.field_requestor_name_value NOT LIKE 'admin' ESCAPE '\\') ))
ORDER BY field_revision_field_requestor_name_field_requestor_name_val ASC
metzlerd’s picture

This is really how forena is designed to work, giving you access to the raw table data so that you can write more complicated queries than views is capable of achieving on its own. If you want to access the rendered fields you need to use the entity loader in your data block. See the documentation referenced in #3 for additional information.

On a side note, the reason that SQL from views won't run as MySQL is that it wraps all table names in {} so that the multi-site prefixing can be established. If you aren't using table prefixing you can remove the {} from the table names and the queries will run in MySQL tools.

metzlerd’s picture

Version: 7.x-4.6 » 7.x-4.7

I finally had the time to verify the test a process using the current code and was able to get it working using the following steps:

  1. Create a new empty report called "nodetest" using the body illustrated below. Don't add any data blocks to it at this time.
  2. Create a new view that uses the forena report display style. Change the "Create from Report" settings on this display reference to "nodetest" (do not use reports/nodetest as the defaults might suggest)
  3. Preview the report and verify that it works.

Sample report body that can be used for nodetest report so that you can see what fields you have added:

  <div frx:renderer="FrxXML"/>
metzlerd’s picture

Here's a small patch that will be committed shortly to correct the labeling in the views field.

metzlerd’s picture

Status: Active » Needs review
dcrellen’s picture

Though this thread I finally get that I won't be able to use Views and Forena Reports the way I envisioned. I have created a quite complex query that references several related tables (but not in the same bindle) to calculate several categories belonging to a specific facility based on the items that the facility owns within each category where each item could have a different value. You could liken it to an inventory of many items in different classifications. It works like a charm in a standalone report. Thanks to your great Forena development work and the persistency to keep it up.

My problem is this: I need to display this report as a component of multiple reports of non-forena origin but all on the same page. I've been doing this successfully for all but Forena by using the QuickTabs module and Views. QuickTabs passes a parameter to Views to create the report. I thought that this would work where Views would call up my Forena report with the appropriate parameter. Not so.

I then discovered the BEAN module. Got excited again but, unfortunately, I have not found anything on the web that explains how to pass parameters to the BEAN-block nor have I found a way to provide parameter passing to blocks in QuickTabs. Does anyone have any suggestions on how I may make this work? My client is getting impatient as I've spent many days (and nights) trying to solve this problem.

Thanks

metzlerd’s picture

FYI: There is no reason that you can't use the insert view module to embed a view in the forena report. Furthermore, you could get forena to do the parameter substitutions to get the view parameters correct. Also forena contains an input filter that would allow you to embed a report with parameters into a view. This is the approach I would use when trying to mix and match views and forena reports.

dcrellen’s picture

I think I understand your comment on using Views with Forena. However, what I am looking for is to have Views pass a parameter (Views' argument) to Forena which will process the report using the passed parameter and return the report to Views which will make it available as a content pane. This seems to be the only non-custom-program way of providing the report to Drupal Quicktabs module.

(I am experiencing a non-related problem with Forena Reports and SQL which I've just reported as a separate issue -- "Compatibility with MariaDB".)

metzlerd’s picture

Forena supports an input filter that allows you to embed reports anywhere you can use text formats. Other users have used this to embed reports into views. You basically need to enable the report embedding feature in your filter and then reference reports using a syntax like this: [report:my report:state=WA]. My recollection is that when you're doing this in views headers/footers, then there is a token based syntax that allows you to embed tokens that refer to view arguments, but I'm not 100% sure of this. The idea is that you replace the WA with the tokens that the views module will translate. I'm out of the country and not in a place that I can research this for you, but hopefully these clues will help.