I attempted to build a report similar to the Master detail with nested blocks included in the module

My report looks like this

<body>
  <div id="environment" frx:block="RM Applications/environmentparam">
    <div id="anotherid" frx:foreach="*"> 
      <h2 class="mig-comp-header">{Environment}</h2>
      <div id="migrations_block" class="FrxTable" frx:block="RM Applications/migrations">
        <table>
          <thead>
            <tr>
              <th>Components</th>
              <th>Release Name</th>
              <th>Tag</th>
              <th>Mainframe Name</th>
              <th>Mig Date</th></tr></thead>
          <tbody>
            <tr id="migrations" frx:foreach="*">
              <td>{Components}</td>
              <td>{Release_Name}</td>
              <td>{Tag}</td>
              <td>{Mainframe_Name}</td>
              <td>{d}</td>
            </tr>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</body>

So far all is good, the parameter supplied in order for the grouping to occur is working. I've also added a paramater to the outer block to only display a specific Environment...This works fine

Outter block SQL

--ACCESS=access content

SELECT tax_env.name AS Environment
FROM taxonomy_term_data tax_env
WHERE ( tax_env.vid = 9 )

--IF=:Envparam
  AND tax_env.name IN (:Envparam)
--END

The problem I am having is that I want additional filtering/parameters in the inner block such filtering by a specific date.

When I define the paramater both in the report and the SQL, nothing happens.

Inner block SQL 

--ACCESS=access content
SELECT 
  /* node.nid as nid,*/
  /*c.field_parent_component_tid as comp_id, */
  tax_app.name AS Components,
  node_rls_xref.title AS Release_Name,
  node_p_xref.title AS Tag,
  mfname.field_mig_mainframe_name_value AS Mainframe_Name,
  /*env.field_mig_details_env_tid as env_id,  */
  tax_env.name AS Environment,   
  DATE_FORMAT(migdate.field_mig_details_mig_date_value,'%Y-%m-%d') AS d,
  /* pnid not displayed but used to link Tag to node in report */
  node_p_xref.nid as pnid
      
FROM node node
    
LEFT JOIN field_data_field_parent_component c ON node.nid = c.entity_id     
LEFT JOIN field_data_field_mig_details_env env ON node.nid = env.entity_id 
LEFT JOIN field_data_field_mig_details_mig_date migdate ON node.nid = migdate.entity_id 
/* Link to get node title from parent entity migration_request */
LEFT JOIN field_data_field_mig_parent_x_ref p_xref
  ON node.nid = p_xref.entity_id AND (p_xref.entity_type = 'node' AND p_xref.deleted = '0')
LEFT JOIN node node_p_xref ON p_xref.field_mig_parent_x_ref_target_id = node_p_xref.nid
/* Link RM Apps Taxonomy */
LEFT JOIN taxonomy_term_data tax_app
  ON ( tax_app.vid = 6 AND c.field_parent_component_tid = tax_app.tid )
/* Link RM Component Taxonomy */
LEFT JOIN taxonomy_term_data tax_env
  ON ( tax_env.vid = 9 AND env.field_mig_details_env_tid = tax_env.tid )
/* Mainframe name on the parent node */
LEFT JOIN field_data_field_mig_mainframe_name mfname ON (mfname.entity_id = node_p_xref.nid)  
/* Release Name links */  
/* The ReleaseName value is the targetId pointing back to the release node, 
  need to get the title from release node to display on report */
LEFT JOIN field_data_field_mig_release rls ON ( rls.entity_id = node_p_xref.nid )
LEFT JOIN node node_rls_xref ON (rls.field_mig_release_target_id = node_rls_xref.nid)

JOIN (
    /* SUBSELECT to group by Environment, then by Application, 
      then selecting the latest migration from grouped data, this will give us teh most
      recent migration per app, per lab
    */
    SELECT 
      c_sub.field_parent_component_tid as comp_id, 
      env_sub.field_mig_details_env_tid as env_id,         
      MAX(date_sub.field_mig_details_mig_date_value) AS maxdate
    FROM node subnode

    LEFT JOIN field_data_field_parent_component c_sub ON (subnode.nid = c_sub.entity_id)
    LEFT JOIN field_data_field_mig_details_env env_sub ON (subnode.nid = env_sub.entity_id)
    LEFT JOIN field_data_field_mig_details_mig_date date_sub ON (subnode.nid = date_sub.entity_id) 
    /*more joins for possible conditional filters */
    LEFT JOIN field_data_field_mig_parent_x_ref p_xref
      ON subnode.nid = p_xref.entity_id AND (p_xref.entity_type = 'node' AND p_xref.deleted = '0')
    LEFT JOIN node node_p_xref ON p_xref.field_mig_parent_x_ref_target_id = node_p_xref.nid    
    LEFT JOIN field_data_field_mig_release rls ON ( rls.entity_id = node_p_xref.nid )
    LEFT JOIN node node_rls_xref ON (rls.field_mig_release_target_id = node_rls_xref.nid)  
    LEFT JOIN field_data_field_mig_mainframe_name mfname ON (mfname.entity_id = node_p_xref.nid)    
    LEFT JOIN taxonomy_term_data tax_app 
      ON ( tax_app.vid = 6 AND c_sub.field_parent_component_tid = tax_app.tid )
    WHERE (
      ( subnode.status = '1' AND subnode.type ='migration_details' )
      AND (c_sub.field_parent_component_tid IS NOT NULL)
/* These conditional fields are up here vs. in main select because we want to 
search the entire DB for a tag, not the grouped results
i.e. a tag AMPS-TAG-V1 and AMPS-TAG-V2 exist, subselect only returns V2, 
If the conditional is on main select and I search for V1, it will return 0 rows
*/
--IF=:migdate
      AND date_sub.field_mig_details_mig_date_value <=:migdate
--END  

    )
    GROUP BY  comp_id,  env_id
) /* We can't use the nodeid to link back to main query because of an apparent bug in mysql
    where adding the id on the aggregated select will return the first ID and not the id of the 
    maxdate row. More or less adding anything else in the select breaks aggregation...
    SO, the ( Component + Environment + MaxDate ) will act as our KEY
  */
query2 ON c.field_parent_component_tid = query2.comp_id 
      AND env.field_mig_details_env_tid = query2.env_id
      AND migdate.field_mig_details_mig_date_value = query2.maxdate

WHERE   (node.status = '1') AND (node.type IN  ('migration_details')) 
/* Conditional parameters for Forena Reports */

  AND tax_env.name IN (:Environment)


ORDER BY 
  tax_app.name

This is my first attempt a making a Forena report, so I might be doing something wrong, and of course to add more complexity I am doing a subselect because I need aggregation and Views could not handle what I wanted.

I also attempted another sql like this, with the same result...not sure which is better...or if they are both awfull (I'm not an SQL guy)! :-)

--ACCESS=access content
SELECT q1.*

FROM 

( SELECT 
  /* node.nid as nid,*/
  /*c.field_parent_component_tid as comp_id, */
  tax_app.name AS Components,
  node_rls_xref.title AS Release_Name,
  node_p_xref.title AS Tag,
  mfname.field_mig_mainframe_name_value AS Mainframe_Name,
  /*env.field_mig_details_env_tid as env_id,  */
  tax_env.name AS Environment,   
  DATE_FORMAT(migdate.field_mig_details_mig_date_value,'%Y-%m-%d') AS d,
  /* pnid not displayed but used to link Tag to node in report */
  node_p_xref.nid as pnid
      
FROM  field_data_field_parent_component c,
      field_data_field_mig_details_env env,
      field_data_field_mig_details_mig_date migdate,
      taxonomy_term_data tax_app,
      taxonomy_term_data tax_env,
      field_data_field_mig_parent_x_ref field_xref,  
      node node_p_xref,
      field_data_field_mig_mainframe_name mfname,     
      field_data_field_mig_release rls,
      node node_rls_xref

 

WHERE  
  ( c.bundle = 'migration_details' AND c.deleted = 0 )  
AND ( env.entity_id = c.entity_id )
AND ( tax_app.vid = 6 AND c.field_parent_component_tid = tax_app.tid )
AND ( tax_env.vid = 9 AND env.field_mig_details_env_tid = tax_env.tid )
AND ( rls.entity_id = node_p_xref.nid AND rls.field_mig_release_target_id = node_rls_xref.nid )
AND ( c.entity_id = field_xref.entity_id AND field_xref.field_mig_parent_x_ref_target_id = node_p_xref.nid )
AND ( c.entity_id = migdate.entity_id )
AND ( mfname.entity_id = node_p_xref.nid )

/* Conditional parameters for Forena Reports */
/* this somehow matches the column in the envirmont sql?? */
  AND tax_env.name IN (:Env1)


) q1,



(   
SELECT  tax_app.name AS Components,
        tax_env.name AS Environment,
        MAX(migdate.field_mig_details_mig_date_value) AS maxdate
        
FROM  field_data_field_parent_component parent_comp,
      field_data_field_mig_details_env env,
      field_data_field_mig_details_mig_date migdate,
      taxonomy_term_data tax_app,
      taxonomy_term_data tax_env,
      field_data_field_mig_parent_x_ref field_xref,  
      node node_xref,
      field_data_field_mig_mainframe_name mfname,     
      field_data_field_mig_release rls,
      node rls_xref

WHERE (
  ( parent_comp.bundle = 'migration_details' AND parent_comp.deleted = 0 )  
  AND ( env.entity_id = parent_comp.entity_id )

/* Link RM Component taxonomy */
AND 
( 
  tax_app.vid = 6 AND parent_comp.field_parent_component_tid = tax_app.tid 
--IF=:Component
  AND (tax_app.name LIKE ('%' :Component '%'))
--END  
)

/* Link RM Environment Taxonomy */
AND 
( 
  tax_env.vid = 9 AND env.field_mig_details_env_tid = tax_env.tid 
--IF=:Env1
  AND (tax_env.name LIKE ('%' :Env1 '%'))
--END
)

/* Release Name links */
AND 
( 
  rls.entity_id = node_xref.nid AND rls.field_mig_release_target_id = rls_xref.nid  
--IF=:Release
  AND (rls_xref.title LIKE ('%' :Release '%'))
--END 
)

/* Link to get node title from parent entity migration_request */
AND 
( 
  parent_comp.entity_id = field_xref.entity_id AND 
  field_xref.field_mig_parent_x_ref_target_id = node_xref.nid 
--IF=:TagName
  AND (node_xref.title LIKE ('%' :TagName '%'))
--END 
)
/* MIGDATE */
AND 
( 
  parent_comp.entity_id = migdate.entity_id 
--IF=:migdate
  AND migdate.field_mig_details_mig_date_value <=:migdate
--END    
)

/* Mainframe name on the parent node */
AND 
(
  mfname.entity_id = node_xref.nid
--IF=:mfname
  AND mfname.field_mig_mainframe_name_value LIKE ('%' :mfname '%')
--END    
)


  ) /*END WHERE CLAUSE */

GROUP BY  Components,  Environment
  
) query2 


WHERE 
(q1.Components = query2.Components 
 AND q1.Environment = query2.Environment
 AND q1.d = query2.maxdate)

ORDER BY 
  q1.Components

Comments

janeks’s picture

janeks CreditAttribution: janeks commented

Did you tried debug mode?

The first thing to use for such problems is to use Debug mode for your data source, available under:
http://yourserver/admin/config/content/forena/data/configure/
http://yourserver/admin/config/content/forena/data/configure/yourdatasource

It will output your queries/sql how they are prepared before sending them to DB. It could give you some idea what is happening or not.

Another tip - try to split your query for first time report to something more simple or at least to use database Views.

mxlav’s picture

mxlav CreditAttribution: mxlav commented

I'll have a look at the debugger, thanks didn't know it was there!

Note, when I run the SQLoutside of the outer block, using admin/structure/forena/data, the parameters are working.

metzlerd’s picture

metzlerd CreditAttribution: metzlerd commented

Can I suggest that you use lowercase parameters. That has caused some problems in the past. If you want to pass through parameters to the innner report that are not contained in the outer select statement you may need to use the frx:parameters attribute on the child block. This would allow you to specify specific values to the child report: 

<div frx:block="RM Applications/migrations" frx:parameters="mydate={parm.someotherdateparm}">

The other possiblity is the date filters you are applying are giving you trouble.... so I'd work the inner block until you get the SQL results correctly.

mxlav’s picture

mxlav CreditAttribution: mxlav commented

@metzlerd, thanks for taking the time to reply.

  • Inner block SQL has been tested on it's own and all parms are working
  • changed all parms to lower case, same results

Could you provide a little more explanation on the frx:parameters bit, I don't quite understand how to use this?

metzlerd’s picture

metzlerd CreditAttribution: metzlerd commented

By default forena will use the columns from the outside query as the parameters to the inside query. So it would expect the mydate parameter to be a column in the outside query. Sometimes the column names aren't the same as the parameter names, or you need to explicitly pass parameters into the block that aren't in the outer block. The frx:parameters attribute lets you override which parameters are being passed into the data block. The {parm.somotherdateparm} in the example I gave is a specially provided context so the you can reference the original parameter in the report.

Does that make some sense?

metzlerd’s picture

metzlerd CreditAttribution: metzlerd commented
Category: bug » support

Has this issue been solved to your satisfaction?

Pierre.Vriens’s picture

Pierre.Vriens CreditAttribution: Pierre.Vriens commented
Status: Active » Postponed (maintainer needs more info)

What an amazing use case for forena, taking into account the "This is my first attempt a making a Forena report, so I might be doing something wrong" ...

It would be nice to know the answer to David's latest comment, which is why I updated the issue status. If the issue is still relevant, maybe the available docu about report parameters in the 7.x-4.x reporting tutorials might help (also for the question asked in #4)?

It'd be nice also if anybody could provide some more details about the debug mode that Janeks mentioned in #1. I'm aware of that option within forena admin, but is there anything else to know about it, or to use it for, apart of what Janeks wrote? I'd be happy to rework such details into something added to the reporting tutorials. Would it make sense to suggest it as an extra topic in the #1944418: 7.x-4.x videos and documentation issue? Something like a report not producing the expected results, because of some SQL error that is not obvious to notice (or at least not for somebody who is not an SQL expert ... like Janeks or David ...), but for which the debug option comes in very handy?