Report with many queries

[bloggerman]

Have two queries on differeing topics

1) I have been asked to look into a report and advice means to improve it. The report has about 30 queries, joined, unioned to come to the required point. I don' think this is correct. The performance is bad too. My query is that in such scenarios does use of dimensional functions and cubes come as the first thought of a better way to report, removing the need to have multiple queries. This given its a summary report. I know there are many other aspects that need to be looked at before multi - dimensional approach can be recommended. I would request an opinion assuming the scope to be only as above, assuming nothing else goes against the cube approach.

2) Conditional Blocks vs Render variable: Blocks are normally used in cases where we need to hide and show objects. This can also be done using a render variable on the object. Any reason why conditional formatting is prefered.

[sir_jeroen]

in regards to point 2:
I don't think using conditional blocks vs Render variable make any difference.
Both are rendered as they are selected, Personally I prefere render variables because all objects are visible and you don't have to switch between views...

[jive]

HI,
For the first question: I don't know on what database you work, but it will be a good idea to get a view build on the database side or even a materialized view (for Oracle). If you can have one of them the select in the report will be short may be just 'select * from your_view'. This way the search of information will be done on the database side by the database and you can tweak the sql you used to get the most of it in term of performance. If it's stay too long you should check if aggregates table can be done on the database side. If you can't have view and just work with on the Cognos, As you say cube could be the solution, but you will have to generate the cube.

[Arsenal]

the use of blocks and render variables depends on your situation. For example, if you have just a few items to hide/show you could use a render variable. If you have an entire list to conditionally display with either 1000 rows for html or 99999 rows for Excel (real life scenario in a project of mine), you're not going to sit and make two lists and apply render variables to each column. In this case, you would use a block, have a copy of the list with appropriate row settings in each block and then set the block to render appropriately when the output is in one of the 3 Excel formats.

(In my case, I could also do it via 2 different report pages using a render variable ;D)