10 list objects in one report - performance

[cognosun]

We have a report studio report with 10 list objects in it.

Each list pointing to one query ( total 10 queries).

It's not a data specific report, just each list object shows 2 month sales data per each dimension.

It's taking 30 minutes to give output in excel. We have page-breaks on some list objects because of which there would be around 20 tabs in the final excel output.

Is there any fine tuning tips to reduce this 30 minutes time.

We've got conditional style applied for all the 10 list objects, this is the only formatting we did, and 3-4 calculations.

Any suggestions please.

Many Thanks in advance,
Joys

[wyconian]

Hi

I'm guessing that each query is filtered for 2 months worth of data.

Could you add a calculated item into a query which identifies the 2 month period your looking for then group everything by that in just one query?

e.g. have a calculated item something like

case when date bewteen date1 and date 2 then 1
when date between date 3 and date 4 then 2 etc

You could then just have one query instead of 10 and group (or section) everything by this new item.

I would imagine the issue is your hitting the database 10 times to get the data, it's probably hitting the same tables which may also be causing a lock or a wait.

Another option would be to have one query with all your data which feeds the 10 queries for your lists and have filters on the list queries.

[cognosun]

Unfortunately I can't do that as the requirement is like first 5 list containers would be on one excel tab ( i.e. 1 page)

From 6-10 list containers , each list has a seperate page break....i.e. 5 seperate page breaks.

If I merge them into a query there would be a problem.

[wyconian]

You can still get the querries coming out on different tabs.  If you add a pageset you can break the pages as you need to.  You would need to create another item to break the pages on and group the pageset by that item.

Good luck