Using data contexts
This question is a (great) example of using "Data contexts" in Forena. To see an example of a "data context" at work (in a demo site), refer to the example about Master Summary with drill down. If in the report you click any of the linked columns (such as for state = VA), you'll get the Users in state Virginia report. In that report you should note these things:
- ... in state Virginia (in the report title).
- ... state with code = VA (near the top of the report).
If in the above example you pick another state, such as IL (Illinois), you'll get the report specific to that state.
Read more about "Data Contexts" in the Forena documentation, which you can also find in your own site within /reports/help.reportingfrx#datacontexts (after you installed Forena).
Below are some details about what's needed in this specific case (to use the user's country as your chart tile), based on these assumptions:
- The user's country is contained in stored in a datablock named (say) "pec_reports/data_block2", within a column (say) "UserCountry".
- The user's country is (say) "XYZ".
- You want your chart title to be like (say) "Chart for country XYZ".
Based on these assumptions, this is what you should do:
Add a data context to the document in the section of the document, which are preloaded before the report is rendered. The example below illustrates the syntax for preloading data in the head section:
<head>
..
<frx:data id="countryinfo" block="pec_reports/data_block2"/>
</head>
Any data from any other section of a report may be used by referencing the datablock by its id and appropriate column name of that datablock. The example below contains an example of referencing the UserCountry column contained in pec_reports/data_block2:
<div> ... {countryinfo.UserCountry} ... </div>
The syntax for such reference is like so:
- "Id" of the datablock, followed by a ".", and followed by the "ColumnName".
- Preceeded by a "{" and followed by "}".
Same is true for any of the tags used to specify the details of the chart you want to create. So in this case you could use something like so:
frx:graph_title="{countryinfo.UserCountry}"
Ensure your SQL returns the correct value(s)
If you get a blank chart title after applying the changes as I suggested above, then most probably that is because the value of that chart title is blank. Looking at the SQL statement for your pec_reports/data_block2, here are a few tips to further verify (debug?) the actual SQL statement you're using:
- Create a temporary (for debugging purposes only) tabular report using that same
pec_reports/data_block2 data block, and run that report while logged in. Or as a variation, add that tabular report in your report with the chart, but in front of that chart. Do you get the expected "country" value for that user? I wouldn't be surprised if you get an empty output (which would confirm something is wrong in your SQL statement, and hence also explain your blank chart title). In that case continue with step "2." and/or "3." below.
- In your from clause, change
users to {users}, and rerun your temporary report in step 1.
- In your where clause, change
current_user to the value of the uid of your own user, and rerun your temporary report in step 1.
If none of the above seems to help, then I would use either (or both) of these other routes:
Have a look at the data blocks (SQL statements) related to the Drupal Administration reports that are delivered with Forena also, such as:
- Active Users
- Logs for User
- Role Details
Use the query writing tool that comes with Forena, which is called the Forena Query Builder. This is a separated module that comes with Forena. It can be enabled as per standard Drupal instructions to enable an additional module. For a video tutorial about this query writing tool, checkout Define data blocks with optional filters.
