Subreports in a Sage/Mas environment

Monday 13 January 2014 @ 10:51 pm

I was working with a customer today, troubleshooting a report reading MAS 90. The report had a subreport at the detail level and he said that the full report could take up to 24 hours to run. I tested the subreport and found that the selection formula was not being converted into a WHERE clause in the SQL of the subreport.  So every instance of the subreport (thousands) had to do a serial read of the entire table.  The subreport seemed pretty simple so my first approach was to recreate it and test it as a stand alone report.  It took only a few seconds to run and I could see the WHERE clause in the SQL.  But as soon as I inserted the new version to replace the existing subreport the SQL would no longer generate a WHERE clause.

So then I studied the relationship between the main and the subreport.  I found it was possible to eliminate the subreport and link the subreport data directly to the main report, as long as I didn’t add any filters to the new table.  This configuration would still require a serial read of the table, but it would only need to do one serial read, not thousands.  The report ran in a few minutes.

But later I started to wonder if anyone else had run into this quirk.  A little web sleuthing turned up a 2009 document listing MAS 500 Best Coding Practices.  And this little nugget was in point #9:

Avoid subreports, most cases the constraint can’t be passed as the where clause of the subreport query.

So I wasn’t imagining things. I don’t know if this ‘feature’ is still part of the current product.  But if you are a MAS user with slow subreports, you might want to check your WHERE clause. And if you need help finding an alternative configuration, give me a call.

Comments are closed.

Recrystallize Pro

Crystal Reports Server