1. Trevor Campbell
  2. Report Editor
  3. Thursday, November 23 2017, 11:47 PM
  4.  Subscribe via email
I'm having trouble mapping a subreport. I have included 2 screen shots below.

sub_report.jpg shows what I want to create. The yellow is the subreport based on the c_cutlist field.

sub_report1.jpg shows what I have done so far. It works fine.

I have created the subreport, which works ok on it's own. But when I add it to the main report and map the c_cutlist fields it doesn't work. Do I have to assign the c_cutlist field to a parameter and assign the parameter to the subreport query? If so I'm not sure how to do that.

Thanks in advance
Attachments (2)
Comment
There are no comments made yet.
Sergey Pashkov Accepted Answer
What kind of database is used? Where is it located - on the local network?

The query should be very fast.
Have you measured just execution time of this one query for some ordinary parameter?

The total number of inserted subreports varying, but it must be a big number for such time - 100?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 1
Trevor Campbell Accepted Answer
Sergey,

Thanks so much for mentioning about the materialized views. I didn't know there was such a thing. I looked it up modified my queries. The report now takes 7 secs. And that includes the refreshing of the view and running the report remotely.

This is epic. Once again thanks.:D :D :D :D
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 2
Sergey Pashkov Accepted Answer
Very good. Also, did you try to use materialized views? Although they require the manual refresh on the source data change as I understand.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 3
Trevor Campbell Accepted Answer
Hi Sergey,

Thanks for your reply.

Your answer gave me a clue to what might be happening.

So I did a quick test by creating the 2 tables with the data I gave you and I got the same result. 21 Pages in 1-2 secs.

The report is sourced from 2 views I have created with some calculations in it. So I think the hold up must be postgresql doing the calculations. In Access I have got Access queries doing the calculations. But with this report I have Postgresql doing the calculations. This usually speeds things up, not slowing it down.

After more testing I found that the view that created the data for the subreport was the problem. So I made a few tweaks to the view and got it down to under 2.5 mins working remotely. Which is acceptable for this report. When using it on the local network this will be more than halved.:D :D :D

Once again, Thanks.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 4
Sergey Pashkov Accepted Answer
Hi Trevor,

Thanks for the samples, but I'm still unable to reproduce the problem.

I created a database 'live_simple', loaded CSVs into two tables, changed WHERE part of the report queries, 19 pages generated in less than a second.

Attached project and SQL dump.

1. What OS are you using for reports? Windows? And OS where the server is installed

2. Could you try to enable the PostgreSQL logging in postgresql.conf
https://stackoverflow.com/questions/722221/how-to-log-postgresql-queries

That worked for me

#log_directory = 'pg_log' to log_directory = 'pg_log'
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' to log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
#log_statement = 'none' to log_statement = 'all'
#logging_collector = off to logging_collector = on
sudo /etc/init.d/postgresql restart or sudo service postgresql restart


Additionally enabled duration:
log_duration = on
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 5
Trevor Campbell Accepted Answer
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 6
Sergey Pashkov Accepted Answer
Hi Trevor,

Could you please give us the project and some data to reproduce this problem?

We’ll check in profiler where the time is lost.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 7
Trevor Campbell Accepted Answer
OK. I have done a lot of testing. I have got the report down to 9.5mins. However the exact same report in MS Access 2000 takes 30secs. This was done on the local network using a the same Postgresql database and the same data. When I run the queries individually they are fast.

I have done a lot of reports with Valentina Reports and they are all faster than MS Access 2000, until I add the subrport. This is my first report with a subreport.

Ideally, I would like to not have to use a subreport. But that would require having a 'Page Body - Header & Footer', which I have seen in other reporting tools. I'm not sure if that is an option for this to be added to Valentina Reports or not.

Thanks
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 8
Trevor Campbell Accepted Answer
I am using a postgresql database. Although it is running through a VPN I am comparing times both through a VPN. All my reports without subreports work extremely fast, 4x faster than ms access. I will try it on local network as well.
The total number of subreports would be about 60.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 9
Sergey Pashkov Accepted Answer
Hi Trevor,

Do you use parameters in the query and map_parameters property of the subreport?
You should input c_cutlist for the parameter used in the subreport query:
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 10
Trevor Campbell Accepted Answer
The problem seems to be when the subreport is added. If I take it out the subreport it only take about 10-15 secs for the whole report.

I am running almost the same data in another report, which doesn't have a subreport, and it only takes 40secs for the whole report. This report is in another post about 3 columns from about a week ago.

I have attached the query and the report.
Attachments (2)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 11
Sergey Pashkov Accepted Answer
Hi Trevor,

We have to find what is the reason for slowness.

Please show your subreport query.

How long does it take to execute just one query?
If you multiply this time by the number of subreports, does it match?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 12
Trevor Campbell Accepted Answer
The number of groups changes. And are different each time it's run. So the subreport needs to be based on the c_cutlist field in the main report.
It currently works but is too slow to use.
MS Access does the same report in 2mins. This report is taking 15-16mins. All other reports I've converted from access to Valentina work much quicker then Access by far. I was hoping this would do the same.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 13
Sergey Pashkov Accepted Answer
There should be one query per group, but I'll recheck.
How many groups do you have in the main report?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 14
Trevor Campbell Accepted Answer
I've got it to work. But it takes an insane amount of time. 15mins to run a report. Not sure if there is a way to speed it up.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 15
Trevor Campbell Accepted Answer
This didn't work. I have attached screenshots of how I created the subreport to see if I've done something wrong.
Attachments (4)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 16
Trevor Campbell Accepted Answer
Thanks for the response. I will try that but isn't that the same as mapping the the c_cutlist fields in each report?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Report Editor
  3. # 17
  • Page :
  • 1


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