Hi Marc,

Thanks for using Community Hub.

I reached out to our reporting team about this and they advised that it's not something they would normally support. They further advised that sub-reports can be very slow because of the way they handle data.

However, I'll leave this thread open in case anyone else in the Community has experience in this area as there are some Community members who are very experienced with report design.

Regards,

Andy
Sage UKI

It's feasible but not obvious/simple.

In your sub report you need to add a criteria for DEPARTMENT.NUMBER. Then back in the parent report select the sub report and open the criteria property. NOTE: This is the criteria property for the sub report and NOT the criteria property of the main report so selecting the sub report first is important, otherwise you will likely open the wrong dialog. In the criteria dialog you should see one criterion called DEPARTMENT_NUMBER. Select this and click Edit. In the expression editor enter the value CRITERIA.DEPARTMENT_NUMBER_FULL and click OK, and then OK again on the criteria dialog.

There is an overhead to a sub report but in this example it is unlikely to cause a significant performance issue provided the sub report is in the report header as that way it is only ever run once.

Hope that helps

Thank you for the suggestion.

I hadn't tried CRITERIA.DEPARTMENT_NUMBER_FULL previously as I assumed that it was a string.

Using that I am only getting the first department now (whether it is in the criteria range or not).

Gives the result

On the surface it looks OK from your screenshots. Hard to say what isn't quite right but I've shared up a simple example report here that works in the way I think that you are after with the sub report showing all of the departments covered by the criteria set on the main report.

Hope that helps

Thank you for the sample report. Even in your report I am still only seeing the Default department 0.

I have had limited success changing the sub report criteria to CRITERIA.DEPARTMENT_NUMBER_TO or CRITERIA.DEPARTMENT_NUMBER_FROM. These only work in very specific situations.

I tried creating a Switch Case expression (using a nested ?: )to set the criteria using the CRITERIA.DEPARTMENT_NUMBER_TYPE but that only partially worked and threw type conversion errors depending on which type was chosen from the drop down box.

The example report works OK for me I'm using the same criteria as you are and running it against the demo data:

What version of Accounts/Report Designer are you using?

Sage v29.2.398.0 update 1
Report Designer v5.0.347.0

I'm on v30 which has an updated version of report Designer in it. I'm not aware of a specific fix that has gone in around this sort of thing but I would recommend upgrading to the latest in any case.

Just had a quick check in v29 and it shows the same issue as you get. Upgrading to v30 should sort it out though as I can see the code that has been fixed that would be affecting this now.