Hello,
I've just created a report that contains a subreport

Report is launched
mRpt = prj.MakeNewReport( app.pl_Reportfilename, ds, app.pl_sql1)
mRpt.SetParameterValue( "param1", app.pl_Params(0) )

but I don't know how to set the subreport datasource using a parameter that is an sql expression.

Thank you

Hello,

Not clear, could you please add more details about your task?
Subreport uses the same datasource as the main report, so no way to change it.

Hello Sergey

There is an example of report datasource
select com_ivacompres.id as idx , com_ivacompres.*, sup_proveidors.nom from com_ivacompres, sup_proveidors
Where id_proveidor = sup_proveidors.id
And com_ivacompres.codi_proveidor between 1 and 1000
And com_ivacompres.data between "2019-01-01" and "2019-12-31"
order by data, id"

A the end of the report, I need to print some sumaries based on group of data previously printed:

select ivap, sum(base) as SumaBase, sum(ivai) As SumaIva, sum(total) As SumaTotal from com_ivacompres, sup_proveidors
Where id_proveidor = sup_proveidors.id
And com_ivacompres.codi_proveidor between 1 and 1000
And com_ivacompres.data between "2019-01-01" and "2019-12-31"
order by data, id"
group by ivap
order by ivap

I thought that subreport shoud be the the best wayt to achieve that.

Thank you

So you want to pass parameters to the subreport query.
The parameters you set for the main report are passed to the subreport query.

I did it.
Unfortunately without success.

This is subreport query
select ivap, sum(base) as SumaBase, sum(ivai) As SumaIva, sum(total) As SumaTotal
from com_ivacompres, sup_proveidors
Where id_proveidor = sup_proveidors.id
And com_ivacompres.codi_proveidor between $P(param5) and $P(param6)
And com_ivacompres.data between $P(param7) and $P(param8)
group by ivap
order by ivap

param5 and param6 are integer (1) AND (99999999)
param7 and param8 are date "2019-01-01" AND "2019-12-31"

Params are passed correctly because I tested them on the page header of subreport

I don't know the reason why the query doesn' return any row

howerver , if I replace the query parameters for fixed values then it works fine. I triyed to remove date parameters but it doesn't work.

What I am doing wrong ?

Thank you

You're right, the step of transferring parameters to subreport query is not working.
We'll fix it and release a new version in the nearest time (1-2 days)

If you need this report right now - as a workaround you can use the following approach:
add each parameter needed by the subreport to the SELECT list and give it an alias:

select com_ivacompres.id as idx , com_ivacompres.*, sup_proveidors.nom from com_ivacompres, sup_proveidors,
$P(param5) as param5value,
$P(param6) as param6value,
$P(param7) as param7value,
$P(param8) as param8value
Where id_proveidor = sup_proveidors.id
And com_ivacompres.codi_proveidor between $P(param5) and $P(param6)
And com_ivacompres.data between $P(param7) and $P(param8)
order by data, id


Then you can set map_parameters property of the subreport:
param5 = param5value
param6 = param6value
param7= param7value
param8 = param8value

Hello Sergey,

I'm sorry to bother you but using this approach I get this error
Datasource error: "Error( 1054 ) 42S22: "Unknown column 'param5value' in 'field list'"".
but it is in column list

select ivap, sum(base) as SumaBase, sum(ivai) As SumaIva, sum(total) As SumaTotal,
$P(param5) as param5value,
$P(param6) as param6value,
$P(param7) as param7value,
$P(param8) as param8value
from com_ivacompres, sup_proveidors
Where id_proveidor = sup_proveidors.id
And com_ivacompres.codi_proveidor between $P(param5) and $P(param6)
And com_ivacompres.data between $P(param7) and $P(param8)
group by ivap
order by ivap


and mapped

Thank you

$P(param5) as param5value, ... - it is for the query of the main report, not subreport. Sorry, maybe you've seen how I used a wrong query at first but then edited

Hello,

I'm sorry but it doesn't work.
As far as I understant main query should be:

select com_ivacompres.id as idx , com_ivacompres.*, sup_proveidors.nom ,
$P(param5) as param5value,
$P(param6) as param6value,
$P(param7) as param7value,
$P(param8) as param8value
from com_ivacompres, sup_proveidors
Where id_proveidor = sup_proveidors.id

and subreport should be

select ivap, sum(base) as SumaBase, sum(ivai) As SumaIva, sum(total) As SumaTotal
from com_ivacompres, sup_proveidors
Where id_proveidor = sup_proveidors.id
And com_ivacompres.codi_proveidor between param5value and param6value
And com_ivacompres.data between param7value and param8value
group by ivap
order by ivap

I tryied to change param?value to $P(param5) but neither work.

What's wrong ?

Thank you

Hello jaume,

In subreport query you write: between $P(param5) and $P(param6) ...
But did you set the map_parameters property for the subreport?
It connects subreport parameters with the main query values param5=param5value
(where param5 - is from subreport query, param5value - is from the main query)

Fix will be available in the version 9.0.7.