Another way to remove subreports from Crystal Reports – ListAgg

I always enjoy tuning reports for customers. I guess it’s the same satisfaction that people get from doing crosswords – a problem is presented (poor performance), you just settle down and work through the problem bit by bit.

First thing I do is to review the subreports. While a subreport can be an essential part of a report, once a subreport is being executed 100s of times, performance grinds to a halt. If you place a subreport in the detail section of a report and there are 1000 detail sections, there will be 1000 queries fired at your database. Not good!

Often, the subreport is just returning a single value. These are easy to remove using a SQL Expression. For example, if the subreport is displaying the last order date for a customer, you’d have a SQL Expression of:

(
select max([Order Date]) from orders
 where orders.[customer ID] = "Customer"."Customer ID"
) LastOrderDate

Where “Customer”.”Customer ID” is the field in the main report that contains the Customer’s ID. This is called a ‘Correlated Subquery’. An excellent description can be found on Wikipedia.

The limitation of this is that only a single value can be returned. In my case, I needed a list of items. The customer had a very definite performance target, so I had to look further.

Basically, I needed to take several rows of data and combine them into a single field. I first looked at having a single subreport and storing all the values in an array. Two problems – too complex and limited to 1000 values without adding even more complexity.

This is where I hit my next problem – I can do nothing to the database (view/SP) so everything has to be done in SQL Expressions. Then to further add to my woes, the database was Oracle; I’m much more familiar with MS SQL Server.

After a little bit of hunting around and a couple more false starts, I found a function called ListAgg. ListAgg takes a recordset and allows you to aggregate the values ‘over’ one of the columns, with an optional separator such as semi-colon or column. I needed both a ‘quality code’ and a ‘quality level’, for a specific ‘service requirement’, all as one list:

(
select  LISTAGG ( concat(concat(quality_code , ' : '), coalesce(quality_level,0)), '; ') 
WITHIN GROUP 
(ORDER BY concat(concat(quality_code , ' : '), coalesce(quality_level,0))) "QC"
 FROM SERVICE_REQ_QUALITIES srq
where  "SVC_SERVICE_REQUIREMENTS"."SERVICE_REQUIREMENT_ID"= srq.SERVICE_REQUIREMENT_ID 
) srqList

Which looks horribly complicated, but just returns: ‘Option1: 5; Option2: 6’ etc etc.

More importantly, it runs like the wind.

For those on SQL Server, there isn’t a direct equivalent, just various workarounds using FOR XML. Fortunately, it’s due in the next release as STRING_AGG

This is just one of many approaches taken to improve report performance. If your reports are too slow, you know where to find us.