Tim - thanks for the quick reply. I agree that an 'execute any sql query' would be the most flexible way to go.

Q1: do you envisage an upgrade of the current Report Macro with the Expression being a SQL query? If so do we worry about someone doing an INSERT rather than a SELECT - or is this a buyer beware problem - after all only Administrators can create the macros.  Its not as though we have an sql injection problem.

Q2: I had a play with macro:eval(@test) where the @test macro just returned multiple lines of text.  I can happily set the odt document to set any font for the returned text.  Hence I suspect that your mention of 'fixed width font' relates to the problem of being unable to inject a tab character to separate the columns of information. I had a play with the translate function to translate the | characters into tabs, ie [translate(macro:eval('@test'),'|','')] - but that didn't work - it translated the |'s to &'s - ie it did not recognise the  as a tab character.

If my analysis is correct, then the 'must use fixed width font' problem would go away if there was a method of getting the macro expander to inject a tab character (eg using say \t or ^J or  or whatever. Then one could write the sql to be like SELECT date, '\t', prodname .... and swriter would do the columns for you.

I suspect this is going to get to 'Tim G pays for yet another feature' ;-)

Regards, Tim G 

PS - I woke up this morning realising three things:

a)  I had not touched on the problem of passing parameters to the SQL query - my feeling is that provided that $patient.id and $customer.id are supported so that say:

 select DATE_FORMAT(p2.activity_start_time,'%e/%m/%Y'), IFNULL(ed.value,e2.name) from participations p
left join entities e on p.entity_id = e.entity_id
left join participations p2 on p2.act_id = p.act_id
left join entities e2 on p2.entity_id = e2.entity_id
left join entity_relationships er on er.target_id = e2.entity_id
left join entities e3 on e3.entity_id = er.source_id
left join entity_details ed on ed.entity_id = e2.entity_id
where p.entity_id = {$patient.id}
and p.act_arch_short_name = 'act.patientMedication'
and p2.arch_short_name = 'participation.product'
and er.arch_short_name = 'entityRelationship.productTypeProduct'
and e3.name like 'Vacc%'
and ed.name = 'printedName'
order by p2.activity_start_time; 

will return the vaccination history for the current patient, then we are in good shape. [Note that I have no idea as to whether the above query is the most efficient way to get the vacc history, but it works.]

b) if we are to use the Report Macro setup, then we need a special (perhaps null) entry in the Report field to indicate that the Expression is a SQL query, and that parameters (ie things between {}) should be parsed for and replaced before executing the query

c) there needs to be a definition as to how data is returned from the query. In the above I am returning two fields, the date and the product name. It would be neat if these could be simply returned with a tab character between them. Alternatively, if we invent a mechanism for encoding a tab within macro text, then the rule could be that the SQL query returns a single string, ie one should adjust the above to something like

 select CONCAT(DATE_FORMAT(p2.activity_start_time,'%e/%m/%Y'),'\\t', IFNULL(ed.value,e2.name)) from ... 

Regards, Tim G