Because of David's suggestion #1 in #2350343: Combination Graph, I'm considering starting some research/experiments with the google charts stuff.

It would help me to do so if anybody could explain a bit what that json stuff in forena is about (the 3rd phrase in that suggestion). Maybe some basic sample/scenario about how to use what's available in forena, or why that json stuff was added to forena? Maybe some phrasing we can use to add somewhere in the reporting tutorials (it might help others to also explore the json stuff)?

CommentFileSizeAuthor
#4 2362855-json-invalid-argument.patch381 bytesmetzlerd

Comments

Pierre.Vriens’s picture

Pierre.Vriens commented
Title: Details about JSON in forena? » Details about forena's JSON fmt to use it as chart(s) input

OK, I've made some progress on this HowTo question I asked myself before. Here is a recap, at least for myself, about what I've learned, and gotten to work ... I'm using www.MySite.com here as my (fake) domain name, replace it by your own domain of course ...

Part 1:
At first I started with just the sample.states sample, to produce the JSON equivalent of
http://www.MySite.com/reports/sample.states

  1. I visited sample report 'samples.state', located at http://www.MySite.com/reports/sampledb.states
  2. Using David's hint in #2350343-1: Combination Graph, I just over-typed the URL in my browser so that it looked like http://www.MySite.com/forena/json/sample.states (that is: I just changed 'reports' to 'forena/json' ... but forgot to add the 'db' part in it ...). Too bad, it didn't really work, I just got a nearly empty page content, with only '[' and ']' ... Moreover, it resulted in an error like this also (showed up a little later, with that typical Drupal-delay thing):
        Undefined repositorysample
    Warning: Invalid argument supplied for foreach() in forena_json() (line 541 of ...../sites/all/modules/forena/forena.module).

    Now I know/understand: 'sample' is not the repository name, because that is 'sampledb' ...

  3. Then I corrected the URL (adding the 'db' in it) to http://www.MySite.com/forena/json/sampledb.states ...
    Eureka! That worked, now I got what it should look like, i.e.:
    [{"code":"AL","name":"Alabama","state":"AL"},{"code":"AK","name":"Alaska","state":"AK"},{"code":"AS","name":"American Samoa","state":"AS"},{"code":"AZ","name":"Arizona","state":"AZ"},{"code":"AR","name":"Arkansas","state":"AR"},{"code":"CA","name":"California","state":"CA"},{"code":"CO","name":"Colorado","state":"CO"},{"code":"CT","name":"Connecticut","state":"CT"},{"code":"DE","name":"Delaware","state":"DE"},{"code":"DC","name":"District of Columbia","state":"DC"},{"code":"FM","name":"Federated statess of Micronesia","state":"FM"},{"code":"FL","name":"Florida","state":"FL"},{"code":"GA","name":"Georgia","state":"GA"},{"code":"GU","name":"Guam","state":"GU"},{"code":"HI","name":"Hawaii","state":"HI"},{"code":"ID","name":"Idaho","state":"ID"},{"code":"IL","name":"Illinois","state":"IL"},{"code":"IN","name":"Indiana","state":"IN"},{"code":"IA","name":"Iowa","state":"IA"},{"code":"KS","name":"Kansas","state":"KS"},{"code":"KY","name":"Kentucky","state":"KY"},{"code":"LA","name":"Louisiana","state":"LA"},{"code":"ME","name":"Maine","state":"ME"},{"code":"MH","name":"Marshall Islands","state":"MH"},{"code":"MD","name":"Maryland","state":"MD"},{"code":"MA","name":"Massachusetts","state":"MA"},{"code":"MI","name":"Michigan","state":"MI"},{"code":"MN","name":"Minnesota","state":"MN"},{"code":"MS","name":"Mississippi","state":"MS"},{"code":"MO","name":"Missouri","state":"MO"},{"code":"MT","name":"Montana","state":"MT"},{"code":"NE","name":"Nebraska","state":"NE"},{"code":"NV","name":"Nevada","state":"NV"},{"code":"NH","name":"New Hampshire","state":"NH"},{"code":"NJ","name":"New Jersey","state":"NJ"},{"code":"NM","name":"New Mexico","state":"NM"},{"code":"NY","name":"New York","state":"NY"},{"code":"NC","name":"North Carolina","state":"NC"},{"code":"ND","name":"North Dakota","state":"ND"},{"code":"MP","name":"Northern Mariana Islands","state":"MP"},{"code":"OH","name":"Ohio","state":"OH"},{"code":"OK","name":"Oklahoma","state":"OK"},{"code":"OR","name":"Oregon","state":"OR"},{"code":"PW","name":"Palau","state":"PW"},{"code":"PA","name":"Pennsylvania","state":"PA"},{"code":"PR","name":"Puerto Rico","state":"PR"},{"code":"RI","name":"Rhode Island","state":"RI"},{"code":"SC","name":"South Carolina","state":"SC"},{"code":"SD","name":"South Dakota","state":"SD"},{"code":"TN","name":"Tennessee","state":"TN"},{"code":"TX","name":"Texas","state":"TX"},{"code":"UT","name":"Utah","state":"UT"},{"code":"VT","name":"Vermont","state":"VT"},{"code":"VI","name":"Virgin Islands","state":"VI"},{"code":"VA","name":"Virginia","state":"VA"},{"code":"WA","name":"Washington","state":"WA"},{"code":"WV","name":"West Virginia","state":"WV"},{"code":"WI","name":"Wisconsin","state":"WI"},{"code":"WY","name":"Wyoming","state":"WY"}]

Interesting so far, I do have "my" JSON sample now. Now what ... ? Euh, that is not a good sample to actually create a chart from it, right? Unless maybe a chart in so called "MAP" format (to see if there are maybe any gaps, i.e. some states are not represented in that sampledb )? Something you can do with the chart module ... But that type of chart is not what I'm interested in to complete my testcase, so I'm just going to swap to another sample report (see part 2) ...

Part 2:

So I want AbitMORE (oeps) ... and swap my testcase to
http://www.MySite.be/reports/sample.user_distribution_simple?state=OH
Reason why is that it does produce something with 3 cities (Chandler, Fernley and Rehoboth Beach), each with their number of users (3,179, 4,493 and 8,828). By accident also allows me to experiment with JSON combined with a forena parm ... So let me change this URL like so now: http://www.MySite.be/forena/json/sampledb.user_distribution_simple?state=OH (with the 'db' added right away now). Grrrrrrrrrrr, it doesn't seem to work, I only get that nearly empty page again (with only '[' and ']'). Afterwards an error like this also:

Warning: Invalid argument supplied for foreach() in forena_json() (line 541 of ......./sites/all/modules/forena/forena.module).

If I then try a similar sample like http://www.MySite.com/forena/json/sampledb.user_distribution_simple (that is: I omit the forena parm at the end), I run into the same problem (followed by the same Drupal error msg ...)

I'm not sure, but I suspect those comma's in the 3 values for users to be causing the problem ... It'd be interesting to find out (how?) what the result would be if they would have been formatted with a European notion, like (3.179, 4.493 and 8.828) ... how can I experiment with that variation? Don't know yet, which is why I want to change to another sample, this time I'll go for the JSON equivalent of

http://www.MySite.com/reports/drupaladmin.active_users

Here is what the (tiny but correct) report looks like (slightly edited data):

Admin 	      admin@MySite.com 	Thu, 12/02/2015 - 23:15 	logs
Pierre.Vriens 	pierre.vriens@MySite.com 	Tue, 10/02/2015 - 11:37 	logs

The JSON format of it is ... http://www.MySite.com/forena/json/drupal.active_users (note that I have to change "drupaladmin" to "drupal" in this case ... and 'reports' to 'foren/json') ... And guess what: Eureka again!

Here are my results (slightly edited data):

[{"uid":"1","name":"Admin","mail":"monitor@MySite.com","login":"1423779310","status":"1"},{"uid":"9","name":"Pierre.Vriens","mail":"pierre.vriens@MySite.com","login":"1423564627","status":"1"}]

Note: I also tried using the sample at http://www.Mysite.com/reports/sample.state_summary (which has counts formatted without the ',' as the thousands separator, but does include the SVG Graph). That resulted in a similar nearly empty page with only '[' and ']' also ... another grrrrrr.

Part 3:
I've been experimenting with user/1, so any permissions wouldn't get into the way. But I understand from #2350343-4: Combination Graph that if you're using another user, you must ensure that, in order for JSON to produce actual results, you have this permission granted: "Access data from data blocks directly". Sorry friend David, but from the description that goes with it (= 'Useful for ajax calls to data blocks'), 'I' do not really realize that there is a relationship with that JSON magic also. Can you think of rephrasing that description somehow, so that at least it includes "JSON" somewhere?

Summary:

Hm, now what? I did find the answer to my original question, but I haven't figured out the reason for the failed experiment (in part 2) with http://www.MySite.be/reports/sample.user_distribution_simple?state=OH

Apart from that, there is that tiny request to edit the permission description a bit (to somehow add 'JSON' in it).

Any suggestions about how to move forward with this issue? Ideally we get that failed experiment sorted out, so that I can use that continue with that example to make it work in combination with both the chart and charts module ... Probably using PHP function json_decode ... with some coding-prototype that looks similar to a sample I foundhere, i.e.:

$jsondata = file_get_contents('forena/json/sampledb.user_distribution_simple?state=OH'); // Get the JSON data
$mydata = json_decode($jsondata,true);  // Convert to PHP Object (+ make it an array)
print_r($mydata);  // Convert to PHP Array
 $chart['#data'] = $mydata;
 echo theme('chart', array('chart' => $chart)); // For chart module, similar for charts module later on

The last 2 lines in the code above are from the chart docu ...

Am I close?

Pierre.Vriens’s picture

Pierre.Vriens commented
Version: 7.x-4.0-beta4 » 7.x-4.1

Just to be complete, the scenario in my previous comment relates to 7.x-4.1 now ...

Pierre.Vriens’s picture

Pierre.Vriens commented

Inspired by #2437701: XML doc type with DIVs around, and because of my #1 above, I thought it might be interesting to add these additions:

  1. Use sample report http://www.MySite.com/reports/drupaladmin.active_users to do an XML experiment, that report itself should work in any site, right?
  2. http://www.MySite.com/reports/drupaladmin.active_users.xml causes the exact same error as in #2437701: XML doc type with DIVs around, probably because that is not the correct URL variation.
  3. Instead try http://www.MySite.com/forena/xml/drupal.active_users ... (that is: change 'reports' to forena/xml', and 'drupaladmin' to 'drupal') that seems to work great ...

Out of curiosity, I also tried http://www.MySite.com/forena/xml/sampledb.user_distribution_simple?state=OH ... that returned a blank (empty) page, so rather similar problem I think as the failing JSON variation. Followed again with a similar Drupal error msg later on, i.e.:

Warning: Invalid argument supplied for foreach() in forena_json() (line 541 of .../sites/all/modules/forena/forena.module).

I'm assuming that this comment will help to get the other issue resolved. Anybody any help for "me" to address my remaining JSON issues in my previous comment above? Please?

metzlerd’s picture

metzlerd commented
StatusFileSize
new 2362855-json-invalid-argument.patch381 bytes

Here's the patch for the warning

You are confusing reports with data blocks here. The forena/json path returns the data for a data block not a report. You can get "reports as XML" using the .xml extension, but you are referring to the report name, not the data block name in your examples. The correct path for the data you were trying to get is:

forena/json/sampledb.distribution?state=OH

FYI: If you need to manipulate json to get it into a format that google charts understands then you should consider doing that in Javascript, not in php. You'd need to write the javascript to get the data via AJAX anyway.

MickC’s picture

MickC commented

Thanks for the feecback - I can see the JSON results no problem, but wondering about permissions.

I can see the data when logged in or out, but when testing (using PHP, Javascript later) no data is returned even for Admin - only worked when Anonymous was granted 'read data block' and the repository access.

How do we get this to use the existing access control - will Javascript make any difference?

metzlerd’s picture

metzlerd commented

The Access data from data blocks directly is used in ADDITIONAL check to other security checks. If you want people to be able to access json or xml blocks you need to grant this right. It will still enforce repository access control in addition to any rights that are specified in the ACCESS= comment in the block.

I hope that answers your question.

MickC’s picture

MickC commented

OK I checked again and when logged out this time I don't see the data. So I assume f I want to enable it for a user with javascript running to access the data, then ACCESS= may be required on the block.

Now the challenge is the javascript itself - this will be a learning curve so any starter tips would be appreciated.
Do we put the js into a the body using <script> or do we put it into separate js file to be referenced in a skinfo file? If the latter then I assume we'll need a separate js and skinfo file per report.

metzlerd’s picture

metzlerd commented

You can make a file in the same directory as the report with the same name and it will be included. So for example, if the report file was report_foo.frx then create a file called report_foo.js and it will be loaded automatically without any skinfo modification. Skinfo files are really used for adding libraries.

metzlerd’s picture

metzlerd commented

Also, in the latest versions of forena, data can be preloaded into json objects with each report. You might check out a copy of my sandbox project which has an example implementation of a graph using the d3 graphing library:

https://www.drupal.org/sandbox/metzlerd/frx

Or more specifically loock at d3graph.js and d3graph.frx at:
http://cgit.drupalcode.org/sandbox-metzlerd-frx/tree/reports?h=7.x-1.x

MickC’s picture

MickC commented

Hi David - great! That example really helped. Once I got my head around how to customise it to my data it works well. The key was
a) setting the data context correctly using
b) making the script fit into a Drupal.behaviors statement
c) passing data from the forena query into the chart using data.map(function(d) { return d.myfield; })

As I'm aiming to create a dashboard, the trick now will be getting multiple charts into the same report.
I assume that's a case of
1. defining multiple data blocks for the report
2. adding multiple Drupal.behaviours statements, ensuring none overlap
3. ensuring all scripts are loaded in the skinfo file

Is there an alternative way to achieve this? e.g.
- create reports as Drupal Blocks, and simply insert them?
- use FrxInclude?

metzlerd’s picture

metzlerd commented

You should be able to create a dashboard using one report. I've done that several times. You can include multiple data blocks (just give them different ids) in the same report, and they will be made available in the settings array by those ids:

<frx:data id="myFirstBlock" block="mydata/my_first_block" json="myFirstBlock"/>
<frx:data id="mySecondBlock" block="mydata/my_second_block" json="mySecondBlock" />

These would then be available in the json as: 

Drupal.settings.forenaData.myFirstBlock;
Drupal.settings.forenaData.mySecondBlock;

You then write the code to dump different inside different divs. You'll see the existing div. Create a second div to make the second chart in:

<div id="chart">
</div>
<div id="chart2">
</div>

You would add code to build the second chart in the same behavior it would just start by selecting the second chart using d3.select('#chart2').
You only really need one behavior per page that loads charts.

Does that make sense?

metzlerd’s picture

metzlerd commented

Woops I made some edit corrections to my last post.

MickC’s picture

MickC commented

Thanks David - all going well.
I can add separate datablocks and charts using D3 or C3 although having some performance issues using both.
Really only using this to add 'gauges' to the dashboard, which SVGGraph doesn't do.
The nearest thing it could do is a donut graph but it really needs to be a semicircle.
e.g. http://c3js.org/samples/chart_gauge.html
Still looking...

metzlerd’s picture

metzlerd commented

Do you think the performance problems are related to the d3 library, the SVGGraph library or the data queries?

MickC’s picture

MickC commented

It can't be the data query because its only 1 line and I'm just creating 3 gauges from the 1 data row - I don't think it's SVGGraph because I'm not using it in this page, unless there was a potential conflict in using another library.

I think the issue is that I'm using C3, which is an additional js library which in turn uses D3 - which seemed fine for a single gause but not when using 3. It's just for this particular gauge style http://c3js.org/samples/chart_gauge.html

C3's claim is "C3 makes it easy to generate D3-based charts by wrapping the code required to construct the entire chart. We don't need to write D3 code any more. " However I've also seen comments that it could make the js heavy.

I will try and use D3 only to compare e.g. http://bl.ocks.org/msqr/3202712 or http://codepen.io/zawszaws/pen/EFomd, and then somehow try to adapt to be more like the C3 example.

MickC’s picture

MickC commented

Another point about performance: I noticed that the js libraries were not included in Drupal's js compression.

All the additional js libraries appear to be loading as individual scripts in addition to the compressed Drupal js.

Is there a way to take advantage of Drupal;s js compression settings? e.g. what if we loaded js to the theme?
I'm sure it would get compressed, but would Forena work without having them specifically added via the skinfo file?

metzlerd’s picture

metzlerd commented

Hm.... I'll have to research that.

Yes, forena doesn't care where extra js or css comes from.

MickC’s picture

MickC commented

Performance has improved - I've now got 6 charts and 2 tables on the dashboard with good response times.

I revised the code to change var names to be unique, checking that each data var and chart var if relevant had its own name, and of course html element. Maybe it wasn't unique before - some competition between graphs for resources? Not sure - but it's working now with a mix of D3, C3, SVGGraph and Datatables.

SimBASE’s picture

SimBASE commented

thank you for pointing me in the right direction. I was searching for exactly the same issue: Forena JSON for charts (morris.js) and tables (handsontable). forena/json/xxxxDB.xxxx for JSON and forena/xml/xxxxDB.xxxx for XML works well. It also works with parameters:
"returned a blank (empty) page" is because the SQL query and Forena report doesn't have a paramater. After you add a parameter in sql query: WHERE column1:=parameter1 everything works well.