1. jaume
  2. Valentina Reports ADK
  3. Monday, February 25 2019, 09:42 AM
  4.  Subscribe via email
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.

Thank you
Comment
There are no comments made yet.
jaume Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 1
Sergey Pashkov Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 2
jaume Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 3
Sergey Pashkov Accepted Answer
So you want to pass parameters to the subreport query.
The parameters you set for the main report are passed to the subreport query.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 4
jaume Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 5
Sergey Pashkov Accepted Answer
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
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 6
jaume Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 7
Sergey Pashkov Accepted Answer
$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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 8
jaume Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 9
Sergey Pashkov Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Reports ADK
  3. # 10
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories